SQL Server’s STATISTICS IO

-




Usually the goal of performance tuning is to make your query run faster.  One of the easiest ways to get
a faster query is to reduce the volume of data a query is processing.  STATISTICS IO makes it easy to see how much data SQL Server is actually processing.

How it is going to help?

 The data it shows acts as a measuring for your performance tuning.
 It provides a good way of knowing the number of io Sql server has gone to fetch the data
 It is session specific only

How to Use?

-- For current session only
SET STATISTICS IO ON;
GO



How to read the output?

Logical reads: The number of pages SQL Server had to read from the memory in order to
process and return the results of your query.  Query performs slower if number of pages read is high.

 Physical reads: this read happens when the page requested is not present in the cache and needs to be fetched from files on the disk, this operation causes a IO wait. 

Worktables/Workfiles: sql server creates objects in tempdb for efficient way of processing queries, it might suggest that index or rearrangement would be required for query

Read ahead reads: this indicates that pages relevant to query were returned from disk but except for that some additional pages where also fetched. It is not necessary that all the pages retrieved should be used in the query, some pages cloud be used later. It happens mostly when the query exits after first or second conditions and pages are fetched with respect to the whole query   

Using it in performance tuning?

 observing LOGICAL READS is important, it refers to pages pulled from the memory then from physical reads which indicates the number of pages from disk.  However, all pages get loaded from disk into cache before SQL Server is able to use them.

 It clearly tells how many pages in total SQL Server needed to read in order to return the data.

 You could come to a conclusion of
If I add an index, does the total number of pages read go up or down?
What if I add additional filter or restructure my query?
Why one should use?

 Measuring time taken by query is alone not sufficient, at the time you tested what if server was hit by huge number of queries. it isn't a effective way to measure performance. Also server hardware is not the same on UAT as compared to production 

Comments

Post a Comment

Popular posts from this blog

ORA says NO DATA FOUND

-
Image
Linked servers always have a surprise error hidden under its selvee, Managing these issue magnets is a huge task for a DBA , recently i was stumbled upon a same suprise invloving a oracle linked server. The linked server "oracle_db" is present on my prod db server essentially for select and insert purposes, Developers tested the linked server with couple of select querries and all was fine until the deployed a procedure on server and executing the same gave a alien error OLE DB provider "OraOLEDB.Oracle" for linked server "oracle_db" returned message "ORA-01403: no data found" inspecting the code it was seen that the table from linkedserver was directly involved in a " inner join" and other joins ,re running the same satement produced the given error . The code snippet was pinned, diagnosis and resolution was underway After a thorogh R&D it was found that either taking the whole oracle side output into a #Temp table and then using t...
Read more

(session ID -2 is not valid) Dealing with Negative Session ids in Sql Server

-
Image
  SQL server being a complex mechanism of handling processes throughout their life cycle often gives out interesting anomalies, One such was encountered today on one of my production servers, there was a huge blocking on chain of processes and at the root of this chain was a session with the weirdest session id  The session id literally was " -2 "   ,  it is hard to make sense out of it as SQL server always works with positive spids. Research on this is itself a tedious job. After the R&D phase it was somewhat easier to diagnose the problem. The root cause refers to a phase of distributed transaction, A distributed transaction is referred when a process is extended over more than one servers i.e Process A of server 1 needs data to be manipulated on Server 2, so SQL server uses MSDTC (linked server) service to carry this process from one server to another. Going back to our issue, now According to msdn and other online resources, the session id -2 means that ...
Read more