Recently, while working with a large education institute on Comprehensive Database Performance Health Check I was asked a very interesting question about statistics TIME and IO for SQL Server Management Studio, let us discuss that in today’s blog post.
If you have ever got your server slower and reached out to me for consulting help, I am confident that you know that I am not a huge fan of SQL Server Execution Plan, I personally prefer to use STATISTICS IO and TIME before I look at anything else while tuning the query. The matter of fact is that I prefer to look at the query execution plan as the last resource in tuning the queries. You can easily include the statistics IO and TIME for your queries in SSMS by specifying the following syntax.
SET STATISTICS TIME, IO ON
While working together, the client noticed that every time when I run the query it automatically includes the STATISTICS TIME and IO but whenever they run the query they have to explicitly specify those values in the SSMS. The question was how come my queries automatically displays Statistics Time and IO.
Well, it is because of how I have configured my SSMS. In SSMS go to Tools >> Options >> Query Execution >> SQL Server >> Advanced and on this screen select the checkbox for the following option.
- SET STATISTICS IO
- SET STATISTICS TIME
Once enabled above settings every query executed on the same SSMS new query window automatically added STATISTICS TIME and IO.
If you have any such tips, please let me know and I will be happy to post it on the blog with due credit to you. I personally like to customize my SSMS for maximum efficiency and for the full screen.
Here are a few additional blog posts which you may find interesting:
- SQL SERVER – Difference Between Azure Data Studio and SQL Server Management Studio
- SQL SERVER Management Studio 18 – Enable Dark Theme
- SQL SERVER Management Studio – Completion Time in Messages
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
When we Enable Statistics Time and IO , It will improves SELECT statement or it will improves DML commands performance also?