Get Performance Tips Directly From SQL Server

  • ziangij

    SSCertifiable

    Points: 7184

    thanks for the article... it really helped me improving the performance of my query... 🙂

  • terry.bacon

    SSC Enthusiast

    Points: 105

    Cheers Ian - it's a very useful little SP.

    I am also finding it useful in detecting questionable queries - where the indexes for the tables have been planned out, but the sql server is raising issues of missing indexes, because the relevant query isn't written correctly to use indexes that are already in place.

    A case of looking at the missing index hint and saying to yourself, "Why do you think you need that?"

  • ianstirk

    Ten Centuries

    Points: 1310

    Hi Terry,

    I think you've raised an important issue here. Sometimes, when we have planned the indexes, rather than blindly impliment the 'missing' index, we should revisit our query instead e.g. maye we have an implicit conversion happening - or indeed our statistics may be old, which will influence the use of the index.

    Thanks

    Ian

  • Charan Dhavil

    Newbie

    Points: 7

    Is this answered? Please let me know how to fix it. Never used cross apply before

  • r.mitchell

    Old Hand

    Points: 368

    Nice utility. Just found some missing indexes on an cached plan that has ran 2 million times. It wasn't the table I thought was missing indexes and haven't touched it in almost 6 months. Good info to find out.

  • Lynn Pettis

    SSC Guru

    Points: 442334

    paul weegar (6/12/2009)


    Microsoft also has a FREE reporting tool called dashboard_performance that shows missing indexes, plus a lot more. It runs on SQL 2005 SP2 or above. Here is the link for those that are interested:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    What I would like to know is, how reliable/accurate are the estimations? I ask because I have a large table (2.5 million+ rows). One of the columns is called iscom (have no idea what it is used for, as this database was created from a vendors application) which SQL Server thinks should be indexed for every query. Only problem is the only value in this column is 0. Plus I see that SQL Server reports that it wants indexes included on all fields....

    For those who don't like to copy paste:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Viewing 6 posts - 31 through 36 (of 36 total)

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