A little advice before you rush out and add files
I still see a lot of blogs saying that they still see a lot of blogs, threads, questions, etc. about tempdb database configuration now maybe more than ever. (Liked that one, didn’t you?). As a consultant I have the opportunity to look at many SQL Servers, most of which have tempdb 1) configured incorrectly, 2) overconfigured, or 3) anything in between. As a tip, it might be a good idea to follow the idiom of “If it ain’t broke – don’t fix it!” My approach to this conundrum is to first…check to see how tempdb is currently performing. (On a new server, sure, go ahead and size it according to best practice).
What I do for existing SQL Servers is a simple comparison of tempdb transactions – the activity that is happening in tempdb – to the total number of transactions per second on the server. Using the SQL Server Database counters, take the transactions per second in tempdb and divide this by the _total transactions (all databases) – transactions/sec from tempdb. This will give you a rough estimate of the percentage of transactions are touching tempdb. Safe to say, if it’s a large number, throw some more files at it and test for increased throughput. (I have included some inks below for further reading. And see BOL as well). If it’s a small percentage, try leaving it alone. I just looked at three production servers within the past 24 hours and none of them needed this fix. Imagine that.
SQL Server:Databases – Transactions/sec tempdb
/ (SQL Server:Databases Transactions/sec _Total – SQL Server: Databases Transactions/sec tempdb)
Bottom line – use some common sense here by first determining if the databases on the database server are tempdb intensive. You’ll find that SQL Server 2005 and SQL Server 2008 both use tempdb a bit more than SQL 2000. If they are, they’re likely a good candidate for you to create a file per processor/core. If not, leave it as-is.
Lee
--------------------------
Saw a guy once create 39 filegroups for a SQL Server. Nothing like an over engineered database
A few links…
http://blogs.msdn.com/psssql/archive/2009/06/04/sql-server-tempdb-number-of-files-the-raw-truth.aspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
http://blogs.technet.com/josebda/archive/2009/04/03/sql-server-2008-tempdb.aspx
215e6f80-77ab-4a53-8d7c-c3b3d688c0b6|0|.0