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 MaintenanceMany 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 ProceduresAdding 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 FunctionsThere 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:
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.
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:
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:
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.
Validation DBCC OperationsThe 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:
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.
Maintenance DBCC OperationsRegular 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:
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 OperationsSeveral of the DBCC options do not directly fall into any category. They have been included in the following list:
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 FlagsTrace 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:
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. |