Get Core SQLServer Health Metrics using Dynamic SQL

Spread the love

Recently, on his blog, Steve Jones invited the SQL community to write blog posts sharing their way of using SQL dynamically.

Accepting the invitation, in this blog, I am going to talk about how I am using Dynamic SQL to monitor the core health metrics of baselined SQL Server instances as part of the open-source monitoring tool SQLMonitor.

TSQL-Tuesday

In SQLMonitor, on the central/inventory server, we have one view dbo.vw_all_server_info which is populated every 20 seconds with the core SQL Server metrics of each SQL Server instance we baseline. The job which takes care of this task is (dba) Get-AllServerInfo with the below 2-step codes –

If we look at the above 2 job step code blocks, we may guess by now, the base of this core SQL Server health metrics collection is stored procedure dbo.usp_GetAllServerInfo. Below is the result of the stored procedure in general when no parameters are used while calling –

To put it in simple terms, the stored procedure dbo.usp_GetAllServerInfo, when executed, connects to each SQL Server instance baselined with SQLMonitor, and gathers a total of 25-26 core health metrics in an efficient manner without burdening each server. Below are the key features/requirement of this procedure –

  • Actively query specific servers, a list of servers, or all servers depending on the parameter values. Controlled by parameter @servers.
  • Query specific health metrics like cpu cores, sql schedulers, % cpu utilization, memory grant pending etc depending on that what metrics are requested. Controlled by parameter @output.
  • When a table name is passed in parameter @result_to_table, then create the table if not exists, and populate it with the required metrics for mentioned servers.
  • Debug procedure with @verbose in the event of some bug/analysis requirement.
  • The flexibility of adding more metrics collection easily without disrupting existing metric collection.
  • Advanced enough to work in mixed environments where the inventory/central server and each server being baselined could be in different zones, domains, SQL Service Accounts, etc.

Let’s dig deeper into the code of dbo.usp_GetAllServerInfo in order to understand how we are utilizing Linked Server with Dynamic SQL to meet the above requirements.

In the above code, the procedure has parameters that accept comma-separated lists of servers/metrics to collect. We parse these lists and shred the data into table variables to get rid of any SQL Injections possibility. From here, we loop through each server that is qualified and query it using its corresponding Linked Server in combination with OPENQUERY. Below is just a snap inside above-stored procedure body. In this, we can see most of the components involved while fetching one metric at a time.

Dynamic SQL To Query Remote Server Health
Dynamic SQL To Query Remote Server Health

This stored procedure is quite flexible, efficient, and useful for DBA. I often answer a lot of user queries just from the result of this stored procedure without even connecting to the other SQL Servers in my organization.

I hope this would help any DBA/developer looking to explore an effective usage of a dynamic query, and how to utilize the same to connect to various other servers from a single stored procedure.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.