So You Think New Hardware Is The Solution?

Identifying The Problem

So your database performance isn’t what it used to be, so you are going to update the hardware with a new server purchase? You are going to address your performance issues with new hardware, maybe adding new RAM, changing out your slower drives for faster drives, or maybe increasing the number of CPU cores?

Solid State Drives (SSD) are fast and are very cheap. You can now run SQL Server instances with much more RAM that you might ever need. Computer hardware is cheaper than ever, including some very fast systems with tons of CPU power. What is stopping you from solving your database issues with a hardware refresh?

Have you tried to solve your performance issues with tuning your database? I know that this isn’t always possible. Sometimes, specially with third-party solutions this might not be an option, but generally speaking bad code or design in your database might be the issue. Throwing new hardware at the problem might be an easy solution, but it might be better to solve the problem with improvements before you try replacing the slow hardware.

Performance Tuning Opportunities

You might not be the cause of the database problem, but you now own the issue. Maybe you inherited the system and there are no hardware improvements left for you to make, but you need better performance. Maybe you find yourself looking for database performance improvements, and you can’t buy hardware to solve the issue because of a tight budget. Maybe you want to make sure you have all the system improvements in place before you go to the boss and ask for hardware. Whatever the cause, you need to start making performance improvements, and you need to try everything.

Here’s a few performance tuning tips for you try before you assume there is nothing else left to try:

1. Make sure there are no other applications installed on the server, including users accessing Remote Desktop or other Windows applications.
2. Verify the power setting to make sure the server is configured for best performance over power savings.
3. Check the server for missing Windows or SQL Server updates.
4. Check the memory settings for SQL Server to verify the instance is using as much memory as possible, without causing issues with Windows page files.
5. You can take the time to examine your plan cache, including missing indexes, looking for ways to improve performance
6. You can examine existing indexes looking to remove duplicate indexes, which can be a drain on performance.
7. Verify your statistic are up to date, and indexes have been rebuilt.
8. Review the placement of data, log, and backup files for your server. If the installation was done by accepting the default paths, there can usually be improvements that will increase drive performance
9. If your server is running on VMWare, then you will want to correlate query performance to the performance in your virtual environment.Also verify the VM settings to make sure your issue isn’t related to other VM performance issues, like an overload host, poor memory configuration, or shared drives be over-used by a different VM.
10. Review to make certain you have optimized tempdb for performance.
11. Review your archiving strategy to make sure your database is as small as possible. Look at logging tables for opportunities to reduce table sizes and maybe improve application performance

This list isn’t complete, but it might point out some items you haven’t thought of our have forgotten to check. You might find that hidden issue that once you have it resolved you find the server performance is back to expected ranges of behavior and you saved thousands of dollars. It might have also been much faster and easier than a server migration.

Even if you were unable to resolve the issue with database tuning, don’t forget to check if there are application or networking issues.