How system table changes with addition of new objects ?

Note : In order to try this example you need to take a Dedicated Administrator Connection (check BOL http://msdn.microsoft.com/en-us/library/ms189595.aspx). Try this example only on your dev box.

-- Lets create a database as sysobject

CREATE DATABASE sysobject;

USE sysobject

GO

SELECT COUNT(1) cnt FROM sys.sysschobjs;-- Each row in this table represents and object

SELECT COUNT(1) cnt FROM sys.syscolpars;-- Contains a row for every column in a table

SELECT COUNT(1) cnt FROM sys.sysidxstats;-- Contains a row for each index/statistics for table

SELECT COUNT(1) cnt FROM sys.sysallocunits;-- Contains a row for each allocation unit



-- I create a table now and see the changes in the base tables by firing the select statement above

CREATE TABLE Test(c1 int NOT NULL, c2 varchar(10));




As can be seen clearly no of objects increased by 1 to 54, no of columns increased by 2 to 485. Statistics for the table increased the count by 1 in sysidxstats to 182. The allocation unit count goes up by 1 to 104, since the table has added 1 IN_ROW_DATA allocation unit




-- Lets now create an index on this table and see the changes in sysidxstats

CREATE INDEX idx_Test ON Test(c2);

Execute the count again. This time the count for sysidxstats goes up by 1 to 183. This is for the index that we created, also the the count of sysallocunits goes up by 1 to 105 due to the index allocation unit




Lets now add a primary key on this table. Different thing to note here is that the only thing that changed is the object count. It has now increased by 1 to 55.

ALTER TABLE Test ADD CONSTRAINT pk_Test PRIMARY KEY (c1);



Lets add a check constraint now and see. Clearly we can see that check constraint also has gone into the sysschobjs.

ALTER TABLE Test ADD CONSTRAINT ck_Test CHECK (len(c2)<= 6)



Finally add a default constraint now and see. And we see that default constraint also has gone into the sysschobjs.

ALTER TABLE Test ADD CONSTRAINT df_c1 DEFAULT 10 FOR c1;



Get the objects id for the system table sysschobjs. We will use this information to find out the pages that are allocated to this system table

SELECT * FROM sys.objects where name ='sysschobjs'



Based on the object_id received (34) we check the pages allocated to this system table. As can be seen there are 2 pages. We will read the data page 116 and leave the IAM page 117

DBCC IND(sysobject,34,1)



I will only show the extract from this page as it would take too much space to show all the information

DBCC TRACEON(3604)

DBCC PAGE(sysobject,1,116,1)

Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 60

Memory Dump @0x000000000838B326

0000000000000000: 30002c00 e7a4787d 01000000 0000000e 0.,.x}........

0000000000000010: 00552000 00000001 02000000 5e6bfe00 .U .........^k.

0000000000000020: 3d9d0000 dc630101 3d9d0000 0b000000 =...c..=.......

0000000000000030: 01003c00 54006500 73007400 ..<.T.e.s.t.

Slot 55, Offset 0x1362,Length 66, DumpStyle BYTE

Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 66

Memory Dump @0x000000000838B362

0000000000000000: 30002c00 20c96c7e 01000000 00000000 0.,. l~........

0000000000000010: 00504be7 a4787d01 00000000 6078ff00 .PKx}.....`x.

0000000000000020: 3d9d0000 6078ff00 3d9d0000 0b000000 =...`x.=.......

0000000000000030: 01004200 70006b00 5f005400 65007300 ..B.p.k._.T.e.s.

0000000000000040: 7400t.

Slot 56, Offset 0x13a4,Length 66, DumpStyle BYTE

Record Type= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 66

Memory Dump @0x000000000838B3A4

0000000000000000: 30002c00 59ed607f 01000000 00000012 0.,.Y`.........

0000000000000010: 004320e7 a4787d01 02000000 33810001 .C x}.....3...

0000000000000020: 3d9d0000 33810001 3d9d0000 0b000000 =...3...=.......

0000000000000030: 01004200 63006b00 5f005400 65007300 ..B.c.k._.T.e.s.

0000000000000040: 7400t.

OFFSET TABLE:

Row - Offset

56(0x38)- 5028(0x13a4)

55(0x37)- 4962(0x1362)

54(0x36)- 4902(0x1326)



As can be seen clearly above that

• slot 55 : is the entry for table Test
• slot 56 : is the entry for primary key pk_Test
• slot 57 : is the entry for check constraint ck_Test

This also makes the reason why in a database cannot have 2 primary keys with the same name :-). I have taken only a few system tables, however there are other system tables that will have changes as wwell. I leave that to you to investigate.

How files grow within a file group ?

In this blog I will explain how data allocation happens in a file group that has mulitple files under it. Create a database FileG with 2 files under the primary file group. As can be seen clearly I have assigned both the data files in PRIMARY file group the same size (4 MB).

USE master;

GO

CREATE DATABASE FileG

ON PRIMARY

( NAME='FileG_P',

FILENAME='D:\FileG_P.mdf',

SIZE=4MB,

FILEGROWTH=1MB),

( NAME='FileG_S',

FILENAME='D:\FileG_S.mdf',

SIZE=4MB,

FILEGROWTH=1MB)

LOG ON

( NAME='FileG_Log',

FILENAME=

'D:\FileG_Log.ldf',

SIZE=1MB,

FILEGROWTH=1MB);

GO

-- I will now create a table with 2 columns and make sure that each row of the table occupies 1 page

USE FileG

GO

CREATE TABLE Test(c1 int identity, c2 char(8000));

-- Currently there are no pages allocated to this table. This can be verified using the DBCC IND command which returns 0 rows

DBCC IND(FileG, Test, 1)

-- Lets insert 32 rows in the table Test, which will allocate roughly 32 pages + 2 IAM pages

DECLARE @i int= 1;

WHILE (@i <= 32)

BEGIN

INSERT INTO Test VALUES ('a');

SET @i = @i + 1;

END



Execute the DBCC IND again. Clearly it shows that equal number of pages have been allocated from both the files. It looks like the pages have been allocated propotionally. Since both the files were of the same size, same number of data pages (16) were allocated from each file.



DBCC IND(FileG, Test, 1)



To confirm it is propotional, lets now increase the size of one of the files in the database to do that execute the following script to drop and recreate this database. Here I have kept the size of the second file as 8MB, double the size of the first file (4MB)

USE master;

GO

DROP DATABASE FileG;

GO

CREATE DATABASE FileG

ON PRIMARY

( NAME='FileG_P',

FILENAME='D:\FileG_P.mdf',

SIZE=4MB,

FILEGROWTH=1MB),

( NAME='FileG_S',

FILENAME='D:\FileG_S.mdf',

SIZE=8MB,

FILEGROWTH=1MB)

LOG ON

( NAME='FileG_Log',

FILENAME=

'D:\FileG_Log.ldf',

SIZE=1MB,

FILEGROWTH=1MB);

GO

-- I will recreate the same table again

USE FileG

GO

CREATE TABLE Test(c1 int identity, c2 char(8000));

-- Populate 24 rows in the table, which will allocate roughly 24 pages + 2 IAM pages

DECLARE @i int= 1;

WHILE (@i <= 24)

BEGIN

INSERT INTO Test VALUES ('a');

SET @i = @i + 1;

END

Execute the DBCC IND again. The picture is pretty much clear. File 1 (PageFID = 1) has 8 data pages where as File 3 (PageFID = 3) has 16 data pages. The ratio is 1:2 which is the same as the ratio of size of the data file (4 MB/8 MB = 1:2). Thus the data gets distributed propotionally across the data files. As in our case it is 1 extent : 2 extents

DBCC IND(FileG, Test, 1)

Why identity fetch in a transaction is not rolled back ?

Have you ever wondered why the identity values don't rollback even if the underlying transaction is rolled back. With a small example, let's explain

CREATE DATABASE Ident;

-- Create a new table in this database as IdentCol with column c1 as identity

USE Ident

GO

CREATE TABLE IdentCol(c1 intidentity, c2 varchar(10));

-- Lets reset the active transaction log by firing the check point

CHECKPOINT

-- If you want to check what is there in the active transaction log part fire DBCC LOG

DBCCLOG(Ident,4)






-- Begin a transaction insert 1 record into the table and then rollback

BEGIN TRAN T1

INSERT INTO IdentCol VALUES (REPLICATE('a',10));

ROLLBACK TRAN T1

-- Lets see the transaction log again

DBCC LOG(Ident,4)





As (highlighted) can be clearly seen that the generation of the identity column happened outside the transaction (LOP_BEGIN_XACT). This means the identity generation hasn't rolled back even though the insert did. If at the stage we insert 1 row in this table the identity value would be 2



INSERT INTO IdentCol VALUES (REPLICATE('a',10));

SELECT * FROM IdentCol;

Something About Transaction Log

The transaction log in a database maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. The SQL Server Database Engine divides each physical log file internally into a number of virtual log files.

Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. With few examples we will look at the number of VLFs created for a given database. In the first example I create a database with the log file size of 512 KB (minimum possible). I will be using the same script for creation of the everytime with constantly changing the log file size

USEmaster

GO

IF EXISTS(SELECT 1 FROM sys.databases WHERE name ='VLF')

DROP DATABASE VLF;

GO

CREATE DATABASE VLF

ON PRIMARY

( NAME='VLF',

FILENAME= 'D:\VLFP.mdf',

SIZE=4MB,

FILEGROWTH=1MB)

LOG ON

( NAME='VLFL',

FILENAME='D:\VLFL.ldf',

SIZE=1025MB,

FILEGROWTH=1MB);

GO



Now that the database is created we will see how many virtual log files are created. This can be checked using the DBCC LOGINFO command as shown below. For a 512KB of log file there are 2 VLF files created with FSeqNo as 41 and 0. FseqNo when 0 means that the VLF is unused since creation. On the other hand Status 0 means VLF is not being used currently, but may have been used previously. In our case VLF with FseqNo has status 2, which means this is an active VLF (holding active transactions.

The question now remains is what is the CreateLSN field for ? This field has 0 for all the VLFs that were created when the database was created (which exist from the beginning). In our case both existed since the database creation time.

USE VLF

GO

DBCC LOGINFO



Now we will increase the database log file size to 1MB and re-execute the above database creation statement. Once the database has been created, lets see the VLFs using DBCC LOGINFO. As can be seen this time 4 VLFs has been created. Till we reach the 64MB mark there will be only 4 VLFs created

USE VLF

GO

DBCC LOGINFO




Increasing the size of the database log file to 65MB will created 8 VLFs and going beyond 1GB will create 16 as visibile from the snap shot below respectively.

USE VLF

GO

DBCC LOGINFO








This is how a typical log file should be logically organized into VLFs. The diagram has been taken from BOL



Lets not talk about checkpoint. A checkpoint performs the following processes in the database:

• Writes a record to the log file, marking the start of the checkpoint.
• Stores information recorded for the checkpoint in a chain of checkpoint log records.
• One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN). The MinLSN is the minimum of the:
  • LSN of the start of the checkpoint.
  • LSN of the start of the oldest active transaction.
  • LSN of the start of the oldest replication transaction that has not yet been delivered to the distribution database.
• The checkpoint records also contain a list of all the active transactions that have modified the database.
• If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN.
• Writes all dirty log and data pages to disk.
• Writes a record marking the end of the checkpoint to the log file.
• Writes the LSN of the start of this chain to the database boot page.

Let me show you some of this using a small example by creating a table called CheckPt

CREATE TABLE CheckPt(a int);

Before I do any other operation I want to fire a checkpoint in order to clear the existing log

CHECKPOINT



Now we will insert a single row in this table, however we would not commit the transaction.

BEGIN TRANSACTION T1

INSERT INTO Checkpt VALUES (10);

Lets take a look at the transaction log using the command DBCC LOG (databasename, 4). Couple of things to focus here is allocation in IAM, blocking of PFS (Page Free Space) and LOP_INSERT_ROWS (row 20), where we do the actual insert.

DBCC LOG(VLF,4)



Interesting thing is to look at the Log_Record column for this record LOP_INSERT_ROWS. This column is not there in the snapshot, however I have pasted the content for it below. At the offset 51-52 is the page no where the row has been inserted (in this case 4F (hex) = 79 decimal. This is confirmed below using the DBCC IND command as shown below

0x00003E00290000007100000001000200E7020000000002014F000000010000001D00000029000000710000000F00000100000D000000000100000000410003000B00000014000000100008000A000000010000000101000C0000E7A4787D00000102000402030004



DBCC IND(VLF, Checkpt, 1)





Now we fire the check point again. We know that one of the transactions is active (Insert).

CHECKPOINT

Lets look at the LOG using the DBCC LOG

DBCCLOG(VLF,4)

In the snapshot in the highlighted 3 rows you can see the row with LOP_XACT_CKPT is for the transation with LSN 00000029:00000071:0001 (row 1). This is the reason that the Log Record column has this LSN embedded in it. I leave it to you to find out




Finally I will rollback this transaction to show you how the undo information gets added to the log. Currently we have only 25 rows in the table. After rollback we will have few more as can be seen in the snapshot below. Look at the operation LOP_DELETE_ROWS which rollsback the insert statement.

ROLLBACKTRAN T1

DBCCLOG(VLF, 4)

Boot Page

In a database the primary file holds the critical information in a page called the boot page. It is the 9^th page of the primary file in the PRIMARY file group. Let’s see what information this page holds.

-- create a database boot

CREATE DATABASE Boot;

-- Using DBCC PAGE we will examine the details in the boot page

DBCC TRACEON(3604)

DBCC PAGE (Boot, 1, 9, 3)

------- Page Header -----------

Page @0x0000000088D8A000

m_pageId = (1:9) m_headerVersion = 1 m_type = 13

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200

m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064

Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99

m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0

m_slotCnt = 1 m_freeCnt = 6650 m_freeData = 1540

m_reservedCnt = 0 m_lsn = (34:26:3) m_xactReserved = 0

m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1508490807

------- Page Header -----------

-- As can be seen some of the important fields in the header say that : record in this page is 1, page type is 13 which means boot page, free space in the page is 6650 bytes, last lsn that updated this page. We will now look at the data in this page

------------- Page Data -------------------------

DBINFO @0x000000000EFAA060

dbi_dbid = 7 dbi_status = 65536 dbi_nextid = 2105058535

dbi_dbname = Boot dbi_maxDbTimestamp = 2000 dbi_version = 660

dbi_createVersion = 660 dbi_ESVersion = 0

dbi_nextseqnum = 1900-01-01 00:00:00.000 dbi_crdate = 2010-03-18 17:45:52.120

dbi_filegeneration = 0

dbi_checkptLSN

m_fSeqNo = 34 m_blockOffset = 27 m_slotId = 43

dbi_RebuildLogs = 0 dbi_dbccFlags = 2

dbi_dbccLastKnownGood = 1900-01-01 00:00:00.000

dbi_dbbackupLSN

m_fSeqNo = 0 m_blockOffset = 0 m_slotId = 0

------------- Page Data -------------------------

-- Some of the important fields that we need to look at are

• dbi_dbid : unique identifier for the database (Can be obtained from sys.databases as well)
• dbi_dbname : name of the database
• dbi_version : physical version number of the database (660 here). dbi_createVersion is also the same.
• dbi_crdate : creation date of the database
• dbi_dbccLastKnownGood : the datetime of the last clean run of DBCC CHECKDB
• dbi_LastLogBackupTime : last time the log backup was taken
• dbi_differentialBaseGuid: the GUID generated by the last full database backup. All differential backup will check this guid to be restored on to.
• dbi_checkptLSN : This is the last check point lsn. Every time a checkpoint happen the lsn is updated in the boot page. This is where the recovery starts when the database starts

-- I will chow you an example of how the check point gets updated. Cusrrently the checkpoint LSN is

m_fSeqNo = 34 m_blockOffset = 27 m_slotId = 43

-- Lets see what is present in the transaction log. I have just taken the extract of the transaction log

not the entire log so that I can fit it in.

-- I now fire a CHECKPOINT command. Basically I am forcing a checkpoint here

CHECKPOINT

-- Using DBCC LOG we can check that the transaction log now has the following 2 rows for checkpoint

DBCC LOG (Boot, 4)

-- If you see the lsn in the log for checkpoint is "00000022:00000037:003d". I am expecting the same lsn in the boot page as well. Let's fire the DBCC PAGE on boot page and I get the number as below. I need to convert it to hex as shown below

DBCC TRACEON(3604)

DBCC PAGE (Boot, 1, 9, 3)

-- 34 (dec) = 22 (hex)

-- 55 (dec) = 37 (hex)

-- 61 (dec) = 3d (hex)

-- Clearly you can see that the Checkpoint LSN in the log record "00000022:00000037:003d" is the LSN in the boot page.

-- Two points to note here

• Corruption of boot page is the corruption of database
• The information available in the boot page can also be obtained using DBCC DBINFO command

Index Allocation Map

As BOL says an Index Allocation Map (IAM) page maps the extents in a 4-gigabyte (GB) part of a database file used by an allocation unit. An allocation unit is one of three types:

• IN_ROW_DATA : Holds a partition of a heap or index.
• LOB_DATA : Holds large object (LOB) data types, such as xml, varbinary(max), and varchar(max).
• ROW_OVERFLOW_DATA : Holds variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.

Let’s see all this through an example.

USE master;

GO

IF EXISTS(SELECT 1 FROM sys.databases WHERE name ='IAM')

DROP DATABASE IAM;

GO

-- Create a database

CREATE DATABASE IAM;

-- Create a table Test in it

Use IAM

GO

CREATE TABLE Test(c1 int identity(1,1), c2 varchar(8000));

-- Populate this table with some data

DECLARE @i int= 1;

WHILE (@i < 16)

BEGIN

INSERT INTO Test VALUES (REPLICATE('a',8000));

SET @i = @i + 1;

END;



-- Check the pages allocated to this object. Clearly you can see the page 89 is an IAM page as the page type says 10. This IAM page is to manage the IN_ROW_DATA allocation unit. Since this IAM can cover upto 4GB of data size for this allocation unit there is only 1 IAM page required currently.



DBCC IND(IAM, Test, 1)





-- We will now check what is there in the IAM page for this object Test

DBCC TRACEON(3604)

DBCC PAGE(IAM, 1, 89, 1)



-- From the header below you can see the page type is 10 which means IAM page



Page @0x000000009C5A8000

m_pageId =(1:89) m_headerVersion = 1 m_type = 10

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0

m_objId(AllocUnitId.idObj)= 29 m_indexId(AllocUnitId.idInd)= 256

Metadata: AllocUnitId = 72057594039828480

Metadata: PartitionId = 72057594038779904 Metadata: IndexId = 0

Metadata: ObjectId = 2105058535 m_prevPage =(0:0) m_nextPage =(0:0)

pminlen = 90 m_slotCnt = 2 m_freeCnt = 6

m_freeData = 8182 m_reservedCnt = 0 m_lsn =(32:223:7)

m_xactReserved = 0 m_xdesId =(0:0) m_ghostRecCnt = 0

m_tornBits = 0

-- The memory dump of the IAM page clearly shows that the extent has been allocated to it. This is only a partial output as the complete o/p would have taken too much space.

Slot 1, Offset 0xbe,Length 7992, DumpStyle BYTE

Record Type= PRIMARY_RECORD Record Attributes = Record Size = 7992

Memory Dump @0x000000000F1CA0BE

0000000000000000: 0000381f 00008000 00000000 00000000 ..8.............

0000000000000010: 00000000 00000000 00000000 00000000 ................

0000000000000020: 00000000 00000000 00000000 00000000 ................

0000000000000030: 00000000 00000000 00000000 00000000 ................

0000000000000040: 00000000 00000000 00000000 00000000 ................

0000000000000050: 00000000 00000000 00000000 00000000 ................



-- Lets decode the initial few bytes. Specially the 3rd and 4rth byte which is 381f. 381f = 83f1 (reversed bytes) = 1f38 (read right to left) = 7992 (converted to decimal). As can be seen clearly this is the record size of the single record in the IAM. Lets now expand the 5th, 6th, 7th byte which is 000080. The first byte is 00 which means 00000000 (bits). Each bit in this byte covers 1 extent which means 8 pages. If we expand all 3 bytes (000080) it would result in the following bit pattern

-- 00000000

-- 00000000

-- 10000000

-- First byte, all 8 bits 0, which means no allocation for the first 64 pages. Similarly second byte all 8 bits are 0 (64 pages) and finally the third byte first 7 bits are 0 (56 pages) 64 + 64 + 56 = 184 (0 to 183 pages are free (no uniform extent allocated, all mixed extents). Extent starting at 184th page is allocated as represented by the last bit which is set to 1.

-- Let's populate 1 more extent.



DECLARE @i int= 1;

WHILE (@i <= 8)

BEGIN

INSERT INTO Test VALUES (REPLICATE('a',8000));

SET @i = @i + 1;

END;

-- We can clearly see that page 192 to 198 have been allocated from new extent

DBCC IND(IAM, Test, 1)










-- The extract from the IAM page has changed slighlty as can be seen below

DBCC TRACEON(3604)

DBCC PAGE(IAM, 1, 89, 1)



Memory Dump @0x000000000F6FA0BE

0000000000000000: 0000381f 00008001 00000000 00000000 ..8.............

0000000000000010: 00000000 00000000 00000000 00000000 ................

0000000000000020: 00000000 00000000 00000000 00000000 ................



-- 5th, 6th and 7th bytes are the same. However the 8th byte has now changed to 01. 00000001 : This means that the extent that starts with the page 192 is allocated for Test as (64 (1st byte) + 64 (2nd byte) + 64 (3rd byte)) = 192 (0 - 191). We can confirm this by looking at the IAM snapshot above. Pages 192 to 198 are allocated to Test. I will now show how adding one more column to this table will lead to row overflow data movement. This will happen as we already have each row very close the max available size of 8060.



ALTER TABLE Test ADD c3 varchar(500);

-- Updating this column with some data. We now check if the row over flow has happened. Indeed it has. The important thing is now there is one more IAM page 119 which is used to manage the allocation unit ROW_OVERFLOW_DATA. I leave it to you to investigate what is there in IAM page 119

UPDATE Test

SET c3 =REPLICATE('a',500)

-- You can notice the IAM page for ROW_OVERFLOW_DATA and its data pages with page type 3

DBCC IND(IAM, Test, 1)

-- Finally we add one more column as varchar(max) which is going to create the LOB_DATA allocation unit

ALTER TABLE Test ADD c4 varchar(max);

UPDATE Test

SET c4 =REPLICATE('b',8070)

-- You can notice the IAM page for LOB_DATA and its data pages with page type 3. The IAM page is 175

DBCC IND(IAM, Test, 1)