Friday 11 September 2009

Moving tempdb

I'm doing it again, moving the SQL Server tempdb. For some reason people setup tempdb in places which just makes no sense whatsoever - like in the same location as other files. It's well documented that tempdb should be on its own drives as its performance is critical to that of the system as a whole.

What type of drive is arguable, generally I've used raid 10 although raid 1 is viable too.  There are arguments for raid 0 out there for the performance benefit, the downside is that if you lose 1 drive then your whole instance is in trouble so not really worth the risk.

You should have 1 tempdb file for each core (I've seen a suggestion of up to 8 files, after which .5 files per core applies but I've no evidence at that scale) and every file must be exactly the same size so the server can create 1 thread per file and use proportional fill.

Microsoft's own advice (this is from Microsoft staff presenting but doesn't make it into written documentation) is to fill the tempdb drive to 90% and have your tempdb at a fixed size ie figure out the max tempdb size you'll ever need, add a bit just in case & create your disk partition so that the size of the tempdb you calculated is 90% of the partition size then create the tempdb files to go in there. I would put a caveat on that, auto-grow is stupid - why would anyone with a live database want auto-grow to come along and trash their database performance while doing its thing & fragmenting your hard disk - plan properly! That said, I like the idea of planning in advance for file sizes but as long as disk space exists auto-grow I leave auto-grow turned on because it is better to take that performance hit rather than your database stop because it has no space. Naturally, you will be reporting regularly on database space used and free disk space to avoid issues won't you? While on "auto" things, there's no point in having auto create stats on and auto-shrink / auto-close are not appropriate for tempdb.

Big note, from SQL Server 2005, each time the instance restarts the tempdb is copied from MODEL so inherits some config options (eg ALLOW_SNAPSHOT_ISOLATION).

It is worth talking a little about the tempdb log file - yes it has one and is used. However, SQL Server 2005 has been optimised so that instead of the usual before & after log, some after logs are not saved so reducing the traffic and file size.

No real advice exists about the placement of the tempdb log file, I would say that if you are forced to keep your tempdb on the same drive as your data then at least place the log file with your other log files, otherwise the norm appears to be to keep the log file next to the tempdb datafiles.

Here is the Microsoft summary which is unfortunately woolly on some details (eg what type of drive to use) but specific on others http://technet.microsoft.com/en-us/library/cc966545.aspx

If you just want to move your tempdb, that's easy, just run the following query to identify what you already have in the way of tempdb files :
USE tempdb


GO

EXEC sp_helpfile

GO
and then run the following to move your files replacing tempname with the name of the file & c:\tempdb.mdf with your new path, similarly change the templog alter command. You will need to use the alter command for each file that you are moving

USE master

GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempname, FILENAME = 'c:\tempdb.mdf')
GO

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'C:\templog.ldf')

GO

Having done that you will need to restart the database and once successfully started and you have confirmed the changes are in place remember to delete the old tempdb files.