Pyn Logic's Enzo Audit was initially designed to track and log all commands issued against your SQL Server database, track configuration changes, and monitor and audit all activity on your SQL Server database servers. With Enzo Audit 2009, which works with SQL Server 2000 and later, Pyn Logic evolved Enzo Audit from a pure security product to one that also addresses your query performance monitoring needs.
Enzo Audit 2009 comes with two separate installs: a server-side installation that provides auditing capabilities by listening to and monitoring one or more database servers to track and gather selected activity (e.g., connections, T-SQL statements, Remote Procedure Calls—RPCs), and a separate client-side install that provides real-time feedback.
Enzo Audit 2009
The server component, a Windows service installed on each monitored SQL Server box, provides the auditing capabilities through defined “listeners.” A listener is a SQL Server trace that’s started and managed by Enzo Audit. You can apply filters for each listener that let you include or exclude audit information at the database level, and Events that control the type of events you want to capture, such as failed logins or executed SQL code, including embedded SQL, views, and stored procedures. You can also define notification methods in the Enzo Audit Console so that you can receive alerts about specific events. Listeners are defined and configured via the Enzo Audit Console, which is installed as part of the server component install. Be sure to read the server component installation guide prior to applying any configurations because there are many configuration options.
One of Enzo Audit's strengths is its ability to monitor and audit a wealth of SQL Server information, including failed logins, RPCs, password changes, and object access. A great new enhancement, however, is its ability to track executed T-SQL against defined performance thresholds to catch poor-performing queries. For example, when you execute queries, Enzo Audit compares the results and execution of the query to the configured thresholds, events, and filters. If a query exceeds one of the defined thresholds, you’re immediately notified via the client-side alert pop-up window (shown in Figure 1), which provides instant feedback regarding the query that was just executed and the thresholds that were violated.
This approach helps catch problems early in the development life cycle. The query thresholds are defined in the Enzo Audit Console and include query duration, reads, writes, and CPU utilization as criteria for determining query performance. You’ll receive alerts only for your specific queries. The query itself is also stored for reporting and tracking purposes.
Enzo Audit comes with three built-in reports that provide you with an in-depth look at how T-SQL query performance has improved or degraded over time for a given application or user. For example, the Most Improved Batches and Statements report provides trending and history information about specific queries whose performance has improved over a certain period of time, and the Most Degraded Batches and Statements report provides the same information for queries that have degraded in performance. Both of these reports can be filtered for user, application, or date range. A third report, Worst Batches and Statements, displays the 10 worst-performing queries for a given date or user.
Overall, Enzo Audit 2009 is a breath of fresh air in its approach to performance troubleshooting. By putting the focus earlier in the development cycle and providing instant query feedback, performance problems are reduced and you save time in the long run. Enzo Audit might be too expensive for some small development shops, but it’s necessary for midsized to large environments.