Today's distributed client-server applications demand a high performance and robust computing environment. To efficiently and reliably fulfill such requirements, it is important that all components of the application should be optimized in the best
possible way. This chapter covers topics that deal with tuning SQL Server configuration parameters and monitoring techniques.
In a typical client-server based application, end-user requests in the form of T-SQL queries are sent over the network to the SQL Server. The SQL Server executes these requests and, if necessary, sends the results back to the client. Time taken from
request initiation to completion is known as the response time for the request. Measurement of response time defines the performance of the system. If the response time is within "acceptable" limits, performance is considered to be
good. Measurement of performance is somewhat discretionary and is generally based upon the perception of the end users and nature of the business application. For example, a two-second response for a request from an inquiry screen for a customer support
application may be considered good. It may be regarded as extremely slow, even hazardous, however, in a real-time application for steel industry, where speed of the trolley carrying molten steel must be calculated in a matter of a subsecond. For these
reasons, performance of a system is best defined by the users and the designers of the system, because they know the functionality of the various modules of the system and the acceptable response time for each of the modules.
There are various components in a client-server based applications that can have a significant effect on the performance of a system. These components are as follows:
Each of the preceding components, individually or collectively, can contribute to poor performance of the system. Capacity planning is a very important step in the designing an application. You should carefully evaluate and choose these components, so
that they are most effective in your computing environment.
Many of the GUI-based applications are memory-intensive. If there is not enough memory on the client hardware, it will affect the performance of the application adversely. An application that is CPU-intensive (performing large data sorts, for example)
on the front-end will also suffer from performance degradation because of a slower processor. Therefore, at the time of designing the front-end application, estimate the amount of resources, such as memory, CPU, and so forth, that will be required to run
the application smoothly. Compare your estimate with the resources that are currently available, so that you can do a hardware upgrade, if necessary.
A network bottleneck is usually caused by available network bandwidth, network traffic, and the speed of the network. A poorly designed application can create a network bottleneck by retrieving hundreds of rows from the database, even though only a few
rows are necessary for the front-end application. Some of the network-related bottlenecks can be eliminated by efficient application and database design and by using database objects such as stored procedures, rules, triggers, and so on. Other options
include the use of a faster network, keeping the machine in a subnet with less traffic, and adding network redundancy.
Various subsystems of a server hardware can also contribute to poor performance. Some of these subsystems are processors, disk subsystems, and physical memory on the machine. A system that usually suffers from performance problems at hardware level may
exhibit that one subsystem is idle while the other one has high percentage of utilization. For a poorly performing application, low CPU utilization may indicate that the processor may be waiting on a disk subsystem to finish I/Os. Alternatively, if CPU
utilization is constantly at a high number (usually ninety percent is considered high), that could indicate that the congestion is at CPU level. When the CPU is overworked, there may be a need for faster or additional processors (or both) on the system.
Total physical memory on the machine is one of the important factors governing the performance of a system. Insufficient memory on a server will cause more I/Os from the disk. Because accessing a disk is much slower than accessing the memory, a large
number of physical I/Os can lead to performance degradation. If more memory is installed on a server, it can cache a large amount of data, and many requests for data can be satisfied from memory rather than doing physical I/Os.
All the previously mentioned components (client hardware, network, and server hardware) are essential for a well-performing system. They also add to the overall cost of the system, and therefore investment on these components is limited by the budget
constraints. No matter how many resources you provide on the client—the network and the hardware—there is no substitute for an efficient database design, well-written queries, and a well-configured SQL Server. You can gain a tremendous amount of
performance by using normalization and denormalization techniques (when necessary). You can also configure the SQL Server to provide excellent performance and throughput without compromising the data integrity. The SQL Server provides a number of
parameters that can be configured to provide optimal performance for a particular environment. Next, you will learn about the topics that pertain to configuring, tuning, and monitoring the SQL Server.
The SQL Server provides a number of parameters that can be set by the system administrator (sa) to maximize performance of a system. These parameters can be set using the sp_configure system-stored procedure. This stored procedure can be executed using
a query tool such as ISQL/w. The syntax of this procedure is
sp_configure [ parameter_name [, parameter_value ]]
where parameter_name is the name of the configuration parameter you want to set and parameter_value is the value for the parameter. Both the parameters are optional. All the users have permissions to run this stored
procedure, but only an sa (system administrator) can set the value of a parameter. If you execute this procedure without specifying any parameters, the SQL Server returns the current configuration values of the SQL Server. The following example displays
the output of the sp_configure stored procedure executed without any parameters:
Exec sp_configure go name minimum maximum config_value run_value -------------------- ----------- ----------- ------------ ----------- affinity mask 0 2147483647 0 0 allow updates 0 1 0 0 backup buffer size 1 10 1 1 backup threads 0 32 5 5 cursor threshold -1 2147483647 -1 -1 database size 1 10000 2 2 default language 0 9999 0 0 default sortorder id 0 255 50 50 fill factor 0 100 0 0 free buffers 20 524288 6400 6400 hash buckets 4999 265003 7993 7993 language in cache 3 100 3 3 LE threshold maximum 2 500000 500 500 LE threshold minimum 2 500000 20 20 LE threshold percent 1 100 0 0 locks 5000 2147483647 15000 15000 logwrite sleep (ms) -1 500 0 0 max async IO 1 255 25 25 max lazywrite IO 1 255 8 8 max text repl size 0 2147483647 65536 6 max worker threads 10 1024 255 255 media retention 0 365 0 0 memory 1000 1048576 128000 128000 nested triggers 0 1 1 1 network packet size 512 32767 4096 4096 open databases 5 32767 20 20 open objects 100 2147483647 5000 5000 priority boost 0 1 1 1 procedure cache 1 99 30 30 RA cache hit limit 1 255 4 4 RA cache miss limit 1 255 3 3 RA delay 0 500 15 15 RA pre-fetches 1 1000 3 3 RA slots per thread 1 255 5 5 RA worker threads 0 255 3 3 recovery flags 0 1 0 0 recovery interval 1 32767 5 5 remote access 0 1 1 1 remote login timeout 0 2147483647 5 5 remote query timeout 0 2147483647 0 0 remote login timeout 0 2147483647 5 5 remote proc 0 1 0 0 resource timeout 5 2147483647 10 10 set working set size 0 1 0 0 show advanced option 0 1 1 1 SMP concurrency -1 64 0 0 sort pages 64 511 64 64 spin counter 1 2147483647 10000 10000 tempdb in ram (MB) 0 2044 0 0 user connections 5 32767 125 125 user options 0 4095 0 0 (1 row(s) affected)
If you specify only the parameter name, the SQL Server returns the current configuration of that particular parameter. Parameters set by sp_configure take effect at the SQL Server level. You can assign a new parameter value by specifying both parameter
name and parameter value to the sp_configure stored procedure. There are two types of parameters: dynamic and static. Dynamic parameters take effect immediately after executing the sp_configure stored procedure, followed by running the RECONFIGURE T-SQL
command. Some of the dynamic parameters include network packet size, backup buffer size, free buffers, and so on. Static parameters require the SQL Server to be restarted and take effect upon the next start of the SQL Server.
If sp_configure is run without any parameters, the output consists of the following five columns:
The SQL Server internally maintains two tables: syscurconfigs and sysconfigures. The syscurconfigs table contains current configuration values of SQL Server parameters. These parameter values are shown under the run value column when you execute sp_configure without parameters. The sysconfigures table stores new values about the parameters that are set using sp_configure. These parameter values are shown under the config value column when you execute sp_configure without parameters. Dynamic parameter values are written to both these tables. At the time of the SQL Server reboot, the SQL Server copies information from the sysconfigures table and applies them to the syscurconfigs table.
SQL Server configuration parameter values can also be set by using the SQL Enterprise Manager graphical administrative tool. It makes no difference whether you use sp_configure or SQL Enterprise Manager to set parameter values. To get to the SQL Server
configuration screen of SQL Enterprise Manager, select the SQL Server you want to configure (assuming you have registered the server), click the right mouse button, and choose the Configuration option from the pop-up menu. The SQL Enterprise Manager
configuration screen is shown in Figure 31.1.
FIGURE 31.1. Configuring the SQL Server using SQL Enterprise Manager.
At the time of SQL Server installation, the SETUP program takes reasonable default values for all the SQL Server configuration parameters. However, there are certain parameters, such as memory, user connections, number of devices, open databases, and so
on, where the choice made by the SETUP program may not be suitable for a specific environment. The system administrator should change these parameter values accordingly. SQL Server configuration parameters are divided into two broad categories: basic and
advanced. When a SQL Server is installed, the output of sp_configure will list only the basic configurable parameters. There is a SQL Server option called the show advanced options parameter. If this option is set to 1, executing sp_configure (with no
parameters) will list all the configuration parameters. You can set this option on by executing the following:
Exec sp_configure 'show advanced option' , 1 go Reconfigure go
Before we describe the parameters that have a significant impact on SQL Server performance, it will be worthwhile to discuss two SQL Server internal processes—CHECKPOINT and LAZY WRITER. During startup, the SQL Server acquires certain amount of
memory (see the memory configuration parameter later in this chapter) from the operating system. A part of SQL Server memory is used for data caching by creating buffer pools of 2K pages. When a transaction is committed, modified information is stored in
the data cache buffers and also written to the disk device(s) on which transaction log has been created. These modifications may not be written to the database device(s) on which tables reside. This improves the performance of the system because the SQL
Server does not have to perform physical writes to the database device(s) for individual transactions. The disadvantage of this scheme is that if the SQL Server crashes, it may take a long time to recover, because the transactions that have been written on
the transaction log device may not have been written to the database device. When the SQL Server is restarted, all the changes written to the transaction log will have to be rolled forward (that is, written to the database device on which data resides),
which can be a time-consuming process. Therefore, to reduce recovery time, the SQL Server has a CHECKPOINT process. This process wakes up automatically at regular intervals (approximately once a minute) and forces all the committed changes from data cache
(called dirty pages) to be written to the database devices. This way, the SQL Server ensures that committed transactions are also written to the database device, and, in case of SQL Server crash, recovery will be faster because there are less number
of transactions to be applied to the actual tables.
It is also important to remember that there are only limited number of buffers available for the SQL Server. The CHECKPOINT process forces dirty pages to be written to the disk, but does not free up any buffers. That's where the LAZY WRITER process
comes into the picture. This process makes sure that there are always a certain number of buffers available to the SQL Server. This process wakes up automatically, writes the oldest used buffer pages (least recently used pages) to the disk, and frees a
number of buffers until the number of free buffers is above a specified threshold.
The SQL Server supports Symmetric Multiprocessing (SMP). SMP hardware is a server that has more than one CPU, and each CPU can handle a load (for example, I/Os, interrupts, and so on) independent of other CPUs. An SMP support means that a thread is not
tied to a particular processor of the machine and may therefore execute on a different processor each time. SMP support allows the SQL Server to run multiple tasks simultaneously, resulting in excellent load balancing. If there are other applications
running on the machine besides the SQL Server, however, or there is very high load on the SQL Server, it may be desirable to bind thread affinity to processors. The affinity mask parameter is a bit-mapped field where individual bits specify a processor on
the machine. Therefore, on a four-processor server, if you want the SQL Server to use processor 0 and processor 1, use the bit map 00000011 (decimal 3, that is) and execute the following:
Exec sp_configure 'affinity mask', 3 go Reconfigure go
Default for this parameter is 0. That is, there is no affinity between a thread and a processor. This is not a dynamic option, so in order for this parameter to take effect, you must recycle the SQL Server.
You can maximize the performance of the SQL Server by caching as much data as possible in the SQL Server data cache so that the SQL Server does not have to do physical I/Os. Usually, however, database sizes are much bigger than the amount of data cache
available to the SQL Server. In such cases, there will always be situations when the data requested is not found in cache and has to be fetched from the disk. To deal with such situations, the SQL Server maintains a number of free buffers. These free
buffers are provided by the LAZY WRITER process. This number can be set by setting the free buffers configuration parameter. The default value of this parameter is five percent of the total memory allocated to the SQL Server. When the number of free
buffers on the SQL Server falls below this parameter value, LAZY WRITER process writes dirty pages to the disk and adds those pages to the free buffer list until the number of free buffers is greater than or equal to the free buffers parameter value. This
parameter is specified in terms of 2K pages. This parameter should be set to a higher value (around 10 to 15 percent) in an environment that is highly I/O-intensive and in which data requests are generally not met by the SQL Server cache.
SQL Server default locking granularity is page level. When a query requests a large amount of data that spans across multiple pages, the SQL Server places locks on those pages. The type of locks depends upon the nature of transaction. For a SELECT
statement, the lock mode is SHARED; for an UPDATE statement, it is EXCLUSIVE. For a particular T-SQL statement, if the SQL Server has accumulated locks on 200 pages, it escalates the page level lock to table level lock. This lock escalation behavior is
efficient for tables that have fewer records. If a database contains a number of tables that contain millions of records, lock escalation to table level after two hundred page reads is not very efficient, because in a multiuser environment, by holding a
lock on the entire table, data concurrency is drastically reduced. This leads to performance problems. To avoid such situations, the SQL Server provides the following three parameters by which you can control the lock escalation behavior:
All the preceding parameters are dynamic in nature and take effect immediately. The following example sets the LE Threshold maximmum parameter value to 500 pages:
Exec sp_configure "LE threshold maximum", 500 go Reconfigure
logwrite sleep specifies the time (in milliseconds) that the SQL Server should wait before writing a buffer to the transaction log device. The default for this parameter is 0. That means after a committed transaction, if other users are ready to commit
their transactions, the log writer will wait for them. Otherwise, buffers are immediately flushed to the log device. Setting this value to a higher number is useful in an environment where there is high activity on a database and transactions are short.
Setting this parameter to a higher value allows more transactions to be saved in the buffers with fewer physical writes to the log device.
max async IO defines the number of asynchronous threads that can be issued for batch write requests such as checkpoint and bcp. You should increase this parameter value if there are multiple controllers on the system or hardware-based RAID for disk
striping is being used. Because more threads will be used to perform write operations, the system performance improves by using the faster writes. The default value for this parameter is 8. Increasing this parameter is quite useful in an environment that
is write-intensive and in which the disk subsystem is capable of sustaining high write volume. The SQL Server should be recycled for this parameter to take effect.
max lazywrite IO is similar to the max async IO configuration parameter. However, the difference is that max lazywrite IO controls the total number of asynchronous IOs that can be issued by the LAZY WRITER process. You may want to increase this value if
the load is heavy and there are multiple controllers on your system. The default for this parameter is 8. This parameter cannot have a value greater than the max async IO parameter value.
The SQL Server is integrated closely with Windows NT to make use of native operating system threads. Use of Windows NT threads results in better performance and stability of the SQL Server. The SQL Server can listen on multiple network protocols at the
same time. One or more threads are used by the SQL Server for each network protocol that the SQL Server listens on. Threads are also used by the CHECKPOINT and LAZY WRITER processes. The worker threads parameter defines the number of threads that can used
for handling SQL Server connections. The default value for this parameter is 255. If the number of connections is less than this parameter value, each thread handles a user connection. If the number of connections is more than the worker thread
configuration value, connections are handled by the next available thread. There is seldom a reason to increase this value. Threads can cause overhead on the system processors. Therefore, lowering this value can sometimes improve the performance of the
system. For a system with a few hundred user connections, a reasonable number for this parameter is around 125. You may want to experiment with various values to find out the appropriate value for the max worker threads parameter. This is a dynamic
parameter and takes effect immediately. The following example sets max worker threads value to 100:
Exec sp_configure 'max worker threads', 100 go Reconfigure go
memory defines the amount of memory (in terms of 2K pages) available to the SQL Server. SQL Server memory is primarily used for data caching. By caching data, a number of requests for data can be satisfied from memory rather than doing physical
I/Os—resulting in better overall performance of the system. To calculate the amount of memory that should be assigned to the SQL Server, execute the following steps:
Memory allocated to the SQL Server is mainly used by SQL Server code, internal static structures, and configuration parameters (such as user connections, locks, open databases, and so on). The rest of the memory is shared by the data cache and the
procedure cache. Procedure caches are a part of memory area where the SQL Server stores plans for the stored procedures, triggers, and queries. Query compilation also takes place in the procedure cache. The procedure cache is configured as a percentage of
the memory area shared by the data cache and the procedure cache. It is a SQL Server configurable parameter with a default value of 30 percent. To view memory usage on your system, run the T-SQL command DBCC MEMUSAGE. A partial output of this command
follows:
DBCC MEMUSAGE go Memory Usage: Meg. 2K Blks Bytes Configured Memory: 250.0000 128000 262144000 Code size: 1.7166 879 1800000 Static Structures: 0.5066 260 531200 Locks: 0.7439 381 780000 Open Objects: 1.0681 547 1120000 Open Databases: 0.0096 5 10020 User Context Areas: 3.3721 1727 3535882 Page Cache: 169.7083 86891 177952032 Proc Headers: 4.0889 2094 4287568 Proc Cache Bufs: 68.6426 35145 71976960
The following example sets the SQL Server memory to 160M (81920 2K pages):
Exec sp_configure 'memory', 81920 Reconfigure
This is not a dynamic option. Therefore, a SQL Server restart is necessary for this parameter to take effect.
network packet size is a dynamic parameter and specifies the default network packet size for the SQL Server. Setting this parameter to a larger value can improve performance of the tasks that involve larger amounts of data transfer, such as bcp. SQL
Server default network packet size is 4096. You should set it to an appropriate value depending upon your network configuration and application environment.
You can also specify the network packet size from the client. Setting network packet size from a client is quite useful when default SQL Server packet size is adequate for general application needs, but a larger packet size is required for some specific operations (such as bcp).
priority boost signifies the process priority of the SQL Server on a Windows NT operating system. The default value is 0, indicating that the SQL Server will run on the same priority level as other applications on the machine. By setting this value to
1, the SQL Server runs under the real-time priority class under Windows NT. This causes SQL Server threads to be executed before any other application that is running with normal priority. If the SQL Server is the only application running on the machine,
setting this parameter value to 1 may provide significant performance improvement. The SQL Server is required to be started for this parameter to take effect.
The procedure cache is a part of the SQL Server memory area where the SQL Server stores query plans (the mechanism to access the data in the least costly way) for the stored procedures, triggers, rules, defaults, queries, and so forth. Query
compilations also takes place in the procedure cache. The SQL Server query plan is always stored in cache (a query plan is never stored on the disk; only the compiled code is kept on the disk). If you have a large enough procedure cache, subsequent
execution of the same procedure results in the usage of the same query plan and thus faster execution of stored procedures. Because procedure cache is only a finite space in memory, a previously stored query plan gets thrown out (on the Least Recently
Used, or LRU, algorithm) of the procedure cache to make room for a new query plan. Procedure cache is configured as a percentage of the memory area shared by the data cache and the procedure cache. It is a SQL Server configurable parameter with a default
value of 30 percent. If an application uses a large number of stored procedures and triggers, it may be useful to increase this value. To find out an appropriate size for procedure cache, use the DBCC MEMUSAGE T-SQL command. This command displays 20 of the
largest stored procedures in the procedure cache. Execute the main (and big) stored procedures on the SQL Server and find out how many can fit in cache with the existing configuration. If you don't find most of the desired stored procedures in cache, you
may want to increase the size of procedure cache. If you find all of them, consider reducing the size. Some iterations of these steps will help you determine the optimal size of the procedure cache. The following is a partial output of a DBCC MEMUSAGE
command:
DBCC MEMUSAGE go Procedure Cache, Top 20: Procedure Name: proc_get_emp_info Database Id: 6 Object Id: 137278234 Version: 1 Uid: 1 Type: stored procedure Number of trees: 0 Size of trees: 0.000000 Mb, 0.000000 bytes, 0 pages Number of plans: 2 Size of plans: 0.042969 Mb, 45056.000000 bytes, 24 pages
There are a number of situations where the data retrieval from a database is sequential (SELECT lname, fname from employee), especially when doing table scans on large tables. The SQL Server can detect such queries and issue background threads that read
the data that is not yet requested but is expected at a later stage in the query. This read-ahead feature provides the capability to scan data in parallel. There are a number of configuration options that enable you to control this behavior in a way that
best suits your environment. All these parameters are dynamic in nature. That is, the new value takes effect immediately. The following are the SQL Server read-ahead configuration parameters:
This parameter defines the frequency of checkpoints on a SQL Server. If this value is low, the SQL Server will issue checkpoints more frequently. In the event of SQL Server crash, recovery will be much faster simply because there are less number of
records to roll forward or roll back. Because CHECKPOINT is very I/O-intensive (the checkpoint writes all committed changes to the disk), setting the value too low may therefore have an adverse effect on the performance of the system. On the other hand,
setting it too high will cause longer recovery time for the SQL Server in case of a crash. You should determine this value based upon the frequency of transactions in your environment and the amount of downtime that you can afford. The default value for
this parameter is five minutes, signifying that maximum allowable time for a database to recover is five minutes. The following example sets this interval to five minutes (that is, more checkpoints will be issued):
Exec sp_configure 'recovery interval, 3 go Reconfigure
This is a dynamic option and takes effect immediately.
Unless there is very high write activity on the SQL Server, there is seldom reason to change the default value for recovery interval configuration parameter.
The sort pages parameter specifies a maximum number of pages allotted for sorting for each SQL Server connection. The default value for this parameter is 64 2K pages. You may want to increase this value if your application environment employs queries
that involve sorting large number of records.
The SQL Server database provides a scratchpad area to hold temporary objects. This database is a shared area used by all the users of the SQL Server to create temporary objects and hold intermediate data results. This database is also used by the SQL
Server to create internal work tables and for data sorting (work tables created by the GROUP BY and ORDER BY clauses, for example) for its own use. tempdb is created as a part of the SQL Server installation. During installation of the SQL Server, tempdb is
created on the MASTER device with a size of 2M. For most of the applications, this is not sufficient. Database tempdb can be expanded either on a database device or in RAM. If the queries on a system use tempdb extensively, it may be advantageous to put
tempdb in RAM. This parameter enables you to specify the size (in megabytes) of tempdb in RAM. This size is not taken from SQL Server memory, but is taken from the operating system. Make sure that you have enough memory for the SQL Server and tempdb if you
are using this option. You need to restart the SQL Server after changing this parameter. It makes sense to use this option if you have a large amount of physical memory on the machine. As a general rule, you should not put tempdb in RAM at the expense of
SQL Server memory. Usually, you are better off assigning memory to the SQL Server, where you gain performance improvement by caching the data.
It is important to realize that when you put tempdb in RAM, you are allocating memory that could have been used by the SQL Server to cache data. Because of this, sometimes setting this option ON may adversely impact the performance of the system. The tempdb in RAM option is useful in cases where you have a large amount of physical memory on the machine and there is sufficient amount of memory available for the SQL Server data cache and the procedure cache.
The user connections parameter enables you to specify the total number of connections the SQL Server can handle simultaneously. Each user connection uses approximately 40K of memory. Setting it too high is a waste of SQL Server memory. If it is set too
low, after a configured number of connections have been established, any additional login requests will be denied by the SQL Server. Therefore, configure this parameter optimally based on your environment. You must restart the SQL Server after changing
this parameter. The following example changes this parameter to a value of 50:
Exec sp_configure 'user connections', 50
Using Windows NT Performance Monitor, it is possible to monitor SQL Server performance counters. The SQL Server installation program (SETUP.EXE by default), enables integration of SQL Server statistics with the Performance Monitor. There are two modes
by which SQL Server statistics can be collected:
To select these modes and to turn off integration of the SQL Server with Performance Monitor, run SQL SETUP from the SQL Server 6.5 program group and on the Microsoft SQL Server 6.5 Options dialog box (see Figure 31.2). Select the Set Server Options
radio button and click the Continue button. On the next screen (see Figure 31.3 ) choose the mode you want. To turn off monitoring, deselect the SQL PerfMon Integration checkbox and click the Change Options button to activate/deactivate required modes.
It is generally a good idea to monitor the SQL Server in direct response mode. However, if you are looking for certain counters online during a specified time frame, you may want to use 'on demand mode'. After you are finished and would like to perform unattended monitoring (that is, you are redirecting output to a log file), switch back to direct response mode.
FIGURE 31.2. Selecting SQL server options.
FIGURE 31.3. Choosing monitoring mode.
Performance counters will help you detect bottlenecks on the system. This section explains some useful performance counters of the SQL Server object. We assume that you are familiar with Windows NT Performance Monitor. To invoke Performance Monitor, run
SQL Performance Monitor from the SQL Server 6.5 program group and select the machine you want to monitor. In this section, you explore various performance counters for the following Performance Monitor objects:
There are number of counters that are quite helpful in detecting bottleneck at the processor level. Some of the counters include percent of processor time, percent of privilege time, percent of user time, and so forth. Depending upon the usage of each
counter, one can determine the types of activities taking place on a system.
If the SQL Server is the only main process on the machine, this counter represents the time CPU is busy executing a SQL Server request. If the process utilization is constantly at a very high percentage across all the CPUs (above 90 percent), you may
have a processor bottleneck. The first step to fix this situation is to tune the queries on the system that are CPU-intensive (such as the ones doing complex calculations, using the ORDER BY clause). Adding additional CPUs or faster CPUs will also improve
performance of the system.
This counter represents the percentage of time a processor is spending executing Windows NT kernel instructions. This also includes processor time that is spent executing device driver instructions and SQL Server I/O requests. If the percent of
privilege time is very high, that will indicate that the larger amount of I/O activity is taking place on the system. If you observe this counter at a very high number constantly (50–60 percent), adding more physical memory to the system and assigning
it to the SQL Server may help reduce it. Processor time can be very high if your disk subsystem is very slow.
This counter represents the percentage of time a processor is spending servicing application requests. Usually, if this is a high number (between 60–80 percent), that is not necessarily indicative of a problem on the system. If the percent of user
time is above 90 percent, however, that indicates that the SQL Server application is very CPU-intensive. To reduce the percent of user time, you should take a good look at complex queries of the system. Adding additional or faster CPUs may also help remove
the performance bottleneck from the system.
Performance counters defined under this object category help determine if there is a bottleneck on the disk subsystem. Some of these counters include Disk Reads/sec, Disk Writes/sec, Avg. Disk sec/Read, and Avg. Disk sec/Write. By measuring these
counters, you can detect whether there a disk that has higher utilization that others, indicating a load balancing problem on the system.
This counter provides an overall picture of how busy a disk drive is while servicing read and write requests. For an appropriately configured system, you should have almost equal activity on all the disks. If one drive is far more active than others,
you should investigate how your databases are spread out on the disk. For good load balancing, you should spread out the database, transaction log, nonclustered index, and tempdb on different devices. If all the drives are always at very high utilization,
that could indicate that either the overall disk subsystem is slow or there is not enough memory on the system.
These two counters together provide information about the throughput of the disk subsystem. If you are using hardware-based disk mirroring, these counter values are required to be adjusted based on the RAID level being utilized on your hardware. For
example, if the devices are mirrored, there will be a write operation on the primary device and then a write on the mirror device. Therefore, in order to calculate actual throughput of the system, multiply the counter value by the total number of write
operations (based on the RAID level). Typically, you expect to see better throughput for the devices on which transaction log is placed, because it is always a sequential write. Numbers will be slightly lower in cases of database devices, because requests
for data are usually random in nature.
These two counters provide useful information about individual reads and writes on a disk. For example, Avg. Disk sec/Write signifies the number of seconds it takes to complete a write operation. A high number for these counters indicates that the
system is very I/O-intensive and the I/O request queue will be quite large. To remedy this situation, it is advisable to add faster drives to the system.
The SQL Server is well-integrated with Windows NT Performance Monitor. It provides valuable information through various counters that help facilitate monitoring of the SQL Server and detect bottlenecks on the system. Some of most commonly used counters
are described in the following paragraphs.
The number of free buffers on the SQL Server is maintained by the LAZY WRITER process. This process makes sure that buffers are always available in the free pool area (as defined by the free buffers configuration parameter). If this counter displays a
value that is continually far less than the free buffers configuration parameter value or if this value is steadily reducing, it means that LAZY WRITER is not able to keep up with the demand for the free buffers. Increasing the value of the free buffers
configuration parameter is not going to help, because that fixes the problem only temporarily. Increasing the value of the max lazywrite IO configuration parameter may help improve the situation. Adding additional and faster drives will also result in
improved I/O throughput.
This counter defines a percentage of time that a requested database is found in the SQL Server cache. Cache hit ratio is dependent upon the size of memory available to the SQL Server and the size of the database. For better performance, you should have
a high ratio of memory and database size.
These counters define the I/O performance of the system for batch processes (such as the CHECKPOINT process, bulk-copy, and so forth). In a typical application environment, CHECKPOINT is mainly responsible for issuing batch I/O requests. If the batch
size is very large, you may want to increase the frequency of the CHECKPOINT process by setting the recovery interval configuration parameter value to a lower value.
This counter signifies the number of buffer pages written to the disk per seconds by the Lazy Writer process in order to maintain the number of free buffers above the threshold. This counter should be measured with the cache—number of free buffers
counter. Even with a high value for I/O Lazy writes/sec counter value, cache—number of free buffers counter may still be continuously falling. This indicates that the free buffers configuration parameter value should be set to a higher value.
This counter indicates the number of writes per second on the transaction log device. Because writes to the transaction log device are always sequential, you should expect to see a high value for this counter. A consistent low value for I/O Log
writes/sec indicates a slow disk subsystem. In this situation, adding faster drives can make a significant difference on performance.
These counters are useful for monitoring physical reads and writes that are pending. I/O Outstanding Reads and I/O Outstanding Writes provide the most definitive information if there a bottleneck at the disk subsystem. A large number of outstanding
reads or writes on a system indicates that there is a need for faster disks on the system. Spreading your data across multiple drives may also improve performance of the system.
These two counters are used to monitor physical reads and writes of the SQL Server. These counters don't represent batch reads and writes (such as CHECKPOINT, LAZY WRITER, and so forth). For a SQL Server configured with large amount of memory, you
should see little I/O activity on the system, except immediately after SQL Server restart when there is no data in the SQL Server data cache. Reducing the number of physical writes to the database device makes a significant impact on the performance.
This counter provides measurement of transactions in a log record before being flushed to the disk. This counter monitors the SQL Server logwrite sleep configuration parameter. The logwrite sleep parameter specifies the time (in milliseconds ) the SQL
Server should wait before writing a buffer to the transaction log device. The default for this parameter is 0. That means that if other users are ready to commit their transactions, the log write will wait for them; otherwise, buffers are immediately
flushed to the log device. If this counter displays a low value, you may want to set the logwrite sleep parameter to a higher value. Setting this parameter to a higher value enables more transactions to be saved in the buffers with fewer physical writes to
the log device. This counter provides very useful information in an environment where there is high activity on a Server.
This counter is somewhat of a misnomer because it does not provide information about transactions per second. This counter monitors the total number of command batches (a batch may contain multiple SQL statements) executed per second. This is a useful
counter to measure overall performance of the system. A low value for this counter signifies poor performance of a system. If that is the case in your environment, you should investigate all parts of the system, such as database design, processing power,
disk subsystem, and so forth.
This counter monitors the maximum space consumed in tempdb during the time frame in which you are monitoring the SQL Server. This counter is quite useful for determining the size of tempdb. You may want to turn this counter on during peak activity for a
few hours. If tempdb usage is quite heavy and a larger amount of space is being consumed, you may want to consider splitting tempdb across multiple disks.
This counter monitors the maximum number of concurrent connections to the SQL Server during the monitoring window. This counter is quite helpful in determining the peak activity hours for the application. You may want to schedule some of the
time-consuming batch processes during the window when there are less number of user connections on the system. In an environment where the number of users is gradually increasing, this counter can also help you determine whether the SQL Server is
configured for the appropriate number of user connections. If counter value is very close to the user connections configuration parameter, you should increase the number of connections for the SQL Server.
These two counters indicate network traffic to and from the SQL Server. A high value for these counters means that the network traffic is quite high. High traffic on a network can lead to poor overall performance of the system. If Network Reads/sec
counter is high, you may want to evaluate queries and, if possible, convert those to stored procedures. On the other hand, if Network Writes/sec is high, that means that the SQL Server is returning large number of data set. A poorly designed application
can create a network bottleneck by retrieving hundreds of rows from the database, even though only a few rows are necessary for the front-end application.
Procedure cache is a part of the SQL Server memory area where the SQL Server builds and stores query plans for the stored procedures, triggers, rules, views, defaults, and queries. The SQL Server procedure cache is a finite space of buffer pool. This
pool consists of the following:
There are number of counters available for this object on the SQL Server 6.5 that can tell you how much percentage procedure cache is in use by each of these buffers. Some of the main counters are described in the following sections.
This counter monitors the maximum percentage use of procedure cache by the procedures that were active during the monitoring window. If this limit is very close to the total procedure cache, it is necessary to increase procedure cache.
This counter monitors the percentage of procedure cache that is currently being used by the procedures that are currently running. If this limit is very close to the total procedure cache, there may not be enough space for a new procedure in procedure
cache while the other procedures are still executing. In this situation, either you wait until some of the procedures finish their execution or increase total procedure cache.
This counter monitors the percentage of procedure cache that is currently being used by the procedures. Procedures using procedure cache may not be currently running. If this limit is very close to the total procedure cache, there may not be enough
space for a new procedure in procedure cache while the other procedures are still executing. In this situation, either you wait until some of the procedures finish their execution or increase total procedure cache.
This counter provides the size of the procedure cache in terms of 2K pages.
Microsoft SQL Server 6.5 provides a graphical utility called SQL Trace. SQL Trace enables the users to monitor T-SQL commands and remote procedure calls (RPCs) going to a SQL Server. To invoke this tool, run SQL Trace from the SQL Server 6.5 program
group. SQL Trace enables you to define filters where you can monitor T-SQL commands. Information that can be entered for a filter is shown in Figure 31.4. By using SQL Trace, you can monitor the following:
FIGURE 31.4. Creating a New filter.
Once a filter is created, you can run it, pause it, delete it, or save it for future use. Figure 31.4 shows the screen output of a typical filter. Using SQL Trace, it is possible to run multiple filters against a SQL Server. Output of these filters can
be viewed on the screen or directed to a log file. It is also possible to store the output in a script file, which will have only T-SQL commands in it. This tool can also generate some basic information pertaining to performance such as CPU usage, disk
utilization, and so on.
FIGURE 31.5. Monitoring the SQL Server using SQL Trace.
SQL Trace is just a graphical front-end that is built around an extended stored procedure called xp_sqltrace. This extended stored procedure takes a number of input parameters and can redirect the output to the client or to a file (if specified as an
input parameter).
For a system to have an acceptable performance, various components of the system should be optimized to yield high performance. These components include client hardware, server hardware, network configuration, database design, and the SQL Server. The SQL Server provides numerous parameters that can be configured to tune the SQL Server to provide excellent performance and throughput. The SQL Server is well-integrated with Windows NT Performance Monitor and provides a number of counters that help determine bottlenecks in your system. By removing these bottlenecks, you can ensure high performance for the application.
