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 |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2014-12-29 : 13:25:27
|
| I use a script to find missing indexes.It is saying I need an index on the "Users" table with ID for the index and including 95 columns. There is already a primary key on the table (ID) that includes the 95 columns (plus one more that isn't needed in the recommended index).It isn't clear to me why it wants this index since the primary key (clustered index) should already cover it. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-29 : 13:35:57
|
| Either the script has a bug or SQL is just recommending too much. This is why as DBAs we have to look at the report with caution. We have to check our existing indexes to see if it's recommending an exact or near duplicate and also test if SQL has the ordering of the columns correctly. I attended a session at PASS in November where Kendra Little showed that the indexes that SQL recommends are often in an order that doesn't give us the optimal performance boost. Through testing, you figure out if you need to adjust the index or not add it at all.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-12-29 : 14:34:03
|
| Hi Tara,I believe I figured out the problem. Because the developers are using a hybrid GUID/rowid primary key technique in our new database the primary key (GUID) is non-clustered and there is another clustered index on the rowid (not primary key). The non-clustered primary key does not "include" any columns which is why SQL is asking me to create another one that does.I'll ask the developers if I can modify the non-clustered primary keys on all the tables to include all the columns. That should solve the problem.UPDATE: I'm guessing with a clustered primary key you don't need to "include" columns because SQL knows this already based on the base table. But because it is non-clustered SQL needs to reorder everything which means you need to include the necessary columns since they aren't stored that way. If I understand correctly. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-29 : 14:58:41
|
| You can't INCLUDE columns in a clustered index, it happens automatically and is part of the sort.And yes good catch on why SQL is recommending it. Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Asking for an index I don't think it needs