## MS BackOffice Unleashed

- Defining Performance
  - Client Component
  - Network Component
  - Hardware Component
  - SQL Server Component
- Configuring the SQL Server
- Tuning SQL Server Parameters
  - CHECKPOINT and LAZY WRITER Processes
  - Changing affinity mask
  - Allocating free buffers
  - Controlling Lock Escalation Behavior
  - Changing logwrite sleep
  - Setting max async IO
  - Setting max lazywrite IO
  - max worker threads
  - memory
  - Setting network packet size
  - Setting SQL Server priority boost
  - Changing the Size of the Procedure Cache
  - Changing the Read-Ahead Configuration
  - Changing recovery interval
  - Defining the Number of sort pages
  - Creating tempdb in RAM
  - Setting user connections
- Integrating the SQL Server with Windows NT Performance Monitor
- Understanding SQL Server Performance Counters
  - Object: Processor
    - Counter: Processor Time
    - Counter: % Privilege Time
    - Counter: % User Time
  - Object: PhysicalDisk
    - Counter: % Disk Time
    - Counters: Disk Writes/sec and Disk Reads/sec
    - Counters: Avg. Disk sec/Read, Avg. Disk sec/Write
  - Object: The SQL Server
    - Counter: Cache—Number of Free Buffers
    - Counter: Cache Hit Ratio
    - Counters: I/O Batch Average Size and I/O Batch Writes/sec.
    - Counter: I/O Lazy writes/sec
    - Counter: I/O Log writes/sec
    - Counters: I/O Outstanding Reads, I/O Outstanding Writes
    - Counters: I/O Page Reads/sec, I/O Page Writes/sec
    - Counter: I/O Trans. Per Log Record
    - Counter: I/O Transactions/sec
    - Counter: Max Tempdb Space Used (MB)
    - Counter: Max Users Connected
    - Counters: Network Reads/sec, Network Writes/sec
  - Object: SQLServer-Procedure Cache
    - Counter: Max Procedure Cache Active %
    - Counter: Procedure Cache Active %
    - Counter: Procedure Cache Used %
    - Counter: Procedure Cache Size %
- Using SQL Trace
- Summary

#
— 31 —

##
SQL Server Monitoring and Tuning

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.

###
Defining Performance

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:

- Client component
- Network component
- Hardware component
- SQL Server component

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.

####
Client Component

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.

####
Network Component

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.

####
Hardware Component

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.

####
SQL Server Component

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.

###
Configuring 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:

- name: This is the name of the parameter that should be passed to sp_configure in order to set the parameter value.
- minimum: This is the minimum legal value allowed for this parameter by the SQL Server. If you pass a value less than the minimum value, the SQL Server will not set the new value and instead will return an error.
- maximum: This is the maximum legal value allowed for this parameter by the SQL Server. If you pass a value greater than the maximum value, SQL server will not set the new value and instead return an error.
- config value: As mentioned before, some parameters (static parameters) take effect after the SQL Server restarts. The new value of such parameters is shown in this column.
- run value: This column displays the parameter values that are being used by the SQL Server. Therefore, if you changed a static parameter value, the values shown in the config value column will be different from the run value column. At the time of the SQL Server reboot, the config value value for all the parameters is copied into run value. Therefore, immediately after the SQL Server reboots, both these columns should display the same parameter values.

> > 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.

###
Tuning SQL Server Parameters

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

####
CHECKPOINT and LAZY WRITER Processes

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.

####
Changing affinity mask

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.

####
Allocating free buffers

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.

####
Controlling Lock Escalation Behavior

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:

- LE threshold maximum: This parameter signifies the maximum number of locks held by a statement before a lock can be escalated to a table level lock. The default value for this parameter is 200 pages. Set it to a high value if your environment has many tables, with each holding a large number of records.
- LE threshold minimum: This parameter signifies the minimum number of locks held by a statement before a lock can be escalated to a table level lock. The default value for this parameter is 20 pages. This parameter is useful in an environment where you have a number of tables that hold fewer records.
- LE threshold percent: The SQL Server also enables you to specify a percentage value for lock escalation. If this percentage value is exceeded, the SQL Server escalates the lock to table level. The efault for this parameter is 0, which means that locks on a table will escalate to table level if the number of locks held on the table exceeds the LE Threshold minimum parameter.

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

####
Changing logwrite sleep

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.

####
Setting max async IO

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.

####
Setting max lazywrite IO

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.

