September 27, 2013 at 11:49 am
Hi guys,
So a server has been performing badly for the last month+ & last night I created some missing indexes which improved the performance a lot. However I noticed that it had 2*RAID5 disks (both 10k rpm i think...they're 10k something) with the following split:
RAID5 disk #1: software, pagefile, .mdfs, .ldfs
RAID5 disk #2: nothing
I guess it was added a while ago for the database ("Data" in the disk virtual name) but nothing got moved. So I was looking for opinions of what you'd suggest moving? I guess it's simply a matter of .mdf or .ldf? Or you'd move tempdb across too with the .ldfs?
The performance boost of yesterday has pretty much given me free-reign on the server to experiment so other things I was planning to do:
1. increase the cost of parallelism threshold from the default (cxpacket is the 2nd highest stat after the index change, after some wait type similar to IO_NETWORK).
2. Initialise IFI
3. Add extra .mdfs for tempdp (currently 1*4gb mdf)
4. possibly add a couple more indexes depending on the results of my trace earlier today.
I really want to see what effect the isolation level would have on how the wait types vary too although I'm not sure if they'd give the go ahead for that test (won't be this weekend at least) since I'd probably need to restore from backup if it proved ineffective...although for a largely OLAP system it should be better?
The db is 2005 SE
Thanks for any feedback on my ideas 😮 // testing in prod since 2013
September 27, 2013 at 12:10 pm
When I provision for a new SQL Server box here is what I ask for:
1) System disk
2) Data disk
3) Log disk
4) TempDB Data disk
5) TempDB Log disk
Most of systems, these days are virtual, so I'll make sure that disks 2-5 are on raid 1-0 for faster IO. I know there is a 50% storage loss penalty but at least there are fewer iops than raid 5.
Now this is my ideal. I've combined disk 4&5.
Another option is to consider moving tempdb to a solid state drive for optimal performance.
So by you having 2 drives, a system drive & an empty drive, I would have to say how much volume does this (these) database(s) see? I would probably move everything off the system drive.
You will want to move TempDB for sure. I've had several installations of SQL Server that have crashed because TempDB utilized all of the system drive. These machines were set up before I started, so all I had to do was clean up the mess.
Just my 2 cents.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 27, 2013 at 3:22 pm
Seems to me like it must be two different RAID5 sets ... wouldn't different drives on the same RAID set already be using both drives?!
Either way, I'd delay your #1 action to be after everything else, and even then I'd consider very carefully before doing it. You'd likely be better off limiting the MAXDOP setting before messing with the parallelism threshold.
If it is a different RAID set, you should separate the data and log files for the same db onto different sets, but data files from one db can cohabitate with log files from a different db.
I suggest using sys.dm_io_virtual_file_stats to help balance the I/O between the RAID sets.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 27, 2013 at 3:47 pm
There is a lot of unknown information here. If done correctly the logical drives sit on different raid sets or LUNs. But we can't say for sure. In addition there is little information about the database(s) on this box. Is it read heavy, write heavy? What is the growt metrics? A lot of this information is missing which is needed to best configure a 2 drive server.
The best thing to do is describe a starting point so the server can maximize performance with the existing resources. Finally with a blueprint for making the platform even better we can suggest what else can be done.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
September 28, 2013 at 12:16 pm
Ideally you could break the empty RAID5 and use the disks to physically separate the differing datafiles.
TEMPDB should definitely have its own spindle and ideally the log files as well.
That said, certain answers are important:
-How transactional is the database?
-How important is redundancy?
-Could IO become a problem?
-What sort of contention do you see in TEMPDB? Allocation or IO contention?
-Would your DB benefit from using multiple spindles for the MDF?
And most importantly.....
-If you are experiencing problems now, how could you solve them with the resources you have?
September 28, 2013 at 2:30 pm
Hi all,
Sorry for the lack of response, I just got back from SQL Saturday and had no net there :f
Yes, it is 2 RAID5 sets (I'm guessing 3/4 disks per set; the normal setup here seems to be 4 disk raid5 & a 2 disk raid 1 so maybe this one has gone 3-3). Some more info (from before the index additions):
Before the index runs I tried to look at the state of the system based on some Paul Randal blogs; what I found was:
WRITELOG was the highest waiter (22 minutes over a 2.5 hour period; around 1/3 of the wait%). This was followed by LCK_MS, LCK_M_X, CXPACKET & ASYNC_NETWORK_IO each with about 10% of waits.
LatchClassWait_S WaitCount Percentage
ACCESS_METHODS_SCAN_RANGE_GENERATOR36.091982691.07
SpinlockDiffCollisionsDiffSpinsSpinsPerCollision
DIAG_OBJECT200
LOCK_HASH59018
MUTEX12112293
I currently only have 3 weeks of space monitoring (was approached about the database recently & haven't rolled out my monitoring to all instances). It's only 33.3gb (+4gb tlog) so not very big.
I just queried by trace results table & got this (although different time of the day):
SELECT348067
UPDATE98510
DELETE4423
INSERT169855
The top durations were mostly SELECT though (because of the missing indexes). ReadLatency was "19" before the indexes.
My post-index data is like this:
SELECT140706
UPDATE33662
DELETE1626
INSERT5810
Now Read/Write are both slow on the .mdf (32 & 13 respecively). For some reason msdb is the next highest (no jobs running), while tempdb has the lowest latencies quite low latencies (its currently saying 2/1 although I'm not sure if that's the weekend having an effect on lowering it).
LatchClassWait_S WaitCount Percentage
ACCESS_METHODS_SCAN_RANGE_GENERATOR26.314224086.42
In terms of space all files take up 40gb in total & the spare RAID set is 200gb so it all mdfs/ldfs.
@scott why hold off on that one last? It's a dynamic parameter so if users reported bad run times it could easily be returned? I thought the 5s default is generally seen as too low?
@Keva I added the trace details, at peak times it seems to be 60-40 OLAP-OLTP then widens in favour of OLAP.
I didn't run any query to see types of tempdb contention~
No hardware additions possible apparently (was hoping for a first SSD~). They also reboot the server daily (scheduled) which won't help the caching =/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply