Internal 1 – Introduction

Internal 1 – Introduction

Continue on internal post.

Sql server is an application/ software where it runs on system software as well as hardware i.e. Operating system and recourses, Sql server versions should be compatible with Operating system version and resources.

http://msdn.microsoft.com/en-us/library/ms143506(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms143506.aspx

ok, we are not going to discuss more about minimum requirement and compatibility.

Sql server /software  requires system databases to be up and running to make system run. System databases are as follows:

MSResourcedatabase (from 2005 onwards)

Master

Msdb (Microsoft database)

Model

Tempdb

http://msdn.microsoft.com/en-us/library/ms178028.aspx

On the disk where everything is stored, the structure of disk has tracks and sectors. Everything on the disk stores in sector of 512bytes or its own configuration.

But Sql server has its own way of storage. In sql server everything is stored in a chunk of 8k called “PAGES”. And 8 group of pages are called “Extent”. When we work on sql server it knows block of 8k only and it works with 8k blocks whenever we have to access any page it takes 8k of data into memory(quick access) everything works on memory. So sql server creates 8k of block and work on it. that way sql server makes its own way of working as 8k block irrespective how operating system stores the data sql server pass or retrieve the data in 8k* blocks.

*There is some exception on 8k block – for large pages , memtoleave and Log LSN.

There are more about SQLOS or how operating system manages disk – read write.

This series of blogs I will be discussing about database pages on each database data files.

Every database has following pages on their primary data file( file =1)

  • Page 0: header page 
  • Page 1: PFS page
  • Page 2: GAM page
  • Page 3: SGAM page
  • Page 6: DIFF page
  • Page 7: ML page
  • Page 9 :Boot Page

 

To get the detailed information about the page we can use the undocumented dbcc command as

DBCC PAGE:

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

Following link shows detailed information about dbcc page and its options.

http://support.microsoft.com/kb/83065

http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

and the option m_type from dbcc page PAGE HEADER shows what type of page it is.

  • m_type
    • This is the page type. The values you’re likely to see are:

1 – data page.

 2 – index page (or Version Stored Page)

3 – text mix page.

4 – text tree page

6 – work file for hash join in Tempdb

7 – sort page. –

8 – GAM page

9 – SGAM page.

10 – IAM page.

11 – PFS page.

13 – boot page.  

15 – file header page.  

16 – diff map page.  

17 – ML map page.  

Page is categorized into three parts

BUFFER:

Memory information about that page

PAGE HEADER:

Header information

Offset Slot

Record pointer

Allocation unit

Advertisements
This entry was posted in Internal and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s