DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to determine the number of reads in a stored procedure?

Set statistics io on before execution is one way in SSMS Query Editor.

USE AdventureWorks2008;

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SET STATISTICS TIME ON

EXEC uspGetBillOfMaterials 800, '2004-01-05'

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

/*

SQL Server parse and compile time:

   CPU time = 15 ms, elapsed time = 83 ms.

 

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Table 'Worktable'. Scan count 2, logical reads 509, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Product'. Scan count 0, logical reads 178, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BillOfMaterials'. Scan count 90, logical reads 181, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 SQL Server Execution Times:

   CPU time = 32 ms,  elapsed time = 61 ms.

 

 SQL Server Execution Times:

   CPU time = 47 ms,  elapsed time = 144 ms.

*/

Setting up a trace in SQL Profiler is the second way.

Typical OLTP stored procedure may do 100-1000 reads. If it's doing a lot more, it is a candidate for optimization. Batch procedure or summary report may do 10,000 - 500,000 reads. Typical solution aims to isolate them from OLTP activities.

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE