Database Reference
In-Depth Information
Figure 22-3.
Extended Events output showing some of the total cost of the data processing using a T-SQL cursor
As you can see in Figure
22-3
, lots of statements are executed on SQL Server. Essentially, all the SQL statements
within the stored procedure are executed on SQL Server, with the statements in the
WHILE
loop executed several times
(one for each row returned by the cursor's
SELECT
statement).
The total number of logical reads performed by the stored procedure is 8,788 (indicated by the last
sql_batch_completed
event). Well, is it high or low? Considering the fact that the
Production.Products
table has only 6,196 pages and the
Production.WorkOrder
table has only 926, it's surely not low. You can determine the number of pages allocated to
these tables by querying the dynamic management view,
sys.dm_db_index_physical_stats
.