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:

[codesyntax lang=”tsql”]

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%';

[/codesyntax]

 

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

missing

[codesyntax lang=”tsql”]

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]);

[/codesyntax]

 

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

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan