Monthly Archives: July 2010

An Easy Way To Optimize Page Allocation in TempDB

Old Skool Page Lookups

The other day (prompted by a tweet by @buckwoody) I ran the Microsoft Baseline Analyzer on one of my SQL DBs. One of the recommendations it made was that I create multiple data files for tempDB as allocations were slow. I vaguely remember reading about doing this but was still hazy on the details.

So rather than just make the change blindly I wanted to do some research behind the scenes and understand why.

The Mechanics

When you create an entry in tempDB it needs to allocate space just like any other table. There are three types of pages utilized in the allocation process in the tempdb data file: Page Free Space (PFS), Shared Global Allocation Map (SGAM), and Global Allocation Map (GAM).

  • The PFS (specified as a 1) holds 1 byte of data that contains how much free space is in each page.
  • The SGAM (specified as a 3) holds 1 bit per extent which SQL server uses to find whether or not an extent is a mixed extent with some free space or a full extent (there are some optimizations SQL 2008 has where you allocate a uniform extent, but for the sake of this post I will leave that out).
  • The GAM (specified as a 2) holds 1 bit per extent which SQL Server uses to to see which extents are empty to allocate to the new object.

The tempdb page allocation goes something like this:

1. When a temp table is created SQL Server reads the SGAM page to find some free space to allocate for the the table.
2. SQL Server latches the SGAM page and then reads the PFS page to find free pages within the extent.
3. Once free space is found SQL Server creates an exclusive lock on the PFS page and allocates the space.

However, if tempdb is overloaded with allocation requests this can become burdensome with PAGELATCH waits. This can be seen by querying the following:

SELECT session_id, wait_duration_ms,   resource_description
from    sys.dm_os_waiting_tasks
where   wait_type like ‘PAGE%LATCH_%’ and
resource_description like ’2:%’

This query is getting all the PAGE LATCH wait types where the database id is 2 (tempdb). The order for this is dbid:page:type (in the resource_description). If this type is GAM (ex- 2:1:2), SGAM (ex- 2:1:3), or PFS (ex- 2:1:1) it can represent a bottleneck.

One solution to this issue (and the easiest) is to configure multiple tempdb data files.
Microsoft recommends starting with 1 tempdb data file per CPU and adjusting up from there.

There are a couple issues though. One problem is if you create too many data files you may actually get a performance reduction. Another issue is that tempdb will try and fill the file with the most free space first. So you won’t get any benefit unless you equalize all the data files. So a sample configuration for a quad-core system might look like this:

MODIFY FILE (name=tempdev1, size=100MB);
ALTER DATABASE tempdb ADD FILE (name=tempdev2, size=100MB,filename=’Z:\mydata\tempdev2.ndf’);
ADD FILE (name=tempdev3, size=100MB,filename=’Z:\mydata\tempdev3.ndf’);
ADD FILE (name=tempdev4, size=100MB,filename=’Z:\mydata\tempdev4.ndf’);

Now, in theory, you should have uniform page allocation to tempdb. I highly recommend that you utilize the sys.dm_os_wait_stats DMV before and after to see what kind of effect this has on your PAGELATCH’ing and Duration. Your mileage may vary of course.

Share and Enjoy

Category: SQL

No I.T. Training Budget? Twitter to the Rescue!

Computing Circa 1981

As everyone knows the economy has been tough on the I.T. field. Redmond magazine even did a great piece in their June issue called “I.T. Hell: How the Recession Has Affected I.T. Jobs“.

More often than not the first thing out the I.T. budget window is training and travel. Since technology changes almost daily an “axed” training budget puts any kind of professional development on the back burner.

In my case twitter has come to the rescue!

At first I was among the ranks of those that really did not want to know the last time people washed their dog, or how much they love Justin Bieber. However, once I finally took the plunge I felt like I found the mother-lode.

The first hurdle I/we had to overcome was to get management buy in. In my case we had the following points:

  • Direct contact with the public and/or customers.
  • Some of the top people in their respective fields are on twitter and will actually answer questions.
  • Gives the sense that your organization is innovative and technologically savvy.

One additional bonus we found (and really the most valuable benefit) is the free resources and training.

Free you say? Absolutely!

Tons of software vendors and professional organizations are out there hosting webinars, conferences, user groups, and free software contests. Twitter is the gateway to finding out about these if you start to follow those in your field of interest.

Once you have management buy-in all that’s left to do is sign-up, download a client (like tweetdeck), and start tweeting away.

One pitfall I see is lots of people have one twitter account, but your boss may have a real problem if you’re not tweeting business related items during work hours. HR may also have a problem if you’re tweeting dirty jokes after hours on what is supposed to be a work account.

If you’re really interested in being a serious twitterer there are some simple rules to start building your twitter persona. Rather than go into detail on that I will simply point to a great resource by Brent Ozar called “The Simple Twitter Book“.

My final word on twitter is that it has helped me to identify free resources to move my career forward. It has put me in contact with awesome people I would never ever have come in contact with before. It has pushed my vistas out and prevented me from stagnating in a terrible budget year.

Share and Enjoy