SQL Server/T-SQL Tutorial/System Settings/DBCC

Материал из SQL эксперт
Перейти к: навигация, поиск

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>