Targeted Index Performance Improvements

  • ianstirk

    Ten Centuries

    Points: 1310

    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

    Ian

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the article.

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

  • Squig

    Old Hand

    Points: 373

    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.

  • mark.pointon

    SSC Enthusiast

    Points: 190

    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.

    Thanks

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4281

    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

  • Neha05

    Default port

    Points: 1494

    Very well written.

  • mtassin

    SSC-Insane

    Points: 23099

    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]

  • Ness

    SSCarpal Tunnel

    Points: 4282

    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.

    :w00t:

    SQL DBA
    Every day is a school day, and don't trust anyone who tells you any different.
    http://sqlblogness.blogspot.co.uk

  • Eddie Kramer

    SSC Rookie

    Points: 45

    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 24 (of 24 total)

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