TempDB and SQL 2005

Recently I found some more evidence in support of pre-sizing TempDB and turning off auto-grow. We have a server that has starting having disk issues with TempDB every time it reboots. The server is SQL 2005 SP3 CU11 and when it reboots the TempDB drive fills up and we get this message in the error log:

      X:\TempDB_Data\TempDev8.NDF: Operating system error 112(There is not enough space on the disk.) encountered.

The server starts up but only one TempDB file is used. The problem started after tempdb expanded and filled up the drive one time and I shrunk some files and grew the rest so they were all the same size. Then i turned off autogrow thinking the problem was fixed. After the next reboot we got the error message and the size of the one file that did get created was larger than it should have been. Also, when I looked at the properties in SSMS, all of the file sizes were different and some were larger than what I had shrunk them to. What the heck was going on?

To test it out, I installed SQL 2005 on a test machine and used a script to pre-size TempDB so that all the files were the same size.

I picked one file (tempdev8) and increased the size to 2000 MB. I verified the increase by checking the actual file size on disk and by running a query against sys.master_files

SELECT database_id, type_desc, name, physical_name, state_desc, size, size /128 AS sizemb FROM sys.master_files WHERE database_id = 2 order by type_desc, name

I rebooted the server just to set everything and checked again to make sure the file was still 2000MB.
Next I shrunk the file back down to 1800

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev8' , 1800) go

I verified the size change by looking at the actual file size on disk and was happy with the result.

Then I restarted the SQL service and when it came back up the file size on disk was back up to 2000MB. What the heck!
I ran my shrink again and this time checked sys.master_files as well as the file size on disk. While the file size on disk was the same as all the other files, my query from above showed the same results as it did right after I expanded the file the first time

Ah, now I could see what was happening, even though I am shrinking the file, SQL isn't updating sys.master_files so when the service is restarted the size of tempdev8 goes right back to 2000MB.  Now that I knew the problem I had to figure out how to fix it. I had to alter the file after I shrunk it so that the entry in sys.master_files changes. This can be accomplished with the following code

USE [tempdb] GO DBCC SHRINKFILE (N'tempdev8' , 1700) go ALTER DATABASE [tempdb]    MODIFY FILE ( NAME = N'tempdev8', SIZE = 1800MB ) GO

Notice that I shrunk it down to 1700MB then grew it up to 1800MB. When I tried to use the same number for both commands I got an error "MODIFY FILE failed. Specified size is less than current ".  After these commands completed, I checked sys.master_files again and everything looked good. I restarted SQL again and the file stayed at the correct size.

 

The problem on the original server (the one with the error) is that a couple of tempdb files had gotten quite big so that when I shrunk them and then increased the size of all the remaining files, their combined file size from sys.master_files was larger than the drive capacity even though the file size on disk still left 10% of the drive free. I did this same procedure on the affected server and everything looks good. I'll keep an eye on it after the next reboot to verify but I don't expect to have any issues.

Sum Up

While SQL 2005 is no longer supported and everyone should be either off of it or planning to migrate soon, I think this is a good example of unintended consequences. Since we don't currently presize TempDB at work and don't have a max size set, we end up doing a bit of manual adjustment of file sizes. If we had just gone forward with the recommendation then we would have saved ourselves quite a bit of troubleshooting time.

Hasta Luego,RutherfordTX

TempDB - Round Robin and Proportional Fill

Proportional Fill

My post from last week sparked quite a bit of discussion at work about how the data files for TempDB fill up when they are not of equal size. Pretty much all of the documentation and reccomendations out there say to keep the data files the same size so that the round-robin data flow works properly. What we were having trouble deciding is what happens when they are not the same size, for example if you have one file that is 5GB and three files that were 500 MB.

The discussions ended up in two different camps.

1. TempDB would allocate pages in the first larger file until the free space was equal to the three smaller files then the round robin would continue.
2. The allocations would still be handled in a round robin fashion with the larger file just getting more so that they would fill up evenly (from a percentage standpoint). 

Like any good discussion we decided to take this to our test boxes and see if we could figure it out The answer I found comes down to a little guy named proportional fill.

The Test

I took my test server and created four TempDB data files. Look at the image below to see how they looked when I started out. Notice they all have a very similar free space percentage

(The script I used to view this info came from Dave Turpin)

Then I wrote a simple script that would create a temp table and add data to it

DECLARE @x int

SET @x = 1

CREATE TABLE #MyTempTable

(  id BIGINT, SomeValue char(5000))

WHILE @x < 362846

BEGIN;

INSERT INTO #MyTempTable ( id, SomeValue )

VALUES  ( 99999999999, 'ddddddddddddddddd')

SET @x = @x +1

END; 

I picked the number 362846 because I knew that would get the files near 50% full and I wanted to add something to the test half way through. After this ran I checked TempDB again using my script and got the below results

As you can see it looks like the second camp was correct. Proportional fill was used to allocate pages to each data file according to its free space percentage and not it's size. The result is that there were a lot more allocations to the larger file but at the end they all ended up around the same free space percentage

Add to the Test

Now I wanted to see what would happen if you added a new TempDB data file, so I added another 500 MB file to TempDB. My thought was that if I now had four files at 55% free and one file at almost 100% free would it fill up the new file until it was at 55% or continue to allocate pages in all the files

After the file was added I ran my test again and checked the file usage twice

Once about halfway

And when the script finished

As you can see, the allocations were made to all of the files, not just the new one. It is just that more allocations were made to the new 500 MB file than to the old 500MB files during this second test (361MB as opposed to about 192 MB). Proportional Fill strikes again! If you look at how the numbers are trending you can see that if I hadn't set a limit on my while loop all of the files would have been almost equally full when the first one completely filled up.

Sum Up

This was a neat little experiment for me. I enjoy being able to test things out rather than just taking as fact what I read some where. In fact, quite a bit of the documentation I found was misleading on this subject. If you would like a good (and more thorough) look at our friend Fill, check out this post by Jonathan Kehayias:

• Looking at multiple data files and proportional fill with Extended Events

He uses extended events to show this so you can actually see the number of file writes per data file. His test is not specifically on TempDB but the allocation should work the same way.
Also check out this article by Paul Randall for more info about proportional fill:

• Rebalancing data across files in a filegroup

I'll be back in two weeks. Hopefully I can squeeze one more blog post out of all my TempDB testing.

Hasta Luego,

RutherfordTX

Resetting TempDB data files

I'm Back!

Have you ever missed a personal deadline for something (like a making a doctor's or dentist appointment) and the further you get past it, the harder it it is to do it? That happened it me and I found that I can go a LONG time before I finally break down and do it, 2 years and 8 months, apparently. Well in that time I have learned quite a bit about SQL and hope to be able to post something informative about every two weeks that isn't just a rehash of what you can find on other peoples blogs. Anyway, on with the post..

TempDB Configuration

Lately I have been doing some research and testing into the configuration of Tempdb. I won't rehash all the details of how or why you should change the default settings since there are people who I would just be paraphrasing:

• Demystify Tempdb Performance and Management - Robert Davis
• TempDB Performance and Configuration - Kendra Little
• Building Perfect SQL Servers, Every Time - Joey D'Antoni
• Correctly Adding data Files to TempDB - Paul Randall

What I am going to do is tell you about an error I kept getting when I was testing out some different configurations and how I resolved it.

Almost all of the information that you find on the internet about TempDB configuration tells you that you need to have more than one data file for TempDB so you don't suffer performance issues related to page allocation contention. There are some varying opinions on how many you need to have. Some say 1 file per core up to 8, some say 1 file for every 2 cores. There are also opinions on how to properly size the data files and the log file. You can read about them in the links above or with a google search. One important thing to note is that most everyone says you need to right size your data files and turn off auto-growth. This allows TempDB to allocate space in a round robin fashion. If one file is much bigger and has more free space, it will get the writes until the free space is reduced to the size of the other files.

My current task at work is to come up with a script that can automatically create the TempDB files and size them. In our current build doc we just create 8 TempDB files (even if we only have 4 cores) and size them at 1GB with auto grow at 1GB. My plan was to mess around with some different configurations and try out different numbers of data files. I the first test, I just took our existing 8 file standard and sized them to 80% of the disk that they are on.

--- Set the number of files
SET @number_of_files = 8
--- Get the Drive size for TempDB
SELECT @drive_size_mb=total_bytes/1024/1024 FROM sys.dm_os_volume_stats (2,1)
--- Calculate 80% of the total drive size and divide that by the number of files to get individual file size
SELECT @individ_file_size = (@drive_size_mb*.8)/@number_of_files

Once, I was finished with that script I wanted to reset TempDB so that it only had one file so I could try something different. I used DBCC SHRINKFILE to empty out each TempDB file and then REMOVE FILE to remove them.

USE TempDB
GO
DBCC SHRINKFILE (tempdev8,EMPTYFILE);
GO

ALTER DATABASE TempDb
REMOVE FILE tempdev8;
GO

The problem is that when I tried to do that I got an error message:

DBCC SHRINKFILE: Page 9:32 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 4
Cannot move all contents of file "tempdev8" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 6
The file 'tempdev8' cannot be removed because it is not empty.

It looks like something is using my TempDB files so I can't get rid of them. (This is a test server but it does have some other non-prod stuff running on it). I tried restarting SQL service and running the script as soon as it started but I still got the error on most of the files. Something started using the TempDB data files right when SQL started and I couldn't get them deleted. I started thinking about how I could force everything to use just one of the TempDB files so I could delete the other ones. Can you figure out what I did based on something I have already said in this article?

The solution that worked was I increased the size of the main TempDB file so that it was much larger than the other files. Then when I restarted SQL Service all the TempDB writes went to that files since it had more free space then the other ones. Right after restart I ran my delete script and was left with one TempDB datafile so I could start working with another configuration.

Sum Up

I know this isn't a groundbreaking post (gimme a break, it's my first one back) but I love it when you can take some information that you are learning or just read about and then you apply it in a real life situation like this. I'll be back in two weeks, no idea of a topic yet but I'll think of something.

Hasta Luego,
RutherfordTX