لطفا منتظر باشید ...
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.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. |
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.
