| Author |
Topic |
|
shifty1981
Starting Member
20 Posts |
 Posted - 2010-11-26 : 15:01:56
|
| I know all the best practices out there say the tempdb should be on it's own physical drive(s) and should have multiple files. However when checking ave queue depth for the volume it's on, they are well below the max we should see (.05 usually on a RAID1 set). It would appear that we should be fine leaving the tempdb on the boot volume which also has a very low queue depth value during our peak hours. our db is around 500 GB in size the tempdb is around 12GB in size before it shrinks during a maintenance window.What performance counters should I check to see whether dedicating two spare drives to the tempdb is worth it when perhaps a table/index in our database might benefit more from being on it's own spindle?Also when it says to break the tempdb into separate files does it mean then leave them all on the same physical drive? with 2 quad core processors, who has the extra disks to dedicate to separate tempdb files?Thanks! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-11-29 : 09:32:49
|
| During peak times our 1.4TB db averages over a 1 million trans/sec. Yeah, it's high. |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-29 : 09:37:39
|
| >> the tempdb is around 12GB in size before it shrinks during a maintenance windowWhy are you shrinking it? Sounds like it's just going to reduce performance when it grows again.Are you using table variables rather than temp tables where possible?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-11-29 : 09:51:48
|
| I'm not sure what the developers are using (temp variables vs temp tables), but my understanding is that we keep track of customer data with multiple revisions back and when they expire we mark for removal or remove the rows with that data. Then periodically they run a job that rebuilds the database with the non-expired rows, essentially shrinking it. It's not really temp data in the traditional sense since each customer chooses how many revisions they want to keep (for a cost of course). If we don't "shrink" the database we will grow forever with cost and performance being a downer. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-29 : 09:59:40
|
| >> If we don't "shrink" the database we will grow forever with cost and performance being a downer.tempdb?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-11-29 : 10:05:02
|
| tempdb typically is around 3GB to 24 GB depending on the jobs we're running. Every once in a while it screems up to use all the disk space, but that's user error for running to heavy jobs on the DB at the same time. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-01 : 18:57:32
|
"before it shrinks during a maintenance window" (UNless you mean that SQL is restarted, in which case TEMPDB will revert to its configured initial size - but if it immediate regrows again you should change the "initial size" to be whatever the "normal running size" is."Also when it says to break the tempdb into separate files does it mean then leave them all on the same physical drive?"http://www.sqlskills.com/BLOGS/PAUL/category/tempdb.aspx#p1http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx |
|
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-12-02 : 09:20:35
|
quote:
Originally posted by Kristen
"before it shrinks during a maintenance window" (UNless you mean that SQL is restarted, in which case TEMPDB will revert to its configured initial size - but if it immediate regrows again you should change the "initial size" to be whatever the "normal running size" is."Also when it says to break the tempdb into separate files does it mean then leave them all on the same physical drive?"http://www.sqlskills.com/BLOGS/PAUL/category/tempdb.aspx#p1http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx
Thanks. Yes I meant during a restart of SQL services (either just the service or during a reboot). And you are correct about sizing it properly. As for those articles, thanks. I saw them on another site and have concluded that I don't think we need to split ours up just yet. What I also liked about it is that it says there are two different things to look at to determine if they can be on the same disk or should be separated. thanks for your help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 09:26:24
|
"Yes I meant during a restart of SQL services"That's a relief. Shrinking it whilst it is running can cause corruption of other databases  |
|
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-12-02 : 09:38:00
|
| what consequences are there if the tempdb disk runs out of space? is it unsafe to put it on it's own single disk? or in a 2 disk raid 0 if we need the space? we've seen the tempdb grow uncontrollably and it almost filled up a disk. we're using sql server 2008 enterprise r2 if that makes a difference. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-12-02 : 11:21:19
|
quote:
Originally posted by shifty1981
During peak times our 1.4TB db averages over a 1 million trans/sec. Yeah, it's high.
1 million trans/sec? I'm a little skeptical of a number that high. Maybe you mean 1 million transactions per hour?The top tpmC result from the TPC benchmark is 10,366,254 transactions/minute or 172,771 transactions/second on a DB2 system that cost $14,305,430. The top SQL Server tpmC result is 1,807,347 transactions/minute or 30,122 transactions/second on a system that cost $885,600.http://www.tpc.org/tpcc/results/tpcc_perf_results.aspCODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 11:38:51
|
| "what consequences are there if the tempdb disk runs out of space?"I would expect the queries that are running, which are using TEMPDB, to fail. If all your transactions are properly, and flawlessly!, bullet-proof then things will rollback with no collateral damage ... never seems to happen quite like that for us!"is it unsafe to put it on it's own single disk? or in a 2 disk raid 0 if we need the space?"I can't see a problem with that. RAID better than non-RAID - to avoid single-point-of-failure - but that's my only thought." we've seen the tempdb grow uncontrollably and it almost filled up a disk"Best to find the cause of that then and try to cure it (rather than throw more hardware at it in the first instance IMHO). |
|
|
|
shifty1981
Starting Member
20 Posts |
Posted - 2010-12-02 : 11:43:15
|
| thanks for the replies. we do try to find the root cause of issues, but are experienced enough to realize that there are bound to be issues hiding that will come up some day so we just want to plan for the worst and hope for the best! :-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-02 : 11:45:06
|
| Yeah, I know the feeling ... |
|
|
|
|
is tempdb really a bottleneck?