August 7, 2012 at 2:35 pm
Perry Whittle (8/7/2012)
Hernán Rojas (8/6/2012)
The tempdb can only have 1 filegroup so no you can't put 8 files into 2 different filegroups. The recommendation now is files equal to 1\4 - 1\2 the number of cores.
That's a dangerous recommendation as it assumes that the person knows how to recognize tempdb contention and is monitoring for it. I go a lot further into the recommendation for this in my webcast and whitepaper.
Perry Whittle (8/7/2012)
Hernán Rojas (8/6/2012)
However, know that in later versions of SQL Server the pages that suffered from latch contention are now cached, as such in most systems you will almost certainly never encounter any contention.
Incorrect. How the allocation pages are handled has not changed. What has changed is that a certain number of temporary objects are cached and reused by future temp objects so they do not need to be reallocated. This greatly reduces the chance of contention, but it by no means makes it "almost certainly never encounter any contention". You still need to take steps to proactively prevent tempdb contention.
August 7, 2012 at 2:44 pm
Perry Whittle (8/7/2012)
did you understand this?Perry Whittle (8/7/2012)
The tempdb can only have 1 filegroup so no you can't put 8 files into 2 different filegroups. The recommendation now is files equal to 1\4 - 1\2 the number of cores.i.e. 8 cores = 2 - 4 files
24 cores = 6 - 12 files
However, know that in later versions of SQL Server the pages that suffered from latch contention are now cached, as such in most systems you will almost certainly never encounter any contention.
Yes, I did Perry. I understood very well that you can't create file groups for tempdb, and that's very likely that nowodays you don't need and exact one to one count between the CPUs (cores) and tempdb files. Maybe a number about 1/2 of CPUs could do the job (as an starting point), and of course, you need to take metrics to confirm or adjust the number of data files for tempdb.
Robert Davis (8/7/2012)
To get real performance benefits, you need each LUN to be on completely separate disks. Bad disk/SAN design can very easily negate the benefits of multiple LUNs.If the SAN/disks are configured correctly, then I would say that a SQL instance needs at least 4 dedicated LUNs/drives not counting the system/installation drive(s):
1. Database data files
2. Database log files
3. Tempdb files
4. Backup files
OK thank you very much.
Regarding the "VirtualDisk"--Volume mapping used in my SAN, I had learned that I need to use three (or four) different "VirtualDisk" in order to carve each SQL Server volume from a different VirtualDisk to get the performance benefits of multiple LUNs.
Finally It's clear also that probably the best starting point is to have at least 4 dedicated LUNs/drives not counting the system/installation drive(s):
1. Database data files
2. Database log files
3. Tempdb files (data and log files together, in order to give tempdb independence of the user files)
4. Backup files
This is amazing to count with your help.
August 7, 2012 at 2:47 pm
Robert Davis (8/7/2012)
Perry Whittle (8/7/2012)
Hernán Rojas (8/6/2012)
The tempdb can only have 1 filegroup so no you can't put 8 files into 2 different filegroups. The recommendation now is files equal to 1\4 - 1\2 the number of cores.That's a dangerous recommendation as it assumes that the person knows how to recognize tempdb contention and is monitoring for it. I go a lot further into the recommendation for this in my webcast and whitepaper.
Maybe, take it up with Ms its their recommendation. In my opinoin its not as dangerous as the previous 1-1 recommendation too many files can be just as much trouble as too few
Robert Davis (8/7/2012)
Perry Whittle (8/7/2012)
Hernán Rojas (8/6/2012)
However, know that in later versions of SQL Server the pages that suffered from latch contention are now cached, as such in most systems you will almost certainly never encounter any contention.Incorrect. How the allocation pages are handled has not changed. What has changed is that a certain number of temporary objects are cached and reused by future temp objects so they do not need to be reallocated. This greatly reduces the chance of contention, but it by no means makes it "almost certainly never encounter any contention". You still need to take steps to proactively prevent tempdb contention.
Really, i'll stand corrected if you can link the relevant information. My main point is that tempdb can only have 1 filegroup not mutliple
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2012 at 2:57 pm
Microsoft still recommends 1 data file per CPU, unfortunately. And they will continue to recommend this as long as they are able to produce tempdb contention with less than 1:1. They have tested it with 64 CPU server and were able to generate enough contention that it took 64 data files to alleviate it. Sorry, I can't provide links to that as was internal MSFT only documentation. I can discuss the results, but I can't link to it as it's not reachable from the internet.
There is definitely overhead with having more files, but it is neglible compared to the impact of tempdb contention if it occurs.
Lots of people recommend 1 data file for every 2 or 4 data files, myself included, but I include the caveat of only if you know how to identify tempdb contention if it occurs, know how to fix it, and are okay with the impact of it for the length of time it takes for it to be detected and someone intervene. Otherwise, just use 1 data file per CPU and you won't have to deal with it.
August 7, 2012 at 3:16 pm
@robert sure, no problem
@Hernan shared storage is never good for performance, using multiple logical drives you'll get logical separation but that's as far as it goes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2012 at 3:24 pm
Perry Whittle (8/7/2012)
@Hernan shared storage is never good for performance, using multiple logical drives you'll get logical separation but that's as far as it goes
Amen to that. And in my opinion, this is a much bigger misconception.
August 8, 2012 at 6:30 am
Robert Davis (8/7/2012)
GSquared (8/6/2012)
The whole idea is to maximize bandwidth that can be used to read/write in tempdb.....
Same for multiple files. It will often create zero advantage to have multiple tempdb data files, if they're all on the same disk. Why? Because it'll still have to take turns reading and writing. Hard drives read and write sequentially. They only do one thing at a time.
This information is very, very wrong.
The reason it is recommended to use multiple data files for tempdb has absolutely nothing to do with bandwidth. It is about alleviating contention on the allocation pages. for more details:
Read my tempdb whitepaper: http://www.idera.com/Action/RegisterWP.aspx?WPID=37
Watch my tempdb webcast: http://www.idera.com/Events/RegisterWC.aspx?EventID=208
Except for the minor detail that I've actually proved, in a production environment, that multiple tempdb datafiles doesn't always result in improved speed on tempdb actions, I'd agree with you. So, other than the fact that you're wrong, you're right.
Is that the part you're disagreeing with? Are you actually claiming that multiple tempdb files, all on the same platters, will always speed up the server?
Or are you disagreeing with the part about HDDs doing sequential I/O? You included it in the quote from my post, but I'm assuming it's not the relevant portion.
Yes, multiple files will often speed tempdb up. But not always. That's all the point I'm making.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2012 at 8:54 am
The reason you use multiple data files for tempdb is not for disk bandwidth. It is to avoid contention on the allocation pages. Saying that there is no benefit putting multiple files ont he same drive is wrong because there is tremendous benefit from avoiding tempdb contention.
August 8, 2012 at 5:10 pm
Allocation contention on PFS,GAM,SGAM pages of the tempdb data file should be monitored during the pick load time. just in case of any contention on those pages additional tempdb files should be added. 1/4 to 1/2 of the number of CPU cores is most of the times a better approach.
Pooyan
August 10, 2012 at 6:19 am
Robert Davis (8/8/2012)
The reason you use multiple data files for tempdb is not for disk bandwidth. It is to avoid contention on the allocation pages. Saying that there is no benefit putting multiple files ont he same drive is wrong because there is tremendous benefit from avoiding tempdb contention.
Did you actually read what I actually wrote? Nope. Neither did the other guy.
I never said there's no benefit to multiple tempdb files on the same drive. You guys are arguing with your own imagination.
I said there's often no advantage to multiple files on the same drive. Not "there is never any advantage". This is based on (a) personal experience in dozens of environments, (b) extensive research on other DBAs' experience in the situation.
There can be a number of reasons for this. A common one is that the disk I/O is saturated before the allocation issue comes up. No matter how much allocation contention you avoid, if disk I/O is saturated, I/O processes will be slow. Another common one is that modern SAN hardware, properly configured, with the right amount of cache on it, eliminates the bottleneck on allocation completely. Yet another common one is servers where tempdb isn't being hit hard enough for optimization on it to make a measurable difference in overall performance in the first place.
Or are you contending that multi-channel I/O is slower than putting all the files on one disk? 4 files on 4 disks on different I/O channels will, under heavy-load, be faster than 4 files on 1 disk on 1 I/O channel. Saying it won't be is more than a little odd from my perspective. I don't think that's what's being asserted here, but I can't be sure, because the responses to what I wrote have been so non-sequitur it's hard to tell what's being responded to.
As for tempdb's log file, the best way to do that is a separate disk with a separate I/O channel as well. You can't do PiT recovery on tempdb (and shouldn't ever need to, for that matter), so separating the log and data files for tempdb is about performance, not about DR. Other databases, it's mostly about DR, but it can be a performance boost as well, if done correctly.
All I asserted is that multiple I/O channels is best, if tempdb optimization is needed. I don't get why that's being disagreed with.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 10, 2012 at 7:45 am
GSquared (8/10/2012)
...Another common one is that modern SAN hardware, properly configured, with the right amount of cache on it, eliminates the bottleneck on allocation completely.
Not True. The allocation contention in tempdb is due to in-memory contention, nothing to do with IO. (Indicator for adding additional files to tempdb is excessive PAGELATCH_% waits on GAM and SGAM pages, not PAGEIOLATCH_%)
August 10, 2012 at 8:01 am
GSquared (8/10/2012)
All I asserted is that multiple I/O channels is best, if tempdb optimization is needed. I don't get why that's being disagreed with.
Late, and I have not read everything.
There are two distinct reasons for multiple files in TempDB.
One is IO contention and for that you are completely right, multiple IO independent channels is what's needed there.
The second is contention on the in-memory allocation pages. That is alleviated just by adding more files, doesn't matter where they are because all you want is more allocation pages for the round-robin algorithm to be able to hit. They'll be in memory just about all the time, so the IO subsystem is immaterial there.
Of course, then you get the cases which need both. Those are fun.
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
August 13, 2012 at 6:39 am
GilaMonster (8/10/2012)
GSquared (8/10/2012)
All I asserted is that multiple I/O channels is best, if tempdb optimization is needed. I don't get why that's being disagreed with.Late, and I have not read everything.
There are two distinct reasons for multiple files in TempDB.
One is IO contention and for that you are completely right, multiple IO independent channels is what's needed there.
The second is contention on the in-memory allocation pages. That is alleviated just by adding more files, doesn't matter where they are because all you want is more allocation pages for the round-robin algorithm to be able to hit. They'll be in memory just about all the time, so the IO subsystem is immaterial there.
Of course, then you get the cases which need both. Those are fun.
Completely agree.
Then there are the ones where tempdb configuration doesn't matter in the slightest, because recursive UDFs with nested cursors in them render the performance at that level immaterial. Gotta keep it all in perspective - fixing tempdb is generally much lower priority than optimizing data architecture and CRUD code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 13, 2012 at 8:21 am
GSquared (8/13/2012)
GilaMonster (8/10/2012)
GSquared (8/10/2012)
All I asserted is that multiple I/O channels is best, if tempdb optimization is needed. I don't get why that's being disagreed with.Late, and I have not read everything.
There are two distinct reasons for multiple files in TempDB.
One is IO contention and for that you are completely right, multiple IO independent channels is what's needed there.
The second is contention on the in-memory allocation pages. That is alleviated just by adding more files, doesn't matter where they are because all you want is more allocation pages for the round-robin algorithm to be able to hit. They'll be in memory just about all the time, so the IO subsystem is immaterial there.
Of course, then you get the cases which need both. Those are fun.
Completely agree.
Then there are the ones where tempdb configuration doesn't matter in the slightest, because recursive UDFs with nested cursors in them render the performance at that level immaterial. Gotta keep it all in perspective - fixing tempdb is generally much lower priority than optimizing data architecture and CRUD code.
in scenarios such as this fixing tempdb is a sticky plaster over the real problem 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 20, 2012 at 2:33 am
Robert Davis (8/7/2012)
Read my tempdb whitepaper: http://www.idera.com/Action/RegisterWP.aspx?WPID=37
its not working
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply