Geek Speak

Tips and Tricks for Improving SQL Performance

SQL Server performance is a hot topic these days, especially if you're leveraging your SQL Server for a high performance NMS. This can become even more critical when you add applications like NetFlow which tend to carry a significant I/O burden.

In some organizations you can rely on the DBA team to own/maintain/optimize the database servers for you. Unfortunaely, for many of us this isn't an option either because we dont' have a DBA team or because it's such a political mess trying to work with them. This causes us to have to implement and maintain our own database servers to support our apps.

The thing is, most of us network engineers don't know diddly about database servers. So, with that in mind, here are a few tips for optimizing your SQL Server:

Head Geek's Top 5 Tips for Improving SQL Performance

#5 - Add more RAM. Doesn't really matter how much you have, adding more will almost always help. Be sure that your SQL instance and OS are capable of consuming the additional RAM and if not make it so.

#4 -  Just say "no" to RAID 5. It's great for application servers but horrible for database servers where I/O performance is important.

#3 - Place the data and log files (.mdf and .ldf) on separate logical drives and separate channels or controllers.

#2 - Unless your SAN is optimized for high I/O vs. large I/O stick with a locally attached disk arrary.

#1 - Buy disk controllers with battery backed-up write-back cache. The more the better, but at least 256MB.


Flame on...
Josh

Published Jul 21 2008, 05:24 PM by Josh Stephens

Comments

 

mantra6 said:

So here is my conundrum. I monitor at this point about 7000 elements, mostly pings with some SNMP. I have a new server with six drives on one RAID controller, and SQL will have to live on the same box as Orion NPM. Would it be better (performance) to have three mirrored (RAID 1) arrays for system/app, logs and data, or RAID 1 (system/app) on the first two + RAID 10 using the last four for all SQL files?
September 26, 2008 3:33 PM
 

SLXer said:

This is the info i was looking for! happy to find it.. no wonder the io on our sql server has been through the roof.. network engineers running apps is a litttle dangerous!! :)

September 26, 2008 9:23 PM
 

md_schneider said:

I would venture to say that network guys understand response time, that being true the best measure of disk performance for SQL is... response time. While it's true that RAID 10 can generally handle more IOPS, it's not universal (especially when cache is in play). So I would recommend pulling up perfmon on your SQL box and looking as Disk sec/Read and Disk sec/Write. If you're writes/reads are below 10milliseconds (0.010 seconds) then you're in good shape, if you're above 20 milliseconds you need to start looking at more disks, better caching, disk sector offsets, or all of these. This method will ensure your disk is performing as neccesary reguardless of RAID level.

November 19, 2008 10:08 PM

About Josh Stephens

Josh Stephens is a Vice President – and Head Geek – at SolarWinds, where he plays an integral part in the development and delivery of our award-winning network management products. Josh has extensive experience in network management systems, network engineering, and software development. His 15-plus years of experience in technology include designing and deploying advanced networks and network management systems within organizations including the US Air Force, Sprint, MCI/UUNET, and Wal-Mart. He has received several industry certifications including those from Cisco Systems, Microsoft, and HP.