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

Solution 1

In SQL Management Studio, just type in the query, and hit Control-L (display query execution plan). There, you will be able to see whether any indexes are being used. A "table scan" means the index is not used. An "index scan" means the index is used.

Solution 2

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.

Comments

[eidgenossen]

I can't support this answer enough. It's a pet hate as a db admin to find sql code that was developed (including examining the execution plans) with insufficient data, so the optimiser selects an entirely different plan in the context of production data. Test can weed this out, but its much more efficient all around to demand a Dev rig with representative amounts of data.

[Ingenioushax]

Even though this post is over 4 years old now, I learned a lot from it that I didn't know before. Found it very informational.