MSSQL - A looks @ page dump for table and index pages

Objective: We will take a look at the page dump using DBCC for a table and index page (NC - non clustered)

Few notes:

File groups in MSSQL are equivalent of Oracle's Tablespace
Creating Cluster index on MSSQL physically rebuilds the table and is more like IOT in oracle.
So in MSSQL if we dont want the clustered index, better to code the primary key index as non-clustered.
Page size is 8KB in MSSSQL.

References:
https://www.red-gate.com/simple-talk/databases/sql-server/learn/oracle-to-sql-server-crossing-the-great-divide-part-3/
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/viewing-sql-server-non-clustered-index-page-contents/ba-p/370420

Table creation in MSSQL:

CREATE TABLE test_table
    (
      id INT ,
      random_data INT ,
      update_date DATE ,
      vc_small VARCHAR(10) ,
      vc_padding VARCHAR(100)
    ) ;
go
 
CREATE INDEX bt_i_rand ON test_table(random_data) ;
go
 
DECLARE @div INT = 50 ;
DECLARE @mod INT = 100 ;
DECLARE @limit INT = @div * @mod ;
DECLARE @driver INT = 1000 ;
 
WITH    generator
          AS ( SELECT   1 AS id
               UNION ALL
               SELECT   id + 1
               FROM     generator
               WHERE    id < @driver
             )
    INSERT  INTO test_table
            SELECT  id ,
                    ABS(xx % @mod) ,
                    NULL ,
                    NULL ,
                    REPLICATE('x', 100)
            FROM    ( SELECT TOP ( @limit )
                                @driver * ( g1.id - 1 ) + g2.id id ,
                                CAST(NEWID() AS VARBINARY) xx
                      FROM      generator g1
                                CROSS JOIN generator g2
                    ) iv
    OPTION  ( MAXRECURSION 0, FORCE ORDER ) ;
go

To get table space usage details:

SELECT  SUBSTRING(tab.name, 1, 16) table_name ,
        tab.object_id object_id ,
        prt.index_id index_id ,
        SUBSTRING(alu.type_desc, 1, 12) alloc_type ,
        alu.data_space_id ,
        STR(alu.total_pages, 8, 0) tot_pages ,
        STR(alu.used_pages, 8, 0) used_pages ,
        STR(alu.data_pages, 8, 0) data_pages
FROM    sys.schemas sch
        INNER JOIN sys.tables tab ON tab.schema_id = sch.schema_id
        INNER JOIN sys.partitions prt ON prt.object_id = tab.object_id
        INNER JOIN sys.allocation_units alu
                                  ON alu.container_id = prt.partition_id
WHERE   sch.name = 'DBO'
ORDER BY tab.name ,
        prt.partition_id ,
        prt.index_id ,
        alu.allocation_unit_id
go
table_name       object_id   index_id    alloc_type   data_space_id tot_pages used_pages data_pages
---------------- ----------- ----------- ------------ ------------- --------- ---------- ----------
..
test_table        1525580473           0 IN_ROW_DATA              1       81        80         79
test_table        1525580473           2 IN_ROW_DATA              1       25        18         16
(4 rows affected)
1>

Let us now assess the block or page dump in sql server 2019..

To get for a DBCC command:

DECLARE @dbcc_stmt sysname;
SET @dbcc_stmt = 'IND';
DBCC HELP (@dbcc_stmt);
GO

>>> but i didnt get any result for either IND or PAGE commands.

DBCC commands to use:

DBCC IND( database, table, index_id )
DBCC PAGE ( database, file_id, block_id, level)

Both the below command can be run from sqlcmd:

DBCC IND(testdb,test_table,0);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE (testdb, 1, 4192, 3);
GO

but it is better to run the DBCC page command from SSMS to copy/paste the output easily.

Notice the following fields:

PAGE: (1:4192) <<<<<<<<< prints the fileid,pageid in the file

BUFFER Section <<<<<<<<<< A section which talks about the page in buffer

PAGE HEADER <<<<<<<<<<< The header which talks about page type, page free space info, Allocation Map (S/GAM), Allocation Unit etc.

Metadata: ObjectId = 1525580473 << the object to which this page belongs

m_slotCnt = 64 <<<< number of slots in the page (number of rows)

m_freeCnt = 32 <<<< free bytes

m_freeData = 8032 <<<< [124*64 + 96 bytes of header] [8032+32-8192 = 128, this is used for offset tracking]

Allocation status:
GAM (1:2) = ALLOCATED
SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL <<<<<<<<<<< notice the "Page Free Space (PFS)" says this block is 100% full
DIFF (1:6) = CHANGED <<<<<<<<< This block has changed since last db backup (Differntial Changed Map - DCM) "BACKUP DATABASE" command.
ML (1:7) = NOT MIN_LOGGED <<<<<<<<<< This block indicates if this page was impacted by bulk_logged operation (Bulk Changed Map - BCM).Here it is not.

Now the actual row data info starting with slot 0:
Slot 0 Offset 0x60 Length 124
Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 124   <<<<<<<<<<<<<< 124 bytes for each record                
Memory Dump @0x00000002BB1F8060
0000000000000000:   30000f00 01000000 55000000 05010005 000c0200  0.......U...........
0000000000000014:   18007c00 78787878 78787878 78787878 78787878  ..|.xxxxxxxxxxxxxxxx
0000000000000028:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
000000000000003C:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000050:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000064:   78787878 78787878 78787878 78787878 78787878  xxxxxxxxxxxxxxxxxxxx
0000000000000078:   78787878                                      xxxx   
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
id = 1                              
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4
random_data = 85                    
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0
update_date = [NULL]                
Slot 0 Column 4 Offset 0x0 Length 0 Length (physical) 0
vc_small = [NULL]            <<<<<<<<<<< notice this is null, we are going to update this value and see how the page looks later.       
Slot 0 Column 5 Offset 0x18 Length 100 Length (physical) 100
vc_padding = xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
... this repeats until slot 64.

>>>>>> There is no information at the tail part. Just that slot 63 finished dumping info.

Now let us look at the index page the same way:

DBCC IND(testdb,test_table,2);
GO
DBCC TRACEON (3604);
GO
DBCC PAGE (testdb, 1, 4192, 3);
GO

Command:

DBCC IND(testdb,test_table,2);
GO

Output:

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
      1         333   NULL        NULL  1525580473           2               1    72057594043367424 In-row data                10       NULL           0           0           0           0
      1        4200      1         333  1525580473           2               1    72057594043367424 In-row data                 2          0           1        4235           0           0
      1        4201      1         333  1525580473           2               1    72057594043367424 In-row data                 2          1           0           0           0           0
      1        4202      1         333  1525580473           2               1    72057594043367424 In-row data                 2          0           1        4272           1        4238

The blocks were ranging from 4200 to 4272, there were 18 used pages of which 16 are data pages.
We pick 4200 to dump the page info..

DBCC PAGE (testdb, 1, 4200, 3);
GO
Ex. Output:

FileId PageId Row Level random_data (key) HEAP RID (key) KeyHashValue Row Size
1 4200 0 0 0 0x6010000001001E00 (b490a6c8ceeb) 16
1 4200 1 0 0 0x6010000001002800 (2054aeabf8c9) 16
1 4200 2 0 0 0x6010000001003700 (10dc288b0cc5) 16
1 4200 3 0 0 0x6210000001000A00 (942fbc405eeb) 16

Notice the column header "random_data (key)" - the column on which we built the index.

Let us break this number:
0x6210000001000A00
62 10 00 00 01 00 0A 00

I can say "0A" is a slot identifier, but cant confirm on the page id yet!

I will check further on how this leaf node index entry finds its table page & slot.

Additional info:

Running the DBCC with level 2 trace will dump the information like table's.

DBCC PAGE (testdb, 1, 4200, 2);
GO

Ex. Output:

BUFFER:

BUF @0x000001058847FE40
...
PAGE HEADER:
..
m_pageId = (1:4200) <<< fileid:pageid
Metadata: IndexId = 2  <<<< non clustered index (in case of clustered index, it will be 1)
Metadata: ObjectId = 1525580473 <<< the table object id
m_slotCnt = 315 <<< number of leaf entries or index entries
m_freeCnt = 2426 <<< Free bytes
m_freeData = 5136 <<< 315*16 + 96 = 5136
                 
Allocation Status
GAM (1:2) = ALLOCATED
SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x40 ALLOCATED   0_PCT_FULL  <<< this block is still free
DIFF (1:6) = CHANGED <<< block changed since last backup
ML (1:7) = NOT MIN_LOGGED           
DATA:
Memory Dump @0x00000002BF5F8000
00000002BF5F8000:   01020000 00020001 00000000 00000d00 8b100000  ................‹...
00000002BF5F8014:   01003b01 b6000000 7a091014 68100000 01000000  ..;.¶...z ..h.......
00000002BF5F8028:   74000000 46040000 a4000000 00000000 00000000  t...F...¤...........

Hope the blog helps!

Thanks

MSSQL Learning Notes

My course notes on MSSQL..

Day 1:

1. sql server - rdbms

2. sql server install - instance

3. we can have many instances of sql server on the same machine

4. 1 instance of sql server - can have many dbs

5. when accessing multi instance windows server, mention the ip/instance name to connect to the sql server

6. System admin (super user on windows)

7. Database admin (super user on the db)

8. User roles (Users with permission to perform a specific task)

9. T-SQL is the language you interact with sql server

10. Management Studio is the GUI to be used for managing the SQL server

11. SQL CLI by default gets setup along with sql server

12. SQL server installation results in installing the necessary services

13. By default we get SQL Server Agent, SQL server (the actual sql server service), SQL Server Browser installed

14. You can use sql server configuration app to modify the sql server service and other configurations.

15. SSMS (SQL Server Management Studio) is used for connecting to the SQL Server

16. SSMS when started, it will ask you to mention the server type/server name/authentication info.

17. To connect to the sql server...

a. Server Type: Database Engine

b. Server name: host\<instance name>; if there is only one instance and is local server, then use the current hostname alone

c.  Authentication: windows authentication (which is local pc), sql server authentication (local to the server/instance), AD (across the dbs/server)

18. As soon as we connect using SSMS to the sql server, we have to goto the object explorer.

19. In the object explorer you can notice who your and which sql instance your connected to

20. In the security tab, enable sa account (enable from properties->status & set new password properties->password/confirm password)

21. Right click on the instance tab, properties enable sql server and windows authentication tab. This allows users to connect using sql server authentication as well.

22. SSMS allows restore of database using backups preserved before. Right click on the Database tab in the object explorer, restore database option and then select File as source and then click on ... (backup device) on the media and then click on add; you will see the various paths accessible for the backup file location. You can copy the backup over to the default location or you can choose the file from here and then clock restore.

23. Once the db is restoed, we should see the restored db accessible under databases object.

24. Creating a database.. Again right click on the database object and then click on create new database. The new database option will now show up with various options.. 

25. Click on the general option and then type the name of the db

26. We have 2 file groups created by default, 1 - row data where actual data gets stored and other is the log group which stores the transaction log for the database.

27. We can edit the file group location, initial size, incremental size and maxsize as per standard.

28. When we select records from table, the table will be prefixed with <dbname>.<schemname>.tablename

29. SSMS - TSQL looks like is autocommit as well.

30. The update, insert, select (except few format options) are resembling oracle and postgres syntax

31. View creation for now is done using GUI, need to check its TSQL syntax.

32. SQL Server Installation Center is the wizard for adding additional features and components to SQL Server installation existing.

33. To connect to the local SQL Server using SSMS. You can use the keyword localhost or . in the servername field like below..

localhost\MSSQLSERVER

.\MSSQLSERVER

34. Windows authentication mode is very secure and recommended mode of authentication in MSSQL server, since it has options to integerate multi factor, finger print etc.. and is much controlled than database authentication which only has usn/psw validations.

35. To stop user from login, just go over to security->logins->choose the candidate user->clear the checkbox for the login option.

36. If you dont know the instance name of the SQL Server - use none in sqlcmd. Just run the below command without any additional inputs.

sqlcmd

37. The system db purposes... [not used to store user data]

master - the db where all the system metrics are kept

model - the db which will be used in creating a new db (cloning)

msdb - the db used for task scheduling

tempdb - the db used for sorting and other operations.

38. If we dont mention the schemaname while table is getting created, the table will go to dbo schema; which is database owner schema.

Day 2:

39. tinyint use case - age

datatypes:

tinyint

smallint

int

bigint

decimal(p,s) - precision >total number of numbers to numeric(p,s) - same as above

display, scale number of numbers to the right

smallmoney - 200thousand (- to +)

money - 900trillion (- to +)

time  - 24hrs scale

date - jan 1 0001 to 31 dec 9999

datetime2 - both date and time togather

datetimeoffset - datetime2+timezone

char - 8000chars

varchar - same as above

nchar - 4000chars

nvarchar - same as above

varchar(max) - 2GB size

nvarchar(max) - 2GB size

40. To fix the ownership of the db:

SSMS method:

Goto DB>right click>goto files>set owner to the respective user found under login option (use search to find the owner if needed).

