Since Microsoft SQL Server features several embedded tools to monitor events within the server and to refine the physical database design for optimization, it’s likely many of you are already monitoring your database environments. Further, the added benefit of third-party SQL Server monitoring tools is that solutions proactively identify resource bottlenecks before business performance has been affected. Issues are reported to a database administrator (DBAs) before they become painful and expensive – rather than DBAs spending hours monitoring SQL Server with native tools after reports of slow performance.  

While the point of real-time monitoring, with native or third-party solutions, is to make the tools work for you – easing the burden of ongoing management and to let you shift focus to more strategic projects, it is possible database administrators aren’t effectively using Microsoft tools and other solutions to maximize SQL Server monitoring efficiency. Instead they are adding monitoring as one more process to their ever-growing ‘to do’ list.

The IT team at Dell Software customer, Greenstar, Ireland’s leading provider of environmental, waste management and recycling solutions, gets it. They needed a way to monitor the company’s SQL Server environment to address the extreme database downtime that occurred within their environment from time to time. Greenstar deployed Spotlight on SQL Server Enterprise to provide real-time performance views of the environment and detect and diagnose performance issues. The solution alerted the IT team to server performance bottlenecks and determined the root cause. Want tangible results: Greenstar experienced diagnostic time savings of at least 80 percent and decreased time to resolution by approximately 60 percent. Download the full case study here.

In addition to flagging potential issues and troubleshooting as Greenstar had done, a well-executed monitoring strategy is critical as it can significantly improve SQL Server performance. Before we cover the two processes necessary for efficient SQL Server monitoring, let’s review the SQL Server elements that every DBA should monitor. At a minimum, DBA should have a glimpse into SQL Server’s rational engine as well as:

 

  • Database Console Commands (DBCC)
  • Resource Usage
  • Open Activities
  • Error Activity
  • SQL Trace and Trace flags
  • SQL Server Profiler
  • Distributed Replay Utility
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Graphical Showplan
  • Stored procedures
  • Built-in functions

 

Once you’ve established what components should be proactively monitored, it’s important to spend time to identify performance baselines and benchmark performance under various scenarios.

  • Baseline: The first step to ensure monitoring is working for you is to establish a database server baseline, which will provide insight into the “at rest” performance profile of each database server or application. This step enables DBAs to quickly flag when key performance metrics go far above or drop below the baseline. The baseline also provides valuable information and enables DBAs to document background processes. When creating a baseline, keep in mind that the best baselines have include a small number of graphics to represent performance; however, those graphics should include enough information to interpret the results.
  • Benchmarking: Following the baseline process (evaluating the server performance “at rest”), DBAs should work on a benchmark to analyze and project how the server and applications running on that server will perform under common usage scenarios. Look for technology solutions that support the ability to design a load test scenario, run it against a database server and then immediately review the performance of the server using real-time monitors.

Without ongoing and continuous monitoring, DBAs will be left in the dark about database performance.  For more information about SQL Server performance and optimization, read " Top 10 Tips for Optimizing SQL Server Performance" by Richard Douglas