how to check the query is using index

Question

I am using SQL Server and I don't have enough dataset to test the performance of the query.

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

Answer 1

This is actually harder than it looks - a typical modern DBMS (and MS SQL Server is no exception) will sometimes do a full table scan even if index is available, simply because that might be faster on small amounts of data.¹

Example:

• The last statement in this SQL Fiddle example can be satisfied by the index, yet the query plan clearly indicated a table scan:

  

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

  

What's more, the decision the DBMS makes can be influenced by how fresh/accurate the statistics are.

The most reliable test for whether the index is used is to simply fill your database with representative amounts of data and then look at the execution plan. And don't forget to time the actual execution of the query, while you're at it!

---

¹ A simplified example: if the whole table fits into a single database page, it is faster to simply load that page and go through it sequentially, than to wait on the additional I/O for the page containing index.