Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Tempdb log file location and Filegroup optimizations Expand / Collapse
Author
Message
Posted Monday, August 06, 2012 10:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:58 PM
Points: 52, Visits: 109
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.
Post #1340755
Posted Monday, August 06, 2012 11:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1340763
Posted Monday, August 06, 2012 4:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:58 PM
Points: 52, Visits: 109
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.
Post #1340929
Posted Monday, August 06, 2012 11:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:26 PM
Points: 316, Visits: 1,497
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.)
Post #1341010
Posted Tuesday, August 07, 2012 6:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #1341183
Posted Tuesday, August 07, 2012 11:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:58 PM
Points: 52, Visits: 109
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.
Post #1341439
Posted Tuesday, August 07, 2012 12:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:29 AM
Points: 1,592, Visits: 1,488
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




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1341467
Posted Tuesday, August 07, 2012 1:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 5,958, Visits: 12,839
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"
Post #1341519
Posted Tuesday, August 07, 2012 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:58 PM
Points: 52, Visits: 109
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.
Post #1341524
Posted Tuesday, August 07, 2012 1:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 2:58 PM
Points: 52, Visits: 109
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.
Post #1341527
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse