SQL Server � Diagnostic Tools EvaluationBy Justin Hostettler-Davies (c) 2007 OverviewBelow is a list of the key benefits which we were looking for when choosing a tool to manage and administer our SQL Server environment:-
(a) Increases SQL Server availability
(b) Provides real-time monitoring from a Web-like browser
(c) Provides a comprehensive view of current performance
(d) Ability to gather data across multiple server simultaneously
(e) Stores data in a controlled repository
(f) Ability to supply continuous unattended monitoring
(g) Improves the productivity of both skilled and novice DBAs
(h) Offers easy installation and use
I chose to evaluate 2 of the leading SQL-performance monitoring software tools namely, SQLDiagnostic Manager (Idera) and Toad for SQL Server (Quest). These 2 tools were chosen following recommendation from other colleagues. SQL Diagnostic Manager (DM) � IderaSQL diagnostic manager provides an unprecedented level of diagnostic information about the health, performance and status of all SQL Servers in an environment � from a central point of control. Using a unique agentless architecture with a low footprint, SQL diagnostic manager continuously monitors critical SQL Servers, gathering diagnostic information in real-time and keeping database managers informed with customized alerts.
Key benefits provided by SQL diagnostic manager include:
Increases SQL Server availability Enables DBAs and other administrators to quickly investigate, diagnose and correct performance and availability problems. On-screen recovery tasks make it easy to solve a problem once is diagnosed. Convenient tasks include start/stop a SQL Agent job, stop a blocking process, terminate a user connection, or rebuild an index on a table.
Provides real-time monitoring from a Web browser Manage your SQL Server anytime, anywhere. The SQLdm Web Console enables DBAs to monitor the performance of SQL Servers in real time from the office, home, or local internet caf�. Provides a comprehensive view of current performance Collects and displays extensive real-time activity and performance information for processes, caches, alerts, memory, transactions, logs, locks, replication, reorganization status and trace data.
Gathers data across multiple server simultaneously Collects and sorts data, enabling you to view information for one server or for the entire environment. You can easily identify the worst-performing stored procedures across all your servers or learn whether a SQL Agent maintenance job has failed on one server or all simultaneously. Stores data in a Metrics Repository Metrics from across the SQL Server enterprise are stored in a central repository, enabling DBAs to create a variety of reports for strategic analysis � such as trend analysis, capacity planning, data comparison, data correlation, and forecasting. Supplies continuous unattended monitoring SQLdm monitors your mission-critical SQL applications 24 x 7 whether a DBA is logged in or not. A powerful service-driven collection and alerting engine keeps DBAs informed even while away. Improves the productivity of both skilled and novice DBAs Delivers organized data views with built-in data collection. These views eliminate the need to apply complex system commands, while built-in features detail exactly what the interface displays. Offers easy installation and use Installs quickly and supports a mixed environment of Windows integrated security and SQL Server native security. A typical installation and configuration will take less than 30 minutes.
Toad for SQL Server / Spotlight - QuestKey benefits provided by Toad/Quest tools include:
Increase Server availability Spotlight on SQL Server Enterprise to investigate, diagnose and correct performance and availability problems. Toad for SQL Server for on-screen/convenient recovery tasks to make it easy to solve a problem once is diagnosed. Basic tasks such as start/stop a SQL Agent job, stop a blocking process, terminate a user connection, or rebuild an index on a table, again achievable via Toad for SQL Server
Provide a comprehensive view of current performance
Spotlight on SQL Server Enterprise will allow us to collect and display extensive real-time activity and performance information for processes, caches, alerts, memory, transactions, logs, locks, replication, reorganization status and trace data.
Gather data across multiple servers simultaneously
Spotlight on SQL Server Enterprise has the ability to collect and sort data, enabling us to view information for one server or for our entire environment.
Reporting capabilities?
Spotlight on SQL Server Enterprise has the limited ability to create a variety of reports for strategic analysis � will need another tool �Capacity Manager for SQL Server� to handle this
Supply continuous unattended / non system exhaustive monitoring
Spotlight on SQL Server EnterpriseA collection/alerting engine that keeps us informed even when not logged into tool?
Improve the productivity of us DBAs Toad for SQL ServerEliminate the need to apply complex system commands, while built-in features detail exactly what the interface displays.
Easy installation and use ? Spotlight on SQL Server Enterprise and Toad for SQL ServerA tool that installs quickly and supports a mixed environment of Windows integrated security and SQL Server native security.
The use of Quest would require the following three tools:- 1. Toad� for SQL ServerToad� for SQL Server is a database development and administration tool that increases productivity and ensures optimal code quality for your SQL Server environment.But again, even when this tool is purchased � extra modules are available as �bolt-ons�:- Quest DBA Module (Users can increase Toad for SQL Server�s power and flexibility by adding the Quest DBA module to automate day-to-day database management. The integrated Quest DBA module provides a well-rounded environment for DBAs and empowered developers to handle common administration tasks such as quickly comparing and synchronizing database schemas, servers, and data, This module also provides a feature to execute across multiple databases via the Editor interface and an interface to perform security migration across multiple servers. Included in the Quest DBA Module is the Knowledge Xpert for SQL Server Administration and T-SQL. The Knowledge Xpert for SQL Server Administration answers 70-80 percent of your daily SQL Server administration and T-SQL questions instantly. Thousands of topics provide the background information, syntax and working examples you need to apply solutions immediately)
T-SQL Debugger (The Debugger for Toad for SQL Server lets you code and debug)
Knowledge Xpert for SQL Server (Research, analyze and solve SQL Server 2000 problems instantly with this comprehensive coding resource Xpert Tuning (supplements SQL Server tuning skills for developers, stepping up their application tuning capabilities)Benchmark Factory for SQL Server Developer's Edition (to test user load on your SQL Server database)
Toad Data Modeler (Toad Data Modeler helps organizations create, maintain and document their database systems)
To gain all of these benefits you would need the complete package � which gets bundled up as Toad for SQL Server Suite 2. Spotlight� on SQL Server EnterpriseAllows you to discover, diagnose and resolve SQL Server performance issues
Spotlight on SQL Server Enterprise provides an agent-less easy-to-use database issue discovery solution that enables you to identify and resolve SQL Server performance problems within your SQL Server environment. This powerful tool pinpoints the underlying SQL server contention issues and processes for fast and efficient database administration. Through one single interface. 3. Capacity Manager for SQL ServerCapacity Manager for SQL Server provides a complete, detailed portrait of resource consumption and storage requirements with easily interpreted reports, charts and graphs. A consolidated view permits you to manage and control all databases and datafiles from a single interface. Over time, the rate of growth of instances, databases, and objects, and the rate of utilization of CPU, Memory and I/O can be plotted, enabling you to evaluate the most storage resource-intensive portions of architecture and those objects which are growing most rapidly. Over-allocated space can be reclaimed from databases, databases in need of extra storage can be augmented and server resources can be managed more effectively. Finally, table partitioning is fully supported, including rolling partitions and switching partitions in/out, making Capacity Manager an ideal solution to help manage your transition to SQL Server 2005.
RecommendationsIn the limited amount of time I have had to evaluate Ideras� SQL diagnostic manager I was impressed by its ability to diagnose and resolve problems quickly, often before the user knows a problem exists. Because it is housed �all under one roof� it meant the majority of the benefits described before were easy to find and use. With Quest, the existing tool we have (Spotlight), was limited it what benefits it could meet, and in order for more vigorous evaluation to be performed, we would need to get hold of trial versions of all three tools, and spend a lot more time trying to evaluate the 3 products rather than Ideras sole tool.SQL diagnostic manager is an extremely effective tool for identifying performance issues that may involve SQL Server. The �Worst Performing� section is especially important as it immediately focuses on any problem areas. Not only does SQL diagnostic manager tell us which stored procedures are slow, which tables need fragmenting etc, it can also pinpoint specific lines of SQL � and for a non-programming team as we are would save us hours of time!Quest offered similar benefits to the above but in many instances would require the crossover between two products (Spotlight and Toad for SQL Server, although each tool does provide easy to use links between each other) Idera�s ability to produce db growth statistics / charts etc particular impressed me allowing me to easily and quickly produce growth charts and plot future capacity planning etc.With the Quest tools � this was only available via the Capacity Manager for SQL tool. To conclude:- Both products could save us a lot of time by taking care of the overhead of database monitoring in a SQL Server environment. SQL Diagnostic Manager would give us everything we need to administer and manage our SQL Server environment in one easy-to-use tool. And I believe that to be the key overriding feature here, the easier a tool is to use for the individual concerned the better, having every in one bundle eases the role of the DBA � these tools are meant to be of use to us, we wouldn�t want to spend hours working out how to use a product (or in Quests case 3 of them!) I used Idera to report on one of our current production servers to gain real-time statistics for the training department during certain exercises. I was unable to carry out this exercise using Spotlight for SQL Server alone.
No pricing information has been supplied to me during this evaluation process.
Justin Hostettler-Davies (JHD) has 10 years DBA expereince in a cross platform environment specialising in both Oracle on Unix and SQL Server on Windows. JHD has spent most of his career supporting mission critical applications for an Emergency Response organisation in the UK. JHD has kindly agreed to share some of his most valuable experiences with us through a series of articles, available exclusively at
gre-sqlserver-solutions.com!
|
