Reviewing some queries and noticed a warning that stated “Operator used tempdb to spill data during execution with spill level“.
Here is the query truncated:
select count(tblB.pkBookingID) from [dbo].[tblBooking] tblB WITH (NOLOCK) inner join [dbo].[tblBookingItem] tblBI WITH (NOLOCK) ON tblB.pkBookingID = tblBI.fkBookingID AND tblBI.pkBookingItemID = ( select max(tblBI_Inner.pkBookingItemID) from [dbo].[tblBookingItem] tblBI_Inner WITH (NOLOCK) where tblBI.fkBookingID = tblBI_Inner.fkBookingID and tblBI_Inner.active = 1 and tblBI_Inner.primaryRoom = 1 ) inner JOIN [dbo].[tblBookingDateTime] tblBDT WITH (NOLOCK) ON tblBI.fkBookingID = tblBDT.fkBookingID AND tblBI.pkBookingItemID = tblBDT.fkBookingItemID INNER JOIN dbo.tblGeneralFieldInformation tblGFI ON tblB.pkBookingID = tblGFI.fkBookingID where tblB.deleteBooking = 0 and tblBI.primaryRoom = 1 and tblBI.active = 1 AND tblGFI.active =1
Estimated Query Plan
Here is our estimated query plan:
- Nothing big
- A couple of Merge Joins and a Hash Match
- A couple of Stream Aggregates
- Stream Aggregate Showplan Operator
The Stream Aggregate operator groups rows by one or more columns and then calculates one or more aggregate expressions returned by the query. The output of this operator can be referenced by later operators in the query, returned to the client, or both. The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort Operator prior to this operator if the data is not already sorted due to a prior Sort operator or due to an ordered index seek or scan. In the SHOWPLAN_ALL statement or the graphical execution plan in SQL Server Management Studio, the columns in the GROUP BY predicate are listed in the Argument column, and the aggregate expressions are listed in the Defined Values column.
- Stream Aggregate Showplan Operator
Actual Query Plan
Here is what happens when we actually run the query:
Our eyes quickly turn to the Yellow Warning Signal:
- We see a warning on the Hash Match Join
- Keep in mind that the Hash Match precedes the Stream Aggregate
- And, so we should review the Hash Match Operator and see what is being sorted
Hash Match Aggregate Operator:
- There is a big divide between the “Estimated Number of Rows” and the “Actual Number of Rows”
- The “Estimated Number of Rows” is 1
- The “Actual Number of Rows” is 397722
- The “Defined Values”
- Scalar Operator(ANY([DB].[dbo].[tblGeneralFieldInformation].[fkBookingID] as [tblGFI].[fkBookingID]))
- Estimated Execution Mode
- False –> Which means no parallelism
- There are 3 warnings
- Operator used tempdb to spill data during execution with …
- spill level 1
- spill level 2
- spill level 3
I suspect the original query is a bit slow due to the correlated sub-query on [dbo].[tblBookingItem].
Let us re-write using Windowing Functions.
Query uses Windowing Functions:
;with cteBI as ( select pkBookingItemID , fkBookingID , primaryRoom , active , DENSE_RANK() over (PARTITION by tblBI.fkBookingID order by tblBI.[pkBookingItemID] desc ) as rankID from [dbo].[tblBookingItem] tblBI WITH (NOLOCK) where tblBI.active = 1 and tblBI.primaryRoom = 1 ) select count(tblB.pkBookingID) from [dbo].[tblBooking] tblB WITH (NOLOCK) /* inner join [dbo].[tblBookingItem] tblBI WITH (NOLOCK) ON tblB.pkBookingID = tblBI.fkBookingID AND tblBI.pkBookingItemID = ( select max(tblBI_Inner.pkBookingItemID) from [dbo].[tblBookingItem] tblBI_Inner WITH (NOLOCK) where tblBI.fkBookingID = tblBI_Inner.fkBookingID and tblBI_Inner.active = 1 Make sure that it is the primary room and tblBI_Inner.primaryRoom = 1 ) */ inner join cteBI on tblB.pkBookingID = cteBI.fkBookingID and cteBI.rankID = 1 inner JOIN [dbo].[tblBookingDateTime] tblBDT WITH (NOLOCK) ON cteBI.fkBookingID = tblBDT.fkBookingID AND cteBI.pkBookingItemID = tblBDT.fkBookingItemID INNER JOIN dbo.tblGeneralFieldInformation tblGFI ON tblB.pkBookingID = tblGFI.fkBookingID where tblB.deleteBooking = 0 and cteBI.primaryRoom = 1 and cteBI.active = 1 AND tblGFI.active =1
Let us do a quick comparison
SQL Server Profiler
We are filtering:
- We can see the Hash Warnings between the beginning and completion of the correlated join; as expected there are 3 of them
- There is also a big schism is the amount of I/O between both queries
Diagnostics via Dynamic Management View
Let us compare the queries by utilizing Dynamic Management Views.
- Need one of the following permissions
- Need sysadmin
- VIEW SERVER STATE
As we are using a development box, we are able to clear memory and procedure cache.
dbcc dropcleanbuffers dbcc freeproccache
Actual Dynamic Management View ( DMV) Query
SELECT plan_handle = ecp.plan_handle , [schema] = object_schema_name(sqlText.objectid) , [object] = object_name(sqlText.objectid) , sqltext = sqlText.text , ecp.objtype , [memoryObjectType] = omo.[type] , planSize = sum(ecp.size_in_bytes) , [pageSize] = sum(page_size_in_bytes ) , workerTime = cast(cast(avg(qs.total_worker_time / 1E6) as decimal(10,2)) as varchar(40) ) + ' secs' , readsLogical = avg(qs.total_logical_reads) , executionCount = sum(qs.execution_count) , totalRows = avg(qs.total_rows) FROM sys.dm_exec_cached_plans AS ecp JOIN sys.dm_os_memory_objects AS omo ON ( ( ecp.memory_object_address = omo.memory_object_address ) OR ( ecp.memory_object_address = omo.parent_address) ) CROSS APPLY sys.dm_exec_sql_text(plan_handle) sqlText LEFT OUTER JOIN sys.dm_exec_query_stats AS qs on ecp.plan_handle = qs.plan_handle where sqlText.text not like '%sys%' and sqlText.dbid = db_id() group by ecp.plan_handle , object_schema_name(sqlText.objectid) , object_name(sqlText.objectid) , sqlText.text , ecp.objtype , omo.[type] order by sum(qs.total_logical_reads) desc , sum(page_size_in_bytes ) desc GO
- Filter out queries that reference the sys schema
- Only look in our current user database [ db_id() ]
With tending a bit of attention and time, we can tackle sorts and consequent temp-db workload.