Suppose you notice write latency on TEMPDB is well above acceptable values (in our case 150ms). What can you do?
First you need to know TEMPDB is not like any other user database (because it’s a system DB).
There is no automated checkpoint process writing your dirty pages to disk. Only if your transactionlog fills to 70% of its capacity, an automated checkpoint is triggered. Because most processes in TEMPDB are minimally logged, this is unlikely to happen. So let’s forget all about checkpoints.
So you might conclude everything happens in buffer cache.
Question: but why are we seeing such huge volumes of write IO’s resulting in bad latency values (using sys.dm_io_virtual_file_stats)?
Answer: because the system only allocates a small part of the buffer cache for TEMPDB. You can check this by running
|SELECT DB_NAME (database_id) AS ‘Database Name’, SUM(1) AS PageCountFROM sys.dm_os_buffer_descriptorsWHERE DB_NAME(database_id) IN (‘MyUserDB’, ‘TEMPDB’)
GROUP BY database_id
ORDER BY DB_NAME(database_id)
Solution1: avoid disk IO and enlarge the part the buffer cache allocates for TEMPDB.
Problem1: you can’t manipulate the internal allocation behavior of the buffer cache. (boehoe)
Solution2: add more memory and hope the additional buffer cache gets allocated for TEMPDB.
Problem2: bad luck. After adding 16 GB of memory to the SQL instance, buffer cache allocation looks like this:
Even after running dbcc dropcleanbuffers in the middle of the run, TEMPDB takes only +/-7000 pages in the buffer cache. The rest of the buffer cache remains free.
Reason/ worktables don’t pass by the buffer cache
Solution3: buy storage with a huge write cache.
Solution4: buy storage with a huge amount of spindles to use for TEMPDB.