SQL Server Missing Indexes Recommendation? YES/NO

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 [] ON [dbo].[Tasks] ([EmployeeID]) INCLUDE ([ID],[Name],[TaskDesc]);
 
CREATE NONCLUSTERED INDEX [] ON [dbo].[Tasks] ([EmployeeID]) INCLUDE ([ID],[Name]);
 
CREATE NONCLUSTERED INDEX [] ON [dbo].[Tasks] ([EmployeeID],[Name],[TaskDesc]) INCLUDE ([ID]);
 
CREATE NONCLUSTERED INDEX [] 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

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

guest
0 Comments
Inline Feedbacks
View all comments

About The Author

Search Articles

Categories

Follow Fard Solutions