July 28, 2015 at 4:46 am
I used following query to identify missing indexes:
SELECT mid.statement , mid.included_columns, mid.equality_columns, mid.inequality_columns,
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [NCIX_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') + ' GO' AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
and mid.database_id = 10
order by mid.statement
its showing lost of indexes to be created on same table with similar columns.
CREATE INDEX [NCIX_21974_21973_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([RoleId], [MenuId], [isActive]) GO
CREATE INDEX [NCIX_21960_21959_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([RoleId], [isActive]) INCLUDE ([MenuId]) GO
CREATE INDEX [NCIX_21962_21961_TL_SRV_Role_Menu] ON [TL_SRV_Role_Menu] ([isActive]) INCLUDE ([RoleId], [MenuId]) GO
I think I need to only create few if an index is covering all columns then I do not need to create more indexes for separate columns or should I create separate index as suggested?
Similarly, there is one another suggestion with following case:
CREATE INDEX [NCIX_20187_20186_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([SerialNo],[StationaryStatus]) GO
CREATE INDEX [NCIX_20189_20188_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([SerialNo]) GO
CREATE INDEX [NCIX_14943_14942_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([StationaryStockDetailId], [SerialNo]) GO
CREATE INDEX [NCIX_14946_14945_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStockDetailId], [StationaryStatus]) INCLUDE ([SerialNo]) GO
What would be best suggestion, should I create all indexes above or use minimum number of indexes which covers all columns as mentioned in above create index statements?
Shamshad Ali
July 28, 2015 at 5:32 am
The missing index DMV is a place to start looking at indexes, not the final answer. You need to take the recommendations, evaluate them, test each one and see what effect it has, and then only implement the indexes which are useful.
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
July 28, 2015 at 10:43 am
And remember, the order of the keys on the indexes matters quite a bit, especially for the first column since that determines what is in the histogram of the statistics. This means in a case where you have ID1 & ID2 in one index, ID2 & ID1 in a different index may work better for some queries, differently than the first index. As Gail says, testing is the only way to be sure.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply