Don't rely on Windows' PerfMon tool to give you the kind of performance data you require for enterprise-level, SLA-bound database workloads. It cannot provide enough information or provide you with the kind of extensive reporting that third-party tools do. These five SQL Server performance tools dig deep into your SQL Server's performance and retrieve metrics that can pinpoint and diagnose those elusive and annoying performance problems. It's easy to optimize systems for the best end-user experience when you have the right tool for the job.
1. DB Performance Center XE (XE)
Embarcadero's 24x7 database monitoring tool monitors metrics that are most important to database administrators (DBAs) and to ensure 100-percent database uptime: memory, I/O, contention, space, network, objects, users and SQL. XE provides historical and real-time performance metrics, an at-a-glance performance overview and detailed performance reporting. In addition, if you have a mixed database environment, you don't have to have multiple tools to monitor their performance, XE monitors them as well.
XE integrates with Microsoft's Task Scheduler and is an agentless solution. You can create customized reports for management and executives interested in quality of service rather than geeky metrics. And, you can export the reports into just about any format they prefer.
In addition to the other powerful aspects of XE, the tool can automatically capture SQL sessions so you can evaluate and mitigate SQL problems as they occur. With this feature, you'll see the process ID (PID), application name and hostname to help you troubleshoot the problem efficiently when the clocking is ticking.
2. SQL Solutions' SQL Suite
SQL Solutions offers a set of modular SQL Server tools, Trace Analyzer, Deadlock Detector, Ultimate Debugger and Heartbeat, from which users can pick and choose the most beneficial for an environment. SQL Solutions directs these tools and their functionality toward DBAs and devlopers for troubleshooting and debugging.
SQL Trace Analyzer locates and diagnoses performance bottlenecks, identifies poorly performing SQL code and solves database performance problems. Using Trace Analyzer, you can find the most frequently executed stored procedures, identify the longest running stored procedures, monitor stored procedure resource utilization, identify high I/O tables and views, and correlate performance data with traces.
Quickly locate and fix SQL deadlocks with SQL Deadlock Detector. SQL deadlocks occur when two sessions or processes attempt to update the same record or set of records. This causes the database to choose one of the processes as a 'victim' and roll back its changes, allowing only one of the updates to occur. These failures prompt support calls and create unhappy users. But, if you use deadlock detection, you can identify deadlocks, resolve them and alter your SQL code to prevent them.
SQL Solutions' Ultimate Debugger (Debugger) is a powerful SQL debugger tool that assists database developers in their SQL statement debugging efforts. Debugger records detailed information for each traceable element within the SQL code, stored procedures, user-defined functions, and triggers in a line-by-line manner. Perhaps the most powerful feature of this debugger is that you can watch procedures as they're executed for live debugging.
If you want to know what's going on with your SQL Server's performance from a diagnostic viewpoint, SQL HeartBeat might give you the eyes and ears you need. Find out if your performance bottleneck is CPU, memory, I/O or some other metric that standard tools can't find. You could have answers to your performance problems in as little as one day. One day's worth of monitoring can tell you why your system is slow or performance is not at peak capacity.
All of SQL Solutions' Tools are available as free time-limited downloads so that you can try before you buy.
3. SQL Server Monitor
Part of NetIQ's AppManager Suite is its SQL Server Monitor (SSM). SSM provides a full-scope monitoring and alerting solution for your SQL Server environment. You can find out how your SQL Servers perform from an end user perspective and take action to remedy problems proactively.
SSM monitors and analyzes SQL Server performance down to the SQL statement level. It also provides a high level of fault management and alerting. This allows you to prevent outages and maintain availability of your SQL data. You can also program the system to alert at custom thresholds and provide corrective actions in an automated fashion. SSM provides real-time data monitoring, collects performance data and stores all data into a SQL database for easy retrieval and analysis. Download a free trial of the latest AppManager Suite to see what SQL Server Monitor can do for you.
4. Heroix Longitude
Heroix provides several tools for monitoring and reporting on various environments in the Longitude product family. Its database-oriented monitoring and reporting solution is an agentless metrics monitor for SQL Server, Oracle and MySQL databases. The database monitoring tools are easy to use, affordably priced and comprehensive in scope. You can download a free trial of the Longitude product for evaluation.
Longitude monitors SQL Server 7, 2000, 2005 and 2008. Some of the key metrics measured are disk I/O, lock wait times, available memory, processor threads, page rates, number of user connections and transaction latency. Longitude also includes extensive reporting and alerting capabilities.
5. Zero Impact SQL Monitor
From SQL Power Tools, Zero Impact SQL Monitor (Zero Impact) claims to capture 100-percent of all end-user SQL, and does it without any middleware, proxies or connections to the database. Zero Impact installation isn't required to be on the local database host nor does it connect to, query or poll the database server for SQL. It lives up to its name of Zero Impact.
Some of Zero Impact's notable features are 30-day performance trend analysis, capture of 100-percent of all SQL text, real-time alerting, real-time monitoring of end-user performance, real-time monitoring of database response times, and advanced reporting and analysis. You can download a free trial from the SQL Power Tools website.
Ken Hess is a freelance writer who writes on a variety of open source topics including Linux, databases, and virtualization. He is also the coauthor of Practical Virtualization Solutions, which was published in October 2009. You may reach him through his web site at http://www.kenhess.com.