Background
Reviewing some queries and noticed a warning that stated “Operator used tempdb to spill data during execution with spill level“.
Query
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
Query Plan
Estimated Query Plan
Here is our estimated query plan:
Explanation:
- Nothing big
- A couple of Merge Joins and a Hash Match
- A couple of Stream Aggregates
- Stream Aggregate Showplan Operator
https://technet.microsoft.com/en-us/library/ms189907%28v=sql.105%29.aspx
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:
Quick Explanation:
- 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:
Quick Explanation:
- 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
- Row
- Parallel
- False –> Which means no parallelism
- Warnings
- There are 3 warnings
- Operator used tempdb to spill data during execution with …
- spill level 1
- spill level 2
- spill level 3
Query Rewritten
Windowing Functions
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
Execution Plan
Performance Analysis
Let us do a quick comparison
Query Cost
Statistics I/O
Image:
Statistics Time
Image:
|
SQL Server Profiler
Configure:
We are filtering:
Output:
Image:
Tabulated:
|
Quick Explanation:
- 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.
Requirements:
- Need one of the following permissions
- Need sysadmin
- VIEW SERVER STATE
Query
Clear Cache
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
Explanation:
- Filter out queries that reference the sys schema
- Only look in our current user database [ db_id() ]
Output:
Tabulated:
|
Summary
With tending a bit of attention and time, we can tackle sorts and consequent temp-db workload.
