SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Real-World Performance Advice
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

philipstratford
Starting Member

United Kingdom
8 Posts

Posted - 10/03/2012 :  11:40:22  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/03/2012 :  12:50:25  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2075 Posts

Posted - 10/04/2012 :  01:53:35  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page

philipstratford
Starting Member

United Kingdom
8 Posts

Posted - 10/04/2012 :  06:01:37  Show Profile  Reply with Quote
Thanks all for taking the time to reply.

quote:
Originally posted by chadmat
STAY 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 chadmat
Also, 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 jackv
As 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?

Edited by - philipstratford on 10/04/2012 12:07:21
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/04/2012 :  13:26:28  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2075 Posts

Posted - 10/05/2012 :  01:29:13  Show Profile  Visit jackv's Homepage  Reply with Quote
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.html
c)Have a plan to decide on which IO activity needs to be sacrificed if comprimises need to occur on the data|log|tempdb split

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000