[Top] [Prev] [Next]
Chapter 6
Monitoring Tools and Configuration Utilities
Overview
System 11 is equipped with a comprehensive set of tools for monitoring, configuring, and tuning a SQL Server System. These tools are essential for establishing a fully functioning system, of course; but equally important, they are at the heart of SQL Server's ability to adapt to any enterprise requirements and offer consistent, high performance as those requirements change. This chapter gives an overview of the rich set of tools and utilities System 11 provides for customizing your computing environment. This chapter covers the following topics:
Tuning Resources
No RDBMS right out of the box can perform to its maximum potential for all possible applications without some adjustments for the computing environment. That's why a good RDBMS must include a set of performance tuning utilities and monitoring tools. Sybase offers numerous tools and aids for monitoring SQL Server performance so you can achieve the highest performance possible for your distributed client/server database system.
Database design, thorough query analysis, and appropriate indexing are the fundamental influences on system performance. However, beyond these factors, you can further optimize SQL Server in your specific environment. Sybase supports performance tuning in the following ways:
Monitoring Tools
When tuning SQL Server, the fundamental measures of success appear as increases in throughput and reductions in application response time. Unfortunately, tuning SQL Server cannot be reduced to printing these two values. In most cases, your tuning efforts must take an iterative approach involving a comprehensive overview of SQL Server activity, careful tuning and analysis of queries and applications, and monitoring locking and access on an object-by-object basis.
You can run monitoring tools both before and after tuning SQL Server configuration parameters to gather data for comparison. This data gives you a basis for performance tuning and lets you observe the results of configuration changes.
You can also use monitoring tools when the system exhibits the behavior you want to investigate. For example, if you are interested in finding out how the system behaves under typically loaded conditions in an OLTP environment, run monitoring tools both during the normal OLTP load and during batch jobs.
Various monitoring tools give you many ways to see how your system is behaving: SQL Server Monitor, SQL Server Historical Monitor, and the system procedures sp_monitor and sp_sysmon.
SQL Server Monitor
SQL Server Monitor is a performance-tuning tool that greatly simplifies the task of tracking down performance bottlenecks and verifying solutions. Figure 6-1 depicts a SQL Server Monitor environment.
Through a graphical user interface, SQL Monitor displays operating statistics such as:
You can take snapshots of SQL Server performance data before and after you tune, so you can track and verify that your tuning has the affect you expect it too. It also helps you determine if opening up one bottleneck has created another, a common occurrence in performance tuning.
SQL Monitor accesses server statistics through shared memory, so it has only a slight affect on SQL Server performance. Figure 6-2 shows a display of SQL Server performance trends.
Monitor Historical Server
Monitor Historical Server is an Open Server application that attaches to SQL Server shared memory to obtain statistics of SQL Server performance.
As it runs, Monitor Historical Server collects performance data that SQL Server stores in shared memory. Monitor Historical Server stores the data so you can defer inspection and analysis until it is convenient for you.
sp_monitor
SQL Server keeps track of how much work it has done in a series of global variables. You can display the current values of some of these global variables with the system procedure sp_monitor.
sp_sysmon
The system procedure sp_sysmon is a tool that provides a comprehensive overview of system performance. Once you identify the area that seems to be responsible for poor performance, you can use SQL Server Monitor to pinpoint contention on a per-object basis.
sp_sysmon is a quick way to get lots of information about the SQL Server system. The following sample shows sp_sysmon output for the "Transaction Profile" section.
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Committed Xacts 120.1 n/a 7261 n/a
Transaction Detail per sec per xact count % of total
------------------------- --------- --------- ------- ----------
Inserts
Heap Table 120.1 1.0 7260 100.0 %
Clustered Table 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total Rows Inserted 120.1 1.0 7260 25.0 %
Updates
Deferred 0.0 0.0 0 0.0 %
Direct In-place 360.2 3.0 21774 100.0 %
Direct Cheap 0.0 0.0 0 0.0 %
Direct Expensive 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total Rows Updated 360.2 3.0 21774 75.0 %
Deletes
Deferred 0.0 0.0 0 0.0 %
Direct 0.0 0.0 0 0.0 %
------------------------- --------- --------- -------
Total Rows Deleted 0.0 0.0 0 0.0 %
Table 6-1 summarizes the SQL Server performance categories that sp_sysmon reports on.
Table 6-1: Monitoring categories for sp_sysmon
|
Category
|
Description
|
|---|
|
Kernel utilization
|
Reports on SQL Server activities. It tells you how busy SQL Server engines were during the time that the CPU was available to SQL Server, how often the CPU yielded to the operating system, the number of times that the engines checked for network and disk I/O, and the average number of I/Os they found waiting at each check.
|
|
Task management
|
Provides information about open connections, task context switches by engine, and task context switches by cause.
|
|
Task context switches
|
Summarizes causes for tasks switching off engines.
|
|
Transaction profile
|
Reports on transaction-related activities, including the number of data modification transactions, user log cache (ULC) activity, and transaction log activity.
|
|
Transaction management
|
Reports on transaction management activities, including user log cache (ULC) flushes to transaction logs, ULC log records, ULC semaphore requests, log semaphore requests, transaction log writes, and transaction log allocations.
|
|
Index management
|
Reports on index management activity including nonclustered maintenance, page splits, and index shrinks.
|
|
Lock management
|
Reports on locks, deadlocks, lock promotions, and freelock contention.
|
|
Data cache
|
Reports summary statistics for all caches, and statistics for each named cache. It reports spinlock contention, utilization, cache searches including hits and misses, pool turnover for all configured pools, buffer wash behavior including buffers passed clean, already in I/O, and washed dirty, prefetch requests performed and denied, and dirty read page requests
|
|
Procedure cache
|
Reports on the number of times stored procedures and triggers were requested, read from disk, and removed from cache.
|
|
Memory
|
Reports on the number of pages allocated and deallocated during the sample interval.
|
|
Recovery
|
Indicates the number of checkpoints caused by the normal checkpoint process, the number of checkpoints initiated by the housekeeper task, and the average length of time for each type. This information is helpful for setting the recovery and housekeeper parameters correctly
|
|
Disk I/O
|
This category is useful when checking for I/O contention. It prints an overview of disk I/O activity: maximum outstanding I/Os, I/Os delayed, total requested I/Os, and completed I/Os. A second section includes output for the master device and for other configured devices, reporting reads, writes, and semaphore contention.
|
|
Network I/O
|
Reports on network activities for each SQL Server engine.
|
Automated System Administration
Sybase offers two automated database managers for SQL Server; both have a sophisticated graphical user interface that greatly simplifies system administration tasks. SQL Server Manager handles all the routine system administration tasks. Backup Server takes care of all backup and restore.
SQL Server Manager
SQL Server Manager is a system and database administration tool for SQL Server. Its powerful collection of features and graphical user interface make exacting administrative tasks easy to perform. It can take care of many of the configuration tasks that you might otherwise perform manually using the sp_configure system procedure. SQL Server Manager helps with the following typical system or database administration tasks:
Figure 6-3 shows the SQL Server Manager interface.
SQL Server Manager easily performs complicated system administration. Some of the special features that support this capability are:
Performance and Maintenance Utilities
SQL Server provides a wide variety of commands and system procedures for fine-tuning performance, many of which were introduced in the last chapter.
Query Tuning Options
New query tuning options support the capabilities of named caches and provide additional control when tuning queries:
Query Processing Options
set options can customize the execution of queries and display of results, show processing statistics, and provide other diagnostic aids for debugging Transact-SQL programs. Some of the set options are listed and described later in this chapter.
Transact-SQL lets you control how queries are processed with its set command, which turns query processing options on or off for the duration of a user's work session. The many set options are generally categorized as those that:
Table 6-2 lists some of the more frequently used set options for query processing and analysis.
Table 6-2: Frequently used set options
|
Option
|
Effect
|
|---|
|
chained on | off
|
When off, user explicitly initiates transactions with begin transaction command.
When on, SQL Server begins a transaction just before the first data retrieval, data modification statement at the beginning of a session and after a transaction ends. (fetch also begins a transaction.).
|
|
close on endtran on | off
|
When off, allows the cursor to remain open across transactions, improving performance. When on, SQL Server closes all cursors opened within a transaction at the end of that transaction.
|
|
cursor rows n
|
Causes SQL Server to return the specified number of rows for each cursor fetch request from a client application.
|
|
nocount
|
Turns off the display of the number of "rows affected" from a query that returns rows.
|
|
noexec
|
Compiles each query but does not execute it.
|
|
parseonly
|
Checks the syntax of each query and returns any error messages, without generating a sequence tree, compiling, or executing the query.
|
|
rowcount
|
Causes SQL Server to stop processing the query after the specified number of rows is returned. To turn this option off so that all rows are returned, use set rowcount 0.
|
|
table count
|
Sets the number of tables considered at one time while optimizing a join. This option can improve the optimization of certain join queries, but increases the compilation cost.
|
|
showplan
|
Generates a description of the processing plan for the query. It is often used in conjunction with statistics io. showplan allows you to see whether the query uses the indexes that are in place; statistics io gives you an idea of the effectiveness of the indexes.
|
|
statistics io
|
Displays the number of table scans, logical accesses (cache reads), and physical accesses (disk reads) for each table referenced in the statement. It also displays the number of pages written (including pages in the transaction log).
|
|
statistics time
|
Displays the CPU time it took to parse and compile each command, and each step of the command.
|
The Database Options
sp_dboption controls the database options for a single database on SQL Server. Table 6-3 lists some of the most frequently used database options.
Global Variables
SQL Server tracks information in global variables that any user can query. Global variables are useful for decision-based processing. They are used in batches, stored procedures, triggers, and cursors. Table 6-4 lists some of the variables.
You can display the current values of some of these global variables with the system procedure sp_monitor.
System Procedures for Querying System Tables
The master database (SQL Server's controlling database) contains all of the SQL Server-supplied tables. Each user database is created with its own additional subset of system tables as well. System tables are alternatively referred to as the data dictionary or the system catalogs.
System tables are a rich source of information. You can query system tables just like any other tables. For example, the following statement returns the names of all the triggers in the database:
select name
from sysobjects
where type = "TR"
Here is a list of SQL Server system procedures that make it easy to gather information from system tables.
Usage Information
Two system procedures report on CPU and I/O usage by SQL Server login name to provide information for chargeback accounting. The sp_reportstats system procedure prints information for an individual login name, or for all users if no login name is provided:
sp_reportstats
Name Since CPU Percent CPU I/O Percent I/O
-------- ----------- ----- ----------- ----- -----------
bea Sep 15 1993 16 13.7931% 65 23.8970%
karenp Sep 24 1993 37 31.8965% 53 19.4852%
fred Sep 23 1993 6 5.1724% 4 1.4705%
wanda Sep 17 1993 13 11.2069% 12 4.4117%
ruth Sep 19 1993 27 23.2758% 96 35.2941%
robbie Sep 20 1993 11 9.4827% 7 2.5735%
The system procedure sp_clearstats first runs sp_reportstats, and then clears the data from the system tables to initiate a new accounting period. The CPU and I/O data is stored in the syslogins system table.
Index Statistics
The update statistics command regenerates and stores information about the distribution of the key values in indexes. Using this command helps SQL Server make decisions about which indexes are best to use to process a specific query.
SQL Server's ability to optimize queries depends on the accuracy of distribution steps. When you create or re-create an index on a table that already contains data, SQL Server generates statistics about the density and distribution of keys in the index. If the data changes over time and affects the distribution of keys, you can run the update statistics on that index to re-generate the statistics and maintain good performance.
Configuration Parameters
The system procedure sp_configure gives you control over many aspects of SQL Server behavior, performance, and physical resource management.
System 11 lets you associate a display level to limit the amount of output SQL Server gives you. The three levels of display are: basic, intermediate, and comprehensive.
A user with a basic display level will see only a very small set of parameters. It is suitable for users who intend to adjust the following SQL Server parameters: recovery interval, number of devices, number of locks, number of open databases, number of open database objects, number of user connections, and stack size.
A user with an intermediate display level has the same capabilities as the user with a basic display level, but will see an additional 40 percent of the configuration parameters.
A user with a comprehensive display level will see all of the configuration parameters.
sp_configure's configuration parameters are preset to reasonable default values upon installation. Table 6-5 lists the sp_configure parameters and their effect.
[Top] [Prev] [Next] [Bottom]