PAGE Type in SQL Server

There are different types of pages in SQL Server. No matter what types of pages are, the layout of the page is the same. A data file consists of numbers of 8k-pages. 

• A Page includes 8192 bytes. First 96 bytes are used for header. 
• The rest of the space is for data. A variable length row offset array (or slot array) is located at the end of every page
• Count of records (size of array) is saved in the header.
• Records in a page are not sorted even though it is an index page. If the data needs to be sorted, the offset array will be is sorted by the key of the index.

There are about 14 types of pages in SQL Server data file.

• Type 1 – Data page.
• Data records in heap
• Clustered index leaf-level
• Location can be random
• Type 2 – Index page
• Non-clustered index
• Non-leave-level clustered index
• Location can be random
• Type 3 – Text Mixed Page
• Small LOB value(s), multiple types and rows.
• Location can be random
• Type 4 – Text Page
• LOB value from a single column value
• Location can be random
• Type 7 – Sort Page
• Temporary page for sort operation.
• Usually tempdb, but can be in user database for online operations.
• Location can be random
• Type 8 – GAM Page
• Global Allocation Map, track allocation of extents.
• One bit for each extent, if the bit is 1, means the extent is free, otherwise means the extent is allocated (not necessary full).
• The first GAM page in each file is page 2
• Type 9 – SGAM Page
• Shared Global Allocation Map, track allocation of shared extents
• One bit for each extent, if the bit is 1, means the extent is allocated but has free space, otherwise means the extent is full
• The first SGAM page in each file is page 3
• Type 10 – IAM Page
• Index Allocation Map. Extent allocation in a GAM interval for an index or heap table.
• Location can be random.
• Type 11 – PFS Page
• Page Free Space. Byte map, keeps track of free space of pages
• The first PFS is page 1 in each file.
• Type 13 – Boot Page
• Information about the page
• Only page 9 in file 1.
• Type 14 – Server Configuration Page (It may not be the official name)
• Part of information returned from sp_configure.
• It only exists in master database, file 1, page 10

• Some Added Type in SQL Server 2008
  Type 15 – File Header Page
• Information about the file.
• It’s always page 0 every data page.
• Type 16 – Differential Changed map
• Extents in GAM interval have changed since last full or differential backup
• The first Differential Changed Page is page 6 in each file
• Type 17 – Bulk Change Map
• Extents in GAM interval modified by bulk operations since last backup
• The first Bulk Change Map page is page 7 in each file.

When you know all about your code u start loving it.

Post Reference: Vikram Aristocratic Elfin Share, http://technet.microsoft.com/

Retrieving Database Page Metadata

You can use DBCC PAGE command to retrieve meta-data about a database page. Before executing DBCC PAGE you need to execute DBCC TRACEON (3604) in order to return the output to the screen.

It is useful to look into the database pages.

Syntax for using DBCC PAGE is

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

The printopt parameter has the following meanings: (each option provides differing levels of information)

• 0 - print just the page header
• 1 - page header plus per-row hex dumps and a dump of the page slot
• 2 - page header plus whole page hex dump
• 3 - page header plus detailed per-row interpretation

Now let’s start with the Page Detail of a database table object.

CREATE TABLE EmployeeTable

(

emp_id INT IDENTITY(1,1),

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

hired_date DATETIME NOT NULL,

active BIT NOT NULL DEFAULT 1,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY (emp_id),

)

Go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Pragyan', 'Mohanty', '1999-03-15'

go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Swagatika', 'Patnaik', '2001-07-05'

go

insert into EmployeeTable (first_name,last_name,hired_date)

select 'Sagarika', 'Das', '2002-11-10'

go

Now to check the Page detail for this newly created table we need to find the file Id and the Page Number, which we can get by using DBCC IND command.

 DBCC IND ( {dbname}, {table_name},{index_id} )

Where index_id, we can get from querying sys.indexs, -1 shows all indexes

DBCC IND ( 'testDemo', 'EmployeeTable' , -1 )

PageFID PagePID IAMFID IAMPID ObjectID    IndexID iam_chain_type  PageType

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

1       1725    NULL   NULL   290100074   1       In-row data     10      

1       1724    1      1725   290100074   1       In-row data     1       

(2 row(s) affected)

Note: Page Type 1 means Data Page and Type 10 means Index Allocation Map

So from here we have got File Id = 1 and Page Id = 1724

Now we can DBCC PAGE Command to see the detail of Page and Storage information.

Before we can run DBCC PAGE, it's required that trace flag 3604 be set to instruct the engine to send output to the console; otherwise you won't see anything!

DBCC TRACEON(3604)

DBCC PAGE ('testDemo', 1, 1724, 1);

You can use DBCC PAGE ('testDemo', 1, 1724, 1) WITH TABLERESULTS; to get your result in table format.

By executing the DBCC PAGE you will get the result of Page Header, Data and OFFSET Table / Array.

Reference: http://technet.microsoft.com/

Enjoy code like you gotta dance when there's nobody watching ;)

Post Reference: Vikram Aristocratic Elfin Share & http://technet.microsoft.com/

PAGE and EXTENTS in SQL Server

Page and Extents are logical division of data storage in SQL Server. The data file (.mdf, .ndf) are logically divided into Pages numbering from 0…n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.

PAGE: The page size is 8 KB. the page types used in the data files of a SQL Server database.

Page has three important sections:

• Page header
• Actual data i.e. Data row
• Row pointers or Row offset 

There are different types of Pages in SQL Server database.

Data	Data rows with all data except text, ntext, and image data.
Index	Index entries.
Text/Image	Text, ntext, and image data.
Global Allocation Map, Secondary Global Allocation Map	Information about allocated extents.
Page Free Space	Information about free space available on pages.
Index Allocation Map	Information about extents used by a table or index.
Bulk Changed Map	Information about extents modified by bulk operations since the last BACKUP LOG statement.
Differential Changed Map	Information about extents that have changed since the last BACKUP DATABASE statement.

Data pages contain all the data in data rows except text, ntext, andimage data, which is stored in separate pages. Data rows are placed serially on the page starting immediately after the header. A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page and each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

The maximum amount of data contained in a single row is 8060 bytes, not including text, ntext, and image data.

Data rows are put on the page serially, starting immediately after the header.

OFFSET TABLE: A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

EXTENTS: Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB.

To make its space allocation efficient, SQL Server 2000 does not allocate entire extents to tables with small amounts of data. SQL Server 2000 has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects.

NOTE: Log files do not contain pages; they contain a series of log records.

Reference: http://technet.microsoft.com/en-us/library/aa174529(v=sql.80).aspx

Never fail to spot a dreadful piece code; it will offer idea of superlative choice to implement the same.

Post Reference: Vikram Aristocratic Elfin Share

Pause your execution of block till a particular time using WAITFOR TIME

If you want your part of code to execute after a particular time then you can achieve this with the help of SQL Server WAITFOR TIME clause here you have mention the time along with the WAITFOR.

Let’s take a short example to see how we can achieve this. THRIST

select GETDATE() as 'Time'

WAITFOR TIME '05:40:00'

select GETDATE() as 'Delayed Time'

SELECT 'Hi its Morning'

Time

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

2013-10-10 05:39:56.440

(1 row(s) affected)

Delayed Time

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

2013-10-10 05:40:00.000

(1 row(s) affected)

Hi its Morning

Here we can see how we are able to achieve the delay of our batch using WaitFor Time.

Never miss a good chance to do good piece code.

Post Reference: Vikram Aristocratic Elfin Share

Pause your execution of block or delay execution using WAITFOR DELAY

There are situation when you want your piece of code to wait for some input after it proceed for next group of statement, if the scenario is like this you can achieve your wait using WAITFOR. So SQL Server has an option to wait for delay your batch until some short of input arrived.

Let’s take a short example to see how we can achieve this.

select GETDATE() as 'First Time'

waitfor delay '00:00:03'

select GETDATE() as '3 Second delay'

waitfor delay '00:00:02'

select GETDATE() as '2 Second delay'

waitfor delay '00:00:01'

select GETDATE() as '1 Second delay'

First Time

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

2013-10-10 05:24:39.007

(1 row(s) affected)

3 Second delay

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

2013-10-10 05:24:42.007

(1 row(s) affected)

2 Second delay

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

2013-10-10 05:24:44.007

(1 row(s) affected)

1 Second delay

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

2013-10-10 05:24:45.007

(1 row(s) affected)

Here we can see how we are able to achieve the delay of our batch using WaitFor Delay.

The way SQL developer says Database is poles apart. You database is safe as tongue in your mouth.

Post Reference: Vikram Aristocratic Elfin Share

Separating First Name and Last Name from Name

There are bargains when user want to separate first and second name from Name field generally I have seen this for creating email address for in-house projects.

So if your requirement is to separate name to first name and last name then you can easily implement it with the help of charIndex and substring function of SQL.

Let’s do a practical for the same

create table #demoSeprateName

(name varchar(20),

first_name varchar(10),

last_name varchar(10))

insert into #demoSeprateName(name) values('Neha Sharma')

insert into #demoSeprateName(name) values('Richa Sharma')

select substring(name,1,charindex(' ',name)) as 'First Name',

substring(name,charindex(' ',name) + 1,len(name)) as 'Last Name'

from #demoSeprateName

First Name           Last Name

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

Neha                 Sharma

Richa                Sharma

(2 row(s) affected)

So here we have used charIndex function, it will give the position of the specified character, and substring function with which give the subsring of a given string starting from position mention to length specified.  

Let’s update the table with first_name and last_name value

update #demoSeprateName

set first_name = substring(name,1,charindex(' ',name)),

last_name = substring(name,charindex(' ',name) + 1,len(name))

name                 first_name last_name

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

Neha Sharma          Neha       Sharma

Richa Sharma         Richa      Sharma

(2 row(s) affected)

Now if you want to generate email address in the format of firstName.LastName@abc.com you can do this

select ltrim(rtrim(substring(name,1,charindex(' ',name))))

+ '.' +

ltrim(rtrim(substring(name,charindex(' ',name) + 1,len(name))))

+ '@xyz.com'

as 'Email Address'

from #demoSeprateName

Email Address

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

Neha.Sharma@xyz.com

Richa.Sharma@xyz.com

Be serious about Coding, if you want to have any amusement in coding.

Post Reference: Vikram Aristocratic Elfin Share

Group by with Union

There is always a question for fresh SQL Engineers, how do I add group by with Union statement, doing this throw error.

It is quite possible and very easy to implement. Lets take an example to check this

Here we are creating two table TableA and TableB with two fileld id and name, then we will do the union with group by

create table TableA

(id int,

name varchar(15))

Command(s) completed successfully.

create table TableB

(id int,

name varchar(15))

Command(s) completed successfully.

Inserting few rows into TableA

insert into TableA values(1,'Prachi')

insert into TableA values(2,'Shilpa')

insert into TableA values(3,'Bhagyashree')

  

Inserting few rows into TableB

insert into TableB values(1,'Pragyan')

insert into TableB values(3,'Subankeri')

insert into TableB values(4,'Neha')

Now I want to do union of these two tables and group the result by ID

select id,name from TableA

union all

select id,name from TableB

group by id

Msg 8120, Level 16, State 1, Line 3

Column 'TableB.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Let see what an alternative is

select * from

(select id,name from TableA

union all

select id,name from TableB)as FinalTable(id,name)

group by id,name

Conclusion:  we can use group by after putting the union query result in a select statement thereafter doing group by.

Programmer born alone as a warrior, live alone as a fighter  and die alone as a calm, sometime in the course of his programming life he create a false impression for the  moment that he is not unaided, he too has girl friend to love beyond code.  

Post Reference: Vikram Aristocratic Elfin Share

Identify the Key Index Maintenance Tasks

There are many which can be performed as a Maintenance task for Index Maintenance, but here we will see some high end task at least which should be included in your Index Maintenance task.

1. Index Fragmentation, Identify and remove index fragmentation
2. Outdated Index, Identify skewed and outdated index and column statistics and ensure they are representative and up-to-date
3. Missing Indexes, Identify and create missing indexes
4. Remove Unused Indexes, Identify and remove unused indexes
5. Index Maintenance Job, Creating and monitoring index maintenance jobs

Let’s take a brief look at each one of these, one at a time.

Remove Index Fragmentation

The easiest way to remove index fragmentation is to defragment all of your database’s indexes every day. If you have a small database, and you have defined maintenance times, this might be a viable option. On the other hand, many databases are relatively large and must be available 24x7 throughout the day. In these cases, it is often not practical to defragment every index every day. Instead, you want to have the ability to identify only those indexes that really need to be defragmented, and focus on those.

Skewed and Outdated Index and Column Statistics and Ensure they are and Up to Date

Index statistics are used by the query optimizer to help in determine when and which index should be used hen executing a query. If these statistics are stale, or out-of-date, then the query optimizer might not make the best choice when selecting the indexes used to perform the execution of query.

Identify and Create Missing Indexes

User can make use Index tuning wizard to know the missing Indexes. Else he can look out each query to analyze the execution plan to find the missing Index statistics, once you have indentified the missing index, your task involve in creating those indexes.  Take the advantage of tool that can help you identify many missing indexes with minimal effort? While I refer to this process as automation, it is not really 100% automated. Some of the steps of this maintenance task still should be done by hand, and DBAs also need to make a judgment about the indexes the Database Engine Tuning Wizard recommends before you blindly let it create new indexes for your databases.

Identify and Remove Unused Indexes

What’s the point of using up resources modifying indexes that never will be used? Unused indexes should be removed from your databases. SQL Server 2005 and SQL Server 2008 make it easy to identify unused indexes in a database using DMVs. Essentially, these DMVs track every time an index is used, which means you can easily determine which indexes can be safely removed. While in theory you could entirely automate this maintenance task, I prefer to do it manually because there are often some circumstances that automation can’t easily take into account.

Creating and Monitoring Index Maintenance Jobs

This is the step where you create the scripts necessary to perform automated index maintenance, and then schedule them with the SQL Server agent. In addition, you will want configure your SQL Server to notify you if any of these jobs fail, so you can ensure that index maintenance is performed as you expect.

In coming articles we will pick each Index Maintenance task and go deeper to each Indexes problem n there by resolving the issues concern with Indexes.

Why coders take pleasure in stupidity, because they are highly intelligent alien race 

Post Reference: Vikram Aristocratic Elfin Share

If I drop a Clustered Index, what will go off, and what will happen to Non Clustered Indexes?

If you drop the cluster index on the table, the table data will not gets deleted instead the offset table of the cluster index for maintaining the order of table data will gets removed from page, however since indexes use the clustering key to look up the corresponding row of data, so when a clustered index is dropped, the nonclustered indexes must be modified to use another method to lookup the corresponding data row because the clustering key no longer exists.

No for non cluster index the only way to jump directly to a record in the table without a clustered index is to use its physical location in the database (i.e., a particular record number on a particular data page in a particular data file, known as a row identifier—RID), and this physical location must be included in the nonclustered indexes now that the table is no longer clustered. So when a clustered index is dropped, all the nonclustered indexes must be rebuilt to use RIDs to look up the corresponding row within the heap.

Enjoy madness, if you are doing code, upshot will be chill

Post Reference: Vikram Aristocratic Elfin Share