BACKUP DATABASE [TestDb] TO DISK=N'C:\download\TestDb_Adhoc_backup.bak'
WITH NAME = N'TestDb-Full Database Backup',
COPY_ONLY, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
100 percent processed.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 17913 pages in 7.380 seconds (18.962 MB/sec).
*/
BACKUP DATABASE [TestDb] TO DISK=N'C:\download\TestDb_Adhoc_compress_backup.bak'
WITH NAME = N'TestDb-Full Database Backup',
COPY_ONLY, COMPRESSION, NOFORMAT, NOINIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
100 percent processed.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
BACKUP DATABASE successfully processed 17913 pages in 5.422 seconds (25.809 MB/sec).
*/
RESTORE VERIFYONLY FROM DISK=N'C:\download\TestDb_Adhoc_backup.bak' WITH NOUNLOAD, NOREWIND
GO
/*
The backup set on file 1 is valid.
*/
ALTER DATABASE [TestDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
/*
RESTORE DATABASE [SalesDB] FROM DISK = N'C:\download\SQL08Backup.BAK' WITH FILE = 37, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [SalesDB] FROM DISK = N'C:\download\SQL08Backup.BAK' WITH FILE = 43, NOUNLOAD, STATS = 10
GO
*/
RESTORE DATABASE [TestDb] FROM DISK=N'C:\download\TestDb_Adhoc_backup.bak' WITH REPLACE, NOUNLOAD, NOREWIND, STATS = 10
GO
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 17912 pages for database 'TestDb', file 'TestDb' on file 1.
Processed 1 pages for database 'TestDb', file 'TestDb_log' on file 1.
RESTORE DATABASE successfully processed 17913 pages in 4.340 seconds (32.245 MB/sec).
*/
ALTER DATABASE [TestDb] SET MULTI_USER;
GO
DBCC CHECKDB ('TestDb') WITH NO_INFOMSGS;
GO
/*
Command(s) completed successfully.
*/
Sunday, January 16, 2011
SQL 2008 - Backup/Restore using T-SQL
Saturday, January 15, 2011
SQL 2008 Core Studies - Index Internals
/*
05 - Index Internal.sql, by review Employee table
Remark: Learned from sqlskills.com
*/
USE MCM2008;
GO
-- Creating the Employee table, its clustered index and the
-- index on SSN
CREATE TABLE Employee
(
EmployeeID INT NOT NULL IDENTITY,
LastName NCHAR(30) NOT NULL,
FirstName NCHAR(29) NOT NULL,
MiddleInitial NCHAR(1) NULL,
SSN CHAR(11) NOT NULL,
OtherColumns CHAR(258) NOT NULL DEFAULT 'Junk'
);
GO
-- Add the clustered index
ALTER TABLE Employee
ADD CONSTRAINT EmployeePK
PRIMARY KEY CLUSTERED (EmployeeID)
ON [PRIMARY];
GO
-- Add the nonclustered unique key
ALTER TABLE Employee
ADD CONSTRAINT SSNUK
UNIQUE NONCLUSTERED (SSN);
GO
SP_HELP EMPLOYEE
GO
/*
Name Owner Type Created_datetime
---------- ------ ------------ -----------------------
Employee dbo user table 2011-01-15 15:31:25.047
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
---------------- ------- --------- ----------- ----- ----- --------- ----------------------- ----------------------------------- ------------------------------
EmployeeID int no 4 10 0 no (n/a) (n/a) NULL
LastName nchar no 60 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
FirstName nchar no 58 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
MiddleInitial nchar no 2 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
SSN char no 11 no no no SQL_Latin1_General_CP1_CI_AS
OtherColumns char no 258 no no no SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
----------------- ---------- -------------- -------------------
EmployeeID 1 1 0
RowGuidCol
------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
-----------------------------------
PRIMARY
index_name index_description index_keys
-------------------- --------------------------------------------------------------------------------- ------------------------
EmployeePK clustered, unique, primary key located on PRIMARY EmployeeID
SSNUK nonclustered, unique, unique key located on PRIMARY SSN
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
------------------------------------ ---- ------------------------------------- ------------- ------------- -------------- ---------------------- ---------------------
DEFAULT on column OtherColumns DF__Employee__OtherC__00551192 (n/a) (n/a) (n/a) (n/a) ('Junk')
PRIMARY KEY (clustered) EmployeePK (n/a) (n/a) (n/a) (n/a) EmployeeID
UNIQUE (non-clustered) SSNUK (n/a) (n/a) (n/a) (n/a) SSN
No foreign keys reference table 'EMPLOYEE', or you do not have permissions on referencing tables.
No views with schema binding reference table 'EMPLOYEE'.
*/
USE IndexInternals;
GO
SELECT index_depth AS D,
index_level AS L,
record_count AS 'Count',
page_count AS PgCnt,
avg_page_space_used_in_percent AS 'PgPercentFull',
min_record_size_in_bytes AS 'MinLen',
max_record_size_in_bytes AS'MaxLen',
avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
(DB_ID('IndexInternals'),
OBJECT_ID('IndexInternals.dbo.Employee'),
1,
NULL,
'DETAILED');
GO
/*
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
3 0 80000 4000 99.3081294786261 400 400 400
3 1 4000 7 91.7540400296516 11 11 11
3 2 7 1 1.09957993575488 11 11 11
(3 row(s) affected)
*/
USE master;
go
IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
DROP TABLE sp_tablepages;
go
CREATE TABLE sp_tablepages
(
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
CONSTRAINT sp_tablepages_PK
PRIMARY KEY (PageFID, PagePID)
);
GO
------------------------------------------------------------------------------
-- How do you use sp_tablepages?
-- Just truncate the table before insert and then select!
------------------------------------------------------------------------------
USE IndexInternals;
GO
TRUNCATE TABLE sp_tablepages;
INSERT INTO sp_tablepages
--EXEC ('DBCC IND (AdventureWorks2008, [Sales.SalesOrderDetail], -1)');
EXEC ('DBCC IND (IndexInternals, Employee, 1)');
GO
SELECT IndexLevel
, PageFID
, PagePID
, PrevPageFID
, PrevPagePID
, NextPageFID
, NextPagePID
FROM sp_tablepages
ORDER BY IndexLevel DESC, PrevPagePID;
GO
sp_helpindex 'Employee'
GO
/*
index_name index_description index_keys
----------------- ----------------------------------------------------------- ---------------
EmployeePK clustered, unique, primary key located on PRIMARY EmployeeID
EmployeeSSNUK nonclustered, unique, unique key located on PRIMARY SSN
*/
SELECT index_depth AS D,
index_level AS L,
record_count AS 'Count',
page_count AS PgCnt,
avg_page_space_used_in_percent AS 'PgPercentFull',
min_record_size_in_bytes AS 'MinLen',
max_record_size_in_bytes AS'MaxLen',
avg_record_size_in_bytes AS 'AvgLen'
FROM sys.dm_db_index_physical_stats
(DB_ID('IndexInternals'),
OBJECT_ID('IndexInternals.dbo.Employee'),
2,
NULL,
'DETAILED');
/*
D L Count PgCnt PgPercentFull MinLen MaxLen AvgLen
---- ---- -------------------- -------------------- ---------------------- ----------- ----------- ----------------------
2 0 80000 179 99.3661106992834 16 16 16
2 1 179 1 44.2055843834939 18 18 18
(2 row(s) affected)
*/
SET STATISTICS IO ON
SELECT e.*
FROM dbo.Employee AS e
WHERE e.SSN = '123-07-9980';
GO
/*
EmployeeID LastName FirstName MiddleInitial SSN OtherColumns
----------- ------------------------------ ----------------------------- ------------- ----------- -----------------------
37281 Ugo A. Scott 123-07-9980 Junk
(1 row(s) affected)
Table 'Employee'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/
SQL 2008 Core Studies - Log File Internals
/*
04 - Logfile Internal.sql, how a transaction log
is circular in nature and how it can skip
active VLFs
Remark: Learned from sqlskills.com
*/
USE master
GO
IF DATABASEPROPERTY ('MCM2008', 'Version') >0
DROP DATABASE MCM2008;
CREATE DATABASE MCM2008 ON PRIMARY (
NAME = 'MCM2008DATA',
FILENAME = N'C:\SQLskills\MCM2008_data.mdf')
LOG ON (
NAME = 'MCM2008_log',
FILENAME = N'C:\SQLskills\MCM2008_log.ldf',
SIZE = 5MB,
FILEGROWTH = 1MB);
GO
USE MCM2008;
GO
SET NOCOUNT ON;
GO
-- Make sure the database is in SIMPLE
-- recovery model
ALTER DATABASE MCM2008 SET RECOVERY SIMPLE;
GO
-- What does the log look like?
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 2 64 0
2 1245184 1253376 0 0 0 0
2 1245184 2498560 0 0 0 0
2 1499136 3743744 0 0 0 0
*/
-- Create a table that will grow very
-- quickly and genrate lots of transaction
-- log
CREATE TABLE BigRows (
c1 INT IDENTITY,
c2 CHAR (8000) DEFAULT 'a');
GO
-- Insert some rows to fill the first
-- two VLFs and start the third
INSERT INTO BigRows DEFAULT VALUES;
GO 300
-- What does the log look like now
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 2 64 0
2 1245184 1253376 20 2 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 0 0 0 0
*/
-- Now start an explicit transaction which
-- will hold VLF 3 and onwards active
BEGIN TRAN
INSERT INTO BigRows DEFAULT VALUES;
GO
-- Now checkpoint to clear the first two
-- VLFs and look at the log again
CHECKPOINT;
GO
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 0 64 0
2 1245184 1253376 20 0 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 0 0 0 0
(4 row(s) affected)
*/
-- Now add some more rows that will fill
-- up VLFs 3 and 4 and then wrap around
INSERT INTO BigRows DEFAULT VALUES;
GO 300
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 20 0 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0
(4 row(s) affected)
*/
-- Now add some more rows - the log is
-- forced to grow. What do the VLF
-- sequence number look like?
INSERT INTO BigRows DEFAULT VALUES;
GO 300
DBCC LOGINFO;
GO
/*
4 new VLFs created because of auto-grow,
4 new VLFs created because of space reseving for rollback.
*/
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 24 2 128 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0
2 253952 5242880 25 2 64 24000000061600024
2 253952 5496832 26 2 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024
(12 row(s) affected)
*/
-- Will checkpoint clear it now?
CHECKPOINT;
GO
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 24 2 128 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0
2 253952 5242880 25 2 64 24000000061600024
2 253952 5496832 26 2 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024
(12 row(s) affected)
*/
-- Find the open transaction
DBCC OPENTRAN
GO
/*
Transaction information for database 'MCM2008'.
Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (21:942:2)
Start time : Jan 15 2011 12:23:17:820PM
SID : 0x01050000000000051500000088910179c111e2566c1dbe1ef4010000
*/
-- How about now?
COMMIT TRAN;
GO
CHECKPOINT;
GO
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 0 128 0
2 1245184 1253376 24 0 128 0
2 1245184 2498560 21 0 64 0
2 1499136 3743744 22 0 64 0
2 253952 5242880 25 0 64 24000000061600024
2 253952 5496832 26 0 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024
(12 row(s) affected)
*/
-- Insert more rows to wrap the transaction
-- log, pay attention to "Parity" column.
INSERT INTO BigRows DEFAULT VALUES;
GO 400
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 0 128 0
2 1245184 1253376 24 0 128 0
2 1245184 2498560 33 2 128 0
2 1499136 3743744 34 2 128 0
2 253952 5242880 25 0 64 24000000061600024
2 253952 5496832 26 0 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 28 2 64 24000000061600024
2 253952 6291456 29 2 64 24000000212700024
2 253952 6545408 30 2 64 24000000212700024
2 253952 6799360 31 2 64 24000000212700024
2 286720 7053312 32 2 64 24000000212700024
(12 row(s) affected)
*/
INSERT INTO BigRows DEFAULT VALUES;
GO 400
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 35 2 64 0
2 1245184 1253376 36 2 64 0
2 1245184 2498560 33 0 128 0
2 1499136 3743744 34 0 128 0
2 253952 5242880 37 2 128 24000000061600024
2 253952 5496832 38 2 128 24000000061600024
2 253952 5750784 39 2 128 24000000061600024
2 286720 6004736 40 2 128 24000000061600024
2 253952 6291456 29 0 64 24000000212700024
2 253952 6545408 30 0 64 24000000212700024
2 253952 6799360 31 0 64 24000000212700024
2 286720 7053312 32 0 64 24000000212700024
(12 row(s) affected)
*/
T-SQL Used to shrink the transaction log
Not all commands are necessary, some commands are used to fix error countered.
BACKUP LOG TestDB
TO DISK = N'C:\TestDB_Adhoc_TransLog.Bak';
GO
-- Get The file Name
EXEC sp_helpdb 'TESTDB'
GO
USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO
USE master
GO
ALTER DATABASE TestDb SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
ALTER DATABASE TestDb SET ONLINE
GO
USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO
/*
Cannot shrink log file 2 (TestDb_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected)
*/
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
GO
USE TestDb
GO
-- SHRINK it to 10MB
DBCC SHRINKFILE ('TestDb_log',TRUNCATEONLY);
GO
/*
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
13 2 63 63 56 56
(1 row(s) affected)
*/
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE TestDb
MODIFY FILE (
NAME = 'TestDb_log',
SIZE = 10);
GO
Friday, January 14, 2011
SQL 2008 Core Studies - Data File Internals
/*
03 - Datafile Internal.sql, various tempdb space usage scenarios
Remark: Learned from sqlskills.com
*/
USE master;
GO
-- Make sure we start with a clean database
RESTORE DATABASE [SalesDB]
FROM DISK = N'C:\download\SalesDBOriginal.bak'
WITH FILE = 1,
MOVE N'SalesDBData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\SalesDB.mdf',
MOVE N'SalesDBLog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\SalesDB_1.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
/*
RESTORE DATABASE SalesDB
FROM DISK = N'C:\download\SalesDBOriginal.bak'
WITH STATS = 10, REPLACE;
GO
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBData.mdf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'SalesDBData' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBData.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 3
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBLog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 3
File 'SalesDBLog' cannot be restored to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SalesDBLog.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 3
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
*/
/*
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 24472 pages for database 'SalesDB', file 'SalesDBData' on file 1.
Processed 4 pages for database 'SalesDB', file 'SalesDBLog' on file 1.
Converting database 'SalesDB' from version 611 to the current version 655.
Database 'SalesDB' running the upgrade step from version 611 to version 621.
Database 'SalesDB' running the upgrade step from version 621 to version 622.
Database 'SalesDB' running the upgrade step from version 622 to version 625.
Database 'SalesDB' running the upgrade step from version 625 to version 626.
Database 'SalesDB' running the upgrade step from version 626 to version 627.
Database 'SalesDB' running the upgrade step from version 627 to version 628.
Database 'SalesDB' running the upgrade step from version 628 to version 629.
Database 'SalesDB' running the upgrade step from version 629 to version 630.
Database 'SalesDB' running the upgrade step from version 630 to version 631.
Database 'SalesDB' running the upgrade step from version 631 to version 632.
Database 'SalesDB' running the upgrade step from version 632 to version 633.
Database 'SalesDB' running the upgrade step from version 633 to version 634.
Database 'SalesDB' running the upgrade step from version 634 to version 635.
Database 'SalesDB' running the upgrade step from version 635 to version 636.
Database 'SalesDB' running the upgrade step from version 636 to version 637.
Database 'SalesDB' running the upgrade step from version 637 to version 638.
Database 'SalesDB' running the upgrade step from version 638 to version 639.
Database 'SalesDB' running the upgrade step from version 639 to version 640.
Database 'SalesDB' running the upgrade step from version 640 to version 641.
Database 'SalesDB' running the upgrade step from version 641 to version 642.
Database 'SalesDB' running the upgrade step from version 642 to version 643.
Database 'SalesDB' running the upgrade step from version 643 to version 644.
Database 'SalesDB' running the upgrade step from version 644 to version 645.
Database 'SalesDB' running the upgrade step from version 645 to version 646.
Database 'SalesDB' running the upgrade step from version 646 to version 647.
Database 'SalesDB' running the upgrade step from version 647 to version 648.
Database 'SalesDB' running the upgrade step from version 648 to version 649.
Database 'SalesDB' running the upgrade step from version 649 to version 650.
Database 'SalesDB' running the upgrade step from version 650 to version 651.
Database 'SalesDB' running the upgrade step from version 651 to version 652.
Database 'SalesDB' running the upgrade step from version 652 to version 653.
Database 'SalesDB' running the upgrade step from version 653 to version 654.
Database 'SalesDB' running the upgrade step from version 654 to version 655.
RESTORE DATABASE successfully processed 24476 pages in 5.466 seconds (34.982 MB/sec).
*/
USE SalesDB
GO
-- First off: large query spill to tempdb
--
-- Nasty query
--
SELECT S.*, P.*
FROM Sales S JOIN Products P
ON P.productID = S.ProductID
ORDER BY P.NAME;
GO
-- What tasks are taking up space right now?
--
SELECT
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
FROM ( SELECT session_id, request_id,
SUM (internal_objects_alloc_page_count) as task_alloc,
SUM (internal_objects_dealloc_page_count) as task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
sys.dm_exec_requests AS t2
WHERE t1.session_id = t2.session_id AND
(t1.request_id = t2.request_id) AND
(t1.task_alloc + t1.task_dealloc > 0)
ORDER BY t1.task_alloc DESC;
GO
/*
session_id request_id task_alloc task_dealloc sql_handle statement_start_offset statement_end_offset plan_handle
---------- ----------- -------------------- -------------------- ----------------------------------------------------- ---------------------- -------------------- -------------------------------------------------------
57 0 66496 504 0x02000000C473422048B523B93F30F55A04C2B881820087A1 138 -1 0x06001000C4734220B8805117000000000000000000000000
(1 row(s) affected)
*/
-- And what's the task doing
--
SELECT * FROM sys.dm_exec_sql_text
(0x02000000C473422048B523B93F30F55A04C2B881820087A1);
GO
/*
dbid objectid number encrypted text
------ ----------- ------ --------- --------------------------------------------------------------------------
NULL NULL NULL 0
-- First off: large query spill to tempdb
--
-- Nasty query
--
SELECT S.*, P.*
FROM Sales S JOIN Products P
ON P.productID = S.ProductID
ORDER BY P.NAME;
(1 row(s) affected)
*/
-- Get query plan in XML format
SELECT * FROM sys.dm_exec_query_plan
(0x06001000C4734220B8805117000000000000000000000000)
GO
-- Get query plan in TEXT/XML format
SELECT * FROM sys.dm_exec_text_query_plan
(0x06001000C4734220B8805117000000000000000000000000,0,-1)
GO
-- Secondly: version store
--
ALTER DATABASE SalesDB SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Go to perfmon and watch version storage
-- added from $SQLINSTANCE$:Transactions\
-- Version store size scale = 0.0001
--
UPDATE Sales SET Quantity = 1;
GO
-- RAW totals for all tempdb data files
-- How big is the version store?
-- Each page is 8k
--
SELECT * FROM sys.dm_db_file_space_usage;
GO
SQL 2008 Core Studies - File Streams
/*
02 - New Structures.sql, to setup and investigate FILESTREAM tables
Remark: Learned from sqlskills.com
*/
-- Setup FILESTREAM at OS level
-- (This has already been done)
-- Check the FILESTREAM configuration level
EXEC sp_configure 'filestream_access_level';
GO
/*
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
filestream access level 0 2 2 2
*/
-- if it isn't enabled, enable FILESTREAM at the
-- instance level
EXEC sp_configure 'filestream_access_level', 2;
RECONFIGURE
GO
-- Create a database. Note the FILESTREAM filegroup
/*
C:\>mkdir C:\SQLskills
C:\>mkdir C:\SQLskills\FileStreamTestDB
*/
USE master
GO
IF DATABASEPROPERTY('FileStreamTestDB','Version') > 0
DROP DATABASE FileStreamTestDB;
CREATE DATABASE FileStreamTestDB ON PRIMARY
( NAME = FileStreamTestDB_Data,
FileName = N'C:\SQLskills\FileStreamTestDB\FSTestDB_data.mdf'),
FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
( NAME= FileStreamTestDBDocuments,
FILENAME = N'C:\SQLskills\FileStreamTestDB\Documents')
LOG ON
( NAME = FileStreamTestDB_log,
FILENAME = N'C:\SQLskills\FileStreamTestDB\FSTestDB_log.ldf');
GO
-- Create two tables with FILESTREAM columns
USE FileStreamTestDB
GO
CREATE TABLE FileStreamTest1 (
DocId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
DocName VARCHAR(25),
Document VARBINARY(MAX) FILESTREAM);
GO
CREATE TABLE FileStreamTest2 (
DocId UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
DocName VARCHAR(25),
Document1 VARBINARY(MAX) FILESTREAM,
Document2 VARBINARY(MAX) FILESTREAM);
GO
-- Now look at the file system again
-- New directories for the tables just created, with a
-- subdirectory for each FILESTREAM column
/*
C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
C:\SQLskills\FileStreamTestDB\Documents\282c7a64-2d4d-4f39-adb7-c11a8a37bf82\5f857a24-0f32-4a33-8956-c3cd9e499921
C:\SQLskills\FileStreamTestDB\Documents\282c7a64-2d4d-4f39-adb7-c11a8a37bf82\de639d81-62c5-4c4e-b1ac-8a4c60e9c3d7
*/
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'Donghua Luo',
CAST ('SQLskills.com' AS VARBINARY(MAX)));
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'KaitLyn Luo',
CAST ('dbaglobe.com' AS VARBINARY(MAX)));
GO
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1;
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x53514C736B696C6C732E636F6D,SQLskills.com
E11BC00C-7994-4CA5-9650-78C2B9382018,KaitLyn Luo,0x646261676C6F62652E636F6D,dbaglobe.com
(2 row(s) affected)
*/
--Note the tow files in the FILESTREAM folder
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:02 PM 13 00000013-00000147-0002
01/14/2011 03:02 PM 12 00000013-00000150-0002
*/
-- Now what happens when we update a FILESTREAM value
UPDATE FileStreamTest1
SET Document = CAST (REPLICATE('a',8000) AS VARBINARY(MAX))
WHERE DocName LIKE '%Donghua%';
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:02 PM 13 00000013-00000147-0002
01/14/2011 03:02 PM 12 00000013-00000150-0002
01/14/2011 03:08 PM 8,000 00000013-00000159-0005
*/
-- Look again and see that the original file hasn't been
-- deleted, there are three files now, representing two
-- values. It will be garbaged collected later.
-- open the second file in notepad to demostrate that
-- someone with privileges can access the files. In
-- this case, it's plain text.
-- Now delete the file and try selecting from the table
-- again.
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x616161616161 (...) 6161,aaaaaa (...) aa
Msg 5552, Level 22, State 1, Line 1
FILESTREAM file named with GUID 'e11bc00c-7994-4ca5-9650-78c2b9382018' that belongs to FILESTREAM data file ID 0x0 does not exist or cannot be opened.
*/
-- Error 5552 and connection broken
-- try DBCC CHECKDB
DBCC CHECKDB (FileStreamTestDB)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
/*
Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000013-00000150-0002" for column ID 3 (column directory ID bfeaaaa1-59dd-4d67-999d-c8e70b4bd777) in object ID 2105058535, index ID 0, partition ID 72057594038779904, page ID (1:161), slot ID 1.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FileStreamTest1' (object ID 2105058535).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'FileStreamTestDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (FileStreamTestDB).
*/
-- DBCC CHECKDB does an extensive check.
-- Try creating a ramdom file in the FILESTREAM directory
-- and running DBCC CHECKDB again
DBCC CHECKDB (FileStreamTestDB)
WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
/*
C:\>echo "A" > C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt
*/
/*
Msg 7908, Level 16, State 1, Line 1
Table error: The file "\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt" in the rowset directory ID 7dd5a1b7-a824-458d-b0f8-0354517c18be is not a valid FILESTREAM file.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 7904, Level 16, State 2, Line 1
Table error: Cannot find the FILESTREAM file "00000013-00000150-0002" for column ID 3 (column directory ID bfeaaaa1-59dd-4d67-999d-c8e70b4bd777) in object ID 2105058535, index ID 0, partition ID 72057594038779904, page ID (1:161), slot ID 1.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FileStreamTest1' (object ID 2105058535).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'FileStreamTestDB'.
*/
-- Try to fix it
-- For FILESTREAM, it can be restored from backup
/*
C:\>del C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777\test.txt
*/
ALTER DATABASE FileStreamTestDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (FileStreamTestDB, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE FileStreamTestDB
SET MULTI_USER;
GO
SELECT *, CAST (Document AS VARCHAR(MAX))
FROM FileStreamTest1
GO
/*
DocId,DocName,Document,
DBF70A90-979E-4366-9D86-D956AF6EE6C1,Donghua Luo,0x616161616161 (...) 6161,aaaaaa (...) aa
(1 row(s) affected)
*/
INSERT INTO FileStreamTest1 VALUES (
NEWID (), 'KaitLyn Luo',
CAST ('dbaglobe.com' AS VARBINARY(MAX)));
GO
/*
Directory of C:\SQLskills\FileStreamTestDB\Documents\7dd5a1b7-a824-458d-b0f8-0354517c18be\bfeaaaa1-59dd-4d67-999d-c8e70b4bd777
01/14/2011 03:08 PM 8,000 00000013-00000159-0005
01/14/2011 03:51 PM 12 00000014-00000050-0002
*/
-- Map filenames back to records
DBCC IND ('FileStreamTestDB','FileStreamTest1',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 162 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 161 1 162 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 166 NULL NULL 2105058535 2 1 72057594039042048 In-row data 10 NULL 0 0 0 0
1 165 1 166 2105058535 2 1 72057594039042048 In-row data 2 0 0 0 0 0
(4 row(s) affected)
*/
--
DBCC TRACEON (3604);
DBCC PAGE ('FileStreamTestDB',1,161,3);
GO
/*
PAGE: (1:161)
BUFFER:
BUF @0x04365F8C
bpage = 0x116B2000 bhash = 0x00000000 bpageno = (1:161)
bdbid = 15 breferences = 0 bUse1 = 23525
bstat = 0xc00109 blog = 0xbb797979 bnext = 0x00000000
PAGE HEADER:
Page @0x116B2000
m_pageId = (1:161) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x8208
m_objId (AllocUnitId.idObj) = 27 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039697408
Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 20 m_slotCnt = 2 m_freeCnt = 7916
m_freeData = 360 m_reservedCnt = 0 m_lsn = (20:81:7)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -636224135
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
Slot 0 Offset 0x60 Length 88
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 88
Memory Dump @0x6467C060
00000000: 30001400 900af7db 9e976643 9d86d956 †0.....÷Ûž—fC.†ÙV
00000010: af6ee6c1 03000002 00280058 80446f6e †¯næÁ.....(.X.Don
00000020: 67687561 204c756f 03000000 00000080 †ghua Luo........
00000030: 900af7db 9e976643 9d86d956 af6ee6c1 †..÷Ûž—fC.†ÙV¯næÁ
00000040: 02000000 14020000 00000000 13000000 †................
00000050: 59010000 05000000 †††††††††††††††††††Y.......
Slot 0 Column 1 Offset 0x4 Length 16 Length (physical) 16
DocId = dbf70a90-979e-4366-9d86-d956af6ee6c1
Slot 0 Column 2 Offset 0x1d Length 11 Length (physical) 11
DocName = Donghua Luo
Document = [Filestream column] Slot 0 Column 3 Offset 0x28 Length 48
ColType = 3 FileId = -2147483648 UpdateSeq = 2
CreateLSN = 00000013:00000159:0005 (19:345:5) TxFMiniVer = 0
XdesId = (0:532)
Slot 1 Offset 0x110 Length 88
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 88
Memory Dump @0x6467C110
00000000: 30001400 1ddfdc57 00ff5749 935a3c32 †0....ßÜW.ÿWI“Z<2
00000010: 2d9f7ebd 03000002 00280058 804b6169 †-Ÿ~½.....(.X.Kai
00000020: 744c796e 204c756f 03000000 00000080 †tLyn Luo........
00000030: 1ddfdc57 00ff5749 935a3c32 2d9f7ebd †.ßÜW.ÿWI“Z<2-Ÿ~½
00000040: 01000000 20020000 00000000 14000000 †.... ...........
00000050: 50000000 02000000 †††††††††††††††††††P.......
Slot 1 Column 1 Offset 0x4 Length 16 Length (physical) 16
DocId = 57dcdf1d-ff00-4957-935a-3c322d9f7ebd
Slot 1 Column 2 Offset 0x1d Length 11 Length (physical) 11
DocName = KaitLyn Luo
Document = [Filestream column] Slot 1 Column 3 Offset 0x28 Length 48
ColType = 3 FileId = -2147483648 UpdateSeq = 1
CreateLSN = 00000014:00000050:0002 (20:80:2) TxFMiniVer = 0
XdesId = (0:544)
*/
SQL 2008 Core Studies - Data Structure
/*
01 - Data Structure.sql
Remark: Learned from sqlskills.com
*/
USE master
GO
IF DATABASEPROPERTY('MCM2008','Version') > 0
drop database MCM2008;
GO
CREATE DATABASE MCM2008;
GO
USE MCM2008;
GO
CREATE TABLE DbCCPageTest (
intCol1 INT IDENTITY,
intCol2 INT,
vcharCol VARCHAR(8000),
lobCol VARCHAR(MAX)
);
INSERT INTO DbCCPageTest VALUES (
1, REPLICATE('ROW1',600), REPLICATE('Row1Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
2, REPLICATE('ROW2',600), REPLICATE('Row2Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
3, REPLICATE('ROW3',600), REPLICATE('Row3Lobs',1000));
INSERT INTO DbCCPageTest VALUES (
4, REPLICATE('ROW4',600), REPLICATE('Row4Lobs',1000));
-- Introducing DBCC IND
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 147 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 146 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 154 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 145 NULL NULL 2105058535 0 1 72057594038779904 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
(8 row(s) affected)
*/
-- Pick a data page and use DBCC PAGE on it.
-- Trace flag is required to make output
-- go to the console rather than the errorlog.
-- Takes db, file, page, print the option 1-3
DBCC TRACEON (3604);
GO
-- Option 1 is a hex dump of each record
-- plus interpreting the slot arry
--DBCC PAGE ('MCM2008',1,XX,1);
DBCC PAGE ('MCM2008',1,147,1);
GO
-- Data page with data records
-- Option 2 is a hex dump of the page plus
-- interpreting the slot array
DBCC PAGE ('MCM2008',1,154,2);
GO
-- Option 3 interprets each records folly
DBCC PAGE ('MCM2008',1,154,3);
GO
-- Note the off-row link. Let's follow it
DBCC PAGE ('MCM2008',1,153,3);
GO
-- Text page
-- Now let's face a forwarding record by
-- updating a row in the middle of a page
UPDATE DbCCPageTest
SET vcharCol = REPLICATE('LongRow2',1000)
WHERE intCol2 =2;
UPDATE DbCCPageTest
SET vcharCol = REPLICATE('LongRow4',1000)
WHERE intCol2 =4;
GO
-- Introducing DBCC IND
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 147 NULL NULL 2105058535 0 1 72057594038779904 In-row data 10 NULL 0 0 0 0
1 146 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 154 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 156 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 157 1 147 2105058535 0 1 72057594038779904 In-row data 1 0 0 0 0 0
1 145 NULL NULL 2105058535 0 1 72057594038779904 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038779904 LOB data 3 0 0 0 0 0
(10 row(s) affected)
*/
-- Look at the first data page again
DBCC PAGE ('MCM2008',1,154,3);
GO
/*
00000000: 049d0000 00010000 00†††††††††††††††††.........
Forwarding to = file 1 page 157 slot 0
*/
-- Now we've got a new record type, let's
-- follow the link
DBCC PAGE ('MCM2008',1,157,3);
GO
-- Another new record type. What's the
-- difference The back-link ...
-- Now we create a clustered index
CREATE CLUSTERED INDEX Dbcc_CL
ON DbccPageTest (intCol1);
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1)
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 171 1 159
1 170 1 168 2105058535 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 171 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 1 1 72057594038845440 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
(11 row(s) affected)
*/
-- Pick an index page, with m_type=2.
DBCC PAGE ('MCM2008',1,170,1);
GO
-- Index page with index records
-- Let's take a closer look.
DBCC PAGE ('MCM2008',1,170,3);
GO
/*
PAGE: (1:170)
BUFFER:
BUF @0x042C3C44
bpage = 0x0D266000 bhash = 0x00000000 bpageno = (1:170)
bdbid = 14 breferences = 0 bUse1 = 14003
bstat = 0x1c0010b blog = 0x1432121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D266000
m_pageId = (1:170) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 1 m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 30 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594039894016
Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 11 m_slotCnt = 4 m_freeCnt = 8044
m_freeData = 140 m_reservedCnt = 0 m_lsn = (19:246:48)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level ChildFileId ChildPageId intCol1 (key) UNIQUIFIER (key) KeyHashValue
------ ----------- ------ ------ ----------- ----------- ------------- ---------------- ----------------
1 170 0 1 1 159 NULL NULL NULL
1 170 1 1 1 169 2 0 NULL
1 170 2 1 1 171 3 0 NULL
1 170 3 1 1 172 4 0 NULL
(4 row(s) affected)
*/
-- Now add a non-clustered index
CREATE NONCLUSTERED INDEX Dbcc_NCL
ON DbccPageTest (intCol2);
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 171 1 159
1 170 1 168 2105058535 1 1 72057594038845440 In-row data 2 1 0 0 0 0
1 171 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 1 1 72057594038845440 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 1 1 72057594038845440 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 1 1 72057594038845440 LOB data 3 0 0 0 0 0
1 174 NULL NULL 2105058535 3 1 72057594038910976 In-row data 10 NULL 0 0 0 0
1 173 1 174 2105058535 3 1 72057594038910976 In-row data 2 0 0 0 0 0
(13 row(s) affected)
*/
-- Pick another index page and explain the
-- key columns
-- %%lockres%% to investigate the key hashes
DBCC PAGE ('MCM2008',1,173,3);
GO
/*
PAGE: (1:173)
BUFFER:
BUF @0x042BF9C4
bpage = 0x0D0A6000 bhash = 0x00000000 bpageno = (1:173)
bdbid = 14 breferences = 0 bUse1 = 14079
bstat = 0x1c0010b blog = 0x1212121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D0A6000
m_pageId = (1:173) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 32 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040025088
Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 3
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 9 m_slotCnt = 4 m_freeCnt = 8040
m_freeData = 144 m_reservedCnt = 0 m_lsn = (19:309:39)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level intCol2 (key) intCol1 (key) UNIQUIFIER (key) KeyHashValue
------ ----------- ------ ------ ------------- ------------- ---------------- ----------------
1 173 0 0 1 1 0 (05001887e4aa)
1 173 1 0 2 2 0 (07000dc1c9d4)
1 173 2 0 3 3 0 (0900fefcd2fe)
1 173 3 0 4 4 0 (0b00274d9328)
(4 row(s) affected)
*/
-- Now drop the clustered index and look
-- again at the nonclustered index
DROP INDEX Dbcc_CL on DbccPageTest
GO
-- And look at the pages again, with
-- the new index ID
DBCC IND ('MCM2008','DbccPageTest',-1);
GO
/*
PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1 168 NULL NULL 2105058535 0 1 72057594038976512 In-row data 10 NULL 0 0 0 0
1 159 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 169 0 0
1 169 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 171 1 159
1 171 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 1 172 1 169
1 172 1 168 2105058535 0 1 72057594038976512 In-row data 1 0 0 0 1 171
1 145 NULL NULL 2105058535 0 1 72057594038976512 LOB data 10 NULL 0 0 0 0
1 144 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 150 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 153 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 155 1 145 2105058535 0 1 72057594038976512 LOB data 3 0 0 0 0 0
1 146 NULL NULL 2105058535 3 1 72057594039042048 In-row data 10 NULL 0 0 0 0
1 175 1 146 2105058535 3 1 72057594039042048 In-row data 2 0 0 0 0 0
(12 row(s) affected)
*/
DBCC PAGE ('MCM2008',1,175,3);
GO
/*
PAGE: (1:175)
BUFFER:
BUF @0x042C33A8
bpage = 0x0D22C000 bhash = 0x00000000 bpageno = (1:175)
bdbid = 14 breferences = 0 bUse1 = 14427
bstat = 0x1c0010b blog = 0x1212121b bnext = 0x00000000
PAGE HEADER:
Page @0x0D22C000
m_pageId = (1:175) m_headerVersion = 1 m_type = 2
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 33 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594040090624
Metadata: PartitionId = 72057594039042048 Metadata: IndexId = 3
Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 13 m_slotCnt = 4 m_freeCnt = 8024
m_freeData = 160 m_reservedCnt = 0 m_lsn = (19:321:94)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0
Allocation Status
GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED
FileId PageId Row Level intCol2 (key) HEAP RID (key) KeyHashValue
------ ----------- ------ ------ ------------- ------------------ ----------------
1 175 0 0 1 0x9F00000001000000 (a100c7b49351)
1 175 1 0 2 0xA900000001000000 (ac00cd3caf65)
1 175 2 0 3 0xAB00000001000000 (af00df772fbc)
1 175 3 0 4 0xAC00000001000000 (b100499772c2)
(4 row(s) affected)
*/
Subscribe to:
Posts (Atom)
