Large Sorts And Hashes

SQL Sasquatch looks at a scenario in which large sorts or hash operations can cause CPU to skew compared to page lookups per second:

The graph above has tempdb footprint (light blue) stacked on top of used query memory (dark blue) against the left vertical axis.  The green period has very limited use of query memory.  During the yellow period, a moderate amount of query memory was used.  During the red period, a large amount of query memory was used and at a number of points operations spilled into tempdb.  As query memory was used more extensively, the CPU:lookups/sec correlation was more disrupted.

Once fully considered, this makes sense: query memory is “stolen” from the database page buffer pool.  References to pages in the page pool are “page lookups”, but each time stolen query memory is poked and prodded… well, that’s not a page lookup.  But it has CPU cost.

Check out the whole thing; this is a thoughtful look at an interesting data oddity.

Related Posts

When tempdb Spills Attack

Josh Darnell ran into a problem with a SQL Agent job: One of my colleagues reached out to me recently about a production issue where a SQL Server Agent job had failed with this error message: Msg 1105, Level 17, State 2, Line 15Could not allocate space for object ‘dbo.SORT temporary run storage: 140737513062400’ in […]

Read More

Multi-Server Patching with dbatools

Eric Cobb shows how you can use dbatools to upgrade multiple SQL Server instances: Patching SQL Server can sometimes be a time consuming process, especially when you have multiple servers that need to be patched. Remoting in to each box to run through the update wizard is tedious, and if you have multiple patches to […]

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031