Sql Power Tools Pinpoints the Root Cause of Microsoft SQL Server Performance Issues

BACKGROUND: The Architect of the Capitol is responsible to the U.S. Congress for the maintenance, operation, development and preservation of the U.S. Capitol Complex.

PLATFORMS: Sql Power Tools is monitoring a Microsoft SQL Server, version 2000, running on a Windows 2003 server. The Windows client is running on Windows XP Pro workstations.

PROBLEM SOLVED: A commercial off-the-shelf package was obtained to facilitate the coordination of maintenance activities in the U.S. Capitol complex. The package records all maintenance tasks, assignments and characteristics from initial request through completion. The implementation was quite troublesome as the application generated frequent SQL lock/block conditions and unusual spikes in server utilization. The user community reported frequent periods when the application was unreasonably slow and nonresponsive. The implementation was further hampered by a random internal SQL Server crash which was related to database activities. Sql Power Tools was acquired in order to diagnose and remediate these issues.

PRODUCT FUNCTIONALITY: The ability of the product to capture, summarize and archive all of the code passed to the server along with the related performance data has proved critical to solving all of these issues. The definitive results obtained from the product helped to reduce the resistance to change that was encountered by effectively eliminating any doubt as to the root cause of the problems. We archive the logs captured, and this historical data was useful to generate a sense of urgency required to get the necessary changes implemented. One interesting result came from the “Top N” analysis, which revealed that a number of users had created scheduled tasks that executed every few minutes to check on a variety of status conditions. These were eliminated by creating a general purpose feature that provided the needed information to all the users in a timely manner. We encountered a seemingly random event that caused the entire SQL Server to halt processing as a result of an internal error. There was no alternative but to stop and restart SQL Server, often in the middle of the production hours. We were able to isolate the initiation of the problem in such a clear manner that Microsoft was compelled to acknowledge that the issue was with SQL Server. This resulted in corrective action. The logs have provided the evidence required to explain how, when and by whom a variety of ad hoc actions were taken that had caused data in the database to be corrupted or lost.

STRENGTHS: The main strengths include the ability to capture, summarize and archive all of the SQL code passed to the server along with the related performance data with minimal impact to the database server environment. Once equipped with this information, we can isolate and uncover the root cause of a problem and explain what caused it.

WEAKNESSES: When the product was first deployed, we were required to install a vendor GUI on each staff member’s workstation, which proved challenging to manage. Since then, Sql Power Tools has released a 100 percent Web-based user interface that allows any staff member to securely view the performance of multiple database servers through an intuitive and quite attractive graphical interface.

SELECTION CRITERIA: The purchase of Sql Power Tools was justified based upon the fact that this is the only product we found to perform this type of SQL statement data collection and storage with minimal impact on the servers involved.

DELIVERABLES: The product output includes 100 percent of the SQL text and performance statistics, end-user and database service level statistics, Top N SQL statistics and blocked process incidents. The output is stored in generation data sets and/or a database server repository. In addition, reports are created in real time or offline and are accessible from the Web-enabled client interface.

VENDOR SUPPORT: I can state without qualification that Sql Power Tools has delivered an exceptional level of support. Questions have been quickly answered, problems immediately addressed and a number of suggestions have been implemented.

DOCUMENTATION: The documentation is thorough, complete and well-written. Installation and configuration worked precisely as described and were completed without issues or a need to contact the vendor. The configuration options were well documented with useful examples.