Targeted Index Performance Improvements

  • Hi,

    Your results suggest, for the passed parameters, only certain indexes are being used. I'm sorry I can't give a more detailed answer, but I don't know the specifics of system you are using.

    It might be worthwhile running the routine with some other stored procedures.

    It might also be worthwhile running SQL profiler with your stored procedure, to determine what individual SQL statements within your stored procedure are being executed.



  • Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for sharing this, your article is well written and very easy to follow as is the output of the sproc. Any tool that can join the toolbox of a DBA is always welcome.

  • Just wanted to extend my thanks for this. I like what its doing but I think that for my purpose I'm running it over a week. I've stored some results for the intial select and i'll do the same select and run a comparison next week. This gives me more change to capture all activity across business critical databases.


  • If you with to do longer term analysis, save the results so that you can measure deltas. Also remember that restarting the instance resets the counters to 0.

    Buy the ticket, take the ride. -- Hunter S. Thompson

  • Very well written.

  • rja.carnegie (9/17/2009)

    I need to read this article carefully, but my immediate reaction is that the indexes that aren't used or don't even exist are the ones that should be considered for attention. And that's what the Tuning Wizard is for - that, and statistics. I will admit that I do not yet "get" statistics.

    I am often surprised when I design tables with carefully chosen indexes and SQL Server ignores them, whereas if I was still programming a database "row by agonising row" I myself would use the index strategy I designed for the query. Maybe it's the statistics. I look at the Execution Plan and I'm like, "Why aren't you using the nice index I made for you!" Incidentally, I'm single.

    This normally happens for one of two reasons.

    1. The index doesn't cover the query 100% and the amount of data being queried is large enough that the index won't help the query.

    2. You use non SARG-able statements in the where clause or the Join criteria. Things like DATEADD, DATEDIFF, LEFT, RIGHT, SUBSTRING, etc in the where clause cause SQL to start table or at best NC index scanning.

    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Hi Guys,

    Nice script but I would not advise putting this on a production box due to the SQL injection risk of the

    -- Execute passed SQL.

    EXEC (@SQLToRun)

    part of the proc.


    Every day is a school day, and don't trust anyone who tells you any different.

  • Include usage of indexes on views?

    Something like:

    Change: INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]

    To: INNER JOIN sys.objects st ON st.[object_id] = si.[object_id] AND st.type IN ('U','V')

    Your script will be of use to me. Thanks.

Viewing 9 posts - 16 through 23 (of 23 total)

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