TEMPDB and buffer cache

· Uncategorized
Authors

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)

Result:

Database Name PageCount
MyUserDB

3216497

tempdb

6279

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:

Database Name PageCount
MyUserDB

4480740

tempdb

6261

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: