There are two things I always remember when I am working on a Comprehensive Database Performance Health Check. 1) I can always learn new stuff 2) I can be wrong with findings. This is the reason, I am very much alert when I am working with clients on helping them tune their SQL Server Performance. Let us learn today how we can use SET STATISTICS IO ON to check the query’s performance.
I often see that lots of people depend on SQL Server execution plans to compare the performance of the query. While that may be accurate many times, there have been moments when this can give you not the intended answers. It is always a good idea to always learn one method which works relatively dependable across different environments.
In this quick video, we will see that even though the SQL Server Execution plan shows a query which is expensive, may not necessary it is expensive. We should depend on the statistics IO to decide if the query is doing more read or less.
SET STATISTICS IO ON
Please note that the execution plan of SSMS has a different priority and also takes a few additional important aspects when it calculates the cost along with the query. So it would not be fair to see execution plan lies but I will rather say that when you see the query and want to measure its performance, it is always a good idea to check statistics IO along with the execution plan to get a clear idea about the performance of the query.
If you like what you see, please do not forget to subscribe to my youtube channel.
Reference: Pinal Dave (https://blog.sqlauthority.com)
