Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


When to Index


When to Index

Author
Message
dwilliscp
dwilliscp
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 767
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.
dwilliscp
dwilliscp
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 767
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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, MVP, M.Sc (Comp Sci)
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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, MVP, M.Sc (Comp Sci)
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


dwilliscp
dwilliscp
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 767
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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, MVP, M.Sc (Comp Sci)
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search