Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Missing Indexes in SQL Server 2005 Expand / Collapse
Author
Message
Posted Tuesday, June 22, 2010 2:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 11:47 AM
Points: 317, Visits: 517
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...
Post #941363
Posted Tuesday, June 22, 2010 3:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:13 AM
Points: 1,848, Visits: 3,570
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #941373
Posted Friday, July 23, 2010 12:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 389, Visits: 153
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
Post #957785
Posted Friday, July 23, 2010 2:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 41,495, Visits: 34,416
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #957814
Posted Friday, July 23, 2010 7:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:13 AM
Points: 1,848, Visits: 3,570
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.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #957957
Posted Friday, July 23, 2010 7:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:16 PM
Points: 2,812, Visits: 2,543
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.
Post #957961
Posted Friday, July 23, 2010 11:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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


https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #958145
Posted Friday, July 23, 2010 12:30 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 6:13 AM
Points: 1,848, Visits: 3,570
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


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #958168
Posted Friday, July 23, 2010 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 23, 2010 12:50 PM
Points: 1, Visits: 15
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
Post #958186
Posted Friday, July 23, 2010 1:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 41,495, Visits: 34,416
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #958212
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse