All the factors that affect performance can be monitored using different components. These components can be grouped in the following categories:
- Performance Monitor
- Dynamic management views (DMVs) and catalog views
- DBCC commands
- System stored procedures
This section first gives an overview of Performance Monitor and then describes all components for monitoring performance in relation to the four factors: CPU, memory, disk access, and network.
1. Performance Monitor: An Overview
Performance Monitor is a Windows graphical tool that enables you to monitor Windows activities and database system activities. The benefit of this tool is that it is tightly integrated with the Windows operating system and therefore displays reliable values concerning different performance issues. Performance Monitor provides a lot of performance objects, and each performance object contains several counters. These counters can be monitored locally or over the network.
Performance Monitor supports three different presentation modes:
- Graphic mode Displays the selected counters as colored lines, with the X axis representing time and the Y axis representing the value of the counter. (This is the default display mode.)
- Histogram mode Displays the selected counters as colored horizontal bars that represent the data sampling values.
- Report mode Displays the values of counters textually.
To start Performance Monitor, click Start in Windows, type perfmon in the Search bar, click its name in the search results, and then click Performance Monitor under Monitoring Tools. The starting window of Performance Monitor, shown in Figure 20-1, contains one default counter: % Processor Time (Object: Processor). This counter is very important, but you will need to display the values of several other counters.
To add a counter for monitoring, click the plus sign in the toolbar of Performance Monitor, select the performance object to which the counter belongs, choose the counter, and click Add. To remove a counter, highlight the line in the bottom area and click Delete. (The following sections describe, among other things, the most important counters in relation to the CPU, memory, I/O, and network.)
2. Monitoring the CPU
This section contains two subsections related to monitoring the CPU. The first subsection describes several Performance Monitor counters, while the second discusses catalog views and DMVs that you can use for the same purpose.
2.1. Monitoring the CPU Using Counters
The following counters are related to monitoring the CPU:
- % Processor Time (Object: Processor)
- % Interrupt Time (Object: Processor)
- Interrupts/sec (Object: Processor)
The % Processor Time counter displays system-wide CPU usage and acts as the primary indicator of processor activity. The lower the value of this counter, the better CPU usage that can be achieved. You should try to reduce CPU usage if the value of the counter is constantly greater than 90. (CPU usage of 100 percent is acceptable only if it happens for short periods of time.)
The % Interrupt Time counter shows you the percentage of time that the CPU spends servicing hardware interrupts. The values of this counter are important if at least one piece of hardware is trying to get processor time.
The Interrupts/sec counter displays the number of times per second the processor receives hardware interrupts for service requests from peripheral devices. This number generally may be high in environments with high disk utilization or networking demands.
2.2. Monitoring the CPU Using Views
The following catalog views and DMVs are used, among others, to monitor CPU usage:
- sysprocesses
- dm_exec_requests
- dm_exec_query_stats
The sys.sysprocesses catalog view can be useful if you want to identify processes that use the most processor time. Example 20.1 shows the use of this catalog view.
Example 20.1
USE master;
SELECT spid, dbid, uid, cpu
FROM master.dbo.sysprocesses
order by cpu DESC;
The view contains information about processes that are running on an instance. These processes can be client processes or system processes. The view belongs to the master system database. The most important columns of the view are spid (session ID), dbid (ID of the current database), uid (ID of the user who executes the current command), and cpu (cumulative CPU time for the process).
The sys.dm_exec_requests dynamic management view provides the same information as the sys.sysprocesses catalog view, but the names of the corresponding columns are different. Example 20.2 displays, separate from the additional values for the sp_handle column, the same information as Example 20.1.
Example 20.2
USE master;
SELECT session_id, database_id, user_id, cpu_time, sql_handle
FROM sys.dm_exec_requests
order by cpu_time DESC;
The sql_handle column of the view points to the area in which the entire batch is stored.
If you want to reduce the information to only one statement, you have to use the columns statement_start_offset and statement_end_offset to shorten the result. (All other column names are self-explanatory.)
NOTE sys.dm_exec_requests is especially worthwhile if you want to identify long-running queries.
Another DMV that can be used to display the information of such cached Transact-SQL statements and stored procedures using the most CPU time is sys.dm_exec_query_stats. (You can find the description and another example of this DMV in Chapter 19.) Example 20.3 shows the use of this view.
Example 20.3
USE master;
SELECT TOP 20 SUM(total_worker_time) AS cpu_total,
SUM(execution_count) AS exec_ct, COUNT(*) AS all_stmts, plan_handle
FROM sys.dm_exec_query_stats
GROUP BY plan_handle
ORDER BY cpu_total;
The total_worker_time column of the sys.dm_exec_query_stats view displays the total amount of CPU time that was consumed by executions of cached SQL statements and stored procedures since it was compiled. The execution_count column displays the number of times that the cached plans have been executed since they were last compiled. (The TOP clause reduces the number of the displayed rows according to the parameter and the ORDER BY clause. This clause is described in detail in Chapter 24.)
3. Monitoring Memory
This section contains two subsections related to monitoring memory. The first subsection describes several Performance Monitor counters and the second one discusses DMVs you can use for the same purpose. (The DBCC MEMORYSTATUS command can also be used to monitor memory. This command is described in Chapter 15.)
3.1. Monitoring Memory Using Counters
The following Performance Monitor counters are used to monitor memory:
- Buffer Cache Hit Ratio (Object: SQLServer:BufferManager)
- Pages/sec (Object: Memory)
- Page Faults/sec (Object: Memory)
The Buffer Cache Hit Ratio counter displays the percentage of pages that did not require a read from disk. The higher this ratio, the less often the system has to go to the hard disk to fetch data, and performance overall is boosted. Note that there is no ideal value for this counter because it is application specific.
NOTE This counter is different from most other counters, because it is not a real-time measurement, but rather an average value of all the days since the last restart of the Database Engine.
The Pages/sec counter displays the amount of paging (that is, the number of pages read or written to disk per second). The counter is an important indicator of the types of faults that cause performance problems. If the value of this counter is too high, you should consider adding more memory.
The Page Faults/sec counter displays the average number of page faults per second. This counter includes both soft page and hard page faults. As you already know, page faults occur when a system process refers to a virtual memory page that is not currently within the working set in the physical memory. If the requested page is on the standby list or a page currently shared with another process, a soft page fault is generated and the memory reference is resolved without physical disk access. However, if the referenced page is currently in the paging file, a hard page fault is generated and the data must be fetched from the disk.
3.2. Monitoring Memory Using Dynamic Management Views
The following DMVs are related to memory:
- dm_os_memory_clerks
- dm_os_memory_objects
The sys.dm_os_memory_clerks view returns the set of all memory clerks that are active in the current instance. You can use this view to find memory allocations by different memory types. Example 20.4 shows the use of this view.
Example 20.4
USE master;
SELECT type, SUM(pages_kb)
FROM sys.dm_os_memory_clerks
WHERE pages_kb != 0
GROUP BY type
ORDER BY 2 DESC;
The type column of the sys.dm_os_memory_clerks view describes the type of memory clerk. The pages_kb column specifies the amount of memory allocated by using the single page allocator of a memory node.
NOTE The Database Engine memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The next level consists of memory clerks, memory caches, and memory pools. The last layer consists of memory objects. These objects are generally used to allocate memory.
The sys.dm_os_memory_objects view returns memory objects that are currently allocated by the database system. This DMV is primarily used to analyze memory usage and to identify possible memory leaks, as shown in Example 20.5.
Example 20.5
USE master;
SELECT type, SUM(pages_in_bytes) AS total_memory
FROM sys.dm_os_memory_objects
GROUP BY type
ORDER BY total_memory DESC;
Example 20.5 groups all memory objects according to their type and then uses the values of the pages_in_bytes column to display the total memory of each group.
4. Monitoring the Disk System
This section contains two subsections that discuss monitoring the disk system. The first subsection describes several Performance Monitor counters, while the second describes the corresponding DMVs that you can use to monitor the disk system.
4.1. Monitoring the Disk System Using Counters
The following counters are related to monitoring the disk system:
- % Disk Time (Object: Physical Disk)
- Current Disk Queue Length (Object: Physical Disk)
- Disk Read Bytes/sec (Object: Physical Disk)
- Disk Write Bytes/sec (Object: Physical Disk)
- % Disk Time (Object: Logical Disk)
- Current Disk Queue Length (Object: Logical Disk)
- Disk Read Bytes/sec (Object: Logical Disk)
- Disk Write Bytes/sec (Object: Logical Disk)
As you can see from the preceding list, the names of the Performance Monitor counters for the Physical Disk object and the Logical Disk object are the same. (The difference between physical and logical objects is explained in Chapter 5.) These counters have the same purpose for each of the objects as well, so the following descriptions explain the counters only for the Physical Disk object.
The % Disk Time counter displays the amount of time that the hard disk actually has to work. It provides a good relative measure of how busy your disk system is, and it should be used over a longer period of time to indicate a potential need for more I/O capacity.
The Current Disk Queue Length counter tells you how many I/O operations are waiting for the disk to become available. This number should be as low as possible.
The Disk Read Bytes/sec counter shows the rate at which bytes were transferred from the hard disk during read operations, while Disk Write Bytes/sec provides the rate at which bytes were transferred to the hard disk during write operations.
4.2. Monitoring the Disk System Using DMVs
The following DMVs can be useful to display information concerning the disk system:
- dm_os_wait_stats
- dm_io_virtual_file_stats
The sys.dm_os_wait_stats view returns information about the waits encountered by threads that are in execution. Use this view to diagnose performance issues with the Database Engine and with specific queries and batches. Example 20.6 shows the use of this view.
Example 20.6
USE master;
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type;
The most important columns of this view are wait_type and waiting_tasks_count. The former displays the names of the wait types, while the latter displays the number of waits on the corresponding wait type.
The second view, sys.dm_io_virtual_file_stats, displays the file activity within a database allocation. Example 20.7 shows the use of this view.
Example 20.7
USE master;
SELECT database_id, file_id, num_of_reads,
num_of_bytes_read, num_of_bytes_written
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
The columns of the sys.dm_io_virtual_file_stats view are self-explanatory. As you can see from Example 20.7, this view has two parameters. The first, database_id, specifies the unique ID number of the database, while the second, file_id, specifies the ID of the file. (When NULL is specified, all databases—i.e., all files in the instance of the Database Engine—are returned.)
5. Monitoring the Network Interface
This section comprises three subsections related to monitoring the network interface. The first subsection describes several Performance Monitor counters, the second discusses the corresponding DMV, and the last one describes the sp_monitor system procedure.
5.1. Monitoring the Network Interface Using Counters
The following Performance Monitor counters are related to monitoring the network:
- Bytes Total/sec (Object: Network Interface)
- Bytes Received/sec (Object: Network Interface)
- Bytes Sent/sec (Object: Network Interface)
The Bytes Total/sec counter monitors the number of bytes that are sent and received over the network per second. (This includes both the Database Engine and non-Database Engine network traffic.) Assuming your server is a dedicated database server, the vast majority of the traffic measured by this counter should be from the Database Engine. A consistently low value for this counter indicates that network problems may be interfering with your application.
To find out how much data is being sent back and forth from your server to the network, use the Bytes Received/sec and Bytes Sent/sec counters. The former displays the rate at which network data (in bytes) are received, while the latter checks the outbound rate. These counters will help you to find out how busy your actual server is over the network.
5.2. Monitoring the Network Interface Using a DMV
The sys.dm_exec_connections view returns information about the connections established to the instance of the Database Engine and the details of each connection. Examples 20.8 and 20.9 show the use of this view.
Example 20.8
USE master;
SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id=@@SPID;
Example 20.8 displays the basic information about the current connection: network transport protocol and authentication mechanism. The condition in the WHERE clause reduces the output to the current session. (The @@spid global variable, which is described in Chapter 4, returns the identifier of the current server process.)
Example 20.9
USE master;
SELECT num_reads, num_writes
FROM sys.dm_exec_connections;
Example 20.9 uses two important columns of this DMV, num_reads and num_writes. The former displays the number of packet reads that have occurred over the current connection, while the latter provides information about the number of packet writes that have occurred over this connection.
5.3. Monitoring the Network Interface Using a System Procedure
The sp_monitor system procedure can be very useful to monitor data concerning the network interface because it displays the information in relation to packets sent and received as a running total. This system procedure also displays statistics, such as the number of seconds the CPU has been doing system activities, the number of seconds the system has been idle, and the number of logins (or attempted logins) to the system.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.