Slow SQL Server Query? Here’s How to Build a Proper Covering Index

Data

Slow SQL Server Query? Here’s How to Build a Proper Covering Index

As a developer who interacts with SQL Server often, the queries I write for the data I need are often slow. Take the query below as an example:

Example query

This seems like a very simple query without much room for optimization. In fact, it does not take long to run. By using Query Analyzer in the Microsoft SQL Server Management Studio though, we can see the execution plan reveals a potential bottleneck:

A table scan.

A table scan

A table scan means that SQL Server will have to look at each row in the table to see if it should be returned for this query. This is slow. It’s very slow. It could not BE slower. In a case like this, we need an index on the table we are querying, and specifically, we need a COVERING index. 

A covering index is an index which contains all of the fields required for a particular query. Now, I won’t go into the pros and cons of creating indexes for every query in your application. That’s something that needs to be evaluated on a case-by-case basis, and quite possibly involves things beyond the normal developer’s control such as disk space. What I will go into is how to make a proper covering index for this query. 

You should be able to use it as a guide for your own tables and queries.

Step 1: Identify All Fields Used in the Query

This includes both the fields in the WHERE clause and the fields in the SELECT.

Identify Fields

Step 2: Create Your Index

Order the fields as follows: 

  • Fields in WHERE clause first, most distinct values to least distinct
  • Fields in SELECT statement

Note: The order of the fields does make a huge difference and should be done deliberately to increase the speed of this particular query.

Create Index

Step 3 – Test Your Query

Test your query and see what the execution plan says about the Estimated Subtree Cost. The original query was 0.365502. The same query ran with the addition of the covering index is 0.0032831. That’s a reduction of over 99%.

Test Your Query

Whenever you introduce a query, it is your responsibility to make sure that query behaves responsibly and does not have a negative impact beyond what is absolutely required. In this case, the query runs quickly enough without the covering index, but considering the table’s growth over time and the sheer number of queries in any application, removing 99% of the impact cannot be a bad thing. 

So, the next time you write a new query or are tasked with making a slow query run faster, consider using a covering index. Slow query? You’ve got that covered!

More About the Author

Alex Suarez

Data Architect
How to Automate a Tableau Data Source Refresh with Alteryx So, you’ve finally worked out your ETL process using Alteryx and have it exporting a shiny new TDE file. Now what? Using the Run ...
How to Automate a Tableau Data Source Refresh with DMExpress So, you’ve finally worked out your ETL process using Syncsort DMExpress and have it exporting a shiny new TDE file. Now what? Using the ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072