Enterprise SQL Server Management and Performance Monitoring with dbWatch


By:   |   Updated: 2020-10-14   |   Comments (2)   |   Related: More > Monitoring


Enterprise SQL Server Management and Performance Monitoring with dbWatch

Problem

We have a large SQL Server environment across numerous locations that has grown tremendously over the years. However, our small team has difficulty gaining insight into the performance of our environment beyond just simple CPU, memory and disk utilization. The days of logging into every instance for monitoring are no longer realistic. We need to transition our team from single instance management and monitoring to managing the overall environment and addressing our most pressing needs to support the organization. How can we move from managing the trees to managing the forest in an efficient and automated manner with a small team?

Solution

As SQL Server environments continue to grow, so does the management and performance complexity. In the past, DBAs had a handful of critical production instances and the supporting development and testing instances. As SQL Server has grown in popularity, DBAs are now challenged with managing 100’s to 1000’s of SQL Server instances in multiple physical locations and in the cloud. To be productive (and not be fire-fighting constantly), DBAs have tried to build scripts in T-SQL and PowerShell as well as take advantage of some features in SQL Server Management Studio. Unfortunately, those scripts are difficult to maintain, lack insight into the overall environmental needs and can compete with addressing core business needs.

Example: Understanding if a SQL Server instance is over allocated, starved or not in use can be difficult to determine in some circumstances and also costly to the organization. The over allocated instance could be tying up expensive CPU, memory and storage that another SQL Server could use to improve application performance. This reallocation of resources could reduce wait times in the call center, cutting the average cost of hold times and increasing client satisfaction ratings. Without environmental consistency and insight into the overall environment, this is difficult to uncover. This is the time to move from managing individual SQL Server instances to the overall environment.

One solution that is transforming how SQL Server DBAs work is dbWatch. dbWatch has introduced Control Center, an automation tool, managing individual SQL Server instances and the overall SQL Server Farm in one application. Much of these can be accomplished with a few lines of code, exclusive to dbWatch, called Farm Data Language to support:

  • Monitoring
  • Management
  • Dashboards
  • Incident Workflows
  • Automation
  • Reporting
  • Security and Access Control
  • Integration

dbWatch’s Control Center is best suited for large complex IT environments such as Managed Service Providers (MSP’s), health care, government and large enterprises with SQL Server, Oracle and MySQL databases. Let’s see dbWatch in action.

Farm Views of a SQL Server Environment

dbWatch is built on a distributed architecture using Jobs to gather performance and management data across the SQL Server farm. In the interface below, there are three ways to access the data: Summarized Job Status, Farm Jobs or Instance Status.

dbWatch Control Center Job Status

First, the Summarized Job Status in the center of the interface gathers data from each of the jobs and organizes the data as an Alarm, Warning, or OK. DBAs get an overview of job statuses in order to prioritize pressing issues, and monitor logs, backups, and database performance.

Second, the Farm Jobs in the left pane of the interface above enables you to drill into each job status to determine the issue and begin working towards resolution.

Third, the Instance Status on the top left of the interface organizes and includes color coding as OK (green), Warning (orange) or Alarm (red). This enables you to drill into each instance and begin troubleshooting as shown below.

dbWatch Control Center Issue Status

Beyond performance and management troubleshooting, the dbWatch Farm Jobs provide insight into the number of instances and databases as well as SQL Server version and edition. This informative interface provides a global view of the environment and can easily help to identify future upgrade needs.

dbWatch Control Center Instance Data

dbWatch’s Farm view is not limited to high-level metrics. The interface enables you to dig into performance metrics including:

  • CPU
  • Memory
  • Disk
  • Waits
  • File IO
  • DML
  • Logical Reads
  • Page Life Expectancy

These interfaces display metrics relative to the entire environment to understand allocation, resource usage, min, max, average and counter specific data.  Below is an example of the memory usage across the SQL Server farm.

dbWatch Control Center Memory Usage

Data can also be consolidated across metrics to understand allocation and usage as shown below with disk and memory usage per platform. In the example shown, a bar chart displays the top 20 instances. This feature can be customized by adding another variable such as disk usage to gain further insight into the resource utilization.

dbWatch Control Center Total Disk and Memory Usage

dbWatch’s Farm View provides a comprehensive view of your SQL Server environment to understand hardware resource utilization, gain insight into performance metrics and usage patterns across the environment as well as prioritize hardware resources and allocate DBA time to the most pressing needs. We have just scratched the surface with what is available with the SQL Server Farms View. dbWatch is a large enterprise application with the flexibility to provide insight into numerous SQL Server editions in addition to Oracle and MySQL, all in one interface.

dbWatch Problem Resolution

dbWatch seamlessly moves from the overall SQL Server Farm metrics to detailed instance metrics to enable SQL Server DBAs to make informed management and monitoring decisions. dbWatch’s instance level interface has similarities with Microsoft SQL Server Management Studio (SSMS) including the ability to browse objects, correct issues with a point and click interface and also run queries. But with all of the tool’s features, it can be considered an all in one tool for management and monitoring. The dbWatch interface is feature rich with numerous graphics to help pinpoint issues and drill into the database engine to resolve these issues including:

  • dbWatch Alerts
  • Configuration
  • Performance
  • Sessions
  • Disk and Memory Usage
  • Backups
  • SQL Server Error Logs
  • Databases
  • Security
  • SQL Server Agent
  • Operating System Manager
  • Audit
  • SQL Worksheet

Each of these high-level items expands with a list of numerous child options for more granular troubleshooting, analysis and correction. Much of this subsequent data is not available directly in SSMS; it would be necessary to build, maintain and run queries across numerous SQL Server editions. This is another area where dbWatch improves the productivity of DBAs by giving them access to data in an intuitive interface.

