| Author |
Topic |
|
druer
Constraint Violating Yak Guru
314 Posts |
 Posted - 2006-05-02 : 11:02:04
|
| From reading everything I could find I'm 99.9% sure I know that the answer will be "NO", but I wanted to be 100.% sure that I'm not missing anything.One of my clients systems contains some very poorly written stored procedures that just clobber the TEMPDB in terms of writes and reads. There is very little updates to the data, just a ton of TEMPDB I/O. I already have the Data/TempDB and Log on separate disks. Size isn't an issue, just the volume of I/O. I was wondering if there is anyway to configure TEMPDB to split it's I/O to multiple files. So I don't need to split to a new filegroup just to have overflow when the first is full, but if there is a way that I could have a second filegroup that would be used simultaneously then I'd like to take advantage to split the I/O.Thanks for any advice,Dalton |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-02 : 11:30:29
|
| "Size isn't an issue, just the volume of I/O"RAM disk?Kristen |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-02 : 11:34:16
|
| I don't think the TempDB has gone over 4 gig so that could be a possibility.Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-02 : 13:53:51
|
| You can add data files to tempdb. We've got one tempdb that is spread across 8 data files. Each file is set at 200MB with 50MB file growth and unrestricted. They are all on the PRIMARY filegroup, but spread across 2 drives. Here is an associated thread:http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21769445.htmlEDIT: See step 3 in the resolution section for why we implemented 8 data files:http://support.microsoft.com/kb/328551/en-usWe had MS here for about 2 weeks as we had huge performance problems that we could not resolve. They are the ones who recommended this solution.Tara Kizeraka tduggan |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-02 : 14:46:27
|
| AWESOME. The short term performane monitor results seem to show now that the disk writes are split between the two drives where my 2 tempdb files are now. I've got 8 processors so I'll take it up to 8 tempdb files per the recommendations of 1 per processor. But I've only got 2 different RAID arrays, so I'll create the other 6 after hours and split them up as you did with 4 on each. Sorry for your pain in the past, and I sure am grateful that you passed this along and that I took a shot in the dark as right now the application software is on 2 different servers both of which have TEMPDB issues, and we are migrating to a new version where both packages will be on the same server. So the load will be going through the roof. Thanks so much to you and Kristen for following up on this one, (I'd say I owe you one but its more like 10 already, so if you ever need any "select * from tables" written just give me a hollar)Dalton |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-02 : 15:08:58
|
quote:
Originally posted by tkizer
You can add data files to tempdb. We've got one tempdb that is spread across 8 data files. Each file is set at 200MB with 50MB file growth and unrestricted. They are all on the PRIMARY filegroup, but spread across 2 drives. Here is an associated thread:http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21769445.htmlEDIT: See step 3 in the resolution section for why we implemented 8 data files:http://support.microsoft.com/kb/328551/en-usWe had MS here for about 2 weeks as we had huge performance problems that we could not resolve. They are the ones who recommended this solution.Tara Kizeraka tduggan
Did you go with 8 files because you have an 8-way system, or because you have a 4-way system with hyperthreading?CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-02 : 15:24:02
|
| We have a 4-way system with hyperthreading. He referred us to this:http://support.microsoft.com/kb/322385/en-usHere is the quote from MS:General recommendation for number of tempdb files is the same # as there are physical processors on the box.He then goes on to say:Set 'max degree of parallelism' to 4, which is the # of physical processors on the machineSo I'm confused as to why we used 8. Perhaps it was due to some testing that they performed. Note: Another DBA worked with MS on all of this.He goes into quite detail of how important that the data files be equal sized. It has to do with the algorithm used. He says to turn off autogrow as it can interfere with the algorithm. Looking at our environment, we didn't do that.We also added T1118 trace flag, which reduces contention.Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-02 : 17:37:38
|
| I noticed that the KB article said:"This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4."http://support.microsoft.com/kb/328551/en-usDid you upgrade it to SP4? Did that help?CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-02 : 17:40:17
|
| We have not upgraded production to sp4. It takes too long for QA to test it, so we've decided to just go to 2005.Tara Kizeraka tduggan |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-03 : 08:50:16
|
| I didn't think there was a single company in the world that actually followed the advice of a Quality Assurance group and made decisions based on quality. I started my career in QA and have had some 'painful' experiences with companies using the QA group in title only so it does my heart good to hear that. (Of course as an American I'll have to counter that positive effect with some really sinful pastry or chocolate or something, but that's another story.)Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-03 : 11:44:49
|
quote:
Originally posted by druer
I didn't think there was a single company in the world that actually followed the advice of a Quality Assurance group and made decisions based on quality. I started my career in QA and have had some 'painful' experiences with companies using the QA group in title only so it does my heart good to hear that. (Of course as an American I'll have to counter that positive effect with some really sinful pastry or chocolate or something, but that's another story.)Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them."
I don't think Tara exactly said they "followed the advice of a Quality Assurance group and made decisions based on quality"What she said was "It takes too long for QA to test it". Not exactly a whole-hearted endorsement ot the QA process.  CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-03 : 12:34:29
|
| Exactly. We can't go to production without QA testing it, so we're stuck as they do a full regression on our applications for each service pack. The full regression just takes too long. We aren't impacted by any bugs that SP4 fixed so there isn't a good reason for us to bother with it.Tara Kizeraka tduggan |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-03 : 13:43:00
|
| Most companies I've been at have said basically the same things ... "we don't have time to test so let's just go forward ... how bad could it be?" "Ooooooh no what happened ...... I sure wish we would have done more thorough testing."Maybe if Microsoft didn't rush through testing, you wouldn't be in a situation of wanting to rush your Quality Assurance departments to test all of the numerous hot fixes, patches and service packs coming out of Microsoft because of their quality issues. Maybe if there were higher quality standards at companies like GM, Ford they would sell more cars and have fewer recalls. But that would mean being slower to market, and we wouldn't want that. Things that make you go hmmmmmm?Hope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-03-21 : 12:23:55
|
| So is this "multiple data file to match processors" approach still required in 2005? |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2007-03-22 : 08:24:11
|
| This is an interesting read..I ahve a question with regards to the above posts. Is there any benefits of splitting Tempdb across files if we are on RAID 1? |
|
|
|
|
TEMPDB