View the interinternal Data Page's small plug-in internals Viewer

View the interinternal Data Page's small plug-in internals Viewer

I think the name of internals viewer is very good. Check it internally.

This little tool was reproduced by a man in the garden.ArticleYes

Article address:
Heap table and row overflow in SQL server2008 Storage Structure

Http://www.cnblogs.com/trams/archive/2010/09/11/1823727.html

I have studied this tool over the past few days and found it useful. It is especially useful for those who want to study SQL in depth or learn SQL server but want to have a better understanding of SQL.

First, this is a project on codeplex:

Http://internalsviewer.codeplex.com/

I also uploaded my network disk, you can also go to my Network Disk download: http://www.kuaipan.cn/file/id_4401224786924003.htm

Internalsviewerinstaller. MSI installation will not be mentioned. Double-click the installation directly. It is best to disable SSMs before installation.

Officially said: it can be used in sql2005 sql2008, and my own computer is sql2005 windows7.

 

 

After downloading and installing SSMs, open SSMs and you will see an added SSMs menu

 

Click internals viewer and there are two menus:Display transaction logAndAllocation Map

I do not know why the display transaction log cannot be used.Display transaction logWhat is the function?

You have to click "Allocation map". The connection dialog box will pop up after you click it. You can select the authentication method to connect.

After opening, you will see

Buttons above

Select the database to view

PFS: view the idle page of the database

Buffer Pool: Check

Small: adjust the size of small squares

Key: whether to display the database table. If you do not click the key, the table is not displayed.

File details: see the figure above.

Color meaning of small squares

Click a small square to display the information of the small square. Each small square represents a page.

The color of the small square corresponds to the color of the following table, indicating the distribution of the table data in the database.

 

Move the cursor over the small lattice to see which table the small square belongs.

Click a small square with the left mouse button to display the page information.

If you want to view the data of a table, you can click the table to display the data distribution of the table. You can click a small square to view the data of the table ~

Let's start with the first few pages of the database.

 

Database page 1st: File Header page

Database page 2nd: PFS(Page free space) is also called page free space. This page is used to track the utilization of each specific page in a file.

Database page 1: gamThe global allocation map (GAM) page records the areas that have been allocated and used for what purpose.

Database page 4th: SGAMShared global allocation map (SGAM) page records the areas currently used as hybrid areas, and these areas must contain at least one unused page

Page 5th has no data

Page 6th has no data

Database page 1: DCMThe differential changed map (DCM) page tracks the region in a file that has been modified after the latest full database backup. Sqlserver
During Incremental backup, only the partitions with data changes can be incrementally backed up.

Database page 8th: BCMBulk changed map page, when a partition in the file is in the minimum volume or batch log operation is
Used in use.

Database page 1: SYS. sysqnames exists in each database.

4 bytesEach namespace or qualified name marked by ID has a corresponding row.

Information like the DBCC checkprimaryfile command should be stored on this page

Database page 1: boot page the database is started based on the information on this page.

 

Iam page

The following is an error. It should be in a heap table.

 

Page Information

Each small square represents a page. The information on the left is estimated to use these two statements:DBCC Ind () DBCC page ()

 

 

The corresponding table data is shown below

 

Whether this page is a data page or an Iam page is displayed in the upper left corner.

Structure of data rows

For the structure of data rows, you can refer to

Article address: heap table and row overflow in SQL server2008 Storage Structure

Http://www.cnblogs.com/trams/archive/2010/09/11/1823727.html

I excerpted part

Status A is described as follows:
 
Bit0: version information, always 0 in SQL Server 2005/08
 
Bit1-3: 0 = (primary record); 1 = (forwarded record); 2 = (forwarding stud); 3 = (index record); 4 = (overflow data ); 5 = (ghost index records); 6 = (ghost data records)
 
Bit4: indicates that a null bitmap exists (sql2005/08 always has a null bitmap in the Data row)
 
Bit5: indicates that a variable-length Column exists.
 
Bit6: Disabled
 
Bit7: indicates a ghost record exists.
 
In this example, 30-> 00110000 is a row-based bitmap from high-position to position (bit0 is the first on the right). If bit4 is 1, a variable-length column field exists, because there are always null bitmaps in sqlserver2005/2008, bit5 is also 1.
 
Status B is not enabled in sqlserver2005 // 2008, so it is 00

Differences between Iam pages and Data Pages

Heap tables only rely on the iam page (index allocation ing page) in the table to link the heap page. Iam records the page number and location.

The red small square records the distribution of the data pages in the database in the table SYS. syscolpars.

About this software

I clicked on the small square at the bottom of the iam page and an error was reported. Then, a dialog box is displayed, which is used by the software.. NetTo write

When can we write one?

 

Bytes ----------------------------------------------------------------------------------------------------

The function and detailed address of each system page are attached.

PFS page introduction: http://dev.21tx.com/2011/11/24/11378.html
Dcm bcm page introduction: http://tech.it168.com/a2010/0921/1106/000001106857.shtml
Gam SGAM page introduction: http://www.efficient-it.com.cn/space/78/viewspace/itemid/6756.html