Have you ever wondered how your SQL Server systems are performing over time? Do you often need to monitor their health and activity in real-time? If so, you’ll be glad to know that there are tools available to help you with these tasks. One such tool is SQLFacts, a FREE suite of tools designed specifically for SQL Server database engineers.
SQLFacts provides a wide range of tools for performance tuning, monitoring, and status tracking. In a previous article, we discussed the index-related performance tuning tools in SQLFacts. In another article, we covered the query-related performance tuning tools. In this article, we will focus on the monitoring and status tools provided by SQLFacts.
Before we dive into the details, it’s important to note that most of the tools described here do not work with Azure SQL Database. The Azure SQL Database service does not provide access to the underlying SQL Server instance, which is required for these tools to function properly. However, some tools like Bufferin and Statistics can be used with Azure SQL Database. This limitation does not apply to SQL Server on Azure Virtual Machines or Azure SQL Managed Instance.
MetricsNow
The MetricsNow tool is designed to assist with researching unusually slow performance. It provides real-time values for various performance metrics, including RAM usage, SQL code plan caching, CPU activity, file read/write activity, wait statistics, and more. MetricsNow is a stand-alone tool that can be used in emergency situations. It collects monitoring data multiple times to reveal trends and displays the results in seven different result sets.
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. Result set 3 represents the current state of processing activity. Result set 4 shows the change in processing activity over a period of time. Result set 5 displays wait statistics and locks. Result set 6 shows cumulative waits encountered by the SQL Server instance. Result set 7 provides metrics related to data file read/write operations.
It is recommended to save a copy of the result sets during a period when the server is behaving normally. This serves as a useful baseline for comparison when performance issues arise later.
MetricsHistory
The MetricsHistory tool is a comprehensive monitoring system that gathers, stores, monitors, and examines historic performance metrics. It creates tables and stored procedures to collect and store performance data at regular intervals. The collected data can be examined using various stored procedures that return different result sets focused on different performance metrics.
MetricsHistory provides metrics related to RAM usage, SQL code plan caching, CPU activity, file read/write activity, wait statistics, and more. It includes an alerting component that analyzes the collected monitoring data and sends alert messages when potential issues are detected. MetricsHistory does not include a graphical user interface (GUI) with graphs and charts, so if you prefer a GUI for viewing historic performance data, a commercial monitoring tool may be a better choice for you.
AGLatency
The AGLatency tool is specifically designed for databases in Always On Availability Groups. It returns information about transaction log usage for each database and provides latency information between the primary and secondary replica servers. The SQL code for this tool needs to be run on the primary replica.
Databases
The Databases tool provides information for all user databases and tempdb on the SQL Server instance. It includes details such as owner name, recovery model, create date, size/usage information for data files and transaction logs, and dates for the most recent backup operations. The information is presented in multiple result sets for easy analysis.
BACKUP / RESTORE
The BACKUP and RESTORE tools work together to assist with backup and restore operations. The BACKUP tool provides a summary of backup operations and a list of all backup operations. The RESTORE tool generates restore statements based on the backup history and allows for point-in-time restore operations. These tools can be very handy in emergency situations when a point-in-time restore is required.
Auditor
The Auditor tool returns information for SQL Server Audits. It provides increasing levels of detail in different result sets. Result set 4 contains audit results and is included only when there’s exactly one audit with a file destination. The tool also generates SQL statements for each audit with a file destination, which can be found in the Messages tab.
SQLAgent
The SQLAgent tool provides basic information for SQL Server Agent jobs and job schedules. It presents the information for schedules in an hour-by-hour format for each day of the week, allowing for easy visualization of job starts during each hour of the day. Please note that this tool does not include every type of job schedule, but it covers the most common types.
Bufferin
The Bufferin tool returns a list of the data in the SQL Server buffer cache, aggregated by database object. This information is useful for diagnosing small PLE (Page Life Expectancy) values. Please note that this process may take several minutes if the buffer cache is extremely large and fully loaded.
Statistics
The Statistics tool provides information for statistics associated with indexes and columns. These statistics guide the query optimizer in preparing execution plans. The tool includes details such as the date/time when the statistics were last updated, the number of rows and changes since the last update, the threshold for automatic updates, and a generated UPDATE STATISTICS statement. This tool can be used to determine when the threshold of changes is being approached and prepare for UPDATE STATISTICS at a less busy time.
SQL Server consists of many interconnected pieces, and it’s crucial to monitor their health and activity. SQLFacts is a free toolkit that provides a wide range of tools for monitoring your SQL Server systems. Download it today and take advantage of its many features!