Is your SQL Server slowing you down? Are bad queries giving your application a case of the blues? Before you go looking for help with SQL Server, make sure that you’ve done everything you can to solve the problems on your own. Many of SQL Server’s ills can be solved with some easy preventative maintenance, patching, and TLC.
If you aren’t on a recent version of SQL Server, what are you waiting for? SQL Server 2000 and 2005 are no longer under active development – the query engines are intolerably old and diagnostic support has improved by leaps and bounds in newer versions of SQL Server. Once you have the licensing figured out, use our SQL Server setup and post installation checklists to make sure you configure SQL Server the right way the first time through.
Why is a new version of SQL Server going to make things faster? New versions of SQL Server get new versions of the SQL Server query optimizer. While Microsoft sometimes sneaks big performance fixes into a service pack, the biggest improvements come in major version releases. New versions of SQL server also contain fixes for bugs, take advantage of new CPU instruction sets, and are filled with the latest and greatest in software development techniques. In short: you wouldn’t expect a 10 year old sports car to be as fast as a brand new sports car, why would you expect the same thing from SQL Server?
Some vendor applications can’t be upgraded to SQL Server 2008/R2/2012. Odds are these applications are also sitting on older physical hardware. When you’re stuck running old versions of SQL Server on old hardware, it’s a no brainer to virtualize SQL Server and assign enough resources to keep things running quickly.
Even if you can only upgrade from 32-bit to 64-bit SQL Server, take the time to make the change. It’s well worth it to remove the limits of a 32-bit memory space.
4. Upgrade Your Memory
SQL Server 2008 R2 Standard Edition maxes out at 64GB of memory. If you don’t have 64GB of memory in your SQL Server, what are you waiting for? Head on over to your vendor of choice and expense account your way to better performance. Since SQL Server uses memory to cache data and avoid additional trips to disk, you might as well drop a bit of coin and max out SQL Server’s memory. You’ll get the added benefit of additional memory to cache query plans, perform bigger joins and sorts in memory, and may even see a reduction in CPU and disk utilization to boot.
Make sure that you increase SQL Server’s max memory setting after you add more memory, too, or else SQL server won’t take advantage of that new RAM that you just added.
3. Check Task Manager
You’ve upgrade to the latest and greatest SQL Server and you have 64GB of RAM in the server. Things are still slow, what now?
Open up task manager and sort first by CPU and then by memory. If there is anything running that you don’t know about, kill it. If anti-virus is installed, configure exceptions. If other users are RDPing into the server to develop SSIS packages, revoke their access. Get rid of every extra piece of software that is slowing the server down or eating up memory.
If you’re on Windows Server 2008 or newer, you should also make sure that the Windows file system cache isn’t eating up all of your memory. Windows will cache data for file system access when you drag and drop a file, copy it with xcopy, or push a backup across the network. This behavior is by design because it’s supposed to add to the feeling of faster performance for the end user. Unfortunately, it also steals RAM from server-side processes like SQL Server. You can check this by opening up Task Manager, switching to the Performance tab, and checking the Cached number under “Physical Memory (MB)”.
2. Look in the Event Log
Check both the SQL Server error log and the Windows Event Log. Both locations contain a potential motherlode of information that most teams ignore. If SQL Server or Windows is running into any kind of problem, they’re going to be complaining about it. In both locations you’ll be able to see if SQL Server is dumping core, waiting on disks for a long period of time, or encountering any hardware related issues. You’ll also be able to see what other services might be running into problems on the server – just because anti-virus isn’t running right now doesn’t mean it hasn’t been running in the past. The Event Log is another place to look to get an idea about what’s running on your server, what kind of problems it’s having, and what you can do about it.
Filter down the Event Log to just warnings and errors and you may find out if you have hardware that’s on the verge of failing. Most hardware and drivers should report problems up to Windows and the Event Log makes it easy to track down these issues, diagnose the problem, and potentially find a solution. Eliminating hardware problems can be an easy way to eliminate causes of poor performance – you never know what kind of bugs are lurking in out of date drivers and firmware.
While you’re thinking about SQL Server and the Event Log, take a minute to make sure that you have some SQL Server alerts configured. These alerts will make sure that you’re being kept up to date on all of the goings on inside your SQL Server. As the person responsible, you should know if your storage is failing or if SQL Server is encountering serious errors.
1. Run sp_Blitz
The last thing you should do before going to get help is run sp_blitz. Adapted from our consulting, sp_blitz is a fast way to diagnose any configuration problems with your server. The procedure looks at configuration, database design, statistics, and index status to give you a set of general recommendations for improving server help. Don’t freak out after you run sp_blitz; work through the output, check out the reference links, and make the changes that you need to get your server in working order. From tempdb to disabled indexes to startup procedures, sp_blitz has got you covered.
Ask for Help
If you’ve run through these 5 steps and you still can’t figure out what’s wrong with SQL Server, narrow down the question as tightly as possible, then post it on http://StackOverflow.com or http://ServerFault.com. Include as much example code as you can so that others can quickly run it and get an idea of what you’re asking, and they might even post your code back with fixes.
Three other ways you can make your SQL Server faster: