Welcome

Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein



Thursday, 8 October 2009

Commands to help tune queries

SET STATISTICS IO ON

This command displays the amount of disk activity for transact SQL statements. Sample output is

Table 'servers'. Scan count 1, logical reads 7,physical reads 0, read-ahead reads 0,
lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.

To turn off run
SET STATISTICS IO OFF


SET STATISTICS TIME ON

SQL Server firstly makes logical reads to retrieve data from the data cache, if the data is not in the data cache it makes physical reads from disk. This script displays the amount of CPU resources needed by the query to run, parse, compile, and execute each statement in milliseconds

A sample output is

CPU time = 0 ms, elapsed time = 145 ms

To turn off run
SET STATISTICS TIME OFF

No comments:

Post a Comment

Note: only a member of this blog may post a comment.