One of the two SQL Server Speakers of Professional Association for SQL Server in SQLPASS Lahore General Conference. (http://lahore.sqlpass.org/Home.aspx?EventID=5615)
9+ Years of Database Development & Administration experience with Medical Billing, Start up & Financial Companies
Microsoft® Certified Solutions Expert: Data Management and Analytics (SQL Server 2012/2014)
Microsoft® Certified Solutions Expert: Data Platform (SQL Server 2012/2014)
Tuesday, May 05, 2009
Identify missing indexes in sql server 2005
SELECT DISTINCT DB_NAME(Database_ID) [Database] ,OBJECT_NAME(Object_ID) [Table] ,Equality_Columns ,Included_Columns FROM sys.dm_db_missing_index_details mid WHERE Database_ID = DB_ID() ORDER BY 2
At times you create indexes for columns but they are never used because the SQL Server Query Optimizer does recognizes them as suitable indexes and instead it uses the Primary key to scan the index to fetch the records. So when index or table scans are performed the SQL Server tracks them and stores those queries and table information in a table. And suggests you to create some indexes. So you can view those suggestions using the given view.
2 comments:
can you explain more.
At times you create indexes for columns but they are never used because the SQL Server Query Optimizer does recognizes them as suitable indexes and instead it uses the Primary key to scan the index to fetch the records.
So when index or table scans are performed the SQL Server tracks them and stores those queries and table information in a table. And suggests you to create some indexes.
So you can view those suggestions using the given view.
Post a Comment