####
max worker threads

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

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:

1. Calculate the total physical memory on the machine.
2. Subtract the memory required by the operating system from the total physical memory.
3. Subtract the memory used by other applications from the value in the preceding step.

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.

####
Setting network packet size

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).

####
Setting SQL Server priority boost

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.

####
Changing the Size of the Procedure Cache

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

####
Changing the Read-Ahead Configuration

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:

- RA cache hit limit. If the data requested by the query is found each time in the data cache then after certain number of hits on the data cache, the SQL Server cancels read-ahead for the query. This parameter controls this behavior of the read-ahead thread.
- RA cache miss limit. This parameter forces the read-ahead manager to pre-fetch data, if the data requested by the query is still not found in the cache after RA cache miss limit number of attempts.
- RA delay. This is the time in milliseconds that read-ahead manager takes before it initiates a read-ahead thread.
- RA pre-fetches. The read-ahead manager pre-fetches data in terms of extents. An extent on the SQL Server consists of 8 2K pages. This parameter specifies the number of extents that the read-ahead manager will read in one attempt.
- RA slots per thread. This parameter signifies concurrent read-ahead requests on each background thread. The default value for this parameter is 5. For large databases and systems with multiple disk controllers, a higher value for this parameter may result in better performance for queries doing sequential data scans.
- RA worker threads. This parameter designates the total number of threads to be used for queries that require read-ahead. You can disable read-ahead capabilities of the SQL Server by changing RA worker threads value to 0.

####
Changing recovery interval

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.

####
Defining the Number of sort pages

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.

####
Creating tempdb in RAM

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.

####
Setting user connections

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

###
Integrating the SQL Server with Windows NT Performance Monitor

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:

- Direct Response Mode. This is the default statistics collection method. This method of data collection is fast because statistics are compiled independent of the display of the statistics. Therefore, you see information that is "almost" up-to-date.
- On Demand Mode. In this mode, the Performance Monitor demands data from the SQL Server and then displays it. This method of data collection is slower than the direct response mode, but provides up-to-date statistical information. When using the mode, make sure that the collection interval is not very small. Otherwise, you may flood the SQL Server with requests, which will affect the performance of overall system significantly.

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.

###
Understanding SQL Server Performance Counters

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:

- Processor
- PhysicalDisk
- SQLServer
- SQLServer-Procedure Cache

####
Object: Processor

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.

#####
Counter: Processor Time

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.

#####
Counter: % Privilege Time

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.

#####
Counter: % User Time

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.

####
Object: PhysicalDisk

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.

#####
Counter: % Disk Time

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.

#####
Counters: Disk Writes/sec and Disk Reads/sec

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.

#####
Counters: Avg. Disk sec/Read, Avg. Disk sec/Write

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.

####
Object: The SQL Server

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.

#####
Counter: Cache—Number of Free Buffers

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.

#####
Counter: Cache Hit Ratio

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.

#####
Counters: I/O Batch Average Size and I/O Batch Writes/sec.

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.

#####
Counter: I/O Lazy writes/sec

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.

#####
Counter: I/O Log writes/sec

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.

#####
Counters: I/O Outstanding Reads, I/O Outstanding Writes

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.

#####
Counters: I/O Page Reads/sec, I/O Page Writes/sec

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.

#####
Counter: I/O Trans. Per Log Record

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.

#####
Counter: I/O Transactions/sec

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.

#####
Counter: Max Tempdb Space Used (MB)

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.

#####
Counter: Max Users Connected

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.

#####
Counters: Network Reads/sec, Network Writes/sec

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.

####
Object: SQLServer-Procedure Cache

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:

1. Buffers for procedures that are currently executing
2. Buffers for procedures currently not active, but which have been used in the past
3. Buffers that are available

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.

#####
Counter: Max Procedure Cache Active %

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.

#####
Counter: Procedure Cache Active %

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.

#####
Counter: Procedure Cache Used %

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.

#####
Counter: Procedure Cache Size %

This counter provides the size of the procedure cache in terms of 2K pages.

###
Using SQL Trace

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:

- Commands sent by all or specific logins (it is also possible to use wildcard characters to filter certain logins)
- All or specific applications
- All or specific clients of the SQL Server
- Connects and disconnects to the SQL Server
- T-SQL commands or SQL commands with specific patterns (it also possible to use wildcard characters to filter T-SQL commands)
- All or specific remote procedures calls
- Queries that are aborted by the clients

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).

###
Summary

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.