Monday, January 31, 2011

SQL 2008 MCM Studies - Restore Internals

/*
14 - Restore Internal.sql
Remark: Learned from sqlskills.com
*/

IF DATABASEPROPERTY('MCM2008', 'Version') > 0
DROP DATABASE MCM2008;

-- Create the database
CREATE DATABASE MCM2008;
GO

-- Create a dummy device
EXEC SP_ADDUMPDEVICE 'disk',
'MyBackupDevice',
'C:\SQLskills\MyBigbackup.bak';
GO

-- Create a table
CREATE TABLE MCM2008..TestTable (
c1 INT IDENTITY,
c2 VARCHAR (100));
GO

INSERT INTO MCM2008..TestTable
VALUES ('INITIAL DATA: transaction 1');
GO

-- And take a full backup
BACKUP DATABASE MCM2008 TO
MyBackupDevice
WITH FORMAT,
DESCRIPTION='Initial full datatabase Backup';
GO

-- Now add some more data and a backup
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 2');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 3');
GO

BACKUP LOG MCM2008 TO MyBackupDevice
WITH DESCRIPTION='First Log Backup';
GO

-- Add more data
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 4');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 5');
GO

-- Now check the time and save it
SELECT GETDATE()
GO
--2011-01-31 20:28:44.700

-- And add some more data
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 6');
INSERT INTO MCM2008..TestTable
VALUES ('Transaction 7');
GO

-- And take another backup
BACKUP LOG MCM2008 TO MyBackupDevice
WITH DESCRIPTION='Second log backup';
GO

-- Simulate disater
DROP DATABASE MCM2008;
GO

-- So what do we have
RESTORE HEADERONLY FROM MyBackupDevice;
GO
/*
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize
----------------- ----------------------------------------- ---------- ----------------- ---------- -------- ---------- ------------------------- ------------------ -------------------- --------------- ----------------------- -------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------------------- ----------------------- --------- -------- --------------- ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- -------------------- ----------- ------------------------------------ ------------------------------------ --------------------------------------- ------------------------------------ ----------------- ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ---------- ------------------------------------ ------------------ ----------------------- --------------------------------------- ------------------------------------ ----------------------------- ------------------------------------ --------------------
NULL Initial full datatabase Backup 1 NULL 0 1 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 1387520 21000000010100151 21000000016200001 21000000010100151 21000000005400064 2011-01-31 20:22:54.000 2011-01-31 20:22:54.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Database D678602C-5203-46A4-9264-A26A4DA8BA9F 1387520
NULL First Log Backup 2 NULL 0 2 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 73728 21000000009500001 21000000019900001 21000000010100151 21000000010100151 2011-01-31 20:26:51.000 2011-01-31 20:26:51.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Transaction Log 113BAE7D-E6F5-4A9E-BD3A-39B981456835 73728
NULL Second log backup 2 NULL 0 3 102 WINPOC\Administrator WINPOC\SQL08 MCM2008 655 2011-01-31 20:13:56.000 73728 21000000019900001 21000000020300001 21000000010100151 21000000010100151 2011-01-31 20:30:41.000 2011-01-31 20:30:41.000 52 0 1033 196609 100 4608 10 0 4000 WINPOC 512 1E9EEAE3-2408-4F86-802C-8D91382098E9 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 SQL_Latin1_General_CP1_CI_AS 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 0 0 0 0 0 0 0 0 0 0 5A3DD442-6D4F-48B7-AB53-99F6A44B0B58 NULL FULL NULL NULL Transaction Log F8137C8F-6A3E-4B78-A0FE-5DA0B74893D4 73728

(3 row(s) affected)

*/

-- Restore the full backup
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, STATS, REPLACE,NORECOVERY;
GO

-- And the log backups
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,NORECOVERY;

RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3,NORECOVERY;
GO

-- And then finalize recovery
RESTORE DATABASE MCM2008 WITH RECOVERY;
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3
4 Transaction 4
5 Transaction 5
6 Transaction 6
7 Transaction 7

(7 row(s) affected)
*/
-- What if we want to examine things
-- between restores?
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, REPLACE,
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1

