The following are some SQL Server DBCC commands that can be used to optimize SQL Server.
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
Preparted 0.99988494930394334
Adhoc 0.93237136647793051
Replproc 0.0
Trigger 0.99843452831887947
Cursor 0.42319205924058612.
Exec CX 0.65279111666076906
View 0.95740334726893905
Default 0.60895011346896522
Usrtab 0.94985969576133511.
Recall AB 0.0
Check0.67021276595744683
Rule 0.0.
Summary 0.80056155581812771 obtains some key statistics from this command: l
Hit ratio
:Display the percentage of hit items that a specific object can be in the SQL Server cache. The larger the value, the better l
Object count
:Display the total number of hits of specific types of objects in the SQL Server cache L
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. L
AVG. Pages: Measure the average total number of objects in the cache using 8 K pages L
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 to 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 the MSSQLServer service is rarely restarted, the server logs will grow fast, 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:Clears the cache used by the stored procedure of a 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
:Lists A detailed category, which shows how the SQL Server cache is allocated, including cache activity.
7
. DBCC page
:Example of how to view a data page in SQL Server: DBCC page (dbid | dbname), filenum, pagenum [, print option] [, cache] [, logical]) parameter description: dbid or dbname: it can be a database ID or database name. Pagenum: Print option of the page number to be checked: (optional) the value of the print option is 0, 1, 2. 0-(default) show the header information of the page 1-show the header information of the page, the information of each row in 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-the command directly searches for the page number from the disk instead of checking whether the page number is in the cache DBCC page1-if the page is in the cache, obtain pages from the cache first, instead of directly obtaining pages from the disk. Logical: (optional) Whether to obtain the page number from a virtual page or a logical page. The value of this parameter can be 1 or-a virtual page number 1-A logical page number
8
. DBCC sqlmgrstats
:These values are used to generate three different values. They are used in the ad-hoc and pre-compiled tsql statements you want to view how the cache works. Example: DBCC sqlmgrstats result: item status
------------------------------------
Memory Used (8 K pages) 5446
Number csql objects 29098
Number false hits 425490 where: l 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. L number csql objects: indicates the total number of tsql statements in the cache l number false hits: Sometimes, when SQL Server matches a tsql statement that already exists in the cache, an error occurs. Ideally, this number should be as small as possible.
8
. 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 about SQL Server thread management. The result is as follows: statistic value generation ---------------------- scheduler ID 0.0 num users 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 the following is an explanation of some key statistical information: l scheduler ID: Each CPU corresponds to a scheduler, which is the serial number of the scheduler l num User: the current SQ in the scheduling queue L server thread count l num runnable: number of currently running SQL Server threads l num workers: thread pool size L idle workers: idle workers. L cntxt switches: Number of exchange contexts between executable threads DBCC sqlperf (waitstats): provides information about SQL Server read-ahead activity DBCC sqloerf (iostats ): provides the primary SQL Server read and write information DBCC sqlperf (rastats): provides the SQL Server read-ahead activity information DBCC sqlperf (threads ): provides the I/O, CPU, and memory usage information for each SQL Server thread.
DBCC Ind ('pagesplittest', 't1', 1 );
Go
This command list all the pages that are allocated to an index. Here's the output in this case:
The columns mean:
- Pagefid-the file ID of the page
- Pagepid-the page number in the file
- Iamfid-the file ID of the iam page that maps this page (this will be null for IAM pages themselves as they're not self-referential)
- Iampid-the page number in the file of the iam page that maps this page
- Objectid-the ID of the object this page is part
- Indexid-the ID of the index this page is part
- Partitionnumber-the Partition Number (as defined by the partitioning scheme for the index) of the partition this page is part
- Partitionid-the internal ID of the partition this page is part
- Iam_chain_type-see Iam chains and allocation units in SQL Server 2005
- Pagetype-the page type. Some common ones are:
- 1-Data Page
- 2-index page
- 3 and 4-text pages
- 8-gam page
- 9-SGAM page
- 10-iam page
- 11-PFS page
- Indexlevel-what level the page is at in the index (if at all ). remember that index levels go from 0 at the leaf to N at the root page (partition t in clustered indexes in SQL Server 2000 and 7.0-where there's a 0 at the leaf level (Data pages) and a 0 at the next level up (first level of index pages ))
- Nextpagefid and nextpagepid-the page ID of the next page in the doubly-linked list of pages at this level of the Index
- Prevpagefid and prevpagepid-the page ID of the previous page in the doubly-linked list of pages at this level of the Index
