This is an interesting article from the president of SQL Power Tools, which produces tools for SQL Server (and other databases) that can be used to measure database end-user service levels. If you don’t know what an end-user database service level is, then you need to read this article. Before reading this article, I was unfamiliar with this important concept. –Webmaster The foundation behind all e-commerce, DSS and OLTP applications are database servers, such as SQL Server, which manage gigabytes to terabytes of data. Whether the system architecture is 3-tier, web server, distributed, or client-server based–the primary determinant in providing good end-user service is a well-functioning, performance-tuned database server.
The Problem
Unfortunately, conventional database monitoring approaches (such as SQL Profiler and Performance Monitor, and others) do not measure end-user service levels. Why? Because it is difficult to measure end-user response time when hundreds or thousands of users are accessing a database. How end-user service level monitoring is accomplished is also important. Few organizations have the time or operational resources to place agents on every end-user workstation to measure and monitor end-user service levels. Plus, the system architecture of a service level monitoring system must be 100% non-intrusive to the end-users, network and database server; or it simply will not be implementable in a production environment. Poor end-user service in the form of unreliable or inadequate end-user response time from a database server can negatively impact an organization’s financial results. Business opportunities may be lost and operational costs increased when end-users experience poor service. Plus, some end-users can elect to use another service channel when poor end-user service levels are experienced.
The Solution
The implementation of an end-user service level monitoring system is an absolute business must! If the architecture of the service level monitoring system is good, it will also be easy and quick to implement. A service level monitoring system should monitor at the server, database, application, individual end-user and SQL statement level the following service level metrics:
- average end-user response time
- average database server response time
- average network time
- transaction processing rate
- plus other metrics such as errors, bytes and packets transmitted across the network
By monitoring these service level metrics at the database server, database or application level–negatively trending average end-user response time can be immediately detected. These types of service level statistics allow DBAs, administrators, help desks, service representatives and IT management to easily track end-user service levels in real-time. Deteriorating end-user response time can then be immediately resolved prior to it coming to the attention of their end-users. For example, if average end-user response time begins to trend up 2% a day–it most likely will not be noticed by an end-user for a few weeks. However, with service level monitoring–a DBA, administrator, help desk, or service representative–can immediately detect this trend. This way, the problem to be resolved prior to it coming to the attention of the end-user community.
End-User Service Level Monitoring System Architecture
An end-user service level monitoring system should have no performance impact upon the end-users, the network or database servers. The system should also be 100% non-intrusive (e.g. require no intrusive middleware the end-users connect to) and not require agents installed on end-user workstations. The service level monitoring system should also measure end-user service levels by unique SQL statements, stored procedure or package execution. This allows the top N most frequently executed SQL statements to be easily monitored for end-user service level analysis. This is very important from a business perspective. Why? Because the aggregation of the service levels metrics at the level of an application, database or database server may not immediately detect trending SQL statement end-user response time. The classic example is where X% of the unique SQL statements begin to exhibit an increase in end-user response, however, the server level average response time has only slightly increased (e.g. not a sufficient amount at the server level to cause the situation to be investigated).
Conclusion
Service level monitoring of end-user response time, database server response time and network time at the database server, database, application and SQL statement level–allows deteriorating end-user service to be immediately detected and resolved prior to the situation being noticed by end-user business areas. Furthermore, since 80% of all database performance problems are related to poor SQL performance–service level analysis to the SQL statement level will help to proactively identify 80% of the database performance issues! Service level and SQL monitoring systems based upon a network sniffer architecture supports these business requirements with zero performance impact, no middleware, no client agents, a straight forward installation and a low ongoing cost of operation. If you would like to learn more about network sniffer architecture and how it can be used to monitor SQL Server database end-user service levels, you can by visiting our website at http://www.sqlpower.com/.
]]>