Creating Missing Indexes

Beginning in SQL 2005 we have the ability to identify indexes that would have been used if they existed. That doesn't mean we should always create them of course, but often they are useful. Watch Greg Larson show you how to do it!

Greg Larsen is a Lead DBA at Washington State Department of Health (DOH). Greg has been working with SQL Server since 1999. He has authored over 100 articles related to SQL Server. He holds a MCITP Database Administrator and Developer certification for SQL Server 2005 and is a SQL Server MVP. He also does part-time SQL Server consulting. He hosts the www.sqlserverexamples.com website which cont...

aet on 7/1/2009

Bad Qaulity of Video

Lance on 7/27/2009

Too short

Travis on 7/30/2009

Good to know Greg! How about including the code too?

gk on 8/3/2009

VIdeo is not clear ...

yousef on 8/3/2009

congratulation on your MVP.I like every articles on databasejournal with sign of G.Larsen

WChaster on 8/18/2009

hard to see the picture.

Peter Schofield on 9/14/2009

Voice a bit quiet

3EAA01C6D4 on 9/16/2009

Well done, this is sound and valueable video.

sunil on 10/14/2009

nice

sunil on 10/22/2009

very good can i get the code?

Keiran Grogan on 10/30/2009

Excellent content, shame about video quality

Barbara on 12/30/2009

The resolution of the video is too low. cannot see...

avijit on 2/11/2010

picture quality is too bad...its very difficult to view. session is very good but somewhere its not complted

on 4/27/2010

Either the speaker is out of energy or the quality of the audio is poor. The quality of the "content" was good though.

Ravi Raj on 6/27/2010

good one

nilesh bilimoria on 11/16/2010

nice to know . It would be great to have the code to create missing indexes

Greg Larsen on 11/17/2010

Here is the script for this video: use tempdb; go -- Create table set nocount on IF EXISTS(SELECT * FROM sys.objects WHERE name = 'Customer') DROP TABLE Customer; CREATE TABLE [Customer]( [CustomerID] [int] NOT NULL, [TerritoryID] [int] NULL, [AccountNumber] [nvarchar] (10), [CustomerType] [nchar](1) NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL ) ON [PRIMARY] -- populate with data INSERT Customer SELECT * FROM AdventureWorks.Sales.Customer -- Add indexes CREATE CLUSTERED INDEX CustomerID_pk on dbo.Customer(CustomerID); CREATE NONCLUSTERED INDEX CustomerType_ID on dbo.Customer(CustomerType); -- Return Some Data tat is no indexed SELECT AccountNumber, ModifiedDate FROM Customer where AccountNumber > 'AW0O021500'; SELECT TerritoryID, AccountNumber, ModifiedDate FROM Customer WHERE TerritoryID = 1; SELECT AccountNumber FROM Customer WHERE AccountNumber = 'AW00021500'; SELECT AccountNumber FROM Customer WHERE AccountNumber = 'AW00021502'; -- Combined missing index info SELECT * FROM sys.dm_db_missing_index_groups as g JOIN sys.dm_db_missing_index_group_stats as gs on gs.group_handle = g.index_group_handle JOIN sys.dm_db_missing_index_details d on g.index_handle = d.index_handle; -- Build Create Index statements select DB_Name (d.database_id) DBName, d.statement as [ObjectName], gs.unique_compiles, gs.user_seeks, gs.user_scans, gs.avg_total_user_cost, gs.avg_user_impact, 'CREATE INDEX MissingIndex_' + rtrim(cast(d.index_handle as char(100))) + ' ON ' + d.statement + ' (' + case when equality_columns is not null then equality_columns else '' end + case when equality_columns is not null and inequality_columns is not null then ', ' else '' end + case when inequality_columns is not null then inequality_columns else '' end + ') ' + case when included_columns is not null then 'INCLUDE (' + included_columns + ')' else '' end MissingIndex from sys.dm_db_missing_index_groups g join sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle join sys.dm_db_missing_index_details d on g.index_handle = d.index_handle

Edgar Machado on 12/5/2012

Too short and would like a bit more detail in the process.