This section focuses on the functions usually performed by a SQL Server database administrator: configuring SQL Server, threshold management, and performance tuning.
After you have completed your application's development process and have tuned it as appropriate, the next step is to focus on SQL Server and its configuration settings. When configuring SQL Server for the customer's environment(s), start with the default values, and then experiment with changing parameter values after you have obtained a performance baseline. When adjusting parameters to monitor performance, adjust one parameter at a time and measure the difference in performance to get a clear and accurate observation of the individual changes you have made. The following SQL Server configuration settings are the ones that have the most impact on SQL Server's performance with your application:
The values for these settings are based on the needs of your application and your customer's environment.
The memory parameter sets the amount of memory to allocate to SQL Server. SQL Server uses physical memory for server operation overhead, data (buffer) cache, and procedure cache. The amount of memory you allocate should cover SQL Server overhead while effectively distributing the remaining memory between the procedure and data cache. The data cache is the more important of the two caches. The data cache is measured using the Windows NT Performance Monitor to examine the Cache Hit Ratio counter, which is part of the SQL Server object. The Cache Hit Ratio counter indicates the percentage of time a data request was found in the data cache instead of being read from disk.
Ideally, the SQL Server cache hit ratio should be 85 percent or greater when the application system is running in a steady state. You increase the cache hit ratio by increasing the amount of memory reserved for the data cache using the memory, user connections, and procedure cache configuration parameters. The amount of memory you allocate to SQL Server is dependent on the amount of physical RAM installed on the SQL Server computer as well as the computer's role in the domain. You may have to decrease the amount of memory given to SQL Server depending on the server's role. If the computer SQL Server is installed upon is a primary or backup domain controller you may have to reduce the amount of memory you allocate to SQL Server so that Windows NT has enough memory to perform its work with minimal or no page file swapping. The Windows NT page file is used to create a virtual memory space for use with executing processes, services, and applications. Page file utilization can be monitored using the Windows NT Performance Monitor to examine the Page Faults/Sec and Pages/Sec counters, which are part of the Memory object. Page Faults/sec is a count of the page faults in the processor. A page fault occurs when a process refers to a virtual memory page that is not in its Working Set in main memory. Pages/sec is the number of pages read from the disk or written to the disk to resolve memory references to pages that were not in memory at the time of the reference. Pages/sec is the primary counter to observe if you are concerned about excessive memory pressure, called thrashing, and the excessive paging that may result.
The goal of tuning memory is to satisfy SQL Server's memory requirements while minimizing or eliminating page-file utilization as measured by the preceding counters. If you observe paging, you can minimize it by reducing SQL Server's memory, or by isolating and reducing the memory used by the other applications and services running on the computer. On computers with 32 MB or less, you should allocate at least 8 MB to 16 MB to Windows NT and configure SQL Server to use the rest. For computers with more than 32 MB, allocate 16 MB to 20 MB to Windows NT (at a minimum) and allocate the rest of the memory to SQL Server.
For more information about the memory configuration setting, see the Microsoft SQL Server Administrator's Companion and article Q110983 "INF: Recommended SQL Server for Windows NT Memory Configurations" (Knowledge Base, MSDN Library).
The procedure cache parameter specifies the percentage of memory allocated to the procedure cache after the SQL Server memory requirements are met. The SQL Server memory needs are the sum of the memory necessary for the code itself, user connections, locks, open objects, and so on. Having a properly sized procedure cache results in fewer page faults with respect to use of stored procedures, triggers, rules, and defaults. By default, 30 percent of the available memory is reserved for the procedure cache. In systems with large amounts of memory, this is often excessive.
When sizing the procedure cache, the goal is to determine the optimal size that will hold your application's most active stored procedures, triggers, rules, and defaults. In essence, you want to prevent reading stored procedures and the other objects from the disk whenever possible to reduce the I/O cost. SQL Server stores a copy of each stored procedure execution plan that is accessed by more than one user. If the procedure cache is large enough, it prevents the displacement of procedures in the cache by procedures not yet in the cache.
You can use the Windows NT Performance Monitor and the Max Procedure Cache Active% and Max Procedure Cache Used% counters that are part of the Procedure Cache object to size the procedure cache. The counters represent the maximum percentage of procedure cache that has been active or used, respectively, during the monitoring session. As you create your application and database sizing information, you can use these counters to monitor the procedure cache use. If these numbers are consistently low, you can reduce the procedure cache setting, which reallocates the memory for use in the data cache. It is recommended that you keep the procedure cache at 5 percent or greater.
The user connections parameter sets the maximum number of simultaneous connections to Microsoft SQL Server. The actual number of connections needed depends on your application design and database environment. Each user connection requires approximately 40K of memory. Therefore, increasing user connections increases the amount of memory needed for SQL Server overhead and reduces the memory available for the data and procedure caches. In database application environments that require a high number of simultaneous user connections, more physical memory may be required to maintain high data and procedure cache performance levels. The maximum value for this parameter is 32,767. However, this value must be set based upon available memory and application requirements. As a rule, you should use the following equation to determine the user connections configuration setting:
User Connections = # of licensed clients * # of application connections/SQL Server user
Adjust the user connections based on the number of licensed SQL Server clients and the number of concurrent applications connections per application user. This helps ensure that you have configured SQL Server with enough available user connections to satisfy the needs of your licensed users.
The open objects parameter sets the number of database objects that can be open at one time on SQL Server. Each open object requires 24 bytes of memory. Increasing the number of open objects increases the amount of memory needed for SQL Server overhead and reduces the memory available for the data and procedure caches. The default value is 500. However, in most situations you can easily increase the open objects value to 1,000 or even as high as 5,000. The number of open objects you will need is based on the requirements of your application. In many cases, adjusting the number of open objects to be equal to the sum of number of objects defined in each SQL Server database is usually adequate. If the open objects setting is too low, you will receive SQL Server error number 603, "There are not enough system sessions descriptors available to run this query."
The recovery interval parameter sets the maximum number of minutes per database that SQL Server needs to complete its recovery procedures in case of a system failure. This value, along with the amount of activity on each database, is used by SQL Server to calculate when to do a checkpoint on each database. The Microsoft SQL Server lazy writer process does a very good job of managing the data buffer space. Therefore, it is not necessary to tune the checkpoint interval for optimum performance. If up-to-the-minute database recovery is not a high priority for your application environment, you can set the recovery interval very high to ensure that checkpointing occurs infrequently to help boost performance. However, increasing the period between checkpoints also increases the time SQL Server takes to perform automatic recovery when the database is restarted after an ungraceful shutdown (power failure, hardware failure, and so on).
The locks parameter sets the number of available locks for SQL Server. Locks are used for concurrency control. Each lock consumes 60 bytes of memory, so increasing the number of locks increases the amount of memory needed for SQL Server overhead and affects the sizing of the procedure and data caches. The default value is 5,000 locks. However, you should consider increasing the lock value to 10,000 or higher depending on the needs of your application. You can use the Windows NT Performance Monitor and the Total Locks counter that is part of the SQL Server-Locks object to monitor the number of SQL Server locks that are in use. You should adjust this configuration setting to have more than enough locks to satisfy the needs of your application, based on your own multi-user and benchmarking tests.
The max async IO parameter controls the number of outstanding asynchronous batch writes performed by checkpoint and lazy writer. The default is eight and is sufficient in most cases. However, you may want to experiment with this number to try to improve performance. Increasing the parameter allows more asynchronous batch writes to be performed, effectively shortening the period that the system is checkpointing or doing lazy writing. However, if your I/O subsystem cannot sustain the increased write activity, the increased writes can flood the I/O systems and interfere with the ability of SQL Server or other processes to read from the disk, resulting in decreased throughput. The behavior of this parameter is therefore dependent of the underlying I/O subsystem.
SQL Server for Windows NT uses the asynchronous I/O capability of the Windows NT operating system. When the I/O finishes, the operating system notifies the program by using a callback or other Win32 synchronization mechanism. This has two main advantages. First, it makes implementation easier for an application designer, since the operating system can be used to perform asynchronous I/O rather than having to simulate this capability in the application. Second, the multiple outstanding I/O requests can drive certain high performance disk subsystems at greater performance levels than would be possible otherwise.
This is usually only possible with very high-performance, intelligent, disk subsystems because only these systems have the specific features necessary to rapidly accept multiple asynchronous I/O requests from a Win32 application, such as SQL Server. On these systems, increasing the SQL Server max async IO parameter can result in performance improvements during disk-intensive operations. The actual setting used for this parameter and the resultant performance increase varies depending on the exact hardware and your application and database I/O profile. It should not be set arbitrarily high, because inordinate asynchronous I/O consumes system resources.
While determining the optimum setting for your application, you can determine if higher throughput is gained by raising Max Async IO, using the Windows NT Performance Monitor to examine I/O Batch Writes/sec and I/O Transactions/sec SQL Server object counters before and after the change. This assumes the current physical disks are not experiencing I/O congestion.
For more information about SQL Server's configuration settings, see the Microsoft SQL Server Administrator's Companion and various articles in the Microsoft Knowledge Base.
There are methods you can use to make your application perform threshold management and performance tuning in your customer's environment. System performance monitoring and statistical analysis are usually performed by a DBA. The DBA frequently monitors SQL Server to determine whether resources have been exhausted or configuration options need adjustment to enhance system performance. By automating these operations, SQL Server performs more of the tasks usually performed by a database administrator or operator. You can use the Windows NT Performance Monitor to perform this task. If you do not want to require the end user to start Performance Monitor manually, you can use a portion of the Windows NT Resource Kit that executes an application as a service to start Performance Monitor automatically when the computer is restarted.
One of the duties of a database administrator is to evaluate the overall performance of the system and make adjustments if predetermined performance thresholds have been exceeded. SQL Server provides its own Performance Monitor counters so that its performance can be analyzed easily using the Windows NT Performance Monitor. Using the Windows NT Performance Monitor, you can predefine a set of counters that you want monitored. If any of these counters exceeds or falls below a threshold value, Performance Monitor can be configured to generate an alert that executes Sqlalrtr.exe, the event logging utility. Sqlalrtr.exe can then start isql to log into SQL Server and issue a RAISERROR WITH LOG statement that causes an event to be written to the Windows NT event log. When the SQLExecutive service reads that event, it engages the SQL Server alert engine to fire a SQL server alert to perform a task.
The following code segment is a stored procedure called expand_config. The stored procedure is designed to increase any of the SQL Server configuration options defined in the system stored procedure sp_configure. The @inconfig parameter receives the ID number from the spt_values system table that corresponds to the configuration option that is to be increased. The @inval parameter receives the increment value used to increase the configuration option. expand_config gathers the current value for the sp_configure option received through @inconfig and then increases it by the increment received through @inval using sp_configure. This allows you to increase any sp_configure configuration option.
CREATE PROCEDURE expand_config @inconfig int, @inval int AS
begin
declare @curval int, // Variable for the current config value
@chgval int, // Variable for the new config value
@inname varchar(36), // Variable for the config value name
@cmd varchar(110) // Variable to hold the sp_configure command
/* Get the name for this configuration option from the spt_values table.
Select by configuration option number where the type is a configuration option. Configuration options are type 'C' */
select @inname=name from spt_values where number = @inconfig and type='C'
/* Get the current sp_config value for this option. */
select @curval=value from sysconfigures where config = @inconfig
/* Build the sp_configure command to increase this option and increase the configuration option using sp_config, the current value, and @inval. */
select @chgval = @curval + @inval
select @cmd= 'sp_configure '+ "'" + @inname + "'," + convert(varchar, @chgval)
EXEC(@cmd)
EXEC('reconfigure with override')
end
go
This code segment creates a new SQL Server message number 55501 with a severity level of 17 and 'Increase # Open DB Objects' as its message text. This is the message issued when Sqlalrtr.exe executes isql to execute the RAISERROR WITH LOG statement:
EXEC sp_addmessage 55501, 17, 'Increase # Open DB objects', 'us_english', true
go
This code segment creates a SQL Server task called 'Increase Database Object' that is configured to execute on demand:
exec msdb..sp_addtask 'Increase Database Objects',
@subsystem = 'TSQL',
@databasename = 'master',
@enabled = 1,
@freqtype = 2,
@activestartdate = 960101, @activeenddate = 99991231,
@activestarttimeofday = 0, @activeendtimeofday = 235959,
@loghistcompletionlevel = 2,
@emailcompletionlevel = 0,
@command = 'expand_config 107, 500'
go
This code segment creates a SQL Server alert called 'Out of Database Objects' that fires whenever a 55501 error is entered into the Windows NT Event Viewer application log:
exec msdb..sp_addalert @name = 'Out of Database Objects',
@message_id = 55501,
@severity = 0,
@delay_between_responses = 15,
@task_name = 'Increase Database Objects'
go
Using the Windows NT Performance Monitor, the Total Locks counter of the SQL Server-Locks object is monitored to take action when 90 percent of the currently configured locks are used as noted in sp_configure. When this threshold has been exceeded, it can generate a Windows NT alert to run Sqlalrtr.exe to call isql and issue the RAISERROR(55501, 17, -1) WITH LOG statement to write this error to the Windows NT Application event log. When the SQL Server Executive service encounters the 55501 message, it fires the 'Out of Database Objects' alert to perform the 'Increase Database Objects' task. This task executes the expand_config stored procedure defined previously to increase the number of open objects (ID number 107) by 500.
Microsoft SQL Server provides many useful objects and counters for monitoring performance and system thresholds using the Windows NT Performance Monitor. These objects and counters address areas that are important to the performance of all applications and system environments in general. In many cases, the counters that SQL Server provides are sufficient for performance assessment and threshold management purposes. However, in some instances, your customers may need to monitor a particular performance aspect or resource threshold that is not covered by the standard Windows NT or SQL Server objects and counters. Microsoft SQL Server provides an object called User Defined Counters that contains 10 objects SQL User Counter 1 through SQL User Counter 10. These user-defined objects can be customized to monitor any information returned by a SQL statement or any operation that is performed from SQL Server, such as executing a stored procedure.
SQL Server also provides 10 "empty" stored procedures named sp_user_counter 1 through sp_user_counter 10 that correspond to the user-defined counters of the same number mentioned previously. These stored procedures are used to perform the operations needed to populate the user-defined counters. You can use these user-defined stored procedures and values to monitor application-specific thresholds or values not represented in the Windows NT Performance Monitor objects or counters.