Optimizing the OS Configuration

• Optimize programming objects used in SQL Server 2000, including the interaction with the operating system.

Much of the performance of any database system relies on the application and database designers. The use of the network, processor, memory, and disk storage can all be dictated by the type of database design and the use of the applications operating against the database server. For this reason, the operating system usually acts as only a starting point in any performance analysis testing. If the hardware configuration and operating system is not properly configured, however, the database engine won't be able to respond optimally.

The first task that the hardware and operating system serve in any database system is to provide a means to store and operate the database software and objects. The operating system is also responsible for reporting hardware and software problems, as well as making it possible for you to monitor everything executing on the machine.

NT Server and Windows 2000 Server offer some basic utilities and mechanisms for monitoring and viewing server activity. The System Monitor (Windows 2000) and Performance Monitor (NT Server) are tools provided by Microsoft to watch everything that is going on in the system. The operating system also offers other reporting mechanisms that you will also be exploring.

Using Performance/System Monitor

The monitoring tools that ship with Microsoft operating systems are built upon as you install individual application software packages. Each software package places additional information in the form of counters and interfaces into the available System Monitor tool. The following list represents the SQL Server objects that are installed for the use of the Windows 2000 System Monitor and NT Server Performance Monitor tools and what the counter provides:

• Access Methods. Allocation of SQL Server database objects.

• Backup Device. Information about backup devices.

• Buffer Manager. Information about the memory buffers used by SQL Server.

• Cache Manager. Information about the SQL Server cache.

• Databases. Information about a SQL Server database.

• General Statistics. Information about general server-wide activity.

• Latches. Information about the latches on internal resources.

• Locks. Information about the individual lock requests made by SQL Server.

• Memory Manager. Information about SQL Server memory usage.

• Replication Agents . Information about the SQL Server replication agents.

• Replication Dist. The number of commands and transactions read from the Distributor database and delivered to the Subscriber databases by the Distribution Agent.

• Replication Logreader. The number of commands and transactions read from the published databases and delivered to the distribution database by the Log Reader agent.

• Replication Merge. Information about SQL Server merge replication.

• Replication Snapshot. Information about SQL Server snapshot replication.

• SQL Statistics. Provides information about aspects of SQL queries.

• User Settable Object. Allows for custom monitoring. Each counter can be a custom-stored procedure or any T-SQL statement that returns a value to be monitored .

Each object contains a variety of counters, and there can be multiple instances for many of the objects. These aspects are important when it comes to determining what exactly to monitor. In addition, the operating system will only provide a higher level of information. If you need information like tracking the SQL Server objects and the events that they fire, you should be using one of the tools provided with SQL Server that will drill down into the specifics of any condition.

If you would like to monitor the basics of the system (disk, CPU, and memory), the set of counters provided by the operating system can be used without the specific SQL Server counters. An illustration of this is provided in SQL Server Books Online under "Monitoring with System Monitor."

To observe the true value of this tool, you will have to explore different combinations of counters to learn the causes and effects of particular server situations. Step by Step 12.2 uses a common set of counters that can be used as a starting point in monitoring performance.

STEP BY STEP

12.2 Using the Windows 2000 System Monitor.

1. From the Windows Start, Administrative Tools menu, select Performance to open the System Monitor.

2. Select the PLUS/Add button in the toolbar to bring up the Object/Counter selection interface.

3. Select the following counters by finding the object in the drop-down list box and selecting the counter from the list of available counters. Select the Add button. When you have finished, close the window.

   • SQLServer:Buffer Manager: Buffer cache hit ratio

   • SQLServer:Buffer Manager:Page Reads/sec

   • SQLServer:Buffer Manager:Page Writes/sec

   • SQLServer:General Statistics:User Connections

   • SQLServer:Memory Manager:Total Server Memory(KB)

   • SQLServer:SQLStatistics:SQL Compilations/sec

4. Note that if you are performing this exercise on a test machine, there will be little activity shown. To produce a load on the system, you have to start an instance of the Query Analyzer, provide the connection information if necessary, and enter some data queries.

In general, when you select an object the most frequently used counter comes up as the default for the object, although many other frequently used counters are also available.

It can be useful to monitor Windows 2000 and SQL Server counters at the same time. This can help you determine any relevance between the performance of SQL Server and Windows. Monitoring a computer using System Monitor can slightly impact the performance of the computer. Monitor only the counters that need monitoring; too many counters adds excessive overhead.

Counter Values

Disk, CPU, and memory utilization are some of the primary reasons for using the System Monitor. The exam will test by asking you to recognize when the counter values are too high. Two of the primary counters that can be monitored to determine disk activity are the PhysicalDisk: % Disk Time and PhysicalDisk: Avg. Disk Queue Length .

The % Disk Time counter monitors the percentage of time that the disk is busy reading or writing information. If the counter value is more than 90%, this would indicate abnormally high usage. The Current Disk Queue Length counter enables you to see how many system requests are waiting for the disk subsystem. The number of waiting requests should never be more than 1.5 to 2 times the number of spindles in the actual physical disk. Most disks have one spindle, although RAID volumes almost always have more.

CPU usage rates can be monitored to determine the processing use of the server. This could help you determine whether a server is trying to perform too much or whether the server could handle more duties . A continually high rate may indicate a CPU upgrade is needed or you need to provide for additional processors. Conversely, a high CPU usage rate may actually be an indication of a poorly tuned system or inefficient application design. A consistently low value could indicate that you have too much server for the task at hand (a rather nice situation to find yourself in).

A good way to determine CPU utilization is to monitor Processor:% Processor Time counter. The % Processor Time counter monitors the amount of time the CPU spends processing. A consistent state of 8090% indicates problems. For multiprocessor systems, a separate instance of this counter should be monitored for each processor. This value represents the sum of processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.

Additionally, CPU counters that are valuable in the monitoring process are Processor: % Privileged Time , Processor: %User Time , and System: Processor Queue Length . The % Privileged Time counter represents the time the processor is spending executing operating system commands. If this counter is consistently high when the Physical Disk counter is high, consider a faster or more efficient disk subsystem. The % User Time counter measures the time the processor is spending executing user processes such as SQL Server and other applications. The "Processor: Queue Length" counter corresponds to the number of threads waiting for processor time.

When you examine processor usage, consider the type of work that SQL Server is performing and the period in time for which a sample was taken. You can help processor overuse by balancing the workload over several machines. Values around 100%, where many client requests are executing, may indicate that processes are queuing up. Resolve this type of problem by adding more powerful processors or simply more processors.

Monitoring memory usage involves considerations for RAM and Disk Subsystem (remember we are talking about a virtual memory operating system). In general, the Memory: Available Bytes and Memory: Pages/sec counters are quick indicators of potential problems. Available Bytes indicates how much memory is available for use. Pages/sec shows the number of pages either retrieved or written to disk.

Low values for the Available Bytes counter can indicate that there is an overall shortage of memory on the computer or that an application running on the server is not releasing memory. A high rate for the Pages/sec counter could indicate excessive paging. Monitor the Memory: Page Faults/sec when monitoring disk activity to make sure that the disk activity is not caused by paging.

A low rate of paging is typical, even if the computer has plenty of available memory. To determine whether SQL Server, rather than another process, is causing excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance.

To isolate the memory used by SQL Server over other applications and processes, you must consider the fact that normally SQL Server changes its memory requirements dynamically, based on available resources and actual needs. If SQL Server needs more memory, it queries the operating system and uses the memory if it is available. When a determination is made by SQL Server that it no longer needs the memory, it releases it back to the operating system. If this option has been overridden using the Min Server Memory, Max Server Memory, and Set Working Set Size server configuration options, then SQL Server may not be using memory as efficiently as it could. These options should be adjusted only when you have a dedicated SQL Server, and you also have knowledge of all application interactions.

To monitor the amount of memory being used by SQL Server, you should refer to the Process: Working Set , SQL Server: Buffer Manager: Buffer Cache Hit Ratio , SQL Server: Buffer Manager: Total Pages , SQL Server: Memory Manager: Total Server Memory (KB) counters.

Working Set shows the amount of memory used by a process. If this number is consistently below the memory SQL Server is configured to use, SQL Server is configured for more memory than it needs. Buffer Cache Hit Ratio is application-specific. A rate of 90% or higher is desirable. Add more memory until the value is consistently greater than 90%. If the Total Server Memory (KB) counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.

The Event Viewer

