SQL Server Missing Indexes Recommendation? YES/NO

The estimated reading time for this post is 2 minutes

Base on our experience from tuning SQL Server databases for companies , Most of the times we have seen tables are having not less than 8 Non-Clustered Indexes and for sure one Clustered Index on column called ID. While we chat with the DBAs or Developers, we realize that they are either using Database Tuning Advisor (DTA) or they follow Cardinality Estimator recommendations regarding missing indexes. in this post we would like to explain in simple words that why you should not take SQL Server missing index recommendations seriously.

For example, we have a table (Without any index) called dbo.Tasks which has few thousands records and our end users are executing below queries:

SELECT TOP 2 ID,EmployeeID,Name,TaskDesc FROM dbo.Tasks WHERE EmployeeID = 2;
 
SELECT TOP 2 ID,EmployeeID,Name FROM dbo.Tasks WHERE EmployeeID = 2;
 
SELECT TOP 2 ID,EmployeeID,Name FROM dbo.Tasks WHERE Name Like 'D%' AND EmployeeID = 2 AND TaskDesc Like 'D%';
 
SELECT TOP 2 ID,EmployeeID,Name FROM dbo.Tasks WHERE Name Like 'D%' AND TaskDesc Like 'D%';

 

What we get as result in SQL Server are missing index recommendations such as below figure.

missing

CREATE NONCLUSTERED INDEX [<Name OF Missing INDEX, sysname,>] ON [dbo].[Tasks] ([EmployeeID]) INCLUDE ([ID],[Name],[TaskDesc]);
 
CREATE NONCLUSTERED INDEX [<Name OF Missing INDEX, sysname,>] ON [dbo].[Tasks] ([EmployeeID]) INCLUDE ([ID],[Name]);
 
CREATE NONCLUSTERED INDEX [<Name OF Missing INDEX, sysname,>] ON [dbo].[Tasks] ([EmployeeID],[Name],[TaskDesc]) INCLUDE ([ID]);
 
CREATE NONCLUSTERED INDEX [<Name OF Missing INDEX, sysname,>] ON [dbo].[Tasks] ([Name],[TaskDesc]) INCLUDE ([ID],[EmployeeID]);

 

Keep in mind, every above missing indexes are recommended for only that specific query, which means Cardinality Estimator does not check other queries and it does not evaluate the recommended index against other queries.

For above missing indexes, we can solve the performance issue by creating a clustered index on (EmployeeID and ID) and a Non-Clustered Index on (Name and TaskDesc) columns on the dbo.Tasks table to support all those queries.

Be aware that if there are more Non-Clustered Index on a table, there are more I/O overhead on the specific database due to SQL Server internal operations. Always try to have less than 5 indexes including silver bullet index (Clustered Index) in every table to boost up performance, but make sure that those indexes covers almost all of the queries.

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz