Missing Indexes in SQL Server 2005
-
Am I understanding differently or is it the fact that to have a view on the missing indexes we gotta run the query first ? If so would it be useful to simply run a query just to know the missing indexes. Please can somebody throw a light on this...
-
srawant (6/22/2010)
Am I understanding differently or is it the fact that to have a view on the missing indexes we gotta run the query first ? If so would it be useful to simply run a query just to know the missing indexes. Please can somebody throw a light on this...No, SQL 2005 (and later) keeps a recent history of performance statistics in memory, so missing indexes reported are those based on queries executed in the recent past or since last instance restart.
So you don't have to run a query first, it has already kept a log of recently run queries, exec plans, missing indexes etc.
__________________________________________________________________________________
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] -
Its noteworthy to know that DMV's are a reflection of historical query data since SQL Server started.
If you try running it right after restart, the results could be disastrous
-
Nitya (7/23/2010)
Its noteworthy to know that DMV's are a reflection of historical query data since SQL Server started.In this case it's since the database was opened. Taking the DB offline, autoclose, restoring, detach/attach will all clear the missing index DMVs (and several other DB-specific DMVs)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Memory pressure may also cause this information to be erased from the cache, so the time frame during which this info is maintained may be even shorter than the latest time database was started.
__________________________________________________________________________________
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] -
Great article. Short, easy to understand, and helpful. Great follow on discussion to review some of the finer points of indexes and when to create & modify. I have been using some of these DMVs for several months, they are great tools. But with greatness comes responsibility so understand what you are doing and exercise care before proceeding.
-
bob.taylor (9/16/2008)
And in SQL Server 2008, it gets even easier. When you display an actual execution plan in SQL Server Management Studio query windows, you will receive a message in green indicating the missing index information!boB Taylor, MCA: Database
i think it's only in the R2 version. helped solve a performance problem a few weeks ago. for some reason devs think their queries are some state secret and it's a PITA troubleshooting problems
-
As great as "missing indexes" is, it is good to be aware of some pitfalls of the feature:
http://msdn.microsoft.com/en-us/library/ms345485.aspx
__________________________________________________________________________________
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] -
Someone else deserves credit for this "missing indexes" query, but I'm not sure where I found it and whether I've modified it much over the years:
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [missing_index_' + 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 + ')', '') 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
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Reagan Boone
-
alen teplitsky (7/23/2010)
bob.taylor (9/16/2008)
And in SQL Server 2008, it gets even easier. When you display an actual execution plan in SQL Server Management Studio query windows, you will receive a message in green indicating the missing index information!boB Taylor, MCA: Database
i think it's only in the R2 version. helped solve a performance problem a few weeks ago. for some reason devs think their queries are some state secret and it's a PITA troubleshooting problems
SQL 2008 does it (management studio, it doesn't depend on the server version, just the version of the tools)
The information is in the XML plan in both 2005 and 2008, it's just that the 2008 management studio exposes it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
For both the Missing and Unused index tools it is good to remember that these are recommendations. The best tool is properly tuned squishware (that part of the system that exists between your ears).
How many programmers does it take to change a light bulb? None; It's a hardware problem.
How many therapists does it take to change a light bulb. None; The light bulb must change on its own. The therapist is only a guide.
Likewise the missing index tool is a guide.
ATBCharles Kincaid
-
ok
i remember i had the R2 and it popped up in green when i did the execution plan. boss had SQL 2008 SSMS and he said he never saw it. after that he installed the R2 version
-
It's there in 2008.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Would someone kindly elaborate on sys.dm_db_missing_index_group_stats
column user_scans. I tried all kinds of good and bad things in queries to bump user_scans in statistics and it doesnt budge. WHat will cause it to be non-zero?
-
Nice article - good read.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply