Maintaining the System to Get Back Performance


Most post-implementation performance tuning falls under the job scope of the database administrator and for that reason will be found on the companion exam to this one, the administration exam. You will possibly have a few questions on general use and functionality of the tools, so in the sections that follow we will provide a short overview of some of the more important tools.

Stored Procedures Used for Maintenance

Many useful stored procedures are available for people who are a little more advanced with T-SQL and the design of system-level applications. Often a programmatic solution is desired over one that provides only visual feedback. In these instances the output from the stored procedures can be acted on in an automated manner.

The sp_who stored procedure reports snapshot information about current SQL Server users and processes. This is a T-SQL alternative to viewing user activity in the current activity window in SQL Server Enterprise Manager. Similarly, sp_lock reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies. The sp_lock procedure is also a Transact-SQL alternative used to view lock activity in the current activity window in SQL Server Enterprise Manager.

Other procedures also present alternative mechanisms to query for results normally displayed. The sp_spaceused procedure displays an estimate of the current amount of disk space used by a table or database. This is a T-SQL alternative to viewing database usage in the Enterprise Manager.

Still further procedures provide specific information that can be immediately acted on. The sp_monitor statement displays statistics, including CPU usage, I/O usage, and the amount of time idle since last executed. This information can be used as an advanced mechanism for SQL Server monitoring.

Other information of interest to system developers is the availability of a wide variety of built-in system functions. Everything from the name of the server to the name of the user and beyond can be identified for a particular connection.

Various built-in SQL Server functions are available to find out information about the server. This information can be used as an aid in troubleshooting or determining SQL Server utilization, and/or to provide for optimization and performance tuning.

Statistics about SQL Server activity since the server was started are stored in predefined SQL Server counters. Other information pertaining to the server is also stored in similar variables. Functions are all categorized by the type of information provided.

Sometimes the answers won't be found in the system stored procedures, but over the life of a system, the user-defined procedures may have changed drastically. Conversely, the data under these stored procedures may have undergone significant development. In these cases maybe what is needed is a recompile.

Recompilation of Procedures

Adding or altering indexes or changing a stored procedure causes SQL Server to automatically recompile the procedure. This optimization occurs the next time the stored procedure is run, but only after SQL Server is restarted. In instances in which you want to force a recompilation, you can use the sp_recompile system-stored procedure.

Alternatively, you can use the WITH RECOMPILE option when you create or execute a stored procedure. Stored procedures are dealt with in depth in Chapter 9, "Designing a Database Security Plan."

Problem Analysis Through Statistical Functions

There are many statistical functions within the system that can help determine the status of the environment within which the DBMS is operating. For example, you can use the STATS_DATE function to test statistic update settings for any index. Often non-updated statistics cause a system to slow and become unresponsive over time. Periodic checking and UPDATE STATISTICS execution is warranted on most production systems.

What is probably one of the best function sets has been saved until last. Although all the functions mentioned in this unit are important and have their appropriate usage, the most useful set of functions for optimization purposes would be the system statistical functions. But there are many of these related functions, and I have summarized some of the more commonly used ones in the following list:

  • @@CONNECTIONS Returns the number of connections, or attempted connections, the server last started.

  • @@CPU_BUSY Returns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since the server was last started.

  • @@IO_BUSY Enables the database system team to see whether the current file and disk storage configuration is responding well to system stress or would benefit from the use of additional disk storage consideration.

  • @@IDLE Returns the time in milliseconds (based on the resolution of the system timer) that the server has been idle since last started.

  • @@IO_BUSY Returns the time in milliseconds (based on the resolution of the system timer) that the server has spent performing input and output operations since it was last started.

  • @@PACK_RECEIVED Returns the number of input packets read from the network by the server since it was last started.

  • @@PACK_SENT Returns the number of output packets written to the network by the server since it was last started.

  • @@PACKET_ERRORS Returns the number of network packet errors that have occurred on the server since it was last started.

  • @@TOTAL_ERRORS Returns the number of disk read/write errors encountered by the server since it was last started.

  • @@TOTAL_READ Returns the number of noncache disk reads by the server since it was last started.

  • @@TOTAL_WRITE Returns the number of disk writes by the server since it was last started.

As you can see from the wide array of functions SQL Server provides, a lot of useful diagnostic information can be used to optimize throughput while providing maximum performance and user response time. One of the primary database administration tools to begin on this arduous journey is DBCC. You can use DBCC to diagnose and act on some of the most frequent inadequacies of a server configuration.

@@CPU_BUSY enables the database system team to see whether the current configuration is responding well to system stress or whether it would benefit from the use of additional processor consideration. See also "@@IDLE" in the preceding list for related information.


Database Console Command (DBCC)

One of the most useful diagnostic/tuning tools available to the SQL Server database developer and administrator is the DBCC command. The database consistency checker allows you to diagnose and repair some common situations found on the server.

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands and also may be referred to as the Database Consistency Checker. DBCC statements enable you to check performance statistics and the logical and physical consistency of a database system. Many DBCC statements can fix detected problems.

Some DBCC operations provide useful information about the processes that have been performed most recently on the server. This type of information can be quite useful in pinpointing the source of SQL activities. Each of the options, presented in the following list, provides a small piece of a very large puzzle, but collectively they can provide a useful picture of the current server activity:

  • DBCC INPUTBUFFER Provides the last statement sent from a client to the server.

  • DBCC OPENTRAN Provides transaction information for the oldest active transaction, distributed transaction, and nondistributed replicated transaction.

  • DBCC OUTPUTBUFFER Returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.

  • DBCC PROCCACHE Displays information about the procedure cache.

  • DBCC SHOWCONTIG Displays fragmentation information for the data and indexes.

