Chapter 7
Monitoring and Tuning SQL Server
Certification Objectives
*Tune and Optimize SQL Server Memory and CPU Usage *
Limit Resources Used By Queries By Using the Query Governor *
Diagnosing and Resolving Distributed Query Problems *
From the Classroom *
Optimization of Your Time
*Disk Performance Measurement
*Maintenance Statements *
DBCC DBREPAIR
*Miscellaneous Statements *
DBCC TRACEOFF
*Status Statements *
Validation Statements *
Statement
*DBCC CHECKALLOC
*Charts *
Alerts *
Logs *
Reports *
Locking Types *
Shared Locks
*Update Locks
*Exclusive Locks
*Intent Locks
*Deadlocks *
Timeouts *
Transaction Isolation Levels *
Locking Granularity For An Index *
Using Table-Level Hints *
Optimizing the database is one of the most important tasks for the database administrator. With the growing appetite for more speed, and the increase in cost associated with it, the database administrator has to learn to develop the skills to maximize the performance for SQL Server. Monitoring and tuning Microsoft SQL Server can be one of the most difficult jobs for the database administrator. This difficulty is because it draws from many disciplines such as a knowledge of computer and networking hardware, the operating system, the database applications, and Microsoft SQL Server itself. Though there is no right way to tune your server, this chapter will make optimization a much easier task.
Developing a Monitoring and Tuning Strategy
As the size of a database increases through over time, it becomes increasingly crucial to first develophave a monitoring and tuning strategy. Without a strategy, degradation in database performance can pass unnoticed by the database administrator. Making sure that the database is at the optimal state should be an important goal of the administrator.
One of the first things to do is to establish a performance baseline. The performance baseline is the range at which performance is acceptable. Taking periodic measurements of the server and comparing it to the baseline can help you determine the areas where the server needs to be tuned. These tunings are in order to improve performance. Data should be gathered in different areas and at different times to obtain a broad scope of the needs of the entire database.
On the Job: You should consider taking the data and exporting it to a data analysis program such as a spreadsheet so you can analyze the performance trends to predict future utilization needs.
Factors That Impact Performance
Optimizing SQL Server 7.0 is one of the biggest and most frustrating tasks for database administrators because the database always seem to be getting larger and slower. With such a big task, administrators should consider all the factors that impact performance. The main factors that influence performance are server hardware, data application, client application, and the operating system.
Server hardware is the most fundamental area of your database. The many hardware components make upgrading the server very expensive. The main hardware components that you should monitor are memory, CPU, and the hard disk. With high memory usage, Windows NT will emulate memory by swapping pages to the slower hard disk. Upgrading the server memory is the most cost-effective upgrade because there is no such thing as too much RAM. Adding memory will reduce disk access, which will also speed the performance of your hard disk. High CPU utilization may signal a need for a CPU upgrade or an upgrade to a multiprocessor system. An overused hard disk can be a huge problem because it is used both as virtual memory and as data storage. Low disk I/O will degrade transaction response time because the hard drives will take longer to respond. You can optimize disk performance in the following two ways: by upgrading to a faster hard disk controller and/or hard drives, or configuring a stripe set either with or without parity.
Good database design can increase performance dramatically by utilizing less hardware resources. In a highly normalized database, redundancy is reduced and joins are often increased. The database administrator decides to normalize the database to increase query performance because data is more compact. Sometimes complex queries increase CPU usage because it needs to access too many tables via joins. In a denormalized database, administrators would purposely make the data less redundant in order to increase speed. This leads to a reduced number of joins resulting in far less CPU usage. You should carefully consider denormalized database since they often have redundant data.
Application design is usually not the responsibility of the administrator, but of the developer. Despite this, knowledge of good application design can help you understand how the developer wants the database configured. Good application design can be as simple as choosing the correct API, or as complex as using Showplan to display the execution plan for the query.
Though the operating system is one of the most obvious parts of the SQL Server, it seems to be the most neglected. There are many Windows NT settings that can be reconfigured to increase performance of SQL Server. One of these settings includes the increase in server tasking. Increasing this will emphasize or de-emphasize the running priority of SQL Server. Another option that can be set is increasing the network throughput to allow the server to accommodate more network connections.
Tune and Optimize SQL Server Memory and CPU Usage
CPU and memory will take up a large part of your optimizing time. CPU usage and memory usage need to be constantly monitored to ensure that the system is not the bottleneck. If you discover your computer is the bottleneck, your main course of action should be to tune and optimize it, rather than implement the expensive hardware upgrades.
Though SQL Server automatically configures memory for you, there might be some instances where you would want to manually configure the memory settings. SQL Server uses virtual memory to allow servers with different memory configurations to run the same programs. This is accomplished by paging, which is the emulation of physical memory using the hard drive. Though this is always done, excessive paging should be avoided since the hard disk is much slower than physical RAM.
SQL Server automatically configures memory for you by dynamically resizing its own memory requirements. SQL Server gives memory back to the operating system if another application starts and less than five megabytes of memory is available. There is a delay that occurs between the start of a new application and the application loading when SQL Server is releasing the memory. You can manually configure your memory settings to minimize this delay.
The first task in manually tuning and optimizing the memory is to allocate memory for SQL Server. You do this by subtracting memory required for the operating system and non-SQL Server applications from physical memory. The amount of RAM remaining is the maximum amount of memory you can assign to SQL Server. After you calculate how much memory you are allocating to SQL Server, you proceed to set the memory options. There are two methods of setting the memory options. One way is to give SQL Server a fixed amount of memory. You give SQL Server a fixed amount of memory by setting min server memory and max server memory to the same value. That value will correspond to a fixed amount of memory allocated to SQL Server. With this option, SQL Server will never release the allocated memory to any other application. This setting may cause errors when other applications start. Therefore ensuring adequate memory is a necessary precaution you must take. If errors do occur, it might be more prudent to use the second method.
In the second method, configuring min server memory and max server memory sets a range of memory values. You set min server memory to guarantee a minimum amount of memory allocated SQL Server. You set max server memory to prevent SQL Server from using more than the allocated amount of memory. By lowering the amount of max server memory allocated to it, SQL Server will have more memory to release to other applications.
Ideally, you want to allocate as much memory as possible to SQL Server while minimizing the use of paging. To see the effects of paging on your system, use the Windows NT Performance Monitor. The Page Faults/sec counter of the Memory object indicates whether your server is generating any page faults. If the sever is generating faults, you should lower the amount of memory allocated to SQL Server. If the total amount of memory is inadequate, upgrading RAM might be preferred.
The CPU is the core of your server. Like system memory, you can also optimize CPU usage. In symmetric multiprocessor systems, a thread can go from processor to processor. Every time it goes to a different processor, the processor cache must be reloaded, which adversely affects performance. You can disable this by setting the affinity mask option, which assigns a thread to work on only one processor. You can set this option by doing the following: go to the Enterprise Manager, right click on the server to bring up the properties, click on the processor tab, and click on the desired processor to set up the affinity mask option.
Limit Resources Used By Queries By Using the Query Governor
The query governor option restricts the amount of system resources used by the query. It restricts these resources by allowing it only a specific amount of time in which it can run. If the querys running time exceeds that value, the query governor will not execute that query. There are two ways to change the query governor cost limit option: with sp_configure or with SET QUERY_GOVERNOR_COST_LIMIT. If you use sp_configure, the cost limit value is set server-wide.
Exam Watch: Since the query governor cost limit option is an advanced option, it can only be set when show advanced options is set to 1.
If you use SET QUERY_GOVERNOR_COST_LIMIT, the value will be set for that session only. The syntax of sp_configure is:
sp_configure query governor cost limit, <value>
The syntax of SET QUERY_GOVERNOR_COST_LIMIT is:
SET QUERY_GOVERNOR_COST_LIMIT <value>
When value is set at 0, the cost governor cost option is disabled and all queries can run.
Diagnosing and Resolving Distributed Query Problems
In SQL Server 7.0, Microsoft introduces distributed queries. Distributed queries are queries that access data on multiple OLE DB providers. Since this feature is new, diagnosing and resolving problems associated with it can be very frustrating. Since going through every error is beyond the scope of this book, I am going to go through a few simple rules to diagnose and resolve them. Though some of the troubleshooting tips may sound very obvious, many users overlook them.
If you run SQL Server on a system with minimal RAM or hard disk space, you will spend a significant amount of time "optimizing" your system. With todays low hardware prices, and the high cost of qualified systems staff, you should not skimp on the hardware used to run your SQL Server. A Production SQL Server system should meet the following guidelines:
If Performance Monitor is displaying "zero" as your disk activity, even though your disk light flashes on and off, it is because you must manually enable the disk counters. To use the Performance Monitor disk counters, you must first run the Diskperf utility included in Window NT by typing in the following command.
c:\> diskperf -y
by David Smith, MCSE + Internet
From the Classroom
Optimization of Your Time
If you run SQL Server on a system with minimal RAM or hard disk space, you will spend a significant amount of time "optimizing" your system. With todays low hardware prices, and the high cost of qualified systems staff, you should not skimp on the hardware used to run your SQL Server. A Production SQL Server system should meet the following guidelines:
A minimum of two times the amount of disk space that you think you will need. This will allow for expansion without system downtime when needed.
Enough RAM installed so your system is not paging to disk when it is first put into production
A fault tolerant disk sub-system such as mirroring or RAID that insures user data is safe.
Disk Performance Measurement
If Performance Monitor is displaying "zero" as your disk activity, even though your disk light flashes on and off, it is because you must manually enable the disk counters. To use the Performance Monitor disk counters, you must first run the Diskperf utility included in Window NT by typing in the following command.
c:\> diskperf -y
by David Smith, MCSE + Internet
Tools for Monitoring SQL Server
SQL Server 7.0 includesd many tools for monitoring. Knowing the tools available can help you optimize and tune your database. The choice of tool depends on the events to be monitored and the type of monitoring. The tools available for monitoring are:
Showplan
The basis of client application performance is the queries. With many client applications running at once, optimizing a query can help improve performance. Showplan (which is a tool used to optimize queries) displays the query plan used by the query engine to execute your Transact SQL statements. By knowing the execution plan of your query, you can reconstruct your query in a more cost-effective way. There are three different ways to retrieve Showplan information:
The graphical showplan shows the query plan in a graphical format. Exercise 1 shows you how to use graphical showplan.
Exercise 7-1 Using Graphical Showplan
Figure 1: Connecting to SQL Server in Query Analyzer
SELECT au_fname,au_lname, title FROM authors a,titleauthor ta,titles t
WHERE a.au_id=ta.au_id AND t.title_id=ta.title_id
Figure 2: Graphical Showplan
Another way to access Showplan is to use SET SHOWPLAN_TEXT_ON and SET SHOWPLAN_ALL_ON. They both output the Showplan information. To access Showplan information via the SET SHOWPLAN_TEXT ON, follow the instructions.
Exercise 7-2 Using Text based Showplan
USE pubs
GO
SET SHOWPLAN_TEXT ON
GO
SELECT au_fname,au_lname, title FROM authors a,titleauthor ta,titles t
WHERE a.au_id=ta.au_id AND t.title_id=ta.title_id
GO
Figure 3: Text-based Showplan
Statistics I/O
STATISTICS I/O is a SQL Server Query Analyzer query execution option that reports information about the number of scans, logical reads (pages accessed in cache), physical reads (number of times the disk was accessed), and read-ahead reads for each table in the query statement. To view the statistics I/O information, go to Query Analyzer and put insert a query. Next go to the query menu, select set options, and click on show stats I/O. Your screen should be similar to Figure 7-4.
Figure 4: Statistics I/O
Exam Watch: Both Statistics I/O and Showplan are options within the SQL Server Query Analyzer that analyzes the efficiency of your queries.
DBCC
DBCC (Database Consistency Checker) statements are statements within Transact-SQL that check the logical and physical consistency and integrity of your database. Though these statements can be used to correct problems, they are often used to detect them.
On The Job: You should run DBCC statements periodically to ensure consistency and integrity of your data.
There are four categories of DBCC statements:
Maintenance statements perform maintenance tasks on the database files. These parameters are listed in Table 7-1.
Statement | Task |
DBCC DBREPAIR | This statement drops a damaged database. |
DBCC DBREINDEX | This statement rebuilds one or more indexes for a table in the specified database. |
DBCC SHRINKDATABASE | This statement shrinks the size of the data files in the specified database. |
DBCC SHRINKFILE | This statement shrinks the size of the specified data file for the related database. |
DBCC UPDATEUSAGE | This statement reports and corrects the sysindexes table that would result in incorrect usage information by sp_spaceused. |
Table 7-1: Maintenance Statements
Miscellaneous statements perform maintenance tasks on the Microsoft SQL Server
. These statements and tasks are listed in Table 7-2.Statement | Task |
DBCC dllname (FREE) | This statement unloads extended stored procedure DLLs. |
DBCC HELP | This statement returns DBCC syntax information. |
DBCC PINTABLE | This statement marks a table so that its pages will not be flushed from memory. |
DBCC ROWLOCK | This statement enables Insert Row Locking. |
DBCC TRACEOFF | This statement disables the trace flag. |
DBCC TRACEON | This statement enables the trace flag. |
DBCC UNPINTABLE | This statement marks a table that its pages in the buffer cache can be flushed. |
Table 7.2: Miscellaneous Statements
Status statements display SQL Server status information, as listed in Table 7-3.
Statement | Task |
DBCC INPUTBUFFER | This statement displays the clients last sent statement. |
DBCC MEMUSAGE | This statement displays buffer and procedure caches information. |
DBCC OPENTRAN | This statement displays information about the oldest active, distributed and non-distributed replicated transaction within the database. |
DBCC OUTPUTBUFFER | This statement displays the "current output" buffer in HEX and ASCII for a specified SPID. |
DBCC PERFMON | This statement displays SQL Server performance information. |
DBCC PROCCACHE | This statement displays SQL Server procedure cache information. |
DBCC SHOWCONTIG | This statement displays data and indexes fragmentation of a specified table. |
DBCC SHOW_STATISTICS | This statement displays the current distribution statistics for an object on a specified table. |
DBCC SQLPERF | This statement provides transaction-log space statistics in all databases. |
DBCC TRACESTATUS | This statement displays the status of trace flags. |
DBCC USEROPTIONS | This statement returns the SET options that are active. |
Table 7.3 4: Status Statements
Validation statements perform validation checks, as show in Table 7-4
Statement | Task |
DBCC CHECKALLOC | This statement checks the allocation of all pages in the specified database. |
DBCC CHECKCATALOG | This statement checks for consistency in system tables in the specified database. |
DBCC CHECKDB | This statement checks the allocation and integrity of all objects in the specified database. |
DBCC CHECKFILEGROUP | This statement checks the allocation and structural integrity of a specific filegroups tables in the current database. |
DBCC CHECKIDENT | This statement checks the current identity value for the specified table and, if needed, corrects the identity value. |
DBCC CHECKTABLE | This statement checks the integrity of the data, index, text, ntext, and image pages for the specified table. |
DBCC NEWALLOC | This statement checks the allocation of data and index pages for each table within the databases extent structures. |
DBCC TEXTALL | This statement selects tables in the database that have text, ntext, or image columns and runs TEXTALLOC on them. |
DBCC TEXTALLOC | This statement checks the allocation of text, ntext, or image columns for a table. |
Table 7.54: Validation Statements
Performance Monitor
SQL Server Performance Monitor is one of the most important tools in monitoring SQL Server. Performance Monitor can obtain real time information about the performance ofn SQL Server through the use of charts, alerts, logs, and reports.
Figure 7-5 shows the default Performance Monitor counters that come pre-configured by Microsoft. To see this chart on your computer, select Start|Programs|MS SQL Server 7.0|Performance Monitor.
Figure 5: SQL Server Performance Monitor
Charts can be created to monitor the current performance of selected objects and counters. Each chart represents a subset of information that you want to monitor.
Alerts are very powerful in that they can react whenever a counter exceeds a level. You can configure an alert so the timestamp is logged, a network alert is generated, or a program is run.
Logs allow you to record information on current activity of selected objects and events for later analysis. Log files enable you to track counters over a long period of time, thus allowing you to document system performance over a fixed interval of time.
Reports let you display constant real-time values for selected objects. You can configure the intervals at which data is reported, print snapshots, and export data.
Exam Watch: Understand and remember the differences between the reports, alerts, logs, and charts for the Performance Monitor. In certain situations, some methods are preferred over others.
Locks
Client applications often access data at the same time. Microsoft SQL Server utilizes locks to prevent concurrent users from reading data that has been changed but not yet committed. Locks are one of the crucial elements of Microsoft SQL Server. Without locks, users can never be sure that the data they are viewing is correct. New to Microsoft SQL Server 7.0 is dynamic locking. SQL Server will determine the appropriate locking whenever a query is executed. This help simplify database administration and increases performance since the server determines the appropriate locks. Though Microsoft SQL Server 7.0 enforces locking automatically through dynamic locking, you can still configure locking in applications by configuring deadlocks, time outs, transaction isolation levels, locking granularity for an index, and using table-level hints with SELECT, INSERT, UPDATE, and DELETE statements.
Though SQL Server 7.0 has dynamic locking, it is still very important that you can differentiate between the four main locks. You can differentiate between the lock types by their use for each of the main SQL statements and their compatibility with each other.
Shared locks allow connections to read data. Since concurrent reads do not affect the integrity of the data, shared locks can be used concurrently with other shared locks. When the data is locked, no data-modifying transactions can access the data until the shared lock is released.
Update locks are used for update transactions. In an update, data is first locked using a shared lock. Once the data is found, the shared lock is upgraded to an exclusive lock to modify the data. A problem can arise when one of two sessions, having shared locks on the same resource, wants to change to an exclusive lock to modify data, but cannot do that because the exclusive lock is not compatible with the shared lock. To avoid this potential deadlock problem, update locks are used. Only one session can obtain an update lock to a resource at a time. If a transaction modifies a resource, the update lock is converted to an exclusive lock. Otherwise, the lock is converted to a shared-mode lock.
Exclusive locks are used for an insert or delete statement. Exclusive locks are held until the transaction commits or rolls back. Since exclusive locks are used for writes, it is used to prevent access to the data so that integrity is maintained. No other transactions can access data locked with an exclusive lock.
Intent locks are used to lock resources so that resources in a lower hierarchy get locked. This improves performance since only the higher-level intent lock is compared instead of having SQL Server checking the locks in all the resources lower in the hierarchy.
Now that you know the differences between the lock types, Table 7-5 giveshere is a quick reference for possible scenario questions about their use, and which lock type is selected for each use..
"Select * from Table"" | Use Shared locks. They are used for reads. |
"Update a row." | This is a tough one. Should we use Update lock or Exclusive lock? The answer is Update lock. Exclusive lock is only for Insert or Delete. |
"Delete a row" | I mentioned this earlier. Delete and insert uses Exclusive lock. |
"I have a lot of transactions and I need to update my row. . ." | Intent locks. Intent locks puts locks on upper hierarchies. Only those locks on upper hierarchies are compared instead of comparing all locks in the lower level. |
Table 7-5 Different Scenarios for Locks
Deadlock occurs when two applications, which already have locks on separate objects, want a lock on the others object. This causes a stalemate since both applications cannot release their locks and finish their sessions while waiting for the other application to release its locks. SQL Server automatically fixes this by automatically choosing one application, forcing it to release the lock, and allowing the other session to continue. By setting the SET DEADLOCK PRIORITY, you can decide which session is more likely to be SQL Servers next victim. SQL Server will release the lock of the session that has the lower priority. SET DEADLOCK_PRIORITY {LOW|NORMAL}
The LOCK_TIMEOUT setting sets a time limit on how long aa statement will wait for a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the statement is canceled, and an error message stating,: "Lock request time out period exceeded" is returned.
Exam Watch: You can configure timeouts to also prevent deadlocks.
Setting Transaction Isolation Levels is a balance between concurrency and data integrity. The Transaction Isolation Levels are levels that will make the application run at different isolation levels. Deciding which transaction isolation level can dramatically improve performance. There are four transaction isolation levels to choose from:
SET TRANSACTION ISOLATION LEVEL {READ COMMITTED|READ
UNCOMMITTED|REPEATABLE READ|SERIALIZABLE}
Locking Granularity For An Index
The Microsoft SQL Server uses dynamic locking to automatically choose the most efficient locking granularity. The locking granularity differentiates the efficiency of the different locks. The finest lock is the row lock, which is the lock in the lowest hierarchy of the database. The finer the lock, the more overhead is required for SQL Server to compare locks. This overhead is because it has to compare through lower levels. In some cases, however, you may want to choose your own locking granularity for an index. If you are familiar with the type of accesses in your database, setting your own locking granularity may improve performance. For example, if you have a database application that is updated every month, you can allow all concurrent sessions to only get a share lock on the table which cuts down on the overhead. During the day of the update, you can just switch to an exclusive lock on the row. The granularity of locking used on an index can be configured using the sp_indexoption system stored procedure.
Page-level locks, row-level locks, or a combination of page-level and row-level locks can be denied for a given index.
sp_indexoption {[@IndexNamePattern = ] 'index_name'}[,
[@OptionName = ] 'option_name'] [,
[@OptionValue = ]'value']
option_name can be either AllowRowLocks and/or AllowPageLocks. The value is zero for false or one for true.
Although SQL Server automatically sets the optimum table level locking hints, there may be instances where manual settings will be more fitting. For example, if SQL Server uses a coarse locking hint, and you may prefer a finer lock. You can specify a range of table-level locking hints using the SELECT, INSERT, UPDATE, and DELETE statements, which will override the current transaction isolation level for that session.
You can choose from various locking hints:
Server Processes
Processes make up the heart of all transactions, so it makes sense to optimize the processes. The spin counter option and time slice option optimizes processes so that no process can bog down system resources by continuously staying in the system.
The spin counter option specifies a limit on the attempts a process can make when trying to obtain access to a resource. On uniprocessor systems, a process can only try once. With multiprocessor systems, however, the default value is 10,000, which can be altered.
The time slice option is used to set the time limit for a process to be active. If the process exceeds the time limit, the SQL Server kernel assumes the process is stuck and automatically terminates it. You should carefully consider the time slice value. If it is too low, it will slow down the system. This drop in system performances happens because the processes scheduling themselves in and out of the CPU bog down the system. If you set time slice too high, some problems can occur. For example, it can cause long response times when one process doesnt schedule itself out of the CPU until after a long time. This makes the other processes wait in order to be able to run.
Exam WatchCram: Do not confuse the time slice for either the operating system or the SQL Server. The SQL Server kernel controls the time slice for SQL Server.
To make these changes, use the sp_configure system procedure. Since these are advanced options, you can only change the settings when show advanced options is set to one.
SQL Profiler
The Microsoft SQL Server Profiler is a graphical tool that allows system administrators to trace problems by monitoring events. Data is captured continuously in real time, as shown in Figure 7-6. The following exercise will show you everything you ever need to know about traces and the SQL Server Profiler.
Figure 6: SQL Server Profiler
Figure 7: Setting up a trace in SQL Server Profiler
Exercise 7-3 Using SQL Server Profiler
Figure 8: Setting up a trace in SQL Server Profiler
Figure8: Trace Properties in SQL Server Profiler
Exam Watch: Traces can be saved and re-executed later on. There are also predefined traces.
Certification Summary
In this chapter, we discussed the virtues of monitoring and tuning SQL Server. Though this may seem like a daunting task, it is one of the most important jobs for a database administrator. One of the first things that you must do is to develop a tuning and monitoring strategy. In order to develop a strategy, you must first establish a performance baseline. This will help you figure out the range of acceptable performance. Afterwards, you must periodically monitor the areas that can impact performance.
There are three main factors that can affect performance: server hardware, client application, and data application. The monitoring and tuning tools all fall under one of these categories. It is important that you familiarize yourself with each tools function and the category they fall under. This process will allow you to understand the type of performance improvements each tool will have on your SQL Server.
The tool under the server hardware category is the performance monitor. Performance monitor can be used to track performance of disk performance, CPU usage, and memory usage.
The tools under the data application category are min/max server memory, Enterprise Manager, and DBCC. The min/max server memory is used to optimize virtual memory settings for the database. Enterprise manager is used to display information about running processes in SQL Server. DBCC is used to check the logical and physical consistency of the database.
The tools under the client application category are the Query Governor, Showplan, and Statistics I/O. The query governor is used to limit the resources used by a query. Showplan displays the optimal path of the query. Statistics I/O is used to output information about the scans and reads of the tables in the query.
Optimizing a database is never completely finished. There will always be a greater need for use of the database, and you will always be constantly trying to optimize it. The best thing you can do is to periodically monitor the database and try to choose between the greatest benefit with the lowest cost.
Two-Minute Drill