TempDB And Parallelism

Kendra Little looks at cases when a query uses multiple tempdb data files:

As you might guess, things may not always get evenly accessed, even if you have evenly sized tempdb files. One of my queries did a select into a temp table. Although it used all four tempdb files whether or not it went parallel, there were more file_read events against the first tempdb file than against the other four.

It’s an interesting look at this specific question, but also as pedagogical technique.

Related Posts

Risks Of Using Resource Governor To Set Max DOP

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance: I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get […]

Read More

When Table Variables Have Realistic Estimates, Unrealistic Results May Occur

Milos Radivojevic wraps up a series on deferred compilation for table variables by looking at a hack which used to work but no longer does: With this change, the query is executed very fast, with the appropriate execution plan: SQL Server Execution Times: CPU time = 31 ms,  elapsed time = 197 ms. However, the LOOP hint […]

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031