X hits on this document

122 views

0 shares

0 downloads

0 comments

22 / 48

656

CHAPTER 28 PERFORMANCE TUNING

SET STATISTICS IO ON GO

SELECT t.name TerritoryNM,

SUM(TotalDue) TotalDue FROM Sales.SalesOrderHeader h INNER JOIN Sales.SalesTerritory t ON

h.TerritoryID = t.TerritoryID WHERE OrderDate BETWEEN '1/1/2003' AND '12/31/2003' GROUP BY t.name ORDER BY t.name

SET STATISTICS IO OFF GO

This returns the following (abridged) results:

TerritoryNM

TotalDue

Australia

4547123.2777

Canada

8186021.9178

...

Southwest

11523237.5187

United Kingdom

4365879.4375

Table 'Worktable'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 3, read- ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'SalesTerritory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Substituting SET STATISTICS TIME with SET STATISTICS IO would have returned the following (abridged) results for that same query:

...

Southeast

3261402.9982

Southwest

11523237.5187

United Kingdom

4365879.4375

TerritoryNM

TotalDue

Australia

4547123.2777

SQL Server parse and compile time: CPU time = 50 ms, elapsed time = 117 ms.

(10 row(s) affected)

SQL Server Execution Times: CPU time = 40 ms, elapsed time = 87 ms.

How It Works

The SET STATISTICS commands return information about the actual execution of a query or batch of queries. In this recipe, SET STATISTICS IO returned information about logical, physical, and large

Document info
Document views122
Page views122
Page last viewedFri Dec 09 23:55:14 UTC 2016
Pages48
Paragraphs987
Words15822

Comments