Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Get Performance Tips Directly From SQL Server Expand / Collapse
Author
Message
Posted Monday, April 5, 2010 4:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,104, Visits: 371
thanks for the article... it really helped me improving the performance of my query...
Post #896602
Posted Friday, April 9, 2010 3:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:57 AM
Points: 47, Visits: 86
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?"


Post #900282
Posted Friday, April 9, 2010 6:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:07 PM
Points: 49, Visits: 849
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
Post #900411
Posted Sunday, May 9, 2010 11:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 10, 2010 8:24 PM
Points: 1, Visits: 16
Is this answered? Please let me know how to fix it. Never used cross apply before
Post #918668
Posted Friday, June 22, 2012 12:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:04 PM
Points: 102, Visits: 96
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.
Post #1320149
Posted Friday, June 22, 2012 12:32 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
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



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1320152
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse