Tuning DBCC Commands
From SQLServerPedia
|
See Also: Main_Page - Monitoring & Tuning - DBCC Commands
Tuning DBCC CommandsTuning DBCC statements are used for efforts that strive towards improving performance. Executing a DBCC statement won't improve performance by itself, but it will give you the information you need to decide whether performance was better or worse after the code or configuration changes were made. NOTE: Undocumented commands are marked with ***. DBCC ADDEXTENDEDPROCThis command is used by the sp_addextendedproc system procedure to register a new dynamic link library (DLL) file as an extended stored procedure. The command takes two parameters: function name, which is the extended procedure name and the DLL file name. SQL Server has numerous built-in extended procedures. Occasionally you might have a need to develop your own extended procedures to implement programming logic which is unsuitable for Transact-SQL. The following is an example of adding an extended procedure:DBCC ADDEXTENDEDPROC ('xp_read_files', 'xpreadfile2000')
The counterpart of ADDEXTENDEDPROC is DROPEXTENDEDPROC. This command removes previously registered extended procedures from the master database. DBCC DROPCLEANBUFFERSThis statement can be used to remove all database pages from the memory. DBCC DROPCLEANBUFFERS takes no parameters. If you are tuning a particular query or a stored procedure, you should execute DBCC DROPCLEANBUFFERS before each execution to get an accurate estimate of the procedure's execution time. If you fail to execute this statement, SQL Server might cache your query and you might celebrate performance improvement even though you haven't improved anything. DBCC FREEPROCCACHEThis statement can be used to clear the procedure cache to ensure that the next execution of a stored procedure will result in a recompile. Much like DROPCLEANBUFFERS, DBCC FREEPROCCACHE should be used when you're tuning the execution of a stored procedure and need to get an accurate estimate of execution time. This command takes no parameters. DBCC PERFMON***This command is no longer documented in SQL Server 2000, however, in previous releases it was used for studying SQL Server performance statistics. Although undocumented, this command still works. DBCC PERFMON takes no parameters. SQL Server online documentation recommends using the System Monitor counters instead of examining the output of DBCC PERFMON. DBCC SQLPERFThis command is used to generate performance data for the current instance of SQL Server. In releases prior to SQL Server 2000, this command was documented with several different permutations. Although SQLPERF still works, online documentation recommends using the System Monitor performance counters instead. DBCC SQLPERF takes no parameters; instead it accepts one of the following keywords:
DBCC SQLPERF also accepts an optional keyword, "CLEAR", which clears a particular set of statistics. If used with DBCC SQLPERF(LOGSPACE), this keyword has no effect. DBCC SQLMGRSTATS***This command accepts no parameters and returns the number of memory pages used, the total number of Transact-SQL statements that have been cached and the number of false hits. When SQL Server is properly optimized, you should see relatively low amounts in the first and third counter and a large number for the second counter. High numbers of memory pages might mean that statements do not get flushed out of cache as often as needed. False hits occur when SQL Server attempts to find a statement within the cache but the query has already been cleared from cache. Here is a sample execution of DBCC SQLMGRSTATS:DBCC SQLMGRSTATSResults: Item Status Memory Used (8k Pages)4213 Number CSql Objects 26864 Number False Hits 23 DBCC PINTABLE and DBCC UNPINTABLEThese statements can be used to mark a particular table so that it won't be flushed out of the data cache. SQL Server reads table data into its data cache as needed. Once data pages are no longer needed to satisfy queries they're flushed back to the disk. If these pages are needed again, SQL Server will have to read them back into cache. Typically SQL Server will make the best decision as to what needs to be in the data cache. In rare instances you might wish to force SQL Server to keep a particular table in cache, using DBCC PINTABLE. Later, if you decide that the table no longer needs to hang out in data cache, you can "unpin" it using DBCC UNPINTABLE. Both commands accept database ID and table ID as parameters. For example, the following script will pin the authors table of the pubs database:USE pubs GO DECLARE @table_id INT,@db_id TINYINTSELECT @table_id = OBJECT_ID('authors'), @db_id = db_id()DBCC PINTABLE(@db_id, @table_id)
Results: Warning: Pinning tables should be carefully considered. If a pinned table is larger, or grows larger, than the available data cache, the server may need to be restarted and the table unpinned.As the warning indicates, pinning a large table (or any table) should be considered as the last resort in your tuning efforts. Unpinning the table is accomplished with a similar query: DBCC UNPINTABLE(@db_id, @table_id)<a name="B8"> DBCC FLUSHPROCINDB***]] This command is identical to <a href="DBCC4#B3">FREEPROCCACHE]], however it removes only procedures of a particular database from the procedure cache. DBCC FLUSHPROCINDB accepts a database ID as the only parameter. For instance, the following command will flush procedures of the pubs database (ID = 5) from the procedure cache:DBCC FLUSHPROCINDB(5) |