Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
homebrew
Posting Yak Master
114 Posts |
 Posted - 2009-12-16 : 21:16:16
|
| Maybe this is too general, but here goes. I'm looking at an older application written by previous employees, and they created all kinds of crazy indexes, many duplicating each other, or indexing columns that are not used in JOIN or WHERE or ORDER BY. I think they went crazy with the tuning advisor, so I want to clean them up.In general if I have fairly simple queries, joining a few tables, and Table_A is frequently joining other tables on Column_A, with a WHERE condition on Column_B, I assume it makes sense to have an index on Column_A, and another on Column_B. But is there a point to having 1 index on Column_A and Column_B together ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-12-17 : 07:25:03
|
| If there's a filter on Column B and a join on ColumnA then the best index there would be either (A, B) or (B,A) Test both and see which is used. It's not very useful to have 2 separate indexes. While SQL can do what's called an index intersection, it's very uncommon for it to do so. Far more often it will pick one of the indexes, use that, fetch the rest of the columns from the cluster/heap and then do the additional filters--Gail ShawSQL Server MVP |
 |
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2009-12-17 : 09:04:59
|
| Thanks for the replies and that link. |
|
|
|
jagoodie
Starting Member
4 Posts |
Posted - 2009-12-17 : 13:37:29
|
| Thanks for the post tkizer, great link. Is their any reason to not drop any index that has a less than say 5% reads per write? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-17 : 19:57:47
|
| I caused massive performance degradation a few months ago when I dropped an index that had very low reads. The process that used the index apparently only runs once a week, but when it does run it needs that index otherwise it's going to scan. Unfortunately it scanned a table with a few hundred million rows which caused CPU utilization to be constant over 90%. This caused all queries to slow down on this mission critical system. We have a performance requirement that queries should take no more than 300 milliseconds, so this was hugely noticed by our customers.Due to this, I never will drop any indexes unless the reads are zero and also the SQL Server needs to remain up for a while in order for that data to be good. Had I restarted SQL Server the day after the weekly process ran and then ran the DMV report, it would have said zero for reads which is not true since it's a weekly process.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
homebrew
Posting Yak Master
114 Posts |
Posted - 2009-12-18 : 09:53:34
|
| And make sure to script out the indexes before dropping them so you can easily recreate them if needed. |
|
|
|
jagoodie
Starting Member
4 Posts |
Posted - 2009-12-18 : 11:17:24
|
| Both excellent points. Thank you!!! |
|
|
|
|
|
|
|
|
Indexing Rule of Thumb ?