December 2, 2008 at 4:20 am
Setting up a brand new solution with lots of data transfer and reporting, and I want to get it perfect for once!
How would you go about having the drives partitioned?
SQL install onto C:
Primary Data files to D:
Secondary to E:
Logs to F:
Would that seem good from a basic perspective?
December 2, 2008 at 6:44 am
You say partitioned. How many physical drives are we talking here? Raid? SAN?
If you want perfect...
Log file on RAID 10 dedicated array
Data file on RAID 10 (preferably) RAID 5 (acceptable) dedicated array
TempDB on RAID 10 dedicated array
RAID 5/1 array for backups
OS/Swap file elsewhere.
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
December 2, 2008 at 9:02 am
I have a 10 disk RAID 5 setup. Would performance be better splitting to 2 arrays of 5 and logs and data split between the 2?
December 2, 2008 at 9:07 am
From a performance perspective it is going to depend on the utilization of the databases however, if that is the disk availability that you have I would recommend splitting it for a couple of reasons, first being that the log writes are sequential which dictates a different spindle usage pattern and secondly having a RAID 5 array with many disks can be somewhat scary as you have a greater potential failure of more than 1 disk which would result in loss of data. You should validate the second point but I believe this to be true.
So, my gut tells me to split them.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 2, 2008 at 9:21 am
Shark Energy (12/2/2008)
I have a 10 disk RAID 5 setup. Would performance be better splitting to 2 arrays of 5 and logs and data split between the 2?
Honestly, it would be better splitting and doing one of the arrays Raid 10 (for the log). If you can't then, yes, split the array. A single array, logically partitioned is no better than a single array with 1 partition.
Where's TempDB going?
Where are the backups going?
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
December 2, 2008 at 9:26 am
I think I'm gonna go with a RAID 5 10 disk for the Data and Tempdb and Raid 1 2 disk for the logs. If the tempdb useage goes up I'll get an seperate array added for that.
This system is being used for a data store that will have a steady stream of data going in (few hundred transactions a minute) BUT could have some bigger processes moving data around (thousands/millions of records).
EDIT: If going with this plan, which array would I be best sticking backups on?
December 2, 2008 at 10:05 am
Shark Energy (12/2/2008)
EDIT: If going with this plan, which array would I be best sticking backups on?
A separate one. Backups onto the same array as data is firstly an eggs in one basket issue, second is slow as reads and writes are to the same device.
How big are those disks and how big is the DB and log expected to get to?
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
December 3, 2008 at 6:15 am
Since you seem to have 12 drives available, I would use SQLIO and test several configurations. Search online for guidance on how to use that app and even for sample scripts you can use. In addition to the recommendations already put forth for drive config, if space constraints allow I would also try 8 drive RAID10 for user data (and possibly tempdb data) and 4 drive RAID10 for all tlogs (and maybe tempdb data).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 4, 2008 at 1:10 pm
What would your opinion be on the following configuration:
* Windows 2003 Server OS SP3 - Pagefile in C:
* Intel Xeon 3.8ghz processor with hiperthread
* 4 gigs of memory
* Raid 5 = 4 spindles 1 single logical disk space divided 12 gigs for C: and 190 gigs for D:
* SQL Server 2005 with Reports Server Running - 1 Database all in D: as well as the transaction log file.
* IIS running 1 application - ASP.Net 2.0 C# usage is minimal during the day. But heavy everyother week on mondays, which gets real slow when people starts running reports and doing a lot of updating.
Let me know what you think and thanks in advance,
AL
December 4, 2008 at 1:20 pm
Doesn't look like a great config. How heavy's the SQL load going to be?
SQL doesn't work well with hyperthreading. SQL 2005 and 2008 are far better than 2000 was, but they're still not great.
There's no real point in partitioning the array. It's logical partitioning, so there'll be no performance improvement over a single logical drive.
It's not usually recommended to put SQL and IIS together, as they will compete for resources.
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
December 4, 2008 at 1:25 pm
Gail,
Thanks for your insight into the configuration of SQL.
AL
December 8, 2008 at 4:47 am
GilaMonster (12/2/2008)
Shark Energy (12/2/2008)
EDIT: If going with this plan, which array would I be best sticking backups on?A separate one. Backups onto the same array as data is firstly an eggs in one basket issue, second is slow as reads and writes are to the same device.
How big are those disks and how big is the DB and log expected to get to?
1tb split between the 10 disks. The db itself could be around 100gb, 25gb of log. We will be snapshotting the database on a monthly basis.
The backups will take place, but will be taken onto tapes and only kept for a week on the disk itself.
My question now is - I have a RAID 5 10 disk for Data and Tempdb. I have a 2 disk RAID 1 for logs. Do I stick backups on with data or with logs? Best practise there?
Thanks for the advice. I know I may not have taken it all on board (yet) but I will be doing some SQLIO tests and could possibly reconfigure before we go live (in 8 months).
December 8, 2008 at 4:59 am
Shark Energy (12/8/2008)
My question now is - I have a RAID 5 10 disk for Data and Tempdb. I have a 2 disk RAID 1 for logs. Do I stick backups on with data or with logs?
Neither
Best practise there?
Separate array for backups. If you stick the backups with either the data or the logs, then a RAID failure/corruption could take out both the database and the logs in one go. Plus, if the backup is sharing an array, backups will slow down the database and database activity will slow down the backups.
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
December 8, 2008 at 5:09 am
We are talking full AND log backups off onto their own array?
Also the tempdb - I'll have 1 for each core. I'm going to put them in with data over logs. Best idea? I can't have a separate array for those 2. In the end my hardware guys are gonna say no.
December 8, 2008 at 5:29 am
Shark Energy (12/8/2008)
We are talking full AND log backups off onto their own array?
You asked for best practice 😀
All the backups can go onto a single array. For Very large databases (> 1TB) it can be better to have 2 or more arrays for backups so that they can be stripped, but you've got a fairly small DB so no need there.
Also the tempdb - I'll have 1 for each core. I'm going to put them in with data over logs. Best idea? I can't have a separate array for those 2. In the end my hardware guys are gonna say no.
Best idea is a separate array, if you can. If not, data's better than log.
If you've got more than 8 cores, you probably don't need 1 tempDB file per core. Maybe 1/2 no of cores. Above 16 procs reduce that more.
The reasoning behind splitting TempDB was to reduce contention on the allocation pages. It was a major issue in SQL 2000, much less in 2005. The CAT team's done tests and found little to no improvement over a certain number of data files.
Test it. I prefer to start at 1/4 the number of cores, and if I see contention on the allocation pages, increase that to 1/2 the number of cores
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply