Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have been running into issue with my MSSQL Database setup with speed. Here is my scenario.

About 100M Rows Average: 1k Updates Per Second Hard Drives: RAID 10 SSD MDF
--Active Time: 0
Log Drives: 1 SSD LDF - Simple Recovery
--Active Time 99.9
--Queue: 8

I do not need a back up of the log so it is set to simple recovery but my bottleneck is still at my log. I get high WAITLOG times and thus it can not update any faster. I can't do bulk updates/transactions and each update needs to be one at a time.

Is my only option to increase write performance of the log drives, add a RAID drives? Any suggestions on increasing the performance?

share|improve this question
    
How are you doing for memory? –  Chopper3 Oct 19 '13 at 19:07

2 Answers 2

When dealing with large tables you hit a performance wall because an index or the like can't fit in RAM or there isn't enough resources allocated based on the server configuration. Another more complicated but very effective method is find a way to partition or shard the table into smaller ones. You could try to add more RAM and optimize server settings first. RAID 10 ssds are the way to go, not sure if adding more to the RAID will make much difference.

share|improve this answer

If your bottle neck is your log files there are a few areas you can start to look at for improving log throughput. I would start off with this blog post by Kimberly Tripp: http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

I would ensure your disk is properly formatted with an allocation unit size of 64KB and that there isn't an excessive mount of virtual log files (as indicated in the log article above). Additionally make sure that there isn't Anti-Virus software actively scanning the LDF files.

It might be worth while to use something like SQLIO (or another disk stress test tool) to see what the theoretical sequential write performance is on your log drive. You can then compare that with perfmon during your heavy work load to see if you are hitting the limits of your log drive. If you are then I would say it is time to consider looking at a faster drive setup.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.