January 30, 2014 at 10:01 am
Building an answer file for unattended SQL Server installations. I have C:, D:, L:. and T: available.
I was thinking of
C: for OS
D: for system and user data files
L: for user and tempdb log files
T: for tempdb data files
Will I see better performance having tempdb logs on a different drive from tempdb data files even if that drive is shared with user db logs? Would it be better to put tempdb data and logs on T:? Alternately I was thinking of moving the system databases to T as well. They would share with tempdb but be separate from user databases files.
Thanks for any advice.
Howard
January 30, 2014 at 10:10 am
PHXHoward (1/30/2014)
Building an answer file for unattended SQL Server installations. I have C:, D:, L:. and T: available.I was thinking of
C: for OS
D: for system and user data files
L: for user and tempdb log files
T: for tempdb data files
Will I see better performance having tempdb logs on a different drive from tempdb data files even if that drive is shared with user db logs? Would it be better to put tempdb data and logs on T:? Alternately I was thinking of moving the system databases to T as well. They would share with tempdb but be separate from user databases files.
Thanks for any advice.
Howard
You cannot answer those questions with any probability of a good outcome without a LOT more information about the underlying constructs that make up your 4 drives.
How many spindles?
What RAID types?
What rpm?
What connectivity mechanism (iSCSI, FC, direct, etc) and what bandwidths?
If a SAN, numerous questions about paths, network type and config, WHAT ELSE IS SHARED ON SAME SPINDLES, etc.
It is my experience that many who ask this question often have just a few disks (few for me being less than 20ish). If that is the case I have had very good luck just lumping all drives into one RAID10 set with good connectivity and putting everything on that. This gives you the best spindle aggregation and avoids having a) significant space not really be used (tlog and tempdb drives, I'm looking at you) and b) CREATING IO bottlenecks by carving up few spindles into 2-6 disk groups. There are downsides obviously but like I said I have had/seen very good success with this at numerous clients in both OLTP and OLAP scenarios. YMMV.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 30, 2014 at 10:15 am
PHXHoward (1/30/2014)
Building an answer file for unattended SQL Server installations. I have C:, D:, L:. and T: available.I was thinking of
C: for OS
D: for system and user data files
L: for user and tempdb log files
T: for tempdb data files
Will I see better performance having tempdb logs on a different drive from tempdb data files even if that drive is shared with user db logs? Would it be better to put tempdb data and logs on T:? Alternately I was thinking of moving the system databases to T as well. They would share with tempdb but be separate from user databases files.
Thanks for any advice.
Howard
No, put the tempdb log on the same drive as the Tempdb data files, this is actually the default action during the SQL Server installer. The tempdb log is not used the same way as user database logs, so keep it away from them if you can.
What about your backup drive??
What size are the volumes you have specified??
Given the configuration above i would use
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2014 at 10:17 am
The provisioning team gives us VMs with SAN storage that is provided to them by the storage team. It is a big black box to me but thanks for giving me some questions that I can ask them. Maybe with the right information, my questions can be answered.
I was hoping that there were some general best practices about file separation.
January 30, 2014 at 10:18 am
We back up to data domain appliances that replicate the backups to another data center for DR emergencies so I was not factoring in backup space on each server.
January 30, 2014 at 10:23 am
Oh and as for sizes, server team gives us 50GB for C, and I usually request 250GB for data on D and 100GB for logs on L and 100GB for tempdb on T. I want to keep stacking databases until the io, CPU, or memory reach a good usage level.
Sometimes Spotlight on SQL Server tells me that max I/O wait is high so was looking for an optimal separation of data files.
January 30, 2014 at 10:27 am
PHXHoward (1/30/2014)
Oh and as for sizes, server team gives us 50GB for C, and I usually request 250GB for data on D and 100GB for logs on L and 100GB for tempdb on T. I want to keep stacking databases until the io, CPU, or memory reach a good usage level.Sometimes Spotlight on SQL Server tells me that max I/O wait is high so was looking for an optimal separation of data files.
If you're requiring 100Gb for tempdb when the data files have a max size of 250GB i would check the queries that run on the instance 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2014 at 10:28 am
I was also confused because Brent Ozar's checklist has an example of putting tempdb data on one drive and tempdb log on another so thought in my case, I'd move tempdb log to the L drive with my other tlogs.
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/
January 30, 2014 at 10:32 am
I picked 250GB for data and 100GB for logs and 100GB for tempdb so that I'd have room to grow. Don't recall seeing tempdb grow that big unless some application admin is doing something unusual.
January 30, 2014 at 11:51 am
If you can separate the tempdb log and data but always keep them separate from user data and logs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2014 at 5:43 pm
PHXHoward (1/30/2014)
Oh and as for sizes, server team gives us 50GB for C, and I usually request 250GB for data on D and 100GB for logs on L and 100GB for tempdb on T. I want to keep stacking databases until the io, CPU, or memory reach a good usage level.Sometimes Spotlight on SQL Server tells me that max I/O wait is high so was looking for an optimal separation of data files.
1) 50GB is NOT ENOUGH for your boot drive!!! EVERYTHING puts crap there, even if you install it on another disk, and SQL Server is BAD BAD about that. I would ask for 75GB at a bare minimum, and try HARD to get at least 100GB. Oh, and where is your page file going? If that is to be on the C drive you best take that into account too!
2) IO waits are almost NEVER the result of a lack of separation of files (unless you left your database file growth increments at their defaults - you DON'T do THAT, RIGHT??). 😉 The IO performance problem is about a) needing to tune the database and b) that "black box" your SQL Server is asking for IO from.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply