Post reply

Parameter sniffing and index rebuilds

  • May 12, 2011 at 2:37 am

    #250880

    Hi,

    I was wondering if someone could clarify something for me.

    I have a query that I beleive is having parameter sniffing issues, it's a bit of a jack of all trades procedure with lots of;

    (ColumnA = @ColumnA or @ColumnA IS NULL)

    AND

    (ColumnB = @ColumnB or @ColumnB IS NULL)

    I know the procedure is not ideal, but I'll save the discussion on that for another post.

    My issue is that when I come across performance issues and it slows the procedure down (30 seconds +), a simple rebuild of a couple of indexes causes the performance to be dramatically improved (1 second), the indexes I'm rebuilding are not fragmented and are (on the surface of it) correctly configured (fill factor etc).

    I was wondering if doing an index rebuild causes the plan cache to be refreshed for objects which depend on the index, i.e this procedure?

    Any help would be great.

    Thanks,

    Nic

  • May 12, 2011 at 4:27 am

    #1323558

    That's pretty much never going to perform well.

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    An index rebuild invalidates all plans that used those indexes, necessitating a recompile next time the queries run. An Update Statistics on that index would cause the same effect, as would sp_recompile of that procedure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 12, 2011 at 5:36 am

    #1323590

    Thanks for the reply Gail.

    Great article, and thanks for clearing up the question on the index rebuilds effect on the plan cache.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply