How to check a query using an index

Question

I am using SQL Server and I do not have a sufficient dataset to test query performance.

I want to analyze the query and see if the index is used or not. How can I check that

Answer 1

In SQL Management Studio, simply enter a query and press Control-L (query mapping execution plan). There you can see if any indexes are used. "Table scan" means that the index is not used. “Index scan” means that the index is being used.

Answer 2

This is actually more complicated than it sounds - a typical modern DBMS (and MS SQL Server is no exception) sometimes performs a full table scan, even if the index is available, simply because it can be faster with small amounts of data. ¹

Example:

• The last statement in this SQL Fiddle example can be satisfied with the index, but the table scan clearly indicates the table scan:

  

• But if we insert some data , the index is used:

  

What else, the decision that the DBMS makes may depend on how fresh / accurate the statistics are .

The most reliable test for using the index is to simply populate your database with representative amounts of data , and then see the execution plan . And do not forget the time of the actual execution of the request while you are on it!

---

¹ Simplified example: if the whole table fits into one page of the database, it’s faster to just load this page and go through it sequentially than wait for additional I / O for the page with the index.