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 ««12

When to Index Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 6:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 336, Visits: 596
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
SQLRNNR (1/7/2013)
dwilliscp (1/7/2013)
GSquared (1/7/2013)
"When to index" isn't a question of the query. Not really. It's a question of what you're trying to do.

There's no way to tell, from what you wrote, wether you should index something or not.

Start out with a book on performance tuning. Read online articles on it, too, but pick out a good book as a first step.


My readings are:

A Guide for the Accidental DBA, Mastering Server Profiler, Performance Tuning DMV, SQL Server Tacklebox.

Any suggestions?


I'd start with these
A Guide for the Accidental DBA
Performance Tuning DMV


Those are the books that I have already read, in the past year. They are helpful, but do not cover the details of when to create an index. (I believe the index script came out of the accidental DBA book.)


Besides the link that Gail just provided, you can use that script as a starting point. But do not rely solely on the output of that script. For scripts similar to that, I don't even start to look at the proposed index unless I know it is related to a poorly performing piece of the application. Or, I will loosely regard the results starting at an impact level of 1000 or greater.

You should be careful with the column order in the provided script as well. Investigate the Execution Plan that is associated to the missing index and ensure the column order is correct.


Thanks for the help you and Gail provided. I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.
Post #1404754
Posted Wednesday, January 9, 2013 7:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 336, Visits: 596
Oh, one other side note. (this has not happend yet, since there were almost no indexes) But I like to only have about 3 Non-Cluster indexes on tables, unless they are for reporting.

Since our reporting tables only get written to once per day, the overhead of having more indexes is offset by the bonus of not doing table scans... then again the most indexes I have added to any of these tables, so far, has been 4.
Post #1404757
Posted Wednesday, January 9, 2013 7:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
dwilliscp (1/9/2013)
Oh, one other side note. (this has not happend yet, since there were almost no indexes) But I like to only have about 3 Non-Cluster indexes on tables, unless they are for reporting.


Have you tested the impact of more than three indexes? Is it unacceptable?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1404760
Posted Wednesday, January 9, 2013 7:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
dwilliscp (1/9/2013)
I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.


Don't start with missing index stats. That's cart before horse. Start with queries that have poor performance. You're indexing to improve query performance, no indexing to remove entries from a list of index suggestions.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1404767
Posted Wednesday, January 9, 2013 11:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:55 AM
Points: 336, Visits: 596
GilaMonster (1/9/2013)
dwilliscp (1/9/2013)
I was trying to find a better approach than what I am currently doing... that is starting with the query, pulling the top 10 and looking at what tables they are using (to see if there are any indexes on them, and if they are similuar). Then I look at the execution and see if they are spending a lot of time doing table scans (well it is a percent, but you get the idea). I guess the one change is to work my way down to do all those with 1000 ranking or higher.


Don't start with missing index stats. That's cart before horse. Start with queries that have poor performance. You're indexing to improve query performance, no indexing to remove entries from a list of index suggestions.


Ok must be missing something... the only other way I know.. of keeping things tuned.. is the trace that I have running with every reboot. It traps anything with SET @DurationFilter > 180000000. (3 min run time) If this is what you are talking about.. then why would you ever run the query looking for missing index?
Post #1404910
Posted Wednesday, January 9, 2013 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Missing index DMV = suggestions, not directives. If you're looking at a long-running query, you can use that to see what indexes SQL thinks is missing on the relevant tables. Just creating indexes from it blindly will likely give you far too many, far too wide indexes.

p.s. A 3 minute filter's too long. What about the 10 second query that runs 120 times a minute? Is that acceptable, but the 180 second query that runs once an hour not? Is 2 minutes 50 seconds OK and 3 minutes 10 too long? Default application timeout is 30 second.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1404915
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse