Do you sometimes wonder how the pieces of your SQL Server systems are doing over time? Do you often wonder how they are doing in the moment? You can monitor the health and activity of your instances and databases using SQLFacts, a FREE suite of tools for SQL Server database engineers. Please refer to an earlier article for a general description of the SQLFacts toolkit.

SQLFacts provides many tools for performance tuning. A previous article covered the index-related performance tuning tools in SQLFacts. A previous article covered the query-related performance tuning tools in SQLFacts.

This article will focus on the monitoring and status tools in SQLFacts. Be sure to see the README file in the SQLFacts toolkit download for more information about all the tools.

NOTE: Most of the tools described in this article do not run with Azure SQL Database. The Azure SQL Database service does not provide access to the SQL Server instance where the database is hosted. Most of the tools described here depend on information from the instance as the basis for their analysis. The Bufferin tool and the Statistics tool are able to run with Azure SQL Database, but none of the others. This note does not apply to SQL Server on Azure Virtual Machines or Azure SQL Managed Instance.

A previous article (see above) described the Resources tool, which monitors for heavy usage of RAM or tempdb.

A previous article (see above) described the Blocking tool, which monitors for heavy blocking by processes.

MetricsNow

The MetricsNow tool assists with researching unusually slow performance. It provides current values for many performance metrics.

There are metrics about RAM usage, SQL code plan caching, CPU activity, file read/write activity, wait statistics, and much more.

MetricsNow is a stand-alone tool for an emergency situation. It gathers monitoring data several times to reveal trends. There are two parameters near the start of the SQL code to specify how many times the monitoring data will be gathered and how long to wait between each time. After the monitoring data has been collected, seven result sets are displayed. The result sets are described below. It may be wise to save a copy of the result sets during a period when the server is behaving normally, even if it's done as a screen shot. A saved copy provides a useful baseline for comparison when something is going wrong later.

• Result set 1 shows metrics related to current RAM usage by the SQL Server instance.
• Result set 2 shows metrics related to current plan caching by the SQL Server instance.
• Result set 3 shows metrics related to processing activity by the SQL Server instance. It represents the current state.
• Result set 4 shows metrics related to processing activity by the SQL Server instance. It represents the change in state over a period of time (Seconds).
• Result set 5 shows metrics related to wait statistics and locks for the SQL Server instance.
• Result set 6 shows cumulative waits encountered by the SQL Server instance, as observed when each reading was performed.
• Result set 7 shows metrics related to data file read/write operations by the SQL Server instance.

MetricsHistory

The MetricsHistory tool implements a complete monitoring system to gather, store, monitor, and examine historic performance metrics.

There are metrics about RAM usage, SQL code plan caching, CPU activity, file read/write activity, wait statistics, and much more.

MetricsHistory creates seven tables and 14 stored procedures in the current database (often a separate database). The tables will contain the collected historic performance data. The tables are populated by four of the stored procedures. The system was designed to have these four very lightweight stored procedures get executed automatically every few minutes. This is typically done through scheduled SQL Server Agent jobs. The collected historic performance data is examined with nine of the stored procedures. These stored procedures return nine different result sets, each one focused on different kinds of performance metrics. The main result sets are described under MetricsNow (see above). The other two result sets are variations of Result set 5. The collected historic performance data is pruned with the last stored procedure.

MetricsHistory also includes an alerting component in the form of a separate stored procedure. The stored procedure analyzes the collected monitoring data. It looks for any readings that appear to be abnormal. It sends an alert message when it finds a potential issue. The stored procedure uses self-adjusting formulas to avoid excessive alerts. It should be scheduled to run automatically along with the stored procedures that gather monitoring data.

MetricsHistory does basically the same things as a commercial monitoring tool. However, it does not include a GUI with graphs and charts. If you prefer a GUI (other than SSMS) for viewing the historic performance data then a commercial monitoring tool would be a better choice for you.

AGLatency

The AGLatency tool returns information for databases in Always On Availability Groups. The SQL code must be run on the primary replica.

• Result set 1 is for transaction log usage of each database and general information for the Availability Group it's in.
• Result set 2 is for latency between the primary replica server and the secondary replica server(s), for each database.

Databases

The Databases tool returns information for all user databases (and tempdb) on the SQL Server instance.

The information includes owner name, recovery model, create date, detailed information about size/usage for data files and transaction logs, and dates for the most recent BACKUP operations.

• Result set 1 includes owner name, recovery model, create date, and dates for the most recent BACKUP operations.
• Result set 2 includes aggregated information about size/usage for data files.
• Result set 3 includes aggregated information about size/usage for transaction logs.
• Result set 4 includes aggregated information about size/usage for data files by filegroup.
• Result set 5 includes information about size/usage for individual data files and the volumes where they are stored.

BACKUP / RESTORE

The BACKUP tool and the RESTORE tool work together. They can be used when a RESTORE operation must be performed. They can be very handy when a point-in-time RESTORE operation must be done in an emergency.

The BACKUP tool returns two result sets drawing from the BACKUP history retained by SQL Server.

• Result set 1 contains a summary of BACKUP operations.
• Result set 2 contains a list of all BACKUP operations. Identify the row for the last BACKUP operation desired in a RESTORE operation. Copy the BACKUP_ID (backup_set_id) value.

The RESTORE tool generates a series of RESTORE statements up through the BACKUP identified. Paste the BACKUP_ID (backup_set_id) value from the BACKUP tool into the appropriate line of the RESTORE tool (near the start of the SQL code). Modify the next line of the RESTORE tool to specify a date/time for a point-in-time restore, if desired. Execute the RESTORE tool. Copy the contents of the SQLCode column of the result set, paste into another connection window, and adjust if/as necessary.

Auditor

The Auditor tool returns information for SQL Server Audits. Result sets 1/2/3 contain an increasing level of detail. Result set 4 contains audit results. It's included only when there's exactly one audit with a file destination. The Messages tab will contain a generated SQL statement for each audit with a file destination.

• Result set 1 contains information for SQL Server Audits.
• Result set 2 contains information for SQL Server Audit server/database specifications.
• Result set 3 contains information for SQL Server Audit server/database specification details, which includes what's being audited.
• Result set 4 contains audit results, IF there's exactly one audit with a file destination.

SQLAgent

The SQLAgent tool returns basic information for SQL Server Agent jobs and job schedules. The information for schedules is presented in an hour-by-hour format for each day of the week. It provides a simple, visual representation for job starts during each hour of the day. A numeric value in an hour column is the minute for the first job start during the hour.

NOTE: This tool does not include every type of job schedule. It includes schedules with a Daily (every 1 day) frequency or a Weekly (every 1 week) frequency. These are the most common types of job schedules.

• Result set 1 contains basic information for SQL Server Agent jobs.
• Result sets 2 through 8 contain basic information for SQL Server Agent job schedules in a crosstab format for each day. The days of the week to include can be customized.

Bufferin

The Bufferin tool returns a list of the data in the SQL Server buffer cache, aggregated by database object. The information reveals how much of which tables/indexes are currently available in the buffer cache. The information is useful for diagnosing small PLE values (see Page_Life in Metrics).

Please be aware, this process may run for several minutes if the buffer cache is extremely large and fully loaded.

Statistics

The Statistics tool returns information for statistics associated with indexes and columns. The statistics guide the query optimizer as it prepares execution plans.

The information for each statistic includes date/time when last updated, number of rows when last updated, number of changes since last updated, threshold where SQL Server would automatically perform an update, and a generated UPDATE STATISTICS statement. The threshold value is a number of changes since last updated. SQL Server will automatically perform an update after a certain number of changes (rows) have occurred. This tool can be used to determine when the threshold of changes is being approached and prepare to perform UPDATE STATISTICS at a less busy time. This is more likely with versions prior to SQL Server 2016.

There's a variable near the start of the SQL code to specify a percentage of the threshold. This affects whether the generated UPDATE STATISTICS statement is disabled or not.

• Result set 1 is for statistics associated with indexes.
• Result set 2 is for statistics associated with columns.

Summary

SQL Server consists of many pieces and it's critical to know how they are configured and operating. Sometimes you need to view the health and activity of the pieces over time. Sometimes you need to view the status of things in the moment. SQLFacts is a free toolkit with many tools for monitoring your systems. Download it, and take advantage of the many features!