One of the most frequently used DBCC status operations is the SHOWCONTIG. Because it can display information specific to data and index fragmentation, it is useful in determining when to carry out maintenance operations. Many of the DBCC options can be effectively used in troubleshooting as in the following list:

  • DBCC SHOW_STATISTICS Displays the current distribution statistics for the specified target on the specified table.

  • DBCC SQLPERF Provides statistics about the use of transaction log space in all databases.

  • DBCC TRACESTATUS Displays the status of trace flags.

  • DBCC USEROPTIONS Returns the SET options active (set) for the current connection.

All these options can provide valuable data to help you determine how performance can be improved. After status information has been generated, the next task in information retrieval is obtaining validation data that can also give you insight into a server.

Use of DBCC CHECKDB and DBCC CHECKALLOC requires a little further clarification. Use of CHECKALLOC is unnecessary if CHECKDB is used first. CHECKDB contains a superset of options that includes all the functionality provided by CHECKALLOC.


Validation DBCC Operations

The validation options represent tools available that can reveal database storage problems and also provide the mechanisms to modify and fine-tune the environment. To validate various objects on the server, use the following:

  • DBCC CHECKALLOC Checks the consistency of disk space.

  • DBCC CHECKCATALOG Checks for consistency in system tables.

  • DBCC CHECKCONSTRAINTS Checks the integrity of constraints.

  • DBCC CHECKDB Checks the allocation and structural integrity of all the objects in the database.

  • DBCC CHECKFILEGROUP Checks the allocation and structural integrity of tables in a filegroup.

  • DBCC CHECKIDENT Checks the current identity value for a table and, if needed, corrects the value.

  • DBCC CHECKTABLE Checks the integrity of the data, index, text, ntext, and image pages.

  • DBCC NEWALLOC Checks the allocation of data and index pages. Equivalent to CHECKALLOC and used for backward compatibility only.

Some of these functions are very CPU- and disk-intensive. Caution should be exercised around the time of day a DBCC CHECKDB operation is performed. Other functions as well can impact the server and temporarily increase system overhead.

The DBCC DBDEPAIR Compatibility is not used often. DBCC DBREPAIR is included for backward compatibility only. It is recommended that DROP DATABASE be used to drop damaged databases. DBCC DBREPAIR may not be supported in a future version of SQL Server.


Maintenance DBCC Operations

Regular maintenance is needed in all database environments. Data and index pages will become fragmented. Data may become corrupt, file sizes may need to be adjusted, and regular maintenance will help you optimize the server environment. The DBCC options that are used in maintenance processes are listed here:

  • DBCC DBREINDEX Rebuilds one or more indexes.

  • DBCC DBREPAIR Drops a damaged database.

  • DBCC INDEXDEFRAG Defragments clustered and secondary indexes of the specified table or view.

  • DBCC SHRINKDATABASE Shrinks the size of the data files in the specified database.

  • DBCC SHRINKFILE Shrinks a specified data file or log file.

  • DBCC UPDATEUSAGE Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space use reported by sp_spaceused.

Some DBCC options do not directly fit into any one of the aforementioned categories. Listed as miscellaneous options, these DBCC operations can provide assistance, help to free and better use resources, and provide some tracking mechanisms.

Miscellaneous Operations

Several of the DBCC options do not directly fall into any category. They have been included in the following list:

  • DBCC dllname (FREE) Unloads the specified extended stored procedure DLL from memory.

  • DBCC HELP Returns syntax information for the specified DBCC statement.

  • DBCC PINTABLE Marks a table to be pinned, and does not flush the pages for the table from memory.

  • DBCC ROWLOCK Is used for Microsoft SQL Server 6.5, enabling Insert Row Locking operations on tables.

  • DBCC TRACEOFF Disables trace flags. Trace flags are discussed in the next section.

  • DBCC TRACEON Enables trace flags. Trace flags are discussed completely in the next section.

  • DBCC UNPINTABLE Marks a table as unpinned. Table pages in the buffer cache can be flushed.

The DBCC Row Lock Compatibility option is somewhat antiquated. Row-level locking is enabled by default in SQL Server version 2000. The locking strategy is row locking with possible promotion to page or table locking. DBCC ROWLOCK is included for backward compatibility. In a future version of SQL Server, DBCC ROWLOCK may not be supported.


Although DBCC represents one of the premier Microsoft tools available to a SQL Server administrator, there are other alternatives for troubleshooting. Trace flag usage has been a longstanding debugging tool that in the past has proven useful. However, with other graphic tools now available that are easier to use and decipher, the use of trace flags is decreasing. Microsoft has stated that behaviors available with these flags may not be supported in future releases of SQL Server.

Trace Flags

Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues. They are particularly useful in deadlock analysis. Trace flags temporarily set specific server characteristics or switch off a particular behavior. Trace flags are often used to diagnose and debug stored procedures and analyze complex system elements. Four common trace flags are used for troubleshooting different elements of SQL Server:

  • 260 Determines DLL version information.

  • 1204 Finds command affected by deadlock and type of locks partaking.

  • 2528 Disables/enables parallel checking of objects during DBCC use.

  • 3205 Disables/enables tape drive compression support.

To determine dynamic link library version information, see the support for GetXPVersion() in SQL Server Books Online, but other utilities are available without the use of a flag. Parallel DBCC checking should not usually be disabled, and tape dumps and backups should usually be compressed. SQL Server provides many tools to aid in the upkeep of the server. Although specific aspects of the use of these tools will be the role of the administrator, for this exam you will at least need to know what each of the tools does.



    EXAM CRAM 2 Designing and Implementing Databases with SQL Server 2000 Enterprise
    MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition
    ISBN: 0789731061
    EAN: 2147483647
    Year: 2005
    Pages: 154

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net