September 15, 2011 at 8:53 am
I know this could be controversial. Many of us feel that design and index recommendations generated from SQL Server are less than adequate. Perhaps it's my idealism, but I figure that [with a few adjustments] the index recommendations from the DMV's should be solid. Consider Ian Stirk's query:
SELECT TOP 10
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact -- Query cost would reduce by this amount, on average.
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;
This is a great query that lays out what should be added. I get a lot of recommendations for multiple indexes where one should do, so I consolidate them.
Here is the problem: I trust the process will reveal [even if I don't get it right on the first pass] better performance overall and at least lead one down the road to a much superior indexing scheme for any database.
Thoughts? [Please try to keep it specific and empirical.]
September 15, 2011 at 8:54 am
September 15, 2011 at 9:01 am
The index recommendations are a great place to start but a terrible place to finish.
The optimiser based them on a single query at a time, it does not consider a total workload. It won't recommend clustered indexes, filtered indexes, unique indexes or partitioned indexes. It will often recommend indexes that are very similar to existing ones.
Use it to get an idea what's missing, compare the results against current indexes and see if an index can be widened rather than a new one created. Consider whether the recommendation is sensible, the missing index DMV is very fond of suggesting hugely wide indexes that may not be a good idea to create (due to the size, impact on DB size, inserts, etc)
Test any recommendations carefully before implementing. Don't just blindly create indexes without additional consideration.
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
September 15, 2011 at 11:25 am
Just to expand a bit on to the points already mentioned.
I never add indexes solely on what the above DMV query will return. That is not to say the query is not useful, it is very much so, but just as one piece of the overall puzzle of locating important missing indexes.
I usually locate missing indexes from cached exec plans, returned from queries of sys.dm_exec_cached_plans, or, for currently running requests, from passing the plan_handle of sys.dm_exec_requests to sys.dm_exec_query_plan.
I usually ignore indexes with Impact less than 85-90. Then I check in the exec plan which statements had a high relative cost.
Do any of the recommended indexes look like they might benefit any of the high-cost statements?
If an index comes, say, with Impact=99%, is a narrow index, confined to one or two columns, and I have been able to identify a statement that would benefit from such an index, then this is very likely a good candidate for a missing index.
I also correlate this with the results of other queries, such as the one in the opening post.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply