SQL Server DBCC command Overview

SQL Server DBCC command Overview

DBCC dropcleanbuffers: deletes all caches from the buffer pool and clears the buffer pool.

During the test, you can use this command to clear all test data from the sqlserver 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 cache to the disk,

In this way, when running DBCC rropcleanbuffers, all data caches can be cleared, rather than part of them.

 

DBCC cachestats: displays information about objects in the current buffer cache, such as hit rates, compiled objects, and execution plans.

 

DBCC errorlog: If the MSSQLServer service is rarely restarted, the server log (not the database transaction log) will grow fast, and the log opening and viewing speed 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 achieve the same purpose.

 

1. DBCC help commands

DBCC help ('? '): Queries all DBCC commands.

DBCC help ('COMMAND '): Describes the syntax of the specified DBCC command.

DBCC useroptions: return the set Option of the current connection activity (setting ).

 

2. DBCC check and verification commands 

DBCC checkallog ('database name'): checks whether the disk space allocation structure of the specified database is consistent.

DBCC checkcatalog ('database name'): checks the consistency between the system table of the specified database and the system table.

DBCC checkconstaints ('tablename'): checks the integrity of the specified or all constraints on the specified table.

DBCC checkdb: checks the allocation and structural integrity of all objects in the database.

DBCC checkfilegroup: checks the allocation and structural integrity of all tables in the specified file group in the current database.

DBCC checktable: checks the integrity of data, indexes, and test, ntest, and image pages of a specified table or index view.

DBCC checkident: if a large amount of data is deleted, use DBCC checkident to reset the auto-increment value after deletion.

Http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/8fa3e3a8-2ff2-4a68-be3e-92e76c380ef9/

Check the specified current ID value

DBCC sqlperf (umsstats): The most critical reference data num runnable: indicates how many threads are waiting to run. If the value is greater than or equal to 2, the CPU reaches the bottleneck.

Scheduler ID: the number of logical CPUs on the current machine and the number of scheduler IDs.Article

Sqlserver's unique task scheduling algorithm "sqlos"

 

Iii. DBCC maintenance commands

DBCC cleantable ('db _ name', 'table _ name'): Clear the alter table drop column statement to delete variable-length columns or text

DBCC dbreindex: rebuilding one or more indexes of a specified database is similar to alter index rebuild.

DBCC indexdefrag: defragmentation of indexes and non-clustered indexes on tables or views

DBCC pintable (db_id, object_id): Stores Table data in the memory.

To check which tables reside in the memory, the method is: 0: no resident, 1: Resident

1 Use [Gposdb]2 Go3 SelectObjectproperty(Object_id('DBO. systempara'),'Tableispinned')
 

 

DBCC unpintable (db_id, object_id): undo the table residing in the memory

DBCC shrinkdatabase (db_id, INT): shrink the data file and Log File Size of the specified database

DBCC shrinkfile (file_name, INT): compresses the specified data file and Log File Size of the relevant database.

 

 

Iv. DBCC performance Adjustment Command

DBCC dllname (free): detaches a specified DLL from the memory)

Sp_helpextendedproc view the loaded extension proc

DBCC dropcleanbuffers: deletes all buffers from the buffer pool.

DBCC freeproccache: deletes all cached execution plans from the execution plan buffer.

DBCC inputbuffer: displays the last statement sent from the client to the server.

DBCC opentran (db_name): queries the transaction that has been executed for the longest time in a database.ProgramYes

DBCC show_statistics: displays the current statistics distribution of the specified target on the specified table.

DBCC showcontig: displays the data and index fragmentation information of a specified table.

DBCC sqlperf (logspace): view the logs of each database

(Iostats) view Io status

(Threads) view thread consumption

Returns a variety of useful statistics.

DBCC cachestats: displays statistics about the memory size of SQL Server 2000.

DBCC cursorstats: displays SQL Server 2000 cursor statistics

DBCC memorystats: displays how the memory segments of SQL Server 2000 are subdivided.

DBCC sqlmgrstats: displays the SQL statements prepared for first read and pre-read in the buffer zone.

 

5. commands not made public by DBCC

DBCC errlog: Initialize the Error Log File of SQL Server 2000

DBCC flushprocindb (db_id): clears the Stored Procedure cache content of a database in SQL Server 2005 server memory.

DBCC Buffer (db_name, object_name, INT (number of buffers): displays the header information and page information of the buffer.

DBCC dbinfo (db_name): displays the structure information of the database.

DBCC dbtable: displays the table (data dictionary) information for managing data.

DBCC Ind (db_name, table_name, index_id): view the page information used by an index

DBCC rebuildlog: rebuilding the SQL Server 2000 Transaction Log File

DBCC log (db_name, 3) (-1 ~ 4): display the transaction log information of a database in the format of-, 0, or. Each number represents a different format.

DBCC page: view the information on a database data page

DBCC procbuf: displays the buffer headers and stored procedure headers in the process buffer pool.

DBCC prtipage: view the page number pointed to by each row on an index page

Dbcc pss (user, spid, 1): displays the process information currently connected to the SQL Server 2000 Server

DBCC Resource: displays the resources currently used by the server.

DBCC tab (db_id, object_id): displays the structure of the data page.

 

Vi. DBCC tracking mark

Trace flag is used to temporarily set specific features of the server or disable specific behavior, often used to diagnose performance problems or debug stored procedures or complex systems

DBCC traceon (3604): enables the trace flag

DBCC traceoff: Disable trace flag

DBCC tracestats: view the trace tag status

 

 

7. Suggestions for using DBCC officially

1. Run checkdb when the system usage is low.

2. Make sure that other disk I/O operations, such as disk backup, are not performed at the same time.

3. Place tempdb in a separate disk system or fast disk subsystem.

4. Allow tempdb to have enough expansion space on the drive. Use DBCC with estimate only to estimate the space required by tempdb.

5. Avoid running query or batch processing jobs that occupy a large amount of CPU.

6. Reduce the number of active transactions when running the DBCC command.

7. Use the no_infomsgs option to reduce the output of some information.

8. Consider using DBCC checkdb with physical_only option to check the physical structure of pages and records.

Physical_only option: only check physical errors, not logical errors

Physical errors are more serious than logical errors, because physical errors cannot be fixed by sqlserver, and most logical errors can be fixed by sqlserver.

 

Sleepy. Sleepy ~~