Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
philipstratford
Starting Member
8 Posts |
 Posted - 2012-10-03 : 11:40:22
|
| This is my first post so hello to everyone!Amongst other things, I'm the DBA for the SQL databases running on our production server where I work. There are about half a dozen live databases running on a single SQL Server 2005 instance. The server, bought before I started working here, is a pretty meagre thing, with 4GB RAM (32-bit Windows Server 2003). The number of users accessing the databases in total probably never exceeds 150 concurrently (and rarely anything like that high), but in theory could get as high as 300, and they do rely heavily on the application it serves.My question relates to upgrading the server. I've already purchased the hardware and it's an absolute beast, with 64GB RAM, a top-of-the-line CPU, etc. It's running 64-bit Windows Server 2008 R2 (too cautious to go straight for Windows Server 2012) and Windows Server 2008 R2 (cautious again). I'm pretty confident that, given the enormous leap in memory capacity, the new hardware alone is going to lead to a noticeable improvement in performance (am I wrong?). However, as I now have the opportunity to configure the new installation as I see fit, from scratch, I was planning to implement some SQL Server best practices, and that's where I need advice.At the moment I've configured the physical disks into two RAID arrays - a RAID 5 array for the O/S (I may be misremembering the RAID level) and a RAID 50 level for the databases. However, to truly follow best practices, I should really split my larger array into smaller ones and split my databases' files between them, ensuring that the most-used tables are on separate physical disks.What I really want to know is, given the relatively modest size of our organisation and size of user base, is it, in practice, actually going to make any discernible difference if I start splitting database files across physical disks? It's very hard to get a handle on what kind of impact these best practices have in smaller organisations.I can take a wait-and-see approach to other best practices, after monitoring performance on the new server once it's in use and looking for bottle-necks, but I won't really be able to start splitting arrays and creating new ones, so I need to get this right from the outset.Any advice gratefully received, and sorry for the long post! |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-03 : 12:50:25
|
| A few things...The new hardware MAY improve performance, but it is no guarantee, if I/O was your bottleneck, and your databases already fit mostly in memory, then adding memory doesn't help. But, in all likelihood, the new hardware/software should help. STAY AWAY from RAID 5...unless it is a read only partition. Use Raid 10 if at all possible. Number of users is not a valid measure of how busy your server is, what is more important is what are they doing? I, as a single user, could probably write a query that would cripple the server. 2-4 Data Files is usually optimal for performance. Also, don't forget about TempDB. It should have it's own disk, and should be split into multiple files as well.-Chad |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-04 : 01:53:35
|
| Create an IO profile - for example, what are the IOPS requirements? What is the majority of the activity - e.g Bulk Insert , OLTP etc. This will define your requirment regarding RAID levels. RAID 5 can be good with a large write cache. There is a cost implication around using RAID 10 - i.e extra disk etc. As you've suggested - split out the different - Data, Log, tempDB . Also focus on 1) maintenance 2) tuning.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
philipstratford
Starting Member
8 Posts |
Posted - 2012-10-04 : 06:01:37
|
Thanks all for taking the time to reply.quote:
Originally posted by chadmatSTAY AWAY from RAID 5...unless it is a read only partition. Use Raid 10 if at all possible.
May I ask why? I've gone for RAID 50, not RAID 5, which I read is a good mix of RAID 5's resilience and RAID 0's performance. Having said that, if I need to move the TempDBs to their own array, I won't have enough disks for RAID 50 any more anyway.quote:
Originally posted by chadmatAlso, don't forget about TempDB. It should have it's own disk, and should be split into multiple files as well.
But if the disk the TempDBs were on died, no vital data would be lost, right? In other words, they can be on a single physical disk rather than an array as resilience isn't important? Then again, I suppose performance is...quote:
Originally posted by jackvAs you've suggested - split out the different - Data, Log, tempDB.
So the transaction logs should be on a separate disk to the data as well? If I have separate RAID arrays of whatever level (for performance and resilience) for the data, logs, TempDBs and the O/S, that's going to be a hell of a lot of physical HDDs in my server!One other question: Where should the actual SQL Server program be installed to? The default is Program Files, which is on the same partition as the O/S. I thought that'd be fine, but should it be elsewhere? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-04 : 13:26:28
|
| RAID 5 has a write penalty due to having to write parity. So in write intensive workloads, it can cause io write latency (Unless you have a large cache as Jack mentioned, but you are still performing more physical writes than necessary).TempDB is volatile data, you shouldn't be concerned with data los, but performance, as it can be a perf bottleneck.Log should be on different disk than data as the data access patterns between data files and log files is different. Log is mostly sequential writes, while data is random reads and writes.-Chad |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-05 : 01:29:13
|
| Work with storage people to ensure that underlying IO channels - are separated out.Some other things to do: a) does the storage vendor have documentation with performance guidelined?b)Define the IOPs for the database server http://www.sqlserver-dba.com/2011/08/define-iops-for-all-database-servers.htmlc)Have a plan to decide on which IO activity needs to be sacrificed if comprimises need to occur on the data|log|tempdb splitJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Real-World Performance Advice