SQL Server DBCC command Daquan

DBCC dropcleanbuffers: Remove all caches from the buffer pool, clear buffers

This command can be used to clear all test data from SQL Server's data cache (buffer) to ensure the integrity of the test.

It is important to 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 execute checkpoint, write all dirty caches to disk,

This way, when you run DBCC RROPCLEANBUFFERS, you can ensure that all data caches are cleaned up, not part of them.

DBCC cachestats: Displays information about objects that exist 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's log (not the database transaction log) grows fast and the log is opened and viewed slowly

With this command, you can truncate the current server log, primarily to generate a new log. Consider setting up a scheduling task that automatically truncates the server log once a week to execute this command.

Using stored procedure Sp_cycle_errorlog can also achieve the same goal

One, DBCC help class command

DBCC HELP ('? '): Querying all DBCC commands

DBCC HELP (' command '): Query The syntax description of the specified DBCC command

DBCC useroptions: Returns the SET options for the active (set) of the current connection

Second, DBCC Check validation class command

DBCC checkalloc (' Database name '): Checks the consistency of the disk space allocation structure of the specified database

DBCC checkcatalog (' Database name '): Checks for consistency between system tables and system tables in the specified database

DBCC checkconstraints (' tablename '): Checks the integrity of a specified constraint or all constraints on a specified table

DBCC CHECKDB: Checking 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 filegroup in the current database

DBCC checktable: Checks the integrity of data, indexes, and test, ntest, and image pages for a specified table or indexed view

DBCC CHECKIDENT: If there is a large number of data deletions, consider using DBCC CHECKIDENT to reset the self-increment after deletion

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

Checks the specified current identity value

DBCC SQLPERF (umsstats): One of the most critical reference data num runnable: Indicates how many threads are currently waiting to run, if greater than or equal to 2, consider the CPU to reach the bottleneck

Scheduler ID: How many logical CPUs the current machine has and how many Scheduler IDs do you think you can take a look at this article?

SQL Server's unique task scheduling algorithm "Sqlos"

Iii. DBCC Maintenance class commands

DBCC cleantable (' db_name ', ' table_name '): Reclaim ALTER TABLE DROP COLUMN statement to delete variable-length columns or text

DBCC Dbreindex: Rebuilding one or more indexes of the specified database is similar to alter INDEX rebuild

DBCC Indexdefrag: Defragmenting indexes and nonclustered indexes on a table or view

DBCC pintable (db_id,object_id): Table data resides in memory

The way to see which tables reside in memory is: 0: No residency, 1: Resident

1 Use [gposdb]2 GO3 SELECT  

DBCC unpintable (db_id,object_id): Undoing a table that resides in memory

DBCC shrinkdatabase (Db_id,int): Shrinking the data file and log file size for the specified database

DBCC shrinkfile (File_name,int): Shrinking the specified data file and log file size for the associated database

Iv. DBCC Performance Tuning commands

DBCC dllname (free): Unload the specified extension process in memory dynamic-link library (DLL)

Sp_helpextendedproc viewing the loaded extension proc

DBCC dropcleanbuffers: Remove all buffers from the buffer pool

DBCC Freeproccache: Delete 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 a database for the longest executing transaction, which program owns

DBCC show_statistics: Displays the current statistics distribution for the specified target on the specified table

DBCC Showcontig: Displays fragmentation information for data and indexes for a specified table

DBCC SQLPERF (logspace): View log conditions for each DB

(iostats) View IO status

(threads) View Thread consumption

Returns a variety of useful statistics

DBCC Cachestats: Displaying statistics for SQL Server 2000 memory

DBCC Cursorstats: Displaying statistics for SQL Server 2000 cursors

DBCC memorystats: Shows how SQL Server 2000 memory is broken down

DBCC sqlmgrstats: Displays SQL statements for read-ahead and pre-read preparation in the buffer

V. Non-disclosure of DBCC commands

DBCC errlog: Initializing error log files for SQL Server 2000

DBCC flushprocindb (db_id): Clears the stored procedure cache contents of a database in SQL Server 2005 server memory

DBCC Buffer (db_name,object_name,int (number of buffers)): Displays the header and page information of the buffer

DBCC DBINFO (db_name): Displaying structure information for a database

DBCC dbtable: Displaying Tables (data dictionary) information for managing data

DBCC IND (db_name,table_name,index_id): View page information used by an index

DBCC rebuildlog: Rebuilding SQL Server 2000 transaction log files

DBCC log (db_name,3) ( -1~4): View a database of the things log information display format can be: -1,0,1,2,3,4 each number represents a different format

DBCC page: View information for a database data page

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

DBCC prtipage: View the page number that each line of an index page points to

DBCC PSS (user,spid,1): Displays process information that is currently connected to a SQL Server 2000 Server

DBCC RESOURCE: Displays the current resource usage of the server

DBCC TAB (db_id,object_id): Displays the structure of the data page

Vi. DBCC TRACE flag

Trace flags are used to temporarily set specific characteristics of a server or to turn off specific behavior, often for diagnosing performance problems or debugging stored procedures or complex systems

DBCC TRACEON (3604): Turn on trace flag

DBCC Traceoff: Turn off trace flags

DBCC tracestats: View trace Flag Status

VII. recommendations for official use of DBCC

1. Run CHECKDB when the system usage rate is low.

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

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

4. Allow tempdb to have sufficient expansion space on the drive. Use a DBCC with ESTIMATE only to estimate how much space tempdb will need.

5. Avoid running large CPU-intensive queries or batch jobs.

6. Reduce the active transaction while the DBCC command is running.

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

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

Physical_only option: Check only physical errors, not logic errors

Physical errors are more severe than logic, because physical errors are not normally fixed by SQL Server, and most SQL Server can fix a logical error

SQL Server DBCC command Daquan