SQL SERVER – Measure Index Performance

My client of Comprehensive Database Performance Health Check, reached out to me asking if there is a way to measure the performance of the newly created index. Let us learn today how to measure index performance.

SQL SERVER - Measure Index Performance indexperformance-800x453

Well, there are many ways to measure the performance of an index, however, I measure the performance of the index with the help of the following command.

SET STATISTICS IO, TIME ON

I usually run this command before I create the index and measure the IO and time for the query. Right after I create the index, I run the same query again with the command mentioned above and check the IO and TIME.

If the IO and Time are lesser than before, I think the index has done its task. In any case, I usually do not create more than 4-5 indexes per table.

Here are the videos I suggest you watch to understand how STATISTICS IO and TIME works.


Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series. I hope you appreciate learning about the Last 5 SQL in Sixty Seconds Video.

If you have any questions, you can always reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, , ,
Previous Post
SQL SERVER – List of Trace Events
Next Post
SQL SERVER 2022 Features for Performance Optimization

Related Posts

Leave a Reply

Menu