Tuesday, April 5, 2011

SQL Server: High Logical Reads for Strange Worktable

Few days back, on production database server, I found that logical reads value for a specific store procedure is much high then our expectations (benchmarks). I simply took the execution statement and executed with SET STATISTICS IO ON so I can get, which part is forcing more logical reads and got following statistics.
Table 'Worktable'. Scan count 0, logical reads 7523, physical reads 0
Table 'Employee'. Scan count 1, logical reads 106, physical reads 0
Table 'EmployeeAddress'. Scan count 0, logical reads 133, physical reads 0
It was showing me table ‘Worktable’ with highest logical reads. I was dead sure that there is no such table in our production database exists. So WHAT IS THIS WORKTABLE and who created it. 
According to msdn library “The relational engine may need to build a worktable to perform a logical operation specified in an SQL statement. Worktables are internal tables that are used to hold intermediate results. Worktables are generated for certain GROUP BY, ORDER BY, or UNION queries. For example, if an ORDER BY clause references columns that are not covered by any indexes, the relational engine may need to generate a worktable to sort the result set into the order requested. Worktables are also sometimes used as spools that temporarily hold the result of executing a part of a query plan. Worktables are built in tempdb and are dropped automatically when they are no longer needed.”
On further investigation I found that a cursor was used by developers to work on records one by one, which caused creation of this worktable in tempdb. Here is execution plan, where you can easily find that how expensive cursor is (especially in my case).
We preferred to revisit all of store procedure's code and we were successful to avoid this highly expensive cursor and its resultant worktable and successfully achieved our required performance.

1 comment:

  1. We are seeing this, also. How does one go about approaching large queries in order to minimize the risk of Worktable generation?
    Thank you for posting this!
    If you openly declare that Jesus is Lord and believe in your heart that God raised him from the dead, you will be saved. For it is by believing in your heart that you are made right with God, and it is by openly declaring your faith that you are saved.
    For “Everyone who calls on the name of the Lord will be saved.”
    Romans 10:9-10,13 NLT


All suggestions are welcome