The Microsoft Event Viewer tool is provided with all NT and Windows 2000 operating systems. This tool monitors events and records information in application, security, and system logs. It also categorizes and stores information pertaining to the networking security and application services. The application log contains events logged by application programs such as SQL Server. The security log (if events are being audited ) contains valid and invalid logon attempts, as well as events related to resource use. The system log contains events logged by the operating system components .

From the Event Viewer you can quickly spot problems and diagnose the series of steps that led up to any given situation. The event viewer has options available for you to filter events to specific categories and severity. You can also select other computers to view and find out additional information about any event.

To see only the desired events, you need to configure a view for the log with the appropriate selections. If you view the properties of any of the logs, you can find general and filter information under the appropriate tabs. In the General tab you can see and set pertinent information for the log itself. The Filter tab, as displayed in Figure 12.1, can be used to view only the desired errors.

Figure 12.1. The Filter dialog box for event viewer logs.

Figure 12.2 represents a sample of the application log with only the MSSQLSERVER events that are non-informational.

Figure 12.2. Application log with Warning and Error messages.

Of course on every server the goal is to avoid the yellow yield sign and the red circular X flags. In any system these represent potential problems. A warning message indicates something went wrong but the system was able to work around it. A red flag indicates that administrative effort is needed to resolve a more serious problem.

Reporting to the Event Viewer by SQL Server is made to the application event log. Use this log when looking for messages sent by any of the SQL Server related services.

The Windows Application Log

The application log records events that pertain to SQL Server, using any of the following sources:

• MSSQLSERVER . This is the primary source for event information and, along with SQLSERVERAGENT , will be responsible for most of your diagnostic focus. The SQL Server service manages all the database files used by an instance of SQL Server. It is the component that processes all statements sent from client applications. The service allocates resources between users and enforces business rules as defined.

• SQLSERVERAGENT . The SQL Server agent supports the non-data-specific activity in an installation. It is involved in the scheduling of activities, notification of problems, and definition of important contact personnel. This is done in the form of Jobs, Schedules, Operators, and Alerts.

• MSSQLServerOLAPService . The primary service used in OLAP (OnLine Analytical Processing) to manage statistical operations between data cubes.

• MSDTC . The Microsoft Distributed Transaction Coordinator is an operating service accessed by SQL Server for coordinating transaction atomicity across multiple data resources using a two-phase commit. You can enable distributed transaction support in applications by using the appropriate distributed transaction identifiers around the block of code to be handled by MSDTC similar to the following:

   BEGIN DISTRIBUTED TRANSACTION        /* Commands used in the           distributed transaction */ END DISTRIBUTED TRANSACTION 

• MSSQLServerADHelper . The MSSQLServerADHelper service performs the two functions necessary for active directory integration. It adds and removes objects used in Active Directory, and it ensures that the Windows account under which a SQL Server service is running has permissions to update the Active Directory objects.

• SQLServerProfiler . SQL Server and Profiler interface operations.

• SQLCTR . Performance/System Monitor interface operations.

• SQLFTHNDLR . Full Text query handler.

Of course, if you are running multiple versions of SQL Server on the same machines, you will see these also represented as information sources. Some of these sources do not show up on all installations. If a server is not using an aspect of SQL Server, then that source does not report any information. Now that the hardware and operating system interactions have been addressed, it's time to move into the server itself to begin a more granular look.

Query Governor Cost Limit

The query governor cost limit is a configuration option available for limiting the length of time a query will be allowed to run. The option, when set, specifies an upper limit for the time in which a query can run based on a costs estimate. The option does not actually cancel a long running query, but it prevents a query from starting if the estimated elapsed time, in seconds, required to execute a query is larger than the setting allows.

The query estimate is based on the specific hardware configuration of the server. A query that is permitted on one machine may not be permitted on another, even though both machines have the same setting for this option.

If you specify any positive value for this setting, the query governor disallows execution of any query that has an estimated cost exceeding the configured value. If you leave the setting at the default value of 0 (zero) the query governor is disabled and all queries are permitted.

If you use sp_configure or the server properties of the Enterprise Manager to change the value of the query governor cost limit, the value affects all queries run on the server. It is possible to change the value on a session-by-session basis by using the SET QUERY_GOVERNOR_COST_LIMIT statement in a single connection.