(1 row(s) affected)
*/
-- Can we update anything?
INSERT INTO MCM2008..TestTable
VALUES('Transaction 6');
GO
/*
Msg 3906, Level 16, State 1, Line 1
Failed to update database "MCM2008" because the database is read-only.
*/


-- Let's do the next log file
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3

(3 row(s) affected)
*/

-- Now restore the rest, and bring
-- the database online
/*
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3, STANDBY='C:\SQLskills\standbyfile.dat';
GO
*/
RESTORE LOG MCM2008
FROM MyBackupDevice
WITh FILE=3, NORECOVERY;
GO

RESTORE DATABASE MCM2008 WITH RECOVERY;
GO

-- what if we want to stop at a specific point?
RESTORE DATABASE MCM2008
FROM MyBackupDevice
WITH FILE=1, NORECOVERY,REPLACE,
STOPAT='2011-01-31 20:28:44.700';
GO
/*Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
*/
/*
EXEC sp_who2;
GO

KILL 60;
GO

ALTER DATABASE MCM2008 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
*/
RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=2,
STOPAT='2011-01-31 20:28:44.700',
STANDBY='C:\SQLskills\standbyfile.dat';

/*
Processed 0 pages for database 'MCM2008', file 'MCM2008' on file 2.
Processed 7 pages for database 'MCM2008', file 'MCM2008_log' on file 2.
This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.
RESTORE LOG successfully processed 7 pages in 0.008 seconds (6.347 MB/sec).
*/

RESTORE LOG MCM2008
FROM MyBackupDevice
WITH FILE=3,
STOPAT='2011-01-31 20:28:44.700',
STANDBY='C:\SQLskills\standbyfile.dat';
GO

SELECT * FROM MCM2008..TestTable;
GO
/*
c1 c2
----------- ----------------------------------------------------------------------------------------------------
1 INITIAL DATA: transaction 1
2 Transaction 2
3 Transaction 3
4 Transaction 4
5 Transaction 5

(5 row(s) affected)
*/

-- Finalize recovery, with it same as below:
-- RESTORE LOG MCM2008
-- FROM MyBackUpDevice
-- WITH FILE=3, RECOVERY,
-- STOPAT='2011-01-31 20:28:44.700';

RESTORE DATABASE MCM2008 WITH RECOVERY;
GO
SELECT * FROM MCM2008..TestTable;
GO


-- Take another backup so we don't need
-- to go through all that again
BACKUP DATABASE MCM2008 TO
MyBackupDevice
WITH DESCRIPTION='Full backup after recovery';
GO

Understand outer join by examples


SQL> create table p (pid number, pname varchar2(30));

Table created.

SQL> alter table p add constraint p_pk primary key (pid);

Table altered.


SQL> create table c (cid number, cname varchar2(30), pid number,
2 constraint c_pk primary key (cid),
3 constraint c_p_fk foreign key(pid) references p(pid));

Table created.

SQL> create index c_n1 on c(pid);

Index created.

SQL> select * from p;

PID PNAME
---------- -----
1 a
2 b
3 c
0 e

SQL> select * from c;

CID CNAME PID
---------- ----- ----------
11 A 1
12 B 2
22 BB 2
14 DD

SQL> select p.*, c.* from p,c where p.pid=c.pid;

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
1 a 11 A 1
2 b 12 B 2
2 b 22 BB 2

SQL> select p.*,c.* from p,c where p.pid=c.pid(+);

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
1 a 11 A 1
2 b 12 B 2
2 b 22 BB 2
3 c
0 e

SQL> select p.*,c.* from p,c where p.pid=c.pid(+) and c.cname='B';

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
2 b 12 B 2

SQL> select p.*,c.* from p,c where p.pid=c.pid(+) and c.cname(+)='B';

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
1 a
2 b 12 B 2
3 c
0 e

SQL> select p.*,c.* from p,c where p.pid=c.pid and c.cname(+)='B';

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
2 b 12 B 2

SQL> select p.*,c.* from p,c where p.pid>=c.pid(+);

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
3 c 12 B 2
3 c 22 BB 2
3 c 11 A 1
2 b 12 B 2
2 b 22 BB 2
2 b 11 A 1
1 a 11 A 1
0 e

8 rows selected.

SQL> select p.*,c.* from p,c where p.pid>=c.pid;

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
1 a 11 A 1
2 b 11 A 1
3 c 11 A 1
2 b 12 B 2
3 c 12 B 2
2 b 22 BB 2
3 c 22 BB 2

7 rows selected.

SQL> select p.*,c.* from p,c where p.pid>c.pid;

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
2 b 11 A 1
3 c 11 A 1
3 c 12 B 2
3 c 22 BB 2

SQL> select p.*,c.* from p,c where p.pid>c.pid(+);

PID PNAME CID CNAME PID
---------- ----- ---------- ----- ----------
3 c 12 B 2
3 c 22 BB 2
3 c 11 A 1
2 b 11 A 1
1 a
0 e

6 rows selected.

Sunday, January 30, 2011

SQL 2008 MCM Studies - Backup Internals


/*
13 - Backup Internal.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 a table and insert 8MB
CREATE TABLE BigTable (
c1 INT IDENTITY,
c2 CHAR(8000) DEFAULT 'a');
GO

CREATE CLUSTERED INDEX BigTable_CL
ON BigTable (c1);
GO

SET NOCOUNT ON;
GO

INSERT INTO BigTable DEFAULT VALUES;
GO 1000


-- Put the database into FULL recovery
-- model and clear out the log
ALTER DATABASE MCM2008 SET RECOVERY FULL;
GO

BACKUP DATABASE MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Full_0.bak'
WITH INIT, STATS;
GO

BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_0_Initial.bak'
WITH INIT, STATS;
GO


-- Now Rebuild the clustered index to
-- generate a bunch of log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO

-- Backup the log to get a baseline size
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_1_Baseline.bak'
WITH INIT, STATS;
GO

-- Test 1
-- Now rebuilds the clustered index again
-- to generate more log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO

-- Now try a full backup to see if it clears
-- the log
BACKUP DATABASE MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Full_1.bak'
WITH INIT, STATS;
GO

-- If it did, this next log backup should be
-- very small
BACKUP LOG MCM2008 TO
DISK ='C:\SQLskills\MCM2008_Log_2_FullTest.bak'
WITH INIT, STATS;
GO

-- Test 2
-- Now rebuild the clustered index again
-- to generate more log
ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO

-- Now try a checkpoint and see if it clears
-- the log
CHECKPOINT;
GO

-- If it did, this next log backup should be
-- very small
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_3_CheckTest.bak'
WITH INIT, STATS;
GO

-- Now let's try a rebild in BULK_LOGGED
-- recovery model. Does that change the log
-- backup size
ALTER DATABASE MCM2008
SET RECOVERY BULK_LOGGED;
GO

ALTER INDEX BigTable_CL ON BigTable REBUILD;
GO

ALTER DATABASE MCM2008
SET RECOVERY FULL;
GO

BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_4_BulkTest.bak'
WITH INIT, STATS;
GO

-- Test 5
-- Now the case where there's a long-running
-- transaction and the log can't be cleared
-- by the backup. when does it get cleared?

-- In another window, do a long-running
-- transaction
/*
USE master
GO

SET NOCOUNT ON
GO

-- Start the long-running transaction
BEGIN TRAN;
GO

INSERT INTO MCM2008..BigTable DEFAULT VALUES;
GO 1000
*/

-- How much log is being used?
DBCC SQLPERF (LOGSPACE);
GO
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------- ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.15147 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 91.61131 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

*/

-- Now let's take a log backup
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_5_LongTest.bak'
WITH INIT, STATS;
GO

-- Did the percentage used go down?
DBCC SQLPERF(LOGSPACE)
GO

/*
Database Name Log Size (MB) Log Space Used (%) Status
---------------------------------- ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.22875 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 91.61131 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/

-- Commit transaction in another window
/*
COMMIT TRAN
GO
*/
-- Did the percentage used go down?
DBCC SQLPERF(LOGSPACE)
GO
-- This is because of SQL Server release these spaces reserved for rollback
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------------ ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.22875 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 78.01361 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/

-- How about a checkpoint?
CHECKPOINT
GO
DBCC SQLPERF(LOGSPACE)
GO
-- How about a log backup
BACKUP LOG MCM2008 TO
DISK = 'C:\SQLskills\MCM2008_Log_LongTest2.bak'
WITH INIT, STATS;
GO
/*
100 percent processed.
Processed 5 pages for database 'MCM2008', file 'MCM2008_log' on file 1.
BACKUP LOG successfully processed 5 pages in 0.038 seconds (0.886 MB/sec).
*/

DBCC SQLPERF(LOGSPACE)
GO
/*
Database Name Log Size (MB) Log Space Used (%) Status
------------------------------ ------------- ------------------ -----------
master 1.742188 44.61884 0
tempdb 0.4921875 71.62698 0
model 0.4921875 98.4127 0
msdb 5.054688 16.30603 0
ReportServer$SQL08 6.742188 17.04085 0
ReportServer$SQL08TempDB 0.7421875 55.06579 0
AdventureWorks2008 1.992188 23.43137 0
AdventureWorksDW2008 1.992188 20.58824 0
AdventureWorksLT2008 1.992188 23.72549 0
AdventureWorks 1.992188 15.44118 0
AdventureWorksDW 1.992188 30 0
AdventureWorksLT 1.992188 25.09804 0
TestDb 0.7421875 90.13158 0
MCM2008 12.05469 12.69443 0
FileStreamTestDB 0.9921875 65.15748 0
SalesDB 933.9922 0.8197339 0
IndexInternals 99.99219 5.169349 0
Credit 399.9922 0.4311608 0
*/

/*
C:\Users\Administrator>dir c:\SQLskills\MCM*
Volume in drive C has no label.
Volume Serial Number is F0ED-4B30

Directory of c:\SQLskills

01/30/2011 09:25 AM 9,719,296 MCM2008_Full_0.bak
01/30/2011 09:31 AM 10,604,032 MCM2008_Full_1.bak
01/30/2011 09:26 AM 83,456 MCM2008_Log_0_Initial.bak
01/30/2011 09:29 AM 9,388,544 MCM2008_Log_1_Baseline.bak
01/30/2011 09:33 AM 9,124,352 MCM2008_Log_2_FullTest.bak
01/30/2011 09:35 AM 9,257,472 MCM2008_Log_3_CheckTest.bak
01/30/2011 09:40 AM 9,753,088 MCM2008_Log_4_BulkTest.bak
01/30/2011 09:51 AM 9,658,880 MCM2008_Log_5_LongTest.bak
01/30/2011 09:59 AM 124,416 MCM2008_Log_LongTest2.bak
9 File(s) 67,713,536 bytes
0 Dir(s) 12,096,598,016 bytes free
*/

Sunday, January 16, 2011

SQL 2008 Core Studies - Optimizer Statistics


/*
07 - Statistics.sql
Remark: Learned from sqlskills.com
*/

-- Restore Credit database, downloaded from past events, sqlskills.com
RESTORE VERIFYONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak';
GO
/*
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditData.mdf" is not in a valid directory.
Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditLog.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
*/

RESTORE FILELISTONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak';
GO
/*
LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint
-------------- ------------------------------------------------------------------------------------- ---- --------------- ----------- -------------------- ------- ------------- ---------- ------------------------------------ --------------------------------------- --------------------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------------------- ------------------------------------ ---------- --------- ---------------
CreditData C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditData.mdf D PRIMARY 1048576000 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 163708928 512 1 NULL 32000000119200089 0C50A096-EAE8-4F54-B482-774964509C60 0 1 NULL
CreditLog C:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV01\MSSQL\DATA\CreditLog.ldf L NULL 419430400 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL

(2 row(s) affected)
*/


RESTORE VERIFYONLY FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak'
WITH MOVE N'CreditData' TO N'C:\SQLskills\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQLskills\CreditLog.ldf' ;
GO
/*
The backup set on file 1 is valid.
*/

RESTORE DATABASE Credit FROM DISK=N'C:\SQLskills\CreditBackup100\CreditBackup100.bak' WITH --REPLACE,
MOVE N'CreditData' TO N'C:\SQLskills\CreditData.mdf',
MOVE N'CreditLog' TO N'C:\SQLskills\CreditLog.ldf',
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 19984 pages for database 'Credit', file 'CreditData' on file 1.
Processed 1 pages for database 'Credit', file 'CreditLog' on file 1.
RESTORE DATABASE successfully processed 19985 pages in 4.086 seconds (38.211 MB/sec).
*/

USE Credit;
GO

EXEC sp_helpindex Member
GO
/*
index_name,index_description,index_keys
member_corporation_link,nonclustered located on PRIMARY,corp_no
member_ident,clustered, unique, primary key located on PRIMARY,member_no
member_region_link,nonclustered located on PRIMARY,region_no
*/

EXEC sp_helpstats Member;
GO
/*
This object does not have any statistics.
*/

CREATE INDEX MemberName
ON Member (LastName, FirstName, MiddleInitial);
GO

EXEC sp_helpstats Member;
GO
/*
This object does not have any statistics.
*/

DBCC SHOW_STATISTICS('Member', 'MemberName');
GO

/*
Name Updated Rows Rows Sampled Steps Density Average key length String Index Filter Expression Unfiltered Rows
----------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ----------------------- --------------------
MemberName Jan 16 2011 10:57AM 10000 10000 26 0 21.5526 YES NULL 10000

(1 row(s) affected)

All density Average Length Columns
------------- -------------- --------------------------------------------------
0.03846154 5.6154 lastname
0.0001 16.5526 lastname, firstname
0.0001 17.5526 lastname, firstname, middleinitial
0.0001 21.5526 lastname, firstname, middleinitial, member_no

(4 row(s) affected)

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
--------------- ------------- ------------- -------------------- --------------
ANDERSON 0 385 0 1
BARR 0 385 0 1
CHEN 0 385 0 1
DORR 0 385 0 1
EFLIN 0 385 0 1
FLORINI 0 385 0 1
GOHAN 0 385 0 1
HAHN 0 385 0 1
INFANTE 0 385 0 1
JONES 0 385 0 1
KAHRMANN 0 385 0 1
LING 0 385 0 1
MAKAI 0 385 0 1
NELSON 0 385 0 1
OLPHANT 0 385 0 1
PAYNTER 0 385 0 1
QUINT 0 384 0 1
RYAN 0 384 0 1
STEIN 0 384 0 1
TOLSKY 0 384 0 1
UNAKLET 0 384 0 1
VANN 0 384 0 1
WOLFMULE 0 384 0 1
XAVIER 0 384 0 1
YEONG 0 384 0 1
ZUCKER 0 384 0 1

(26 row(s) affected)
*/

SET STATISTICS IO ON
GO

SELECT m.LastName, m.FirstName, m.MiddleInitial,
m.phone_no, m.City
FROM dbo.member AS m
WHERE m.FirstName like 'kim%';
GO
/*
LastName FirstName MiddleInitial phone_no City
--------------- --------------- ------------- ------------- ---------------
ANDERSON KIMDQ

(1 row(s) affected)

Table 'member'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
*/


exec sp_autostats 'Member';
GO
/*
Global statistics settings for [Credit]:
Automatic update statistics: ON
Automatic create statistics: ON

settings for table [Member]

Index Name AUTOSTATS Last Updated
-------------------------------- --------- -----------------------
[member_ident] ON 2008-08-26 17:18:12.593
[member_corporation_link] ON 2008-08-26 17:18:12.673
[member_region_link] ON 2008-08-26 17:18:12.793
[MemberName] ON 2011-01-16 10:57:07.027
[_WA_Sys_00000003_0CBAE877] ON 2011-01-16 16:21:47.097

(5 row(s) affected)
*/

UPDATE STATISTICS [dbo].[Member]
WITH FULLSCAN
GO

exec sp_autostats 'Member';
GO
/*
Global statistics settings for [Credit]:
Automatic update statistics: ON
Automatic create statistics: ON

settings for table [Member]

Index Name AUTOSTATS Last Updated
-------------------------------- --------- -----------------------
[member_ident] ON 2011-01-16 19:30:23.740
[member_corporation_link] ON 2011-01-16 19:30:23.753
[member_region_link] ON 2011-01-16 19:30:23.763
[MemberName] ON 2011-01-16 19:30:23.783
[_WA_Sys_00000003_0CBAE877] ON 2011-01-16 19:30:23.823

(5 row(s) affected)
*/

SQL 2008 - Backup/Restore using T-SQL


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.
*/

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)
*/