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,
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation