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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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