Tweet

Overview

Monitoring Sql Server databases and instances provide the necessary information to diagnose and troubleshoot the performance issues. Once its performance is tuned then, it has to be constantly monitored as everyday data schema. Its configuration changes may lead to a situation where additional and manual tuning is required. In the following section, we will be discussing the SQL database performance monitoring.

What are the Metrics to Monitor on SQL Server?

The monitoring of the Metrics depends upon the performance goals. However, there is a wide range of usually monitored metrics, which offers the information sufficient for basic troubleshooting. It could be monitored in a way to find the root cause that is based on its values, additional or more metrics that are specific memory and processor usage, disk activity, and network traffic. SQL Server offers two built-in monitoring features, i.e. Active Monitoring and Data Collectors.

Active Monitoring

It tracks the most useful performance metrics of SQL Server. To get them, it executes the queries in every 10 seconds that are against its host SQL Server instance. The performance is monitored when the activity monitored is open that makes it a lightweight solution without overhead. The metrics are shown as discussed in five collapsible panes:

• Overview: The pane that views the processor number of waiting tasks, time percentage, number of batch requests, and database I/O operations.
• Processes: It shows the presently running SQL Server processes for each database on the instance. It shows the information of application, login, task state, wait time, command, host used, etc. It filters the specific column value from the information table. It offers useful feature of troubleshooting and deeper analysis. It is tracing the selected procedure in SQL Server profiler.
• Resource Waits: It shows waits for various resources, i.e. memory, network, complication, etc. It displays the wait time, cumulative wait time, recent wait time, and average waiter counter.
• Data File I/O: It shows a list of all database files: NDF, LDF, and MDF, their paths, names, recent read and write activity, and response time.
• Recent Expensive Queries: It shows the queries that are executed in last 30 seconds, which used most of the hardware resources such as memory disk, network, and processor. It enables opening the query in query tab of management studio and opening its plan for execution.

Way to Utilize Active Monitoring

It can be opened with SQL Server Management Studio toolbar’s activity monitor icon, context menu in SQL Server instance, keyboard Ctrl +Alt + A in object explorer. It only tracks a pre-defined set of the important metrics in SQL Server. The metrics cannot be monitored but the monitored one cannot be removed. Therefore, only the monitoring of real time is possible. It does not have any option to store the history for future usage. Therefore, Activity Monitoring is useful for the present monitoring and basic troubleshooting, threshold defined values, monitoring tool where metrics for monitoring can be selected, etc. data storage is necessary.

Data Collectors

It is another built-in performance for monitoring and tuning features of Management studio. It collects the metrics performance from Server instances and stores it in local repository, so that it can be used for further analysis. It uses SQL Server Agent, Integration Services, and Data Warehousing. It allows the user to specify the metrics that user will monitor. It provides three built-in monitor data collectors with the important and commonly monitored performance metrics. If user needs to monitor additional metrics performance, then custom data collectors can be created by using T-SQL code or API.

Method to use Data Collector

Users can follow the steps the mentioned steps but before that user must check that Data collection, Management Data Warehouse, and SQL Server Agent are enabled. Along with this, SQL Server Integration Services are installed.

• In Management studio object explorer, expand the management.
• Choose the configure management data warehouse option in data collection.
• Choose an option to set up data collection.
• Choose the server instance name and database, which will host the management data warehouse, and the local folder in which the collected data can be cached.
• Select the next option » Review Settings » Finish.

Data collection offers three pre-defined sets that are available in object explorer management in the system data collection sets folder, Data collection note: Query Statistics, Disk Usage, and Server Activity. Each has its built-in report as discussed.

• The Disk usage data collection is set of collection of data for database data file, I/O statistics, and transaction log file.
• The reports of Disk usage are available in context menu of data collection. It shows the usage of space by growth trends, data base files, and average day growth.
• Query Statistics set the collection of query code, activity, and query execution plans for the ten expensive queries.
• Server Activity of data collection is a set of data about disk I/O, processor, memory, and usage of network. The report displays CPU, disk I/O, network usage, memory, Server instances, Server waits, and Operating system activity.

Note: Data collection has to be configured and start data capturing. Whereas in Active Monitoring, no option for real-time graphs. However, the data that is captured can be stored for specified number of days.

Conclusion

In the above discussion, SQL Performance Monitoring is described. Its two main metrics are discussed that helps to monitor on the Server, i.e. Active Monitoring and Data Collectors. Both have different functionalities to perform the monitoring on the SQL Server database.