5 Tricks for Ensuring SQL Server Peak Performance

Database administrators wear a lot of hats behind the scenes—from capacity planning to security to data recovery. Perhaps the most visible of the DBA’s responsibilities is SQL Server performance monitoring. Though the day-to-day tasks associated with maintaining a high-performing system may go unnoticed by the outside world, you better believe people will start to pay attention when performance is less than ideal. 

Database outages, slow response times, and other user-facing issues are bad for business. And, because SQL Server performance (or lack thereof) falls squarely in the DBA domain, poorly performing databases are bad for your job security.

Threat of unemployment aside, ensuring SQL Server peak performance is crucial to your organization’s ability to run smoothly, so we’ve compiled five tricks DBAs need to know to optimize SQL Server performance.

Automate Routine Tasks and Maintenance

These days, there is little reason for a DBA to do many day-to-day tasks and maintenance checks manually. Today’s SQL Server performance monitoring tools can automatically monitor disk space, inspect error logs, and verify backups. You can even set up system alerts that not only let you know there’s a problem but also provide information about the severity and type of issue. 

One of the main benefits of these monitoring tools is that many of the performance monitoring features are accessible through your mobile device, making troubleshooting much more flexible. You can monitor and triage your system from practically anywhere at any time.

Track Performance Metrics

Of course it’s important for your SQL Server performance monitoring tools to catch and fix current issues, but there is also a lot of value in keeping track of past performance. Monitoring and analyzing database performance over time helps you identify trends and anticipate future performance issues. Tracking performance history provides data on whether performance is better or worse over a given time period. 

Workload analysis is another way to get performance metrics that help you improve SQL Server performance. Run an analysis to identify how a user, database, host, or SQL statement is impacting your workload. If you isolate a source that is negatively impacting the system, it becomes much easier to take the appropriate steps to fix the problem.

Identify the Root Cause of Bottlenecks

As stated above, once you isolate the source of a bottleneck or performance killer, it is that much easier to correct the issue. Some common sources of bottlenecks and symptoms to watch out for include:

• CPU issues: SQL Server is hogging the processor
• Memory issues: Longer execution times on the queries
• Storage issues: Extreme activity on the disks and long wait times per I/O
• I/O issues: WRITELOG wait time is high compared to total wait time

Once you’ve identified your bottleneck, there are a few go-to fixes you can try that often take care of common performance issues, including: 

• SQL Server tuning: Isolate the specific query that was delayed, the specific wait time causing the delay, and the time impact of the bottleneck  
• Get rid of obsolete and duplicate indexes: Speeds up queries because the optimizer doesn’t need to consider multiple indexes when analyzing query performance and determining a plan.
• Fix fragmentation: Periodically rebuilding or defragmenting your indexes will improve performance by reducing the number of scattered or partially filled data pages SQL Server has to read. 

Run Routine Performance Health Checks

Prevention is almost always better than a cure. Use health checks to identify actual and potential issues within the system. Health checks will help you pinpoint problems with security, disaster recovery, memory, I/O, and configuration before they cause major performance issues.

Regular health checks should include: 

• Checking for missing indexes: Missing indexes make SQL Server slow to respond because it has to search every record for data. But don’t be too quick to just add missing indexes; adding unnecessary indexes can negatively affect performance, too. 
• Watching for disproportionate levels of ad hoc queries: Using ad hoc queries occasionally is no problem. But when they are used too frequently, performance suffers. SQL Server doesn’t reuse these statements, so the procedure cache gets bloated, causing the buffer cache to flush data. SQL Server then has to read data from the disk (slow) instead of from memory. 
• Looking for out-of-date statistics: The SQL Server query optimizer uses statistics to calculate the estimated cost of the operations. Out-of-date statistics may cause the query optimizer to select a sub-optimal execution plan.

Upgrade to the Latest SQL Server Version

This one feels kind of obvious, but if you haven’t already, upgrade to the latest version of SQL Server. SQL Server 2000, 2005, and 2008 are no longer under active development, and the newer versions have a lot of features that will definitely increase performance. These features include the latest version of the SQL Server query optimizer, new CPU instruction sets, and, of course, bug fixes.