View widget for SQL Server Internal Data page Internals Viewer

In the original: View SQL Server Internal Data page Widget Internals Viewer

View widget for SQL Server Internal Data page Internals Viewer

It feels like the internals viewer is a very good name to see inside.

This gadget is the one I saw in the garden before the article was reproduced.

Article Address:
Heap table, row overflow of SQL Server2008 storage structure

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

I've been working on this gadget for a few days, and it's very useful to have friends who want to delve deeper into or just learn SQL Server but want to have a better understanding of it.

First, this is an item on the CodePlex:

http://internalsviewer.codeplex.com/

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

Internalsviewerinstaller.msi installation will not say, directly double-click the installation, it is best to close the SSMs before installation

Officially said: Can be used in SQL2005 SQL2008, my own computer is SQL2005 Windows7 can use

After the download is installed, then open SSMs and you will see the SSMs menu add a

Clicking Internals Viewer will have two menus:display transaction log and Allocation Map

I do not know why display transaction log can not be used, I very much want to know what display transaction log is the function

Had to click "Allocation Map", click will pop up the connection dialog box, we choose to verify the way to connect can not say more

When you open it, you'll see

Several buttons above

Select the database you want to view

PFS: View the free pages of a database

Buffer pool: see

Small: Resizing a small square

Key: Whether to display the table of the database, if you do not click on the key will not show the table

File Details: Look at the chart above

The meaning of the small square color

Clicking on a small square will display the information of that small square, each of which represents a page

The color of the small squares corresponds to the color of the table below, indicating the distribution of these tabular data in the database. One by one

Moving the mouse over a small square will see which table this small square belongs to.

The left mouse button click on a small square will show the information of this page

If you want to see the data of a particular table, you can click on that table, and then it will show the data distribution of that table, and you can click on a small square to see the data of that table ~

Let's start with a few pages from the database.

Database page 1th: File Header page

Database page 2nd: PFS (Page free space), also known as freedom of the page, which is used to track the utilization of each specific page in a file

Database page 3rd: GAM Global Allocation Map,gam page these pages record which areas have been allocated and for what purpose

Database page 4th: SGAM Shared Global Allocation map (Allocation Map,sgam) page These pages record which extents are currently used as mixed-type zones, and those areas must contain at least one unused page

5th page No data

6th page no data

Database page 7th: DCM Variance Change (differential Changed MAP,DCM) page He tracks which area of a file has been modified since the latest full database backup. Sql server
Use incremental backups only to make incremental backups of partitions that have changed data

Data:DCM, BCM for SQL Server 2008 storage Structure

Database page 8th: BCM Bulk Change mapping (Bulk Changed map) page, where a section of the file is in the minimum volume or bulk log operation
Use when using.

Database page 9th: Sys.sysqnames exists in each database.

4 bytes Each namespace or qualified name of the ID tag has a corresponding row.

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

Database page 10th: Boot page database started based on the information on this page

Iam page

It's wrong here, it should be a pile table.

Specific page information

Each small square represents a page, the left side of the information is estimated to use these two statements:DBCC IND () DBCC page ()

The corresponding table data is displayed below

The upper-left corner shows whether this page is a data page or an IAM page

Structure of the data row

About the structure of the data row, you can see the article at the beginning of the

Article address: SQL Server2008 storage structure heap table, row overflow

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

I'm part of the excerpt

Where status A is described below:

BIT0: Version information in SQL Server 2005/08 is always 0

Bit1-3:0= (primary record), 1= (forwarded record), 2= (forwarding stud), 3= (index record), 4 = (overflow data); 5= (ghost index record); 6 = ( Ghost data Record)

BIT4: Indicates a null bitmap exists (there is always a null bitmap in the data row sql2005/08)

BIT5: Indicates a variable-length column exists

BIT6: Not enabled

BIT7: Indicates presence of ghost Records

In this example 30->00110000 it is a row property of the bitmap from the high to the low (the first bit on the right is bit0), BIT4 is 1, which is the field with the variable length column, because there is always a null bitmap in sqlserver2005/2008, so Bit5 is also 1.

Status bit B is not enabled in sqlserver2005//2008, so it is 00

Differences between IAM pages and data pages

Heap tables rely only on the table's IAM pages (Index allocation mapping page) to link the pages of the heap together, and IAM records the page number, page position

The red squares are the records of the Sys.syscolpars. The distribution of his data pages in the database

About this software

I clicked on the small square below the IAM page to give an error, and then popped a dialog box that was originally written using . NET.

When can our countrymen write one?

----------------------------------------------------------------------------------------------------

Finally, the function and detailed address of each system page are attached.

PFS Page Description: http://dev.21tx.com/2011/11/24/11378.html
DCM BCM page Description: http://tech.it168.com/a2010/0921/1106/000001106857.shtml
GAM Sgam page Description: http://www.efficient-it.com.cn/space/78/viewspace/itemid/6756.html

View widget for SQL Server Internal Data page Internals Viewer