Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Query Plans migrating from 2005 to 2008R2 Expand / Collapse
Author
Message
Posted Wednesday, October 9, 2013 11:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
inevercheckthis2002 (10/9/2013)
Kurt W. Zimmerman (10/9/2013)
4) System, Data, Logs & TempDB should be on their separate LUNs.
With sincere apologies the thread hijacking... I'm trying my best to gain insight to the best use of the hardware at hand. It will be in use next week and I'm trying to get it right from the start. I'd follow best practices and recommedations whenever I am able, but sometimes there are limited resources.

I have 16 disks - direct attached storage, one RAID controller. The OS is already on a 2 disk RAID1.

Which is best?

A.) One RAID 10 array across 14 disks, giving me the best I/O from an IOPS perspective. Install data, logs, tempdb - could be separate partitions, if that matters.

B.)One RAID 10 array across 12 disks for Data and Logs (could be separate partitions). One RAID1 (2 disks) for tempdb files, sized as you describe above.

C.) One RAID 10 array across 10 disks for Data. One RAID 1 (2 disks) for logs (simple recovery model). One RAID1 (2 disks) for tempdb files.

It's the same controller and the same disks - wouldn't option A be best?



I would favor C if I had to choose.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1503233
Posted Wednesday, October 9, 2013 12:20 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:45 PM
Points: 28, Visits: 322
I would suggest you find out your percentage of reads to writes before deciding this. The limited hardware you have listed here isn't going to support a very busy database but I have no idea what your dealing with in that area or even your disk speeds. . You could make better use of it if you discover you have 85%reads to writes for instance by going with raid 5. Look what you are suggesting for TempDB and Log. That is going to get you at the most 160-200IOPS with 15K SAS drive. A generic answer of separation may actually hurt you more in such a situation.

I don't always test my SQL scripts, but when I do, I test in Production.
Post #1503249
Posted Wednesday, October 9, 2013 2:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 86, Visits: 369
KTD (10/9/2013)
I would suggest you find out your percentage of reads to writes before deciding this. The limited hardware you have listed here isn't going to support a very busy database but I have no idea what your dealing with in that area or even your disk speeds. . You could make better use of it if you discover you have 85%reads to writes for instance by going with raid 5.

The disks are 15K RPM SAS drives. The databases are used for analytics and have daily and hourly data loads from another database. It needs to be optimized for writes. So, RAID10.

KTD (10/9/2013)
Look what you are suggesting for TempDB and Log. That is going to get you at the most 160-200IOPS with 15K SAS drive.

Exactly!

But, as an aside, are you familiar with SQLIO? Here's my results on a two drive RAID1. I was expecting 200 IOPS max and I'm wondering why it reports what it does. I would have a tempdb with 8 files on the two drive RAID 1.

using system counter for latency timings, 2212939 counts per second
8 threads writing for 120 secs to file F:\TestFile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 24576 MB for file: F:\TestFile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2583.37
MBs/sec: 161.46

KTD (10/9/2013)
A generic answer of separation may actually hurt you more in such a situation.

That was my thinking, but while researching this I almost always see the recommendation to separate them. Thus, my queries here to get some further insight.

It seems that you would support option A - aggregate the I/O over 7 disks mirrored to 7 disks and let the chips fall where they may? That was my original plan, but I started researching this some more to make sure it was the best plan, given the resources.

Is there any way I could test performance of option A over option C, in the next couple of days? Any tools I should try?

Thank you all very much for your comments!


Post #1503293
Posted Wednesday, October 9, 2013 7:44 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:45 PM
Points: 28, Visits: 322
I am fairly confident your not getting over 2000 IOPS from 2 disk.

I have a similar situation as you for my UAT server that is a Dell 510 with a MD1220 DAS and I had to make compromises for this due the budget the organization was willing to spend for UAT. In this case I was able to put in 4 NL storage disk inside the server for space and 6 15K disk for tempdb all Raid10. With 20 disk in the DAS Raid5. Not many writes. Not sure if you have that option to add any disk to the server.


Not knowing your application. It sounds like you could put this DB into Bulk Logged and save a great deal of logging IO. I would think from what you say there might be heavy usage of tempdb. I tend to agree with you on the first option if you cannot get any more investment except that i would split it into two sets of 8 disk. I would be sure your managers know the risk. Sometimes you just have to do what you can with what you are given to get the job done. If you do have time to test these options that would be the best.


I don't always test my SQL scripts, but when I do, I test in Production.
Post #1503349
Posted Thursday, October 10, 2013 5:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 11:59 AM
Points: 86, Visits: 369
KTD (10/9/2013)
It sounds like you could put this DB into Bulk Logged and save a great deal of logging IO.

We're in Simple Recovery Model.
Post #1503478
Posted Thursday, October 10, 2013 6:03 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:45 PM
Points: 28, Visits: 322
Operations that can be minimally logged are minimally logged in both simple and bulk-logged recovery so I think your good to go. I just realized you have this posted in it's own posting. I'll move over there. to read.

I don't always test my SQL scripts, but when I do, I test in Production.
Post #1503496
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse