Lesson 1: Why Monitor SQL Server?

[Previous] [Next]

You should monitor SQL Server to determine whether it is performing optimally and, if it is not, to identify the factors that are adversely affecting performance. This lesson discusses the reasons for monitoring SQL Server performance, lists the factors that affect performance, and describes how to establish a baseline so that you will be able to detect system bottlenecks.

After this lesson, you will be able to

• Explain why monitoring Microsoft SQL Server 7 is important
• Describe factors that affect SQL Server performance

Estimated lesson time: 25 minutes

Reasons to Monitor SQL Server

Monitoring enables you to determine whether a server is performing optimally and, if it is not, to isolate the cause. The primary reasons for monitoring server performance and database activity are to determine the cause of poor system performance, examine total system throughput, and examine the consistency of database data.

Determine the Cause of Poor Performance

Optimal performance delivers minimum response time and maximum overall throughput on SQL Server. Performance varies according to the specific environment and depends on hardware configuration, software settings, and how individuals and applications use SQL Server. Monitoring makes it possible to determine which of these factors is causing less than optimum response time or overall throughput.

Response time is the length of time it takes for SQL Server to return the first row of the result set. Response time is usually referred to as the perceived time, as this is how the user receives visual affirmation that a query is being processed.

Examine the Throughput of All Processing for All Users

Throughput measures the number of queries that can be handled by the server during a given time period, along with the number and size of the rows that are returned to the client.

As the number of users or level of user activity increases, additional queries and processes compete for limited system resources. This competition for resources can cause response time to increase and overall throughput to decrease.

Examine the Consistency of Data

Database structures, such as actual data pages and indexes, can become damaged over time. You can use Database Consistency Checker (DBCC) statements to check the logical and physical consistency of a database.

Factors That Affect Performance

Factors that affect performance can be grouped into six general categories: server hardware, operating system, network, SQL Server, database application, and client application.

NOTE

---

The system administrator may not have control over all of these areas, but each area can be assessed to determine where the greatest performance impact occurs. The factors over which the administrator does have control can then be tuned.

Factors Involving Server Hardware

The factors that can affect the performance of the server include

• Processors—the number of processors, as well as their speed, influences performance.
• Disk I/O—the number of disks, as well as their speed and the number and type of controllers, affects performance.
• Memory—sufficient RAM is crucial for all server processes.

Factors Involving the Operating System

The factors that can affect the performance of the operating system include

• Concurrent Windows NT services and activities—concurrent services or activities compete for the same resources as SQL Server.
• Paging files—the size, number, and location of paging files can have a major impact on system performance. For best performance, the server should have enough RAM to ensure that paging files are infrequently used.
• Disk management—various RAID levels can have a positive or negative impact on performance.

Factors Involving the Network

Network connection speed and activity—concurrent network activity, bandwidth, and data transfer rate—affect performance.

Factors Involving SQL Server

SQL Server factors that affect performance include

• Configuration—many critical settings are managed dynamically by SQL Server, including memory management and user connections. Unless you have reason to change these settings, you should allow SQL Server to manage the resources dynamically.
• Locking—contention for database resources can block processes and adversely affect SQL Server performance.
• Logging—writing a large amount of information to the transaction log can affect performance.
• Concurrent SQL Server activities—maintenance activities, such as backing up and restoring databases, executing DBCC operations, and building indexes, use resources and can slow overall SQL Server performance.v

Factors Involving the Database Application

Database application factors that affect performance include

• Logical and physical design—the level of normalization of the database can affect query performance.
• Transaction control—the level of transaction control that an application uses can determine the number and duration of locks (longer-running transactions hold locks for longer), which affects overall throughput.
• Conflicts—repeated conflicts (attempts to access data that is locked by another process) can slow down an application.
• Queries—how queries are written and whether they are encapsulated in stored procedures can affect their execution time. Queries that are encapsulated in stored procedures generally perform better than ad hoc queries.

Factors Involving Client Applications

Client application factors that can affect performance include

• User requirements—the number of concurrent users and connection and disconnection strategies affect the way SQL Server uses memory.
• Transaction control—minimizing locking conflicts generally improves performance.
• Client response to locking conflicts—how the client deals with resubmitting queries and data modification statements when a collision occurs can affect performance.
• Cursors—the kind of cursor, as well as how much data is retrieved and where data is cached, affect both use and response time.

Detecting Performance Bottlenecks

A bottleneck is any component or activity that limits performance. Every system has bottlenecks, but one of the objectives of monitoring your server is to locate bottlenecks that lower performance below expectations.

Decide What to Examine

Look first at the system-level issues to determine the source of a bottleneck before you examine client-level and query-level issues. For example, system-level bottlenecks that are caused by disk and memory use can affect how the entire application performs, including individual queries. Resolving issues such as disk thrashing before you examine the performance of a particular query is useful.

Know the Acceptable Range

As with any troubleshooting technique, knowing the acceptable range of performance helps identify problem areas. Low numbers can be as meaningful as high numbers. If a number is lower or higher than expected, it may indicate that a problem exists. Sometimes a problem in one area negatively affects or disguises problems in another area. For example,

• A component may prevent the load from reaching another component.
• Network congestion may prevent client requests from reaching the server.
• Clients may have bottlenecks that prevent them from accessing the server.

Discover the actual limits by simulating a workload on SQL Server while you monitor the system by using the tools described in this chapter.

Establish a Performance Baseline

Monitor your system over time to establish a performance baseline. Record measurements to determine the peak hours of database activity, production query or batch command response times, and the time required to back up or restore the database. After you have established a baseline, you can compare the actual performance of your server against the baseline to determine which areas require further investigation.

Configuring a Query Governor

It is possible to submit queries to SQL Server that consume too much of the available server resources and negatively affect performance. This can be due to

• Queries without WHERE clauses
• Queries with too few join clauses, otherwise known as Cartesian Products or Cartesian Joins
• Queries that access databases that do not have referential integrity
• Queries or stored procedures that legitimately attempt to perform too much work.

In order to prevent one long-running query from monopolizing resources and threatening the overall health and performance of the server, system administrators can configure a query governor.

SQL Server refuses to execute any query that has an estimated execution duration, in seconds, that exceeds the value specified by the query governor cost limit. Specifying 0 for this option allows all queries to run, regardless of their estimated duration.

If you use sp_configure or SQL Server Enterprise Manager to change the value of the query governor cost limit, the change is serverwide. Figure 14.1 shows the query governor setting on the Server Settings tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager. Use the SET QUERY_GOVERNOR_COST_LIMIT statement to change the query governor setting for individual connections.

Figure 14.1 The query governor setting in the SQL Server Properties dialog box

Lesson Summary

Monitoring SQL Server lets you determine the cause of poor performance, examine the actual processing throughput for all users, and examine the consistency of the data in your databases. Many factors affect SQL Server performance, so you should never consider individual factors in isolation. Understanding and monitoring the factors that affect SQL Server performance make it possible to detect performance bottlenecks and tune your hardware and software to overcome them.