March 3, 2009 at 2:06 pm
I have a temp db drive that is 292 GB and the server has 4 CPU's. How should I lay out the data and log files for temp db?
Someone told me to create 4 data files and 4 log files and turn autogrowth off and make them the max size. Is that best practice?
March 3, 2009 at 2:54 pm
tempdb is re-created each time SQL Server is started. I wouldn't create such large files since SQL Server will take a long time to restart since tempdb needs to be recreated.
I have created 4 - 20 gb data files and 1 - 10gb log file (quad core CPU) and SQL Server takes up to 8 minutes to start.
March 3, 2009 at 3:08 pm
SA (3/3/2009)
tempdb is re-created each time SQL Server is started. I wouldn't create such large files since SQL Server will take a long time to restart since tempdb needs to be recreated.I have created 4 - 20 gb data files and 1 - 10gb log file (quad core CPU) and SQL Server takes up to 8 minutes to start.
Hmmm...
- Aren't you using instant file initialization ?
- 8 minutes because of tempdb sounds a bit excessive ... but I could be wrong because it also depends on the hardware you have.
* Noel
March 3, 2009 at 3:13 pm
Since this is the first cluster, I'm still testing this setup. 8 minutes is definitely excessive.
This is Microsoft's recommendation, but what are other DBA's doing out there?
I haven't enabled instant file initialization, however any one see any issues with using it, especially since the disk isn't overwritten while it is allocated to these files.
March 3, 2009 at 3:16 pm
SA (3/3/2009)
Since this is the first cluster, I'm still testing this setup. 8 minutes is definitely excessive.This is Microsoft's recommendation, but what are other DBA's doing out there?
I haven't enabled instant file initialization, however any one see any issues with using it, especially since the disk isn't overwritten while it is allocated to these files.
Well I didn't know we were talking about a clustered instance. I am not even sure if it is OK in that case.
* Noel
March 3, 2009 at 4:01 pm
Better create 4 data files of equal size. Creating 4 log files doesn't seem to solve any purpose(Experts comment!).
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/WorkingWithTempDB.doc
MJ
March 3, 2009 at 5:46 pm
Microsoft recommends 1 data file per processor core, so that's how we set up tempdb. As Manu noted there's no real point in making more than 1 log file, at least not that we've seen to date. Our default is to make the log file around 25% of the total of the data files.
Our default is to make the total size of tempdb equal to around 75-80% of the dedicated tempdb drive (or drives if you've got the tempdb data and log files on separate drives).
For example, for an 100GB drive on a single quad-core processor our default tempdb would comprise 4 data files of 15GB and a single 15GB log file.
Leave AutoGrow turned on, but monitor the size of tempdb or drive usage. If AutoGrow is turned off and any tempdb file (data or log) fills then the statement causing this will fail (and others running in parallel may too), generally resulting in irate users and the associated angst. If AutoGrow is turned on and there's capacity on the drive the file(s) will grow, but your monitoring of the tempdb size and/or drive(s) usage will pick that up, allowing you time to investigate whether it's worth expanding the drive(s).
March 3, 2009 at 9:59 pm
Glen,
Are you using instant file initialization?
If not, how long does it take for SQL Server to restart or in case of a cluster to failover? Actually, the failover is instantaneous, its just SQL is not available while tempdb is being recreated. Are the recommendations different for stand-alone vs clusters?
March 3, 2009 at 11:03 pm
We have instant initiailsation turned on for all our servers (SQL2k5+ that is). I know there's a theoretical overhead for every write if this is on (is page "zeroed out"? no: zero it) but that's an in-memory operation that happens in a shorter timeframe than profiler can capture (ie. <0.003 seconds), or at least when I checked (once) shortly after SQL2k5 was released I didn't identify any slower writes and we haven't seen anything since that suggests it's a significant overhead.
SQL2k doesn't fully zero out tempdb either. I'm not certain exactly what it does under the covers, but it's sort of like a "truncate database": leave all the system objects and zero out only the file map pages. As a test I just tried it on one of our old servers (you'd think we'd decommission servers that aren't used any more;)) and it took 1.3 seconds from the "clearing tempdb" message until the "Analysis of tempdb complete" message (tempdb on this server comprises a 20GB data file and 10GB log file). I then expanded it to a 100GB data file (ie. increased the single data file by 80GB), which took over 30 minutes. Bouncing the SQL Server service after this resulted in a duration of 5.6 seconds between the two messages.
I don't have an SQL2k cluster to compare it on, but I'm certain it's the same behaviour on a cluster too.
March 5, 2009 at 7:17 am
I know the "experts" say to have one tempdb data file per core as a "best Practice". I've always personally disagreed with this. The bottom line, it all depends on your temp work load. You have to monitor your system over a period of time to determine your temp workload. You can have 4 tempdb files, each 20gig, but the max any app ever uses is 1gig. What a waste of space and resources, not to mention the maintenance and startup overhead. BTW, if you go with multiple files, the recommendation is for them to be on separate mount points. Placing them all on the same disk is useless. I believe the BOL recommendation is to set autogrow off.
my two-cents. good luck !
Tim White
March 5, 2009 at 10:02 am
i have huge tempdb files and have never noticed any startup overhead. i usually create a tempdb file that is the entire size of the HD which is around 136GB these days and leave it. on servers where there are no dedicated disks for tempdb it will be less.
March 5, 2009 at 7:57 pm
We currently use 16 15 Gb files for tempdb data on a 64 CPU active/passive cluster with 128 Gb of RAM and things start up/failover in less than 2 minutes. Here are a couple of links to check out. My guess is tha you'll need a trace flag (-T1118) to take the designed advantage of a configuration for multiple tempdb data files.
http://support.microsoft.com/kb/328551
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 27, 2009 at 5:03 pm
1 data file per cpu is definately a great idea, the reason for this is for parallel writes i.e. 4 cores, 4 threads, all write at the same time, instead of 1 data file, 4 cores, 1 thread. As for your maximum file size, if you had 1 20Gb file before and 20Gb was all you needed, there really is no need to create 4 data files of 20Gb, you would instead do something a bit more sensible, like 20Gb / 4 = 5 data files of 4Gb each. Of course, it could get even better if your data files are on seperate spindles! 🙂
Plus, doing it this way, it takes a lot less time to grow 4x4Gb files than 1 20Gb file 😉
The performance is similar to what happens when you use partitioning, 1 thread per partition.
I hope this clears things up for you,
Phil
Phil
Although all answers are replies, not all replies are answers.
Blog: http://philjax.wordpress.com
June 28, 2009 at 3:23 am
Phil Jackson (6/27/2009)
1 data file per cpu is definately a great idea, the reason for this is for parallel writes i.e. 4 cores, 4 threads, all write at the same time, instead of 1 data file, 4 cores, 1 thread.
It's a myth that SQL uses 1 thread per file to write. In fact, any thread can issue an IO request to any file.
The reason behind the split TempDB recommendation is to reduce contention on the allocation pages. If there's only one file, the first SGAM gets hit very hard and may cause blocking. Because of this, the usual recommendation is 1 file/2 cores or 1 file/core, to a maximum of 8 cores. They can all be on the same drive because the split is not for IO load.
If IO load is or does later become a problem, the files can be spread among multiple drives
I've never seen a recommendation for the size of those files other than 'as big as your app needs'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2009 at 6:02 am
Absolutely correct, there are no specific threads that write or read from disk. Perhaps I over simplified my explanation. The size of the data files I spoke of were not a recommendation, it was just an example.
However, the point I was trying to get across is that multiple threads will do the read/write to multiple files as the data is filled proportionally across the multiple data files in the tempdb. You will need to monitor the size of the tempdb to find out what size you will need, if your tempdb needs 20Gb and you have 4 cpus, don't throw 4 20Gb data files at it, because that really would be a waste of space, instead, divide the space you require by the number of cpus, in this example it would be 20/4. If you can have the data files on different disk spindles, that would be even better, because it will improve your I/O performance. In my own opinion, turning off autogrow would be a bad idea because the server would grind to a halt if the tempdb was full. You could say that if the tempdb was full, you have not being doing your monitoring very well, but monitoring doesn't catch the unexpected data load using bcp 😉
For the Microsoft white paper on how the tempdb works in SQL Server 2005 (by the MS SQL team), look here http://technet.microsoft.com/en-gb/library/cc966545.aspx
Phil
Although all answers are replies, not all replies are answers.
Blog: http://philjax.wordpress.com
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply