SQL Server/T-SQL Tutorial/System Settings/DBCC
Содержание
- 1 Clearing Out Memory
- 2 DBCC SHOW STATISTICS (tablename, index_name)
- 3 DBCC TRACEON
- 4 Displaying the Oldest Active Transaction with DBCC OPENTRAN and Viewing Lock Activity
- 5 dynamically building the DBCC command:
- 6 Flushing the Procedure Cache
- 7 Reseeding the IDENTITY Value
- 8 Syntax for Reseeding the IDENTITY Value with a New Explicit Value
- 9 Syntax for Retrieving the Current IDENTITY Value of a Table and the Correct Value
- 10 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values
Clearing Out Memory
<source lang="sql">
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS GO The DBCC FREEPROCCACHE command removes all elements from procedure cache so that any new query will require recompi lation. The DBCC DROPCLEANBUFFERS command removes clean buffers from the buffer pool.</source>
DBCC SHOW STATISTICS (tablename, index_name)
<source lang="sql">
10> --DBCC SHOW_STATISTICS (salesne, sne) 11></source>
DBCC TRACEON
<source lang="sql">
3> CREATE TABLE bigrows 4> ( 5> a int primary key, 6> b varchar(1600) 7> ) 8> GO 1> 2> INSERT INTO bigrows VALUES (5, REPLICATE("a", 1600)) 3> 4> INSERT INTO bigrows VALUES (10, replicate("b", 1600)) 5> 6> INSERT INTO bigrows VALUES (15, replicate("c", 1600)) 7> 8> INSERT INTO bigrows VALUES (20, replicate("d", 1600)) 9> 10> INSERT INTO bigrows VALUES (25, replicate("e", 1600)) 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> SELECT first FROM sysindexes WHERE id = OBJECT_ID ("bigrows") 3> GO first
0x830000000100
(1 rows affected) 1> 2> DBCC TRACEON(3604) 3> GO DBCC execution completed. If DBCC printed error messages, contact your system administrator. 1> 2> --DBCC PAGE(yourDatabaseName, 1, 249, 1, 1) 3> --GO 4> 5> INSERT INTO bigrows VALUES (22, REPLICATE("x", 1600)) 6> GO (1 rows affected) 1> 2> --DBCC PAGE(yourDatabaseName, 1, 249, 1, 1) 3> --GO 4> 5> DROP TABLE bigrows 6> GO 1> 2></source>
Displaying the Oldest Active Transaction with DBCC OPENTRAN and Viewing Lock Activity
<source lang="sql">
4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> BEGIN TRANSACTION 4> DELETE employee 5> WHERE ID = 3 6> ROLLBACK TRAN 7> GO (1 rows affected) 1> 2> BEGIN TRAN 3> SELECT ID, first_name 4> FROM employee 5> WITH (TABLOCKX) 6> 7> SELECT request_session_id sessionid, 8> resource_type type, 9> resource_database_id dbid, 10> OBJECT_NAME(resource_associated_entity_id) objectname, 11> request_mode rmode, 12> request_status rstatus 13> FROM sys.dm_tran_locks 14> GO ID first_name
----------
1 Jason 2 Alison 3 James 4 Celia 5 Robert 6 Linda 7 David 8 James 9 Joan
(9 rows affected) sessionid type dbid objectname
rmode rstatus
------------------------------------------------------------ ----------- -----------------------------------
--------------------------
------------------------------------------------------------
51 OBJECT 1 NULL X GRANT 51 OBJECT 1 employee X GRANT
(2 rows affected) 1> 2> 3> 4> drop table employee; 5> GO</source>
dynamically building the DBCC command:
<source lang="sql">
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" DECLARE @Command varchar(255) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT "Reindexing " + @TableName DBCC DBREINDEX(@TableName) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO</source>
Flushing the Procedure Cache
<source lang="sql">
4> 5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
1> 2> 3> 4> CREATE PROCEDURE dbo.usp_employee 5> @SchemaAndTable nvarchar(255) 6> AS 7> EXEC ("SELECT COUNT(*) FROM " + @SchemaAndTable) 8> GO 1> 2> EXEC dbo.usp_employee "Employee" 3> GO
9
1> 2> 3> 4> SELECT COUNT(*) "usp_employee" 5> FROM sys.dm_exec_cached_plans 6> GO usp_employee
81
1> 2> DBCC FREEPROCCACHE 3> GO DBCC execution completed. If DBCC printed error messages, contact your system administrator. 1> 2> drop procedure dbo.usp_employee 3> GO 1> 2> drop table employee 3> GO</source>
Reseeding the IDENTITY Value
<source lang="sql">
5> CREATE TABLE MyTable ( 6> key_col int NOT NULL IDENTITY (1,1), 7> abc char(1) NOT NULL 8> ) 9> INSERT INTO MyTable VALUES ("a") 10> INSERT INTO MyTable VALUES ("b") 11> INSERT INTO MyTable VALUES ("c") 12> SELECT * FROM MyTable ORDER BY key_col 13> 14> 15> DBCC CHECKIDENT ("MyTable" , RESEED) 16> 17> drop table MyTable 18> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a 2 b 3 c
(3 rows affected) Checking identity information: current identity value "3", current column value "3". DBCC execution completed. If DBCC printed error messages, contact your system administrator.</source>
Syntax for Reseeding the IDENTITY Value with a New Explicit Value
<source lang="sql">
DBCC CHECKIDENT ("table_name" , RESEED, new_reseed_value) Reseeding the IDENTITY Value of MyTable with a New Explicit Value 7> CREATE TABLE MyTable ( 8> key_col int NOT NULL IDENTITY (1,1), 9> abc char(1) NOT NULL 10> ) 11> INSERT INTO MyTable VALUES ("a") 12> INSERT INTO MyTable VALUES ("b") 13> INSERT INTO MyTable VALUES ("c") 14> SELECT * FROM MyTable ORDER BY key_col 15> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a 2 b 3 c
(3 rows affected) 1> 2> DBCC CHECKIDENT ("MyTable", RESEED, 50) 3> 4> drop table MyTable 5> GO Checking identity information: current identity value "3", current column value "50". DBCC execution completed. If DBCC printed error messages, contact your system administrator. 1></source>
Syntax for Retrieving the Current IDENTITY Value of a Table and the Correct Value
<source lang="sql">
DBCC CHECKIDENT ("table_name", NORESEED) 6> CREATE TABLE MyTable ( 7> key_col int NOT NULL IDENTITY (1,1), 8> abc char(1) NOT NULL 9> ) 10> INSERT INTO MyTable VALUES ("a") 11> INSERT INTO MyTable VALUES ("b") 12> INSERT INTO MyTable VALUES ("c") 13> SELECT * FROM MyTable ORDER BY key_col 14> 15> 16> --Retrieving the Current IDENTITY Value of the Table MyTable and Its Correct Value 17> DBCC CHECKIDENT ("MyTable", NORESEED) 18> 19> drop table MyTable 20> GO (1 rows affected) (1 rows affected) (1 rows affected) key_col abc
---
1 a 2 b 3 c
(3 rows affected) Checking identity information: current identity value "3", current column value "3". DBCC execution completed. If DBCC printed error messages, contact your system administrator. 1></source>
Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values
<source lang="sql">
DBCC CHECKIDENT checks the current maximum value for the specified table. The syntax for this command is as follows: DBCC CHECKIDENT ( "table_name"
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
) [ WITH NO_INFOMSGS ] NORESEED NORESEED means that no action is taken other then to report the maximum identity value. RESEED RESEED specifies what the current IDENTITY value should be. new_reseed_value The new current IDENTITY value. WITH NO_INFOMSGS WITH NO_INFOMSGS suppresses informational messages from the DBCC output.</source>