August 6, 2012 at 10:46 am
I've been reading articles about tempdb optimizations and I already understand a lot better the different ways to archive it. I read (for example) the following links:
Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
Recommendations to reduce allocation contention in SQL Server tempdb database
http://support.microsoft.com/kb/328551
However, there are a couple of questions I couldn't answer to myself with just the reading. The best practices read as follow:
1. "Put the tempdb database on disks that differ from those that are used by user databases."
But, the "tempdb database" is the datafiles and the log file, Is it enough to have a dedicated disk for the whole tempdb or is necesary to have two disk, in order to split the datafiles and the log files??
2. "Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."
Regarding this, all the additional database files created in order to improve the disk bandwidth for the tempdb should be placed on the primary filegroup, or should I create additional filesgroups?? For example, in a 16 CPUs server I decided to use 8 datafiles, should I have the primary filegroup with 8 datafiles or could be better to have two filegroups with 4 files each??
Thank you in advance for your recommendations.
Hernan.
August 6, 2012 at 11:08 am
The whole idea is to maximize bandwidth that can be used to read/write in tempdb. Keep that in mind on everything you read about it, and it'll help.
For example, your question about 1 disk for data, 1 for logs. If they can be on separate I/O channels, yes, it makes sense, and will usually get better performance. Why? Because it can write to both at the same time without having to take turns going through a single connection.
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.
Also, on systems with adequate RAM and a good buffer on a SAN, disk-optimization for tempdb may not matter at all. Why? Because everything is going to RAM and to the SAN cache (which is effectively RAM in terms of speed), and thus actual disk I/O sometimes doesn't even matter on those servers.
Another thing to keep in mind is that this kind of optimization is only necessary on servers that hit tempdb hard. That typically means either lots of large datasets being joined, sorted, etc., in tempdb, or lots of temp tables being used by lots of queries. You should make sure that's an actual bottleneck before you spend a lot of time (and money) on optimizing tempdb.
If, for example, most of your slow queries are slow because of poor index use, then optimizing tempdb won't get you very far compared to optimizing the queries and indexes. If you see a lot of CPU wait-states and very few I/O wait-states, in your server stats, then tempdb isn't the problem there either. Lots of cursors? Not likely to be fixed via tempdb. And so on through the list of SQL Server optimization steps.
But if you do find you need to optimize tempdb, just keep in mind that the key to it is parallel I/O channels, and the number of files is just a piece of that.
- 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 6, 2012 at 4:06 pm
Thank you GSquared for your answer. I would like to ask something else:
If you only have 3 disk for organize your databases phisical desing, which option would you choose between the following configurations?
a. One disk for database files. one disk for log files. one disk for tempdb (data and log files). or...
b. One disk for database files. one disk for log files including the tempdb log file. One disk for tempdb datafiles.
Thank you for your help.
Hernan.
August 6, 2012 at 11:39 pm
Hernán Rojas (8/6/2012)
Thank you GSquared for your answer. I would like to ask something else:If you only have 3 disk for organize your databases phisical desing, which option would you choose between the following configurations?
a. One disk for database files. one disk for log files. one disk for tempdb (data and log files). or...
b. One disk for database files. one disk for log files including the tempdb log file. One disk for tempdb datafiles.
Thank you for your help.
Hernan.
No single right answer. What kinds of disks are these? SAN or Local? RAID level? SSD vs SATA vs SAS? How much of a load does your application put on tempdb?
It's trivial to move tempdb data/log files and without actual performance metrics from your production environment, I'd go with option C ( Monitor performance until you have proof that you need to worry about where tempdb lives.)
August 7, 2012 at 6:11 am
I guess it would depend on what you mean by "3 disks". If I only had access to three physical disks, I'd set it all up as a single RAID-5 array and put everything on there. Performance will suffer, but it'll protect the data better than any sort of non-RAID setup. I'd rather have a slow but safe server than a slightly faster one that's liable to lose all my databases if a disk fails.
If you mean I have three allocated RAID-protected arrays on a SAN, and can't have that reallocated or whatever, then (b) sounds okay. I'd have to test and monitor for I/O bottlenecks, but it'd probably work okay. Tempdb will be slightly slower than I'd like, but on many systems with a properly configured SAN, you'll never see that in terms of actual performance metrics in the application.
- 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 7, 2012 at 11:36 am
Thank you for your answers to my questions.
I understood you about what "3 disk" means... well, in this context I wanted to mean three allocated RAID-protected arrays on a SAN (3 independent already protected LUNs or Volumens). So, according to GSquared could be better the option b:
b. One LUN for database files. one LUN for log files including the tempdb log file. One LUN for tempdb data files.
I know that is better to take a baseline before to worry about to where to put the tempdb files on a production server, but what about the best practice to install a new production server. I would like to take the best decision about this. Regarding to capture performance metrics from your production environment about tempdb contention and performance, which ones are the best metrics and performance counters to use in order to decide if the tempdb is or not suffering to much workload??
Thank you.
August 7, 2012 at 12:17 pm
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
August 7, 2012 at 1:51 pm
Hernán Rojas (8/6/2012)
1. "Put the tempdb database on disks that differ from those that are used by user databases."But, the "tempdb database" is the datafiles and the log file, Is it enough to have a dedicated disk for the whole tempdb or is necesary to have two disk, in order to split the datafiles and the log files??
Ideally put the tempdb data files and log file on separate drives from each other and evrything else. This isnt always possible so the minimum you should achieve is to separate the tempdb files (data and log) to their own drive together. The main point here is to get them away from the user databases.
Hernán Rojas (8/6/2012)
2. "Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary. Note that a dual-core CPU is considered to be two CPUs."Regarding this, all the additional database files created in order to improve the disk bandwidth for the tempdb should be placed on the primary filegroup, or should I create additional filesgroups?? For example, in a 16 CPUs server I decided to use 8 datafiles, should I have the primary filegroup with 8 datafiles or could be better to have two filegroups with 4 files each??
Thank you in advance for your recommendations.
Hernan.
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2012 at 1:53 pm
Thank you Robert, I'd read your article "DEMYSTIFY TEMPDB PERFORMANCE AND MANAGEABILITY", this is very clear and very useful.
According to the article, it seems like you would use one LUN for the whole tempdb database (option a, in the description below), but let me ask you which is your recommendation regarding the following question:
If you have 3 LUNs (SAN and RAID protected) for organize your databases phisical desing, which option would you choose between the following configurations?
a. One LUN for database files. One LUN for log files. One LUN for tempdb (data and log files). or...
b. One LUN for database files. One LUN for log files including the tempdb log file. One LUN for tempdb data files.
c. One LUN for database files including tempdb data files. One LUN for log files. One LUN for tempdb log file.
Thank you for your help.
August 7, 2012 at 1:58 pm
Thank you Perry.
I agree with you, I think. It could be better to give full independence to the tempdb from the user database and log files.
And again, thank you for your explanation about creation of additional file groups on the tempdb. During my research I found this http://technet.microsoft.com/en-us/library/cc966545.aspx, which reads as follow: "Only one file group in tempdb is allowed for data and one file group for logs. " Which is the same you explained to me.
Thank you everyone for your comments and help about this.
Hernan.
August 7, 2012 at 1:59 pm
Hernán Rojas (8/7/2012)
If you have 3 LUNs (SAN and RAID protected) for organize your databases phisical desing, which option would you choose between the following configurations?a. One LUN for database files. One LUN for log files. One LUN for tempdb (data and log files). or...
b. One LUN for database files. One LUN for log files including the tempdb log file. One LUN for tempdb data files.
c. One LUN for database files including tempdb data files. One LUN for log files. One LUN for tempdb log file.
Thank you for your help.
Depends, are all the LUNs carved from the same set of disks??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2012 at 2:18 pm
Mmm ok. Well, what I have in my SAN solution is a "Virtual disk" composed by a set of disk (about 10 disk I think) in RAID 5 configuration + a hot spare disk. All the three LUNs (or volumes) are created from this virtual disk and then presented to the SQL Server. Each volume can has a different size.
Hernan.
August 7, 2012 at 2:23 pm
Hernán Rojas (8/7/2012)
Mmm ok. Well, what I have in my SAN solution is a "Virtual disk" composed by a set of disk (about 10 disk I think) in RAID 5 configuration + a hot spare disk. All the three LUNs (or volumes) are created from this virtual disk and then presented to the SQL Server. Each volume can has a different size.Hernan.
pointless even separating the files onto separate logical drives in the first place then. Ok for management purposes but don't expect huge performance gains
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 7, 2012 at 2:26 pm
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
August 7, 2012 at 2:28 pm
did you understand this?
Hernán Rojas (8/6/2012)
should I have the primary filegroup with 8 datafiles or could be better to have two filegroups with 4 files each??
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply