SQL SERVER Management Studio – Enable Statistics Time and IO for Every Query

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.

SQL SERVER Management Studio - Enable Statistics Time and IO for Every Query statistics-time-800x262

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.

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

SQL SERVER Management Studio - Enable Statistics Time and IO for Every Query set statistics

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Recompile Stored Procedures Two Easy Ways
Next Post
Task Manager – Confusing Story of CPU

Related Posts

Leave a Reply

Menu