Essential Guide

Hardware for SQL Server: Optimizing performance

A comprehensive collection of articles, videos and more, hand-picked by our editors

Three SQL Server hardware upgrades to enliven performance

You’re contemplating SQL Server hardware upgrades and want a shortlist of easy steps to boost performance. You’re in luck. Read on for tips to make it move like never before.

The time will come -- if it hasn’t already -- when you’ll have to look at your SQL Server computer’s hardware and

decide what, if anything, you can upgrade to get better performance out of the ol‘ box. I’ll assume that you’ve already taken care of the software side of performance: You’ve tuned indexes, defragmented both databases and the hard drives and so on. Where can you get the biggest bang for your buck in a SQL Server hardware upgrade?

Good: Faster Network

Believe it or not, it’s not uncommon for SQL Server to bottleneck at the network level. I have customers who’ve realized that their CRM database server was connected to a 100 Mb Ethernet switch port. Oops. 10 Gigabit Ethernet is pretty widely available these days, so make that your goal. At the least, 1 GbE should be the slowest link to which any server is connected. That said, also take the time to make sure the rest of the network isn’t introducing a bottleneck. Switches, routers, firewalls and proxy servers can all impose their own slowdown, and they can all make SQL Server seem slower than it really is.

Better: More Memory

SQL Server loves memory. Loves it like Avatar’s Na’vi love that glowing-tree-thing of theirs. Stuff your server with all the random-access memory (RAM) it’ll hold. If necessary, ditch older, smaller memory sticks for brand-new, bigger models. Be sure to splurge on the memory, too: You want fast RAM that’s speed-matched to the controller circuitry on your server’s motherboard. You want error-correcting RAM, too.

That said, if you’re dealing with a 32-bit box, don’t bother installing more than 4 GB, because SQL Server won’t see any more than 3 GB of that. In fact, if you are running SQL Server on a 32-bit computer -- or a 64-bit machine with a 32-bit version of Windows installed -- make that your first corrective action. SQL Server wants to be on a 64-bit machine and a 64-bit version of Windows, and SQL Server itself wants to be a 64-bit version. Microsoft is done producing 32-bit versions of Windows Server; you should be done using them to run SQL Server, too.

Best: Faster Storage

SQL Server is an intensely disk-heavy application. On a busy SQL Server, data is constantly streaming on and off disk. The faster your storage subsystem is, the happier SQL Server will be. Get disks with the fastest rotational speed you possibly can -- if they don’t sound like a jet engine when they’re spinning up, you didn’t get the right ones.

Fast transfer times are important, but fast seek time is more important. Disk vendors tend to focus on transfer time -- 3 Gbps, 6 Gbps, whatever -- but if you can’t find the data quickly, then it doesn’t matter how quickly you can get it on and off the disk. Remember that SQL Server deals with data in 8 KB chunks, and it needs to be able to skip nimbly to the chunk it wants. So, disks’ average seek time is one of the most important numbers for you to focus on.

ABOUT THE AUTHOR

Don Jones is a Senior Partner and Principal Technologist for strategic consulting firm Concentrated Technology, LLC. Contact him through www.ConcentratedTech.com.

 

This was first published in January 2011

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Hardware for SQL Server: Optimizing performance

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close