Some SQL Server DBCC commands that are useful but not in the documentation
Here are some DBCC commands for SQL Server, you can use when optimizing SQL Server.
1
. DBCC CacheStats: Shows information that exists in the current Buffer Cache, for example: Hit Rates, compiled objects and execution plans
example:
DBCC Cachestats
Execution result (abbreviation)
Object name Hit Ratio ------------ -------------
Proc 0.86420054765378507 Prepared 0.99988494930394334 Adhoc 0.93237136647793051 ReplProc 0.0 Trigger 0.99843452831887947 Cursor 0.42319205924058612 Exec Cxt 0.65279111666076906 View 0.95740334726893905 Default 0.60895011346896522 UsrTab 0.94985969576133511 SysTab 0.0 Check 0.67021276595744683 Rule 0.0 Summary 0.80056155581812771
You can get some key statistics from this command:
l
Hit Ratio
: Show a specific object can be hit in the cache of SQL Server, the greater the value, the better
l
Object country
: Display the total number of hits in the cache of a specific type of object in SQL Server
l
Avg.cost: SQL Server is used to measure the time required to compile an execution plan, and the memory required for this plan. Depending on this value, you can decide whether the execution plan should be loaded in the cache.
l
AVG.PAGES: Measuring objects in the cache uses the average number of 8K pages
l
LW OjBect Count Count
, LW AVG COST
, WL Avg Stay
LW AVE USE: The values of these columns indicate how many specific objects have been written from the cache. The lower these values, the better.
2
. DBCC DROPCLEANBUFFERS: Remove all and clear buffers from the buffer pool. When testing, using this command can clear all test data from the data cache of SQL Server's data to ensure the fairness of the test. It should be noted that this command only removes clean cache, and does not remove the dirty cache. For this reason, before executing this command, you should execute checkpoint, write all dirty caches to disk, so that all data caches can be cleaned when running DBCC RropcleanBuffers, rather than part of it.
3
. DBCC ERRORLOG: If you raise the MSSQLServer service, the server's log will grow very quickly, and the speed of opening and viewing the log will be slow. Using this command, you can truncate the current server log, mainly to generate a new log. Consider setting up a scheduling task, perform this command automatically truncated the server log per week. Use stored procedures
SP_CYCLE_ERRORLOG can also achieve the same purpose.
4
.
DBCC FlushProcindb:
The cache used to specify the stored procedure of the database in a database instance. Database
Id
It is a must-lose parameter.
In the test, the previous stored procedure plan does not have a negative impact on the test results, and this stored procedure can be used.
example:
Declare @intdbid integer set @intdbid = (Select Dbid from master.dbo.sysdatabases where name = 'database_name')
DBCC FlushProcindb (@InTDBID)
5
. DBCC FreeProccache
: Used to clean up the process cache for all databases. For example, the release process cache will result in re-compiling some parts (eg, special SQL statements) instead of reuse from the cache.
6
. DBCC MemoryStatus
: Lists a detailed classification, and the SQL Server cache is displayed, including cache activities.
Seduce
. DBCC Page
: Used to view the content of a data page in SQL Server
example:
DBCC Page (DBID | DBNAME), Pagenum [, Print Option] [, Cache] [, Logical])
Parameter Description:
DBID or DBNAME: Yes can be a database ID or database name.
Pagenum: The page number to check
Print Option: (Optional) The value of the print option is: 0, 1, 2.
0- (Default) Show header information
1-Display page header information, page of each line, and the offset table of the page. The lines in the page are displayed.
2- The same is the same as the option 1, except that it is not an enlarged display page, but is displayed a single information block.
Cache: (Optional) This parameter is 1 or 0,
0- Command Directly look up the page number instead of checking whether the page number is in the cache DBCC Page
1- If the page is in the cache, you can get the page from the cache, not directly from the disk.
Logical: (Optional) This parameter is used to get obtained from the virtual page or logical page. The value of this parameter can be 1 or 0,
0- a virtual page number
1 - a logical page number
8
. DBCC SQLMGRSTATS
: Used to generate 3 different values, how do these values work in the TSQL statement you want to see in AD-HOC and precompilation.
example:
DBCC SQLMGRSTATS
result:
Item Status ----------------------------------- Memory USED (8k pages) 5446 Number CSQL Objects 29098 Number False Hits 425490
among them:
l Memor Used (8k pages): If the number of memory is very large, this may be a prompt: indicating that some user connections are preprocessing many TSQL statements.
l Number CSQL Objects: Indicates that the total number of statements that have been in the cache is L Number False Hits: Sometimes, when SQL Server matches the TSQL statement that matches the TSQL statement that already exists in the cache, there is an error hit. In the ideal case, this number should be as small as possible.
8
. DBCC SQLPERF
(): This command includes those options with document descriptions and not explained.
DBCC SQLPERF
(Logpace
)
Provides statistics on transaction log space usage in all databases. Specifically, you can refer to the online help.
DBCC SQLPERF (UMSSTATS): Provides statistics about SQL Server thread management
Run this command, the result is as follows:
Statistic
Value
-------------------------------------------------------------------------------------------------------------------------------------- --------
Scheduler ID
0.0
Num User
17.0
Num Runnable
0.0
Num workers
13.0
IDLE WORKERS
6.0
Work queued
0.0
CNTXT Switches
76752.0
CNTXT SWITCHES (IDLE)
47139.0
Scheduler Switches
0.0
Total Work
54056.0
Here are some explanations for key statistics:
l Scheduler ID: Each CPU corresponds to a scheduler, which is the serial number of the scheduler.
l Num User: The number of SQL Server threads currently in the dispatch queue
l Num Runnable: The number of SQL Server threads currently is currently running
l Num workers: the size of the thread pool
l iDle workers: Workers are free.
l CNTXT SWITCHES: Switching the number of contexts between executable threads
DBCC SQLPERF (WaitStats): Provides SQL Server
Read-Ahead Activity
Information
DBCC SQLOERF
(
Iostats
): Provide main
SQL Server
Read and write information
DBCC SQLPERF
(
Rastats
):provide
SQL Server Read-ahead
Information information
DBCC SQLPERF
(
Threads
): Provide each
SQL Server
Thread
of
I / O
,
CPU
And information on memory usage.
