Contention and Multiple TempDB Files
Published: Apr 30, 2017
We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.

However, where some people can fall down is that they don’t realise that tempDB can easily get overwhelmed and therefore can suffer badly from contention.

Note that this isn’t IO contention, this is internal to tempDB based on the access patterns you have to your tempDB database, but there are some simple ways to alleviate this.

I’m not going to go into too much in depth detail here as to what happens when you get contention, but I will show you how to spot it and tell you the things you should do to.

If you want to know some of the internals around this then refer to Paul Randal’s post here.

However, if you just want the basic information, then it’s basically this… when we create and destroy temp tables we have to allocate them data pages to use. These are allocated on a page by page basis from within an Extent (an extent being 64Kb… 8 x 8Kb pages). This means that you can get contention in the allocation of pages to temp tables (called SGAM contention).

A quick solution to this, and something to which there is no downside, is to add TF 1118 as a startup flag in your system. This means that each temp table gets allocated a full extent upon creation and therefore removes the SGAM issue.

The other type of contention is PFS (Page Free Space file) contention. Basically this page tracks the objects in the data file and therefore is written to each and every time you create or delete a temp table. The problem here is that this particular page only allows access via a single thread at a time. Therefore presume we have 2 cores in our SQL Server, this means that we could run 2 processes simultaneously. Each process creates a temp table. The problem here is that only one at a time can write to the PFS page… therefore one is held up and we instantly have contention.

The solution here is to increase the number of tempDB data files that we have in our server because each data file has its own PFS page and therefore each CPU can access its own PFS and contention vanishes.

Now there are downsides of having too many tempDB data files as SQL Server will try to evenly distribute data across all files and therefore too many files can cause issues when using worktables or if memory spills occur etc.

The best way to calculate the number of files can be found in KB2154845 and is as follows:

Okay, enough of the internals (not that I don’t like internals because I very much do, but that this is very in depth and I don’t want to go into it here when there are other brilliant articles already out there… just look on Paul Randal’s blog, example).

So, the key part to this article… how do I spot this contention?

Well it’s actually a lot easier than you might think as they show up very specifically within SQL Server.

What you need to do is to keep an eye on your Waiting Tasks and check out the resource:

select resource_description
from sys.dm_os_waiting_tasks


As your workload is running, keep an eye on this column and look for either of the following making an appearance during times of poor performance:

select e.session_id, w.wait_type, w.resource_description
from sys.dm_exec_sessions e
join sys.dm_os_waiting_tasks w
on e.session_id = w.session_id
where is_user_process = 1


This was an incredibly fabricated scenario to get this to work, but you’ll still be able to see these wait types and resource 2:1:3 (SGAM contention) or 2:1:1 (PFS contention) if you’re having tempDB issues.

Once you know it’s happening then, as mentioned above, use the couple of methods I mentioned to see if you can relieve the contention and boost your performance accordingly.

Also, just a side note as I see this done incorrectly on many occasion… if you are going to add tempDB data files then they have to match the existing ones exactly. That means same size, same growth settings etc. otherwise SQL won’t use them as you expect and your problems may not go away.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron