PLE & Tempdb spills

Question

Post reply

PLE & Tempdb spills

simon_s

Ten Centuries

Points: 1358

January 25, 2022 at 11:18 am

#3976916

Hi

Im investigating poor PLE on one of our servers and using the following query to look at tempdb spills

WITH spills
AS ( SELECT     deps.plan_handle,
SUM(deps.execution_count) AS execution_count,
SUM(deps.total_spills) / 128. AS total_spills_mb,
SUM(deps.last_spills) / 128. AS last_spills_mb,
SUM(deps.min_spills) / 128. AS min_spills_mb,
SUM(deps.max_spills) / 128. AS max_spills_mb,
(SUM(deps.total_spills) / SUM(deps.execution_count)) / 128. AS avg_spills_mb
 FROM       sys.dm_exec_procedure_stats AS deps
 WHERE      deps.total_spills > 0
 GROUP BY   deps.plan_handle       )
SELECT      s.execution_count,
s.total_spills_mb,
s.last_spills_mb,
s.min_spills_mb,
s.max_spills_mb,
s.avg_spills_mb,
deqp.query_plan      into sql_mon..spillsbusy2
FROM        spills AS s    
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) AS deqp 
order by s.avg_spills_mb desc

However whilst it identifies around 10 procs and their execution plans , none of the plans actually reference SpillToTempDb. Is this because its just grabbing the most uptodate plan from the cache and not necessarily the plan that was in use when the spills occurred ?

The follow on question is there a better way to be looking at tracking root cause(s) of PLE ?

many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Page Life Expectancy is a crappy measure of performance. A common pattern is that sawtooth look that it gets. It grows & grows, then there's a data load, so the memory gets dumped, then it grows & grows again. It just gets meaningless without lots of other measures. Then, might as well use the other measures. I prefer overall query performance in general, followed by wait statistics and queues. What things are waiting and how long they're waiting is a much better measure of where the system is hurting rather than PLE.

And yeah, the plan cache may or may not have the spills plan in it. Recompiles, all sorts of stuff, could affect what you see from the cache after the fact. If you're really interested in monitoring for spills, it's built right into the output for sql_batch_complete, and sp_statement_completed so you can use Extended Events. You can also look for hash_warnings & sort_warnings using Extended Events.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

If, however, it's ALWAYS in the proverbial toilet, THAT can be an indication of a much larger problem.

It may not actually show up as "spills", either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)