dbWatch Control Center Instance View

Below is one example of the granularity and flexibility of the dbWatch instance interface.  Session data is broken down in numerous ways (Sessions per Host, Sessions per Login, Sessions per Program, Sessions per Database, Session history and Background processes) to assist with troubleshooting an active incident.  Directly on the interface you can also right click on a SPID and end the session if blocking or deadlocking is occurring.

dbWatch Control Center Sessions View

Below is another example of dbWatch instance level metrics for disk and memory usage. This is a completely different interface as compared to the sessions interface. This interface includes disk space usage by external files, transaction log files, data files and free space in addition to memory metrics related to host memory usage and top memory usage in SQL Server.  This is just a simple example to show how each interface is contextual and is simple to ascertain value.

dbWatch Control Center Disk Space

Once again, this description is just the tip of the iceberg in terms of features to help troubleshoot and correct SQL Server performance and management issues. Together, there are 50+ interfaces at an instance level with a tremendous amount of information that is consolidated and organized. This data is not just raw counters. Informative graphics are included to quickly paint a picture with meaningful data in order to take action. Rather than SQL Server Professionals building and maintaining queries, with dbWatch they can easily manage, monitor and tune their environment and meet core business needs all from the comfort of a single application.

Farm Data Language

dbWatch’s Control Center is four years in the making. At the core of this release is the Farm Data Language which enables much of the functionality of the SQL Server Farm and Instance level views. dbWatch built the Farm Data Language which is a query and path-based language to query across all database platforms (SQL Server, Oracle, MySQL) and versions (SQL Server 2019, 2017, etc.), logical operators (join, merging, parameters, variables, calculations, regex, etc.) and functions (sum, count, average, etc.) to display in visuals (tabular, graphs, etc.).

To use the Farm Data Language, start by specifying an instance. Next, specify the entities or values for your query such as “activity_phyiscal_reads” or “activity_cpu_count”. From there, specify the task such as “Locks held and statistics” or “table and index statistics”.  To add logic, you can specify operators to your query.

Here is one simple example for the session count and disk usage for a particular instance:

instance->i/name{"Database instance"}/$i/total_session_count{"Session count"}/$i/disk_usage{"Disk usage"}

In the example above, we’re tabularizing the instances and displaying their instance name, total session count and disk usage. name{} serves as the column header so as total_session_count{} and disk_usage{}. Inside it the column headers read “Database instance” denoting the given instance name. This also applies to “Session count” and “Disk usage” which are the corresponding aggregated data for each database instance for number of sessions and disk memory consumption, respectively. As you have noticed, /$i/ is the delimiter and the variable i set on the first line of the code. This can be read that we set lowercase i as data will be aggregated per instance-level.

As you browse objects in dbWatch, you can also view all of the Farm Data Language code being issued as shown below:

dbWatch Control Center Farm Data Language

The Farm Data Language is enabling organizations to effeciently access data from numerous instances running on varying platforms with little impact to the source systems. This language also enables DBAs to build custom jobs to best manage and monitor their unique environments.

dbWatch Summary

We have just covered the tip of the iceberg with dbWatch:

  • Farm Views
  • Monitoring
  • Management
  • Resolving SQL Server Issues
  • Dashboards
  • Instance Views
  • Farm Data Language

The dbWatch product is very feature rich and each of these topics can be explored in a much deeper level.

Beyond the items covered in this article, dbWatch also includes unique features related to:

  • Database Maintenance
  • Reporting
  • Incident Workflows
  • Replication Monitoring
  • Automation
  • SQL Worksheet
  • Reporting
  • Web Server
  • Security and Access Control
  • Integration\Extensions
  • Command Line Interface
  • Linux Support
  • DevOps

dbWatch is a robust application to streamline management and monitoring for large database environments (SQL Server, Oracle, MySQL, etc.). This is accomplished by bringing standardization, consistency, automation and efficiency to the environment.  The product enables DBAs to begin managing their overall environment, with the ability to seamlessly drill into an individual SQL Server instance to troubleshoot and resolve issues.

Next Steps

How do I get started with dbWatch?

  1. Identify your SQL Server monitoring and management needs
    1. Enterprise view
    2. Prioritized issues
    3. Issue resolution at instance level
    4. Multi-platform and multi-edition support
    5. No impact to source systems
    6. Query interface
  2. Discuss the needs and options with your team
  3. Reach out to dbWatch for a personalized demo by emailing - [email protected]
  4. Download a trial edition of dbWatch Control Center
  5. Work through a proof of concept in your environment
    1. Click here for the Getting Started with dbWatch Control Center Guide
  6. Contact Support with any Questions
    1. dbWatch FAQ
    2. Video Resources
  7. Demonstrate the value of dbWatch Control Center
    1. Determine new insights your team will have into your database environment
    2. Calculate the time savings for your team
  8. Discuss the next steps with your team

MSSQLTips.com Product Spotlight sponsored by dbWatch makers of Control Center.



Last Updated: 2020-10-14


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article




Thursday, October 15, 2020 - 10:17:32 AM - Jeremy Kadlec Back To Top (86645)
MM,

I believe dbWatch supports PostgreSQL 8.2 through 11.

Thank you,
Jeremy Kadlec

Thursday, October 15, 2020 - 3:35:05 AM - MM Back To Top (86641)
Does this work for Postgresql as well?


download





Recommended Reading

Determining space used for all tables in a SQL Server database

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server High CPU Query Use Monitoring with PowerShell

Monitoring SQL Server Buffer Pool Usage by Database with PowerShell








get free sql tips
agree to terms