Some useful SQL Server DBCC statistics commands

◆ 1. DBCC cachestats: displays information about objects in the current buffer cache, such as hit rates, compiled objects, and execution plans.
Example :.
DBCC cachestats ..
Execution result (scaling ).
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.
Recall AB 0.0.
Check 0.67021276595744683!
Rule 0.0.
Summary 0.80056155581812771.
Some key statistics can be obtained from this command:
Hit ratio: displays the percentage that a specific object can be hit in the SQL Server cache. The larger the value, the better.
Object count: displays the total number of hit objects in the cache of SQL Server.
AVG. Cost: SQL Server is used to measure the time required to compile an execution plan and the memory required for this plan. Based on this value, you can determine whether the execution plan should be loaded into the cache.
AVG. pages: measure the average total number of 8 K pages used by objects in the cache ..
LW ojbect count, lw avg cost, wl avg stay, LW Ave use: the values of these columns indicate how many specific objects have been removed from the cache by the write process. The lower the value, the better. .
　
◆ 2. DBCC dropcleanbuffers: delete all from the buffer pool and clear the buffer pool. During the test, you can use this command to clear all test data from the SQL server's data cache (buffer) to ensure the fairness of the test. Note that this command only removes the clean cache and does not remove the dirty cache. For this reason, before executing this command, you should first execute the checkpoint to write all the dirty caches to the disk. In this way, when running DBCC rropcleanbuffers, all data caches can be cleared, it is not a part of it. .

◆ 3. DBCC errorlog: If MSSQLServer service is rarely restarted, server logs will grow rapidly and the speed of opening and viewing logs will also be slow. This command can be used to truncate the current server log, mainly to generate a new log. You can set a scheduling task and execute this command every week to automatically truncate server logs. Using the Stored Procedure sp_cycle_errorlog can also achieve the same purpose. .

◆ 4. DBCC flushprocindb: used to clear the cache used by the stored procedure of the specified database in a database instance. The Database ID is a required parameter.
This stored procedure can be used to ensure that the previous Stored Procedure plan does not negatively affect the test results during testing.
Example:
Declare @ intdbid Integer Set @ intdbid = (select dbid from Master. DBO. sysdatabases where name = 'database _ name ').
DBCC flushprocindb (@ intdbid )..

◆ 5. DBCC freeproccache: cache is used to clean up all databases. For example, during the release process, the cache will re-compile some parts (such as special SQL statements), rather than re-use them from the cache.

◆ 6. DBCC memorystatus: List A detailed category that shows how the SQL Server cache is allocated, including cache activity.

◆ 7. DBCC page: used to view the content of a data page in SQL Server.
Example :.
DBCC page (dbid | dbname), pagenum [, print option] [, cache] [, logical]).
Dbid or dbname: it can be a database ID or database name.
Pagenum: the page number to be checked.
Print option: (optional) the value of the print option is 0, 1, 2.
0-(default) display page header information
1-display the header information of the page, the information of each row on the page, and the Offset Table of the page. Lines on the page are displayed one by one. .
2-similar to option 1, a single information block is displayed instead of a page row by row. .
Cache: (optional) the value of this parameter is 1 or 0 ,!
0-the command directly searches for the page number from the disk, instead of checking whether the page number is in the cache DBCC page!
1-if the page is in the cache, the page is obtained from the cache first, instead of directly obtaining the page from the disk.
Logical: (optional) whether the page number is obtained from a virtual page or a 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 three different values. These values are used to view how the cache works in ad-hoc and pre-compiled tsql statements.
Example:
DBCC sqlmgrstats
Result :.
Item status.
------------------------------------
Memory Used (8 K pages) 5446.
Number csql objects 29098!
Number false hits 425490.
-----
Memor used (8 K pages): if the number of memory pages is very large, this may be a prompt: Some user connections are preprocessing many tsql statements.
Number csql objects: indicates the total number of tsql statements in the cache.
Number false hits: Sometimes, when the SQL Server matches a tsql statement that already exists in the cache, an error occurs. Ideally, this number should be as small as possible.

◆ 9. DBCC sqlperf (): This command includes the options with instructions and no instructions.
DBCC sqlperf (logspace)
Provides statistics on the usage of transaction log space in all databases. For more information, see online help.
DBCC sqlperf (umsstats): provides statistics on SQL Server thread management.
Run this command and the result is as follows :..
Statistic Value
-----------------------
Scheduler ID 0.0.
Num users 17.0
Num runnable 0.0...
Num worker 13.0
Idle worker 6.0 ..
Work queued 0.0
Cntxt switches 76752.0
Cntxt switches (idle) 47139.0.
Scheduler switches 0.0.
Total work 54056.0

-------------------------------------------------
Some key statistical information is explained as follows :.
Scheduler ID: Each CPU corresponds to a scheduler, which is the serial number of the scheduler.
Num User: the number of SQL Server threads in the scheduling queue.
Num runnable: Number of SQL Server threads currently running.
Num workers: the size of the thread pool.
Idle workers: idle workers. .
Cntxt switches: Number of switches between executable threads

---------------------
DBCC sqlperf (waitstats): provides information about SQL Server read-ahead activity!
DBCC sqloerf (iostats): provides primary SQL Server read and write information.
DBCC sqlperf (rastats): provides information about SQL Server read-ahead activities
DBCC sqlperf (threads): provides the I/O, CPU, and memory usage information for each SQL Server thread.

------------------------------------------
DBCC showfilestats: displays the overall usage of data files in the database (by Area)
--
DBCC showcontig: you can know the specific page, zone usage, fragmentation level, and other information (by PAGE statistics), which has a partial impact on performance.
--------------