Material 13: SQL Server Performance Monitoring
Using Activity Monitor, PerfMon, and SQL Server
Profiler
A. Objectives
After studying this material, students are expected to be able to explain the importance
of database performance monitoring, understand various SQL Server performance indicators,
explain the functions of Activity Monitor, Performance Monitor (PerfMon), and SQL Server
Profiler, and understand the concepts of slow queries, blocking, and deadlocks that can affect
database system performance.
B. Introduction
Databases are the main component in various modern information systems, such as
academic systems, financial systems, hospital systems, and e-commerce applications. As the
number of users and the volume of stored data increase, the need for good database
performance becomes increasingly important. A poorly managed database can experience
performance degradation characterized by slow data search processes, high server resource
usage, or even transaction failures.
To ensure the database continues to run optimally, regular monitoring is required.
Performance monitoring allows database administrators to know the server's condition in
real-time, identify problems that occur, and take corrective actions before the problem impacts
users. Therefore, monitoring is one of the essential activities in database administration.
C. Database Performance Monitoring Concept
Database performance monitoring is the process of observing and measuring various
activities that occur on a database server. The main goal of monitoring is to ensure that the
system can serve user requests efficiently and stably. Through monitoring, administrators can
determine the condition of CPU, memory, and disk usage, as well as currently running query
activities.
In addition to being used to detect ongoing problems, monitoring also serves as an
evaluation tool to identify potential future problems. By understanding server resource usage
patterns, administrators can plan capacity and optimize the system more effectively.
D. SQL Server Performance Indicators
SQL Server performance can be measured through several key indicators. One of the
most frequently used indicators is CPU usage. The CPU is the component responsible for
processing instructions and executing queries. High CPU usage over a long period can indicate
inefficient queries or activities that overload the server.
The next indicator is memory usage. SQL Server utilizes memory to store frequently
accessed data so that the data retrieval process becomes faster. If the available memory is too
low, the server can experience performance degradation because it has to frequently read data
from the disk, which has a slower access speed compared to memory.
Besides CPU and memory, input/output (I/O) activity on the disk is also an important
indicator. I/O activity shows how often the server performs data read and write processes to the
storage media. If I/O activity is too high, the data access process can become slow and affect
the overall system response.
Another indicator to pay attention to is the number of queries processed by SQL Server
within a certain time. The higher the number of processed queries, the greater the workload the
server must handle. Therefore, administrators need to ensure that the server has sufficient
capacity to serve all user requests.
E. Activity Monitor
Activity Monitor is a built-in SQL Server feature used to monitor server activity in
real-time. This feature provides various important information regarding the server's condition so
that administrators can get an overview of the database system's health.
Through Activity Monitor, administrators can see the level of CPU usage, the number of
tasks waiting for specific resources, data read and write activities on the database, and the
number of requests processed by SQL Server. In addition, Activity Monitor also provides
information about running processes and queries that consume large amounts of resources.
Because the information displayed is updated live, Activity Monitor is very useful for quickly
monitoring server conditions and detecting ongoing problems.
F. Queries with High Resource Usage
Not all queries have the same impact on server performance. Some queries can be
executed quickly and use few resources, while other queries can consume large amounts of
CPU, memory, and I/O.
Queries that use high resources usually involve processing large amounts of data, data
sorting processes, data aggregation, or data searching without adequate indexes. Such queries
can cause an increase in CPU usage and slow down overall server performance.
Therefore, administrators need to identify the queries that consume the most resources
so they can be optimized. This identification process can be done using Activity Monitor, SQL
Server Profiler, or other monitoring features available in SQL Server.
G. Performance Monitor (PerfMon)
Performance Monitor or PerfMon is a monitoring tool provided by the Windows operating
system to observe computer resource usage in real-time. Unlike Activity Monitor, which focuses
on SQL Server, PerfMon can be used to monitor the entire system, including CPU, memory,
disk, and network.
In a database environment, PerfMon is very useful for determining whether performance
issues stem from SQL Server or from hardware resource limitations. For example, high CPU
usage might indicate that the server needs query optimization, while nearly depleted memory
might indicate the need for additional RAM capacity.
By utilizing PerfMon, administrators can obtain a more comprehensive picture of the
server condition used to run SQL Server.
H. SQL Server Profiler
SQL Server Profiler is a tool used to record activities that occur on SQL Server. Profiler
allows administrators to see in detail the queries being executed, execution times, user login
activities, and various other events that occur on the server.
The main function of SQL Server Profiler is to aid in the analysis and troubleshooting
process. By using Profiler, administrators can find out which queries are running slowly, what
activities cause a high load on the server, and the patterns of database usage by users.
Because it can record activities in detail, SQL Server Profiler is often used in the process of
testing, performance optimization, and investigating problems in database systems.
I. Slow Queries
A slow query is a query that takes longer than a normal query to produce the results
requested by the user. Slow queries can cause users to wait longer and degrade the service
quality of the information system.
The causes of slow queries are very diverse, ranging from the lack of appropriate
indexes, the use of inefficient query structures, to enormous amounts of data. In addition,
limitations in server resources such as CPU, memory, and disk can also cause queries to run
slower than they should.
Identifying slow queries is an essential step in database optimization because such
queries are often the main cause of system performance degradation.
J. Blocking
Blocking is a condition where a transaction locks the data being used so that other
transactions must wait until that transaction is complete. In a multi-user environment, blocking is
a common occurrence because many users can access the same data simultaneously.
Although blocking is a normal mechanism to maintain data consistency, blocking that lasts too
long can cause a decrease in system performance. Other users who need the same data will
experience delays until the lock on that data is released.
Administrators need to monitor blocking activity to identify the transactions causing the
queue and take necessary actions to reduce its impact.
K. Deadlock
A deadlock is a more serious condition than blocking. A deadlock occurs when two or
more transactions are waiting for resources locked by the other transaction(s) so that neither
transaction can continue its process.
In a deadlock situation, SQL Server will automatically choose one of the transactions as
a victim (deadlock victim). The selected transaction will be rolled back so that other transactions
can continue processing and the system can return to normal.
Deadlocks can cause transaction failures and disrupt user activities. Therefore,
administrators need to understand the causes of deadlocks and perform good transaction
design to minimize the possibility of such conditions occurring.
L. The Importance of Monitoring in Database Administration
Monitoring is an essential part of database administration because it helps maintain
system stability, security, and performance. By conducting routine monitoring, administrators
can detect problems early before they impact users.
Monitoring also aids in the decision-making process regarding server capacity upgrades,
query optimization, and resource management. The information obtained from Activity Monitor,
PerfMon, and SQL Server Profiler can be used as a basis for continuous system analysis and
improvement.
In an organizational environment that relies on information systems, good monitoring will
help ensure that the database is always available, responsive, and able to optimally serve user
needs.