41. getdate is a function to get current date and time from system.

42. For setting default value for the table, use SSMS -> table design wizard > Set Default/Binding value

43. There can be only one clustered index in a table

44. Clustered index are sorted

45. Other indexes created are not clustered but they point the location in the clustered index.

46. Query Store is AWR in oracle, which helps provide us with query run stats including the plan :)

Day 3:

47. creating backup of a db:

Right click on candidate database > Tasks > Backup 

48. Restore the db:

Right click on candidate database > Tasks > Restore

49. Remember choosing the timeline to perform the restore will use the transaction log as well.

50. SQL Server Agent is the one used for scheduling the regular maintenance tasks in SQL Server like Backup, index rebuild etc...

51. Availability Group is always ON and ready to take up the primary role (It applies the read/write tx as it happened in primary using the TX logs) -- might be like the implementation of max protection or max avaialbility of oracle (usage of standby redolog, where the logs are shipped as they were created in primary).

52. Log Shipping - Warm Standby config, where you can add some delays; the TX logs are recovered as instructed (is like max performance or max availability mode of oracle - where we can configure the delay)

53. Fixed Server roles in MSSQL:

sysdba in oracle is sysadmin MSSQL

serveradmin role can be used to edit the configuration and shutdown of the server

security admin role is used to manage the other user accounts

dbcreator is to create/alter/drop/restore the db

public - default role all the users are assigned to

54. DB roles: is equivalent to SYSTEM privs in oracle

db_owner - perform any activity in the db

db_backupoperator - can backup the db

db_ddladmin - CREATE/ALTER any structure or relationship in the db (simillar to create and alter privilege of oracle)

db_datawritter - add/delete/modify data in the db (simillar to insert any/update any/delete any role)

db_datareader - allowed to read any table (select any role).

55. We can use the below query

to grant insert into a schema altogather for a user:

grant insert into schema :: humanresource to humanresource_reader;

56. to mask a column value (a user with alter table privilege) - we can use the below command:

Command Template:

use <dbname>

go

alter table <tablename>

alter column <columnname> add masked with (function = 'email()');

Example:

use landonhotel

go

alter table humanresource.employees

alter column email add masked with (function = 'email()');

57. to drop a column mask:

Command template:

use <dbname>

go

alter table <tablename>

alter column <columnname> drop masked;

Example:

use landonhotel

go

alter table humanresource.employees

alter column email drop masked;

58. To view encrypted column data in SSMS..

We should modify the always encrypted to enabled state in the connection window.

Enable always encrypted -> close the connection -> connect again with (option) always encrypted enabled -> then you can view plain text of encrypted data or you can disable the encryption.

59. You can remove the certificate used for encryption after it is disabled (if you have chosen user certificate during encryption enable) by going to "manage user certificates" > Personal > Certificates > Always encrypted auto certificate1 (right click and remove)

60. to transfer a table from one schema to other:

here from dbo to reference schema the books table is moved.

alter schema reference transfer dbo.books;

61. System DBs:

Master - the db which is used by the SQL Server Engine (contains db tables, views , procedures, function necessary for the sql server engine to function)

Model - the db which is used for creating new user dbs (we can customize this db if needed to create a new db)

msdb - the db which is used by sql server agent, which is used by the agent to schedule jobs, keep track of the jobs, backup information and other maintenance routine.

tempdb - the db which is used for all temp operation like sort, join, index rebuild etc. This db will be recreated everytime the db is restarted.

62. Performance Optimization operations on tempdb:

1. increase the initmb to 100MB

2. autoincrement size by 10%

63. Dynamic Management Views:

Permission to view server scopped DMVs:

view server state

Permission to view DB scopped DMVs:

view database state

64. Sample views are:

view to display the db file usage statistics of current db:

select *

from sys.dm_db_file_space_usage;

view to display connected sessions:

select *

from sys.dm_exec_connections;

view to display index usage stats:

select db_name(database_id) as dbname

,object_name(object_id) as objname

,*

from sys.dm_db_index_usage_stats;

65. To estimate checkdb duration:

dbcc checkfilegroup(0, noindex)

with physical_only,

estimateonly;

go

66. To perform actual check:

use <dbname>

go

dbcc checkdb;

The command checks if there any corruptions in the pages allocated. Reports a summary at the end of the validation. We have options to rebuild index (incase index is corrupted) and repair pages(meaning wiping off).

The checkdb command needs sufficient space in tempdb to perform the operation.

These are my course notes.

Thanks