Zero Impact Service Level Monitor
In either real-time, or for later analysis, the Zero Impact Service Level Monitor is used to view and analyze end-user response time. For ease of analysis, the application allows you to group the data by a time interval you specify, such as 60 seconds, and then to find answers to these questions:
- What is the average end-user response time?
- What is the average database server response time?
- What is the database server transaction processing rate?
- What are the number of rows returned?
- What SQL statements have run at what times?
- How is the network affecting database performance?
These questions, and more, can be answered for most any time period you are interested in. You can also use this data to perform trend analysis to determine how these numbers are changing over time, helping you to predict potential slowdowns before the get significant. The sooner you know of potential problems, the sooner you can start fighting them. The tool can also be used to combat end-user complaints or subjective feedback as the service level analysis provides DBAs with hard metrics and objective data which support any database server performance trends.
Zero Impact Top N SQL Analyzer
This component of the Zero Power Tools suite is designed to help you analyze the actual Transact-SQL (or stored procedure) that is being sent from the client or middle tier to SQL Server, helping you to identify slow performing queries, or queries that run repeatedly.
While reducing a slow query that runs once a day from 5 minutes to 1 minute is important, it is often more important to reduce a fast running query from 2 seconds to 1 second that runs 10,000 times a day. This tool makes it possible to find those queries that need to be tuned the most.
The Top N Sql Analyzer product performs its analysis by normalizing a day or week’s worth of production SQL to pinpoint what SQL is consuming 90% of the database server’s resources. For example, an average normalization reduces a 15 million SQL capture to 2,000-4,000 unique SQL statements. This ensures that DBA’s have accurate knowledge of what SQL is executing the most frequently and taking the most resources (i.e. running the longest on the end user, returning too many rows or bytes, etc.).
The Zero Impact Top N SQL Analyzer can perform these types of analysis:
- Identifies those SQL statements that run most frequently for a given time period. For example, you can view the top 100 most frequently run queries for the last 24 hours.
- Identifies the SQL statements that use the most SQL Server resources.
- Identifies SQL statements that run frequently, and also have poor average end-user response times.
- Calculate the average, cumulative, best response time and worst response time, network time, and rows returned for the top queries run by SQL Server.
- Calculate the service level statistics based on a specified time period for a specific user or users.
These reports, and more, are available to help you pinpoint slow-running queries. DBAs can also be alerted to the best and worst end user or database server response times (15+ alert conditions supported via email, pager, mobile). Alerting allows DBAs to only review poor performing SQL which meets their alert conditions. The alert data can also be sent to development or QA teams for review or performance tuning. Once you have identified the problem queries, you can take the appropriate steps to tune them.
Stealth Blocked Process Monitor
One of the biggest hidden performance problems in SQL Server are blocked processes caused by poorly designed code. Its a hidden problem because it is generally very difficult to identify the causes of blocked processes, that is, unless you are using the Stealth Blocked Process Monitor.
The Stealth Blocked Process Monitor is designed to monitor and detect SQL code that is blocking other user’s from performing their tasks. It has the ability to identify the code in question, including the SQL text and show plan description, the database locks involved, hardware resource utilization, and the length of the block. All this information can then be used to tune that code that is causing the blocking issues.
DBA’s can also specify to only look for blocked processes when end user response time (s) begin to deteriorate or if a process is blocked for more than N seconds send an email, pager or mobile alerts.
In addition, it provides:
- The ability to analyze, graph, and archive blocked processes to a database.
- The ability to reconstruct blocking incidents.
- The ability to rank the SQL code causing the blocking by those that are most frequently involved in the blocking, those that cause the worst end-user response time, and those that use the most server resources.
- Real-time alerts when blocking occurs.
The Stealth Blocked Process Monitor provides an in-depth look at current SQL Server activity that can’t be found anywhere else.