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.
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)
