November 13, 2014 at 5:41 am
Hi
I have just run a query to get missing indexes from the plan cache. From the results I am advised to create a Non clustered index on table a as such
USE [DatabaseA]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Problem_table] ([COLUMN_A])
INCLUDE ([COL_B],[COL_C],[COL_D],[COL_E],[COL_F])
GO
When i look at that table there is already an index with ALL the fields but it is just created as a wide index - plus there are some columns in there that are extra to what the above query is telling me.
What is the difference between the wide index already on the table, and if I where to alter the index by moving the fields from the key into 'included columns'
November 13, 2014 at 6:38 am
The wide index is less efficient in terms of storage, but in terms of covering, it'll be the same. Either an index has all the columns needed to satisfy a query using the key columns, the clustered key columns and any INCLUDE columns, or it doesn't. Any combination of key columns and INCLUDE columns will make an index covering.
The issue with using the missing index DMVs is that they don't show you which query the suggestion is for, so I tend to ignore them. Instead, if I want to know index suggestions based on a given query, I use the plans in cache and query for missing index information there. I have a basic blog post on it here[/url] (the query in the blog post is adequate, but not sophisticated) with links to other posts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 7:37 am
PearlJammer1 (11/13/2014)
What is the difference between the wide index already on the table, and if I where to alter the index by moving the fields from the key into 'included columns'
To add to what Grant has said. The difference is that the Key Columns are seek-able so if all the key columns are used, for instance, in a where clause you can do a direct seek at the root and intermediate levels without necessarily having to go all the way to the leaf level, where if some of the columns are included columns you have to go to the leaf level and then apply a filter operator.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply