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
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.
DBCC SHOW STATISTICS (tablename, index_name)
10> --DBCC SHOW_STATISTICS (salesne, sne)
11>
DBCC TRACEON
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>
Displaying the Oldest Active Transaction with DBCC OPENTRAN and Viewing Lock Activity
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
dynamically building the DBCC command:
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
Flushing the Procedure Cache
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
Reseeding the IDENTITY Value
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.
Syntax for Reseeding the IDENTITY Value with a New Explicit Value
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>
Syntax for Retrieving the Current IDENTITY Value of a Table and the Correct Value
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>
Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values
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.