September 19, 2009 at 7:17 pm
Hi,
We have a Separate Dedicated Drive (T:\) for TempDb on SAN. Both tempDb.mdf & tempDblog.ldf are on Drive T:\
Is this correct configuration OR still I need to place TemDblog.ldf file on to a different drive other than T:\ ?
Please advice..
September 19, 2009 at 9:29 pm
It completely depends upon your system and how much information is being passed through the tempdb. Generally I would say you are ok with them on the same drive.
As an example, I have mine on the same drive. I have 3 production servers all with 30+ GB tempdb .mdf files running on SCSI 15000 rpm disk and never have contention issues.
Fraggle
September 21, 2009 at 1:16 am
gmamata7 (9/19/2009)
Hi,We have a Separate Dedicated Drive (T:\) for TempDb on SAN. Both tempDb.mdf & tempDblog.ldf are on Drive T:
Is this correct configuration OR still I need to place TemDblog.ldf file on to a different drive other than T:\ ?
Please advice..
I would keep them together, splitting the data files and log files is more for user databases. the only thing i would recommend, is having one data file per cpu for your tempdb. so if you have a 8 processor box, you would have 1 mdf and 7 ndf and 1 log file for your tempdb database.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 7:37 pm
Interesting SilverFox. I have never heard that. What is the purpose of that? What type of performance improvements do you see when you do that? Are you putting each mdf/ndf on the same disk? Do you ever run into contention issues with doing that?
Fraggle
September 21, 2009 at 10:42 pm
assigning a temp datafile, to each core (given they were created correctly, all the same size) will avoid contention for the main tempdb usage, major increase in performance.
There is a algorithm involved to "spread the load" between the data files (all seen as the tempdb).
Implemented it myself a few months ago, and never looked back 🙂
I think MS recommends it as a BP.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 22, 2009 at 1:10 am
it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 22, 2009 at 1:45 am
September 22, 2009 at 11:10 am
Here's a link to a page containing details on how to identify tempdb contention, along with the suggestion of creating one data file per CPU:
Cheers
Chris
September 22, 2009 at 11:28 am
Silverfox (9/22/2009)
it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.
Correct, but for the best performance it is better to stop autogrow and make each TempDB Data file equal = (D/N)-P, where D is TempDB_Data disk size, N is number of CPUs, P is parameter, which allows you avoid a space issue (usually =1Gb). 🙂 So, files never grow, and initial size is MAX size.
In this configuration you will jump in performance because TempDB doesn't need any time for space allocation.
September 22, 2009 at 6:21 pm
Sorry, but you have not given us enough information to advise you. How many spindles underly the proposed tempdb drive? Are these spindles shared for any other IO load?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 23, 2009 at 1:27 am
Sergey Vavinskiy (9/22/2009)
Silverfox (9/22/2009)
it is a best practise, we have 16 cpu machines here, tempdb is split, 1 mdf and 15 ndf. size has to be the same, I normally set them to 512mb each, autogrowth 64mb unrestricted.Correct, but for the best performance it is better to stop autogrow and make each TempDB Data file equal = (D/N)-P, where D is TempDB_Data disk size, N is number of CPUs, P is parameter, which allows you avoid a space issue (usually =1Gb). 🙂 So, files never grow, and initial size is MAX size.
In this configuration you will jump in performance because TempDB doesn't need any time for space allocation.
That is the exception rather than the rule, if you size tempdb correctly it will not be an issue.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 8:50 pm
Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?
Fraggle
September 23, 2009 at 9:40 pm
Fraggle-805517 (9/23/2009)
Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?Fraggle
If you have too many files on too few spindles you WILL get performance decrease.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 24, 2009 at 2:16 am
Fraggle-805517 (9/23/2009)
Something I haven't seen anyone discuss is do you need to put each of these on a separate drive or do you still get the performance increase if you keep them on the same drive. Does anyone actually get IO performance decreases with this?Fraggle
I havent worked at a company that implemented it, but you would in theory get a bigger performance increase. as each file would be on a dedicated drive. and yes you do get a performance increase, even if you use multiple files and they are on the same drive, but the increase would be more if they were on separate drives.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply