Tempdb needs a doctor

In the past, we’ve run into issues with contention and the -T1118 flag, hotfix 2040, and multiple tempdb files that led me to go ahead and put this info out there if nothing more than for my future reference.

I ran into a machine today that had the tempdb files in the wrong location. They were also the wrong size and there were not enough of them.

So that presents three problems that had to be solved. The first problem I decided to fix was to get rid of the current files that were sized inappropriately and in the wrong location. No need to double my work.

I’m sure it can just as easily be done by script, but I went into the GUI and deleted each tempdb data file that I didn’t want anymore for the time being. You will get an error message about deleting the file and restarting SQL Server. Once you’ve done that you are left with only one data file and one temp file, even though they may be in the wrong location and the wrong size.

Let’s go ahead and tackle the size issue first. After we’ve just restarted SQL Server lets do something like this:

dbcc shrinkfile (‘tempdev’,1024,TRUNCATEONLY)

Now it’s time to go ahead and move the files to the place we want them:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘I:\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘I:\templog.ldf’)
GO

Now it’s time to add the additional tempdb data files:
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′,
FILENAME = N’I:\tempdb2.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev3′,
FILENAME = N’I:\tempdb3.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev4′,
FILENAME = N’I:\tempdb4.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev5′,
FILENAME = N’I:\tempdb5.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev6′,
FILENAME = N’I:\tempdb6.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev7′,
FILENAME = N’I:\tempdb7.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev8′,
FILENAME = N’I:\tempdb8.ndf’ , SIZE = 1048576KB , FILEGROWTH = 204800KB )
GO

This will create 8 files (for our # of processors) that are 1024MB in size and autogrow at 200MB. At this point it probably wouldn’t hurt to restart the SQL Server. We had a few small issues but I’m not sure they were related.

The links that helped me with this are as follows:
http://www.sqlteam.com/article/moving-the-tempdb-database
http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx
http://weblogs.sqlteam.com/tarad/archive/2008/08/05/How-to-get-physical-CPU-count-on-a-server.aspx
http://sql-server-performance.com/Community/forums/p/23813/135241.aspx
http://support.microsoft.com/kb/328551

  • Rick

    Just curious why this is tagged iSCSI? We’re playing around with putting our tempdb files on iSCSI, and google landed me on this article when I searched for “sql server tempdb iscsi”.
    Initially it doesn’t seem like a great idea, as when the iSCSI drive goes down, so goes the SQL Server instance. But because of the solution we’re using for our clustering, getting the tempdb files off of the drive that’s mirrored will give us the most performance improvement.
    I’d be curious to hear your thoughts.

    Cheers,
    Rick

  • admin

    Hello Rick,
    I apologize for the delay in getting to you.

    I am not sure why it would be tagged iSCSI since the only post I have tagged iSCSI is the post on Jumbo Packets. Maybe it’s something to do with their indexing.

    To your original question:
    Are you storing all of your data and log files on iSCSI? If so, when it goes down you will have a pretty big issue as well. SAN’s are designed not to go down though, much more than the local server in my opinion.

    Here’s a post on sqlservercentral talking about the issue when I encountered it.

    In our case, we had to look at several issues on the local server vs the SAN (I assume you are talking about an iSCSI SAN). In some cases we may have a drive that is 15k and the drives on the SAN are 10k.

    Is it possible to partition the local drive correctly for SQL Server, or is there data there that can’t be moved. I will post something soon about our adventures with the correct partitioning and how it helped us.

    In our case, our SAN was configured as one logical volume so even though it’s supposed to be smart enough to not act like a single disk I still have my doubts. But I didn’t set it up and can’t change it.

    It might be that you can just do some tests to see how it comes out, but because of the mirroring issue I would think you would see performance improvements. If you have multiple tempdb data files maybe you could split them up between the two, but we don’t deal with mirroring so I can’t say for sure.

    If you have any more specific questions I’ll try to help.