TempDB and Allocation Page Contention in simple words

We all know how importnt our tempdb is , especially for SQL Server 2005 and onward suits of product.
It is so important to make sure that your tempdb is always in healthy state.

One of the most common issue you may come across when monitoring TEMPDB is "Allocation Page contention".

I am sure this might be a new terminology for those  who do not deal much with SQL Internals. Let me explain this concept in a very simple words and to show what exactly you can look at to make sure your tempdb is not showing this symptom.

When user creates a temporary table in one of his script then what happens!!!. Your temporary table also need to be assign space also!!! right :)

Now SQL Server has mechanism to do so and thats where three concept comes into highlight:

PFS, GAM and SGAM.
So what happens when sql server sees create table #temp.... in your application?

1. It reads the SGAM page and look for free space in a mixed extent(group of 8 pages shared by more than one object)

2. while doing this SQL Server engine will put exclusive latch on SGAM page and will move to PFS page to find the free page for its object.

3. It will then place latch on PFS page as well and this latch will stay there till page is allocated to the table.


So this is way your temporary table get space in database. So now the first  question  what is an issue here?????

The issue is when your tempdb gets overloaded with allocation requests.

Second question is how do you find if there is a contention in allocating page?

The answer is very simple, Use our favourite "sys.dm_os_wait_stats" and look for a PAGELATCH waits on resource 2:1:1.
2:1:1 is a PFS page and too many PAGELATCH on this recource means there is too much allocation page contention.

Finding an issue is all good but you will only get medal if you fix this :) . So the last question is how do you fix allocation page contention?

Have multiple tempdb files
and follow tempdb best practice guide :) (papers are availaible from MS).

cheers
Puneet

Duplicate Indexes......Good or Bad!!!

This is not uncommon for you to come across a large table with lot of indexes. But Have you ever tried to see if there is any duplicate Indexes exists for that table?

For example If I have a table say "Customer" and define one Non clustered Index on FirstName and then I define another index on FirstName and LastName then Firstname appears twice and the first Index can be called redundant and is not required.

Disadvantages of having duplicate index is extra overhead(extra page). So this means any insertion will take more time.

Little things like this get easily un-noticed.

So go today and find out how many duplicate indexes are in your databases :)

Have fun !