{"id":47,"date":"2011-09-13T22:06:30","date_gmt":"2011-09-13T22:06:30","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=47"},"modified":"2014-03-06T15:39:11","modified_gmt":"2014-03-06T15:39:11","slug":"statistics-io-and-statistics-time","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/statistics-io-and-statistics-time\/","title":{"rendered":"Statistics IO and Statistics TIME"},"content":{"rendered":"

Statistics IO and Statistics TIME can help in performance tuning from a granular level and saves time compared to Execution Plans, SQL Server Profiler, etc. Let\u2019s looks at an example of these two commands:<\/p>\n

There a few different ways in which you can turn on these commands inside SQL Server Management Studio. If you want every query window to open up with statistics you can go to Tools, Options, Query Execution, SQL Server, Advanced and check SET STATISTICS TIME and SET STATISTICS IO:<\/p>\n

\"SQL<\/a>Most of the time you will just want to use statistics for a certain troublesome query. To turn these commands on for a specified query window, open the query window and go to Query, Query Options, Execution, Advanced and check SET STATISTICS TIME and SET STATISTICS IO:<\/p>\n

\"SQL<\/a>Or you can simply use T-SQL and type the following to turn statistics on<\/p>\n

\n
SET STATISTICS IO ON\r\nSET STATISTICS TIME ON<\/pre>\n<\/div>\n
And to turn off simply use:<\/p>\n
\n
SET STATISTICS IO OFF\r\nSET STATISTICS TIME OFF<\/pre>\n<\/div>\n
In this example I\u2019m going to run a query from AdventureWorks using statistics:<\/p>\n
\n
SET STATISTICS IO ON\r\nSET STATISTICS TIME ON\r\n\r\nSELECT pc.FirstName\r\n,pc.LastName\r\n,pc.EmailAddress\r\n,he.Title\r\n,hh.Rate\r\nFROM Person.Contact pc\r\nJOIN HumanResources.Employee he ON pc.ContactID = he.ContactID\r\nJOIN HumanResources.EmployeePayHistory hh ON he.EmployeeID = hh.EmployeeID\r\nWHERE hh.Rate > 10\r\nORDER BY hh.Rate<\/pre>\n<\/div>\n
After the query finishes it will show you results like normal but if you click on the Messages tab you will see some extra information:<\/p>\n
\"SQL<\/a><\/p>\n
You will see IO information at the top:<\/p>\n
Scan Count \u2013 Number of index or table scans
\nLogical Reads \u2013 Number of pages read from the data cache
\nPhysical Reads \u2013 Number of pages read from disk
\nRead-Ahead Reads \u2013 Number of pages placed into the cache for the query
\nLOB Logical Reads \u2013 Number of text, ntext, image, or large value pages read from the data cache
\nLOB Physical Reads \u2013 Number of text, ntext, image, or large value type pages read from disk
\nLOB Read-Ahead Reads \u2013 Number of text, ntext, image, or large value type pages placed into the cache for the query<\/p>\n
Execution TIME information will be directly below:<\/p>\n
CPU Time: How long the query worked with the CPU
\nElapsed Time: How long the query took to gather data<\/p>\n
Statistics, in general, is an excellent starting point to see why you might have a poor performing query. You can gather results such as execution time, compile time, reads, writes, cost, etc.<\/p>\n","protected":false},"excerpt":{"rendered":"
Statistics IO and Statistics TIME can help in performance tuning from a granular level and saves time compared to Execution Plans, SQL Server Profiler, etc. Let\u2019s looks at an example of these two commands: There a few different ways in which you can turn on these commands inside SQL Server Management Studio. If you want […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[97,39,18,57],"class_list":["post-47","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","tag-performance-tuning","tag-ssms","tag-statistics","tag-tuning"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/47","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":5,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":88,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/47\/revisions\/88"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}