SQL Server Solutions with Practical SQL DBA

SQL Server : Index at a glance

2013-04-16T12:17:00.001-07:00

In the last ten posts, we have discussed in details about the SQL server index and its storage structure. In this post I will consolidate all the ten post for easy reference.

What is index ?
In the first post, we have discussed about index in general. We tried to understand the clustered index non clustered index and key lookup with a sample from real life. Read More

What is heap table?
A table with out clustered index is called heap table. Data in heap table are not stored in any specific order.To understand more about the heap structure Read More

B tree structure of Clustered index

A table with clustered index is called Clustered table.Clustered index stores the actual data in the order of clustering key using a b tree structure.A table can have only one clustered index. To understand more about clustered index table storage and access pattern, read this article

B tree structure of a non clustered index on clustered table

We can have many non clustered index on a table.To understand about the non clustered index storage structure and the way SQL server use the non clustered index, read here

B tree structure of a non clustered index on heap table

How non clustered index works on a heap table,Read More

Design Consideration of clustered index key

What you should consider while deciding clustered index key. What will happen if you defined a clustered index on a non unique column ? Read the article Design Consideration of Clustered index.

Overhead on non clustered index on a non unique column

You ever thought about difference in storage structure non clustered index while defining on non unique column. This post will give you more insight.

How to get rid of key lookup operation ?

Book mark look up or Key Look up operation is very common in execution plan. How to get rid of that . Learn about covering index and included column in this post

Filtered index with its limitation

How to define index on part of the table data ? How to define complex unique constraint on a table. How to improve performance of a query which work on small chunk of table data. Filtered index is the new way to solve the issue. Read more

Real time sample of the importance of the index key column order

The column order in the index is very important. It define the way data stored.The data access patter will play a major role in deciding the key column order. Shared some thoughts on this article

If you liked this post, do like my page on FaceBook

SQL Server : Part 10: Importance of Key Column Position While Creating Index

2013-04-16T10:33:00.000-07:00

Now we have discussed about different types of indexes in the last posts. In this post, let us discuss about the key column order (order of the column of indexes). The order of the key column of the index is decided based on the data access pattern and how do you want to organize the data.

The general guidelines for the order of the index key column is to keep the most selective column as the first column.It does not meant that, a unique id column should be first column of all your index. The optimizer will decide to use the index based on the statistics available on the index. I will explain about the statistics in later post. Statistics gives the information about the density of the key column which give uniqueness of index, and histogram that stores the information about the distribution of the values within the column.

Let us consider an example of customer table which stores the information of customers from across the countries.The application running on top of this table deal with customers from a specific country based on the user permission/access location.

CREATE customer (

   Customer_id     INT IDENTITY(1,1) NOT NULL
   CountryCode     CHAR(3) NOT NULL,
   FirstName       VARCHAR(100) NOT NULL,
   LastName        VARCHAR(100) NOT NULL,
   MobilePhone     VARCHAR(20),
   Email           VARCHAR(100)
   
)

GO

CREATE UNIQUE CLUSTERED INDEX Ix_Customerid_Countrycode ON customer(Customer_id,Countrycode)

The clustered index is created based on the general guideline to keep the most selective column on the left side.If I need fetch a single records based on the customer_id, this index will work perfectly.So what is the drawback of this index ? In case if I need to fetch all/many customers based on the countrycode , the optimizer opt for clustered index scan.

SET STATISTICS IO ON
go

SELECT * FROM customer WHERE Countrycode='VNH' AND customer_id=1216468

Let us try to fetch all customers with countrycode VNH. The table has around 620 thousand records and there are 3066 customers with VNH countrycode

SELECT * FROM customer WHERE Countrycode='VNH'

From the execution plan, it is clear that, optimizer has opted for clustered index scan by scanning all 6825 pages used to store this table. We can optimize this by changing the index with countrycode as the first column.

DROP INDEX customer.Ix_CustomerId_CountryCode
GO
CREATE UNIQUE CLUSTERED INDEX Ix_CountryCode_CustomerId ON customer(Countrycode,Customer_id)

SET STATISTICS IO ON
go

SELECT * FROM customer WHERE Countrycode='VNH' AND customer_id=1216468

Let us try to fetch all customers with countrycode VNH.






SELECT * FROM customer WHERE Countrycode='VNH'

From the execution plan, it is clear that , the optimizer used index seek in both the cases and IO operation has reduced drastically in the scenario while fetching all the customers with VNH countrycode.

Other problem will be, while keeping the customer_id as the first column , data will be stored in the order of customer_id column and you will have many pages (almost all pages) will have data belongs to multiple countrycode. This may lead to more blocking/deadlock issues.By defining the index with countrycode as the first column, only a few pages will have data overlapped with multiple countrycode and will help to reduce the blocking issues.The important point is, by defining the index with countrycode as the first column will cause for higher level of index fragmentation, but that can be controlled by defining proper fill factor value. I have experienced this in one of our project and experienced lot of improvement after changing the indexes with countrycode as the first column.

In short the general guidelines about the key column is a good starting point but at the same time you have to consider the data access pattern in your application.Hope this will help you to resolve some issue that you are facing.

If you liked this post, do like my page on FaceBook

SQL Server : Part 9 : Filtered Index : A new way for Performance Improvemnt

2013-04-08T11:57:00.001-07:00

Filtered index is a new feature introduced in SQL Server 2008. All the indexes that we have discussed till now were always on the entire table. In other words, Index and table will have same number of records.With filtered index, it is possible to create an index for a subset of the table.This is achieved by adding a where clause in the index creation statement.This helps to reduce the size of index in terms storage as well as depth of the index.The where condition specified in the index creation statement determine the existence of a record in the index.

This will be a great performance on larger table where a good chunk of queries work on smaller portion of the table.A regular index will be on entire table ignoring the fact that most of the queries are interested in a smaller portion of the index.This makes the index deeper and need more pages to store the index b tree structure which results in more IO. Where as filtered index will be created only for a portion of the table and hence less pages are required to store the index.

Let us consider an example of salesorder table which contain data for last five years. Major part of active queries on this table are based on the last calender year and the current calender year.A simple example of filtered index will be like this.

CREATE NONCLUSTERED INDEX ix_salesorder_Filter
ON salesorder(SalesOrderId,OrderDate,Status,Customer_id,TotalDue)

WHERE OrderDate>'2012-01-01'

Unique column with multiple null values: One of the significant use of filtered index is to define complex unique constraint on a column. The unique constraint will not allow you to have multiple null values in a unique column. How do you enforce uniqueness on column except for NULL values ? For example , Productcode column in the product table can be null but should be unique if the value is defined on that column. Let us see the below example.

CREATE TABLE Product
(
  Productid INT NOT NULL PRIMARY KEY,
  ProductCode     CHAR(10) ,
ProductName     VARCHAR(100)

)
GO
CREATE UNIQUE INDEX ix_Unique_Filtered ON Product(Productcode) WHERE productcode IS NOT NULL
GO
INSERT INTO Product VALUES(1,'AR-5381','Adjustable Race')
INSERT INTO Product VALUES(2,NULL,'Bearing Ball')
INSERT INTO Product VALUES(3,NULL,'BB Ball Bearing')
INSERT INTO Product VALUES(4,'AR-5381','Adjustable Race-Small')

We are able to insert multiple record with NULL value in the productcode column, but when we are trying to insert a record with duplicate value 'AR-5381', it is not allowing to insert duplicate value into the

Productcode column. The unique filtered index defined on this table help us to enforce this kind of uniqueness.

Read operation: The above said is one of the common usage of filtered index.Another usage of filtered index is to support the queries. Let us see a sample below.

USE mydb
GO
SELECT * INTO SalesOrderheader FROM AdventureWorks2008.Sales.SalesOrderheader
GO
--Unique Clustered index
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderheader ON SalesOrderheader(SalesOrderid)
GO
--Filtered Inde
CREATE INDEX ix_filtered_index ON SalesOrderheader(orderdate) WHERE orderdate>'2008-01-01'

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE
orderdate>'2008-05-01'
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE
orderdate='2008-03-01'
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE
orderdate='2007-12-01'

An index is defined on the column orderdate for the records which are ordered after 2008-01-01. There are three select query with different condition on the orderdate. The first one is trying to fetch a range of records which comes under the filtered criteria (sub set of filtered result). Second query try to select records which ordered on specific date which comes under the filter criteria.The third one also try to fetch the orders placed on a specific date, but that date is not come under the filtered range.

The first two queries are made use of the filtered index and the third one gone for clustered index scan. This is because the third query can not be served from filtered index as the predicate of the query is not comes under the filter criteria used in the index creation statement.In short , the records needed to perform the third select operation is not available in the filtered index.The filtered index gives lots of performance improvement for this type of queries especially when the number of records filtered out are much more than the number of records satisfying the filtering criteria.

It is not mandatory that filtering column should be part of the index but in that case predicate of the select statement should exactly match withe filtered index predicate. Let us create an index on orderdate by filtering the records with territoryid<5

CREATE INDEX ix_TerritoryID_Filter ON SalesOrderheader (OrderDate) WHERE TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE
TerritoryID<=5
GO
SELECT salesorderid,orderdate FROM SalesOrderheader WHERE
TerritoryID=4

Let us see the execution plan of the two select statement.

The predicate of the first statement is exactly matching with the where condition of the filtered index creation statement. The second statement, is actually a subset of filtered index, but as the columns territoryid is not part of the index , SQL server will not be able to filter the records and hence execution is performed by clustered index scan.

Limitation of filtered index   The concept of filtered index is  very attractive but there are some limitation in the usage of filtered index , especially when it comes to parameterization.Let us rewrite the query

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'

as

DECLARE @Orderdate date='2008-05-01'

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate

The execution plan is not utilizing the filtered index when we have rewritten the query with local parameter. The reason behind that is , at the compile time , the query optimizer does not know what value will be passed for the parameter @OrderDate. So optimizer has to generate a safe plan to satisfy all the criteria. This is the same issue when we change the database property to forced parameterization or defined the statement as procedure.

ALTER DATABASE mydb SET parameterization forced
GO
SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'
GO

CREATE PROCEDURE GetSalesorder (@OrderDate date)
AS
BEGIN

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate=@OrderDate END

In both these cases optimizer will not use the filtered index as it does not know what value will be passed during the run time and it try to generate a safe plan with out considering the availability of filtered index.

While we changing the database property parameterization to forced, the optimizer will replace all the static predicate with local variable. For example , the statement

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>'2008-05-01'

will be treated as below by the optimizer

DECLARE @Orderdate date='2008-05-01'

SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE orderdate>@Orderdate

The option to force SQL server to use the filtered index is to make dynamic statement as given below.

DECLARE @Orderdate date='2008-05-01'
DECLARE @SQL NVARCHAR(1000)
SET @SQL=N'SELECT orderdate,SalesOrderid FROM SalesOrderheader WHERE
orderdate>'''+CAST(@Orderdate AS CHAR(10))+''''
EXEC (@SQL)
GO

The execution plan is given below

From the execution plan it is clear that ,it is making use of the filtered index.

If you liked this post, do like my page on FaceBook

SQL Server : Part 8 : Explaining The Covering Index or Included Columns

2013-03-25T19:23:00.001-07:00

In our earlier discussion about non clustered index ,we have seen that, the leaf level of a non clustered index contain only the non clustered index key column and clustered index key (if the table is a clustered table). To fetch the remaining column from the clustered index structure or heap structure, SQL server has to do a bookmark/key look up operation.Many time the bookmark or key look up operation might be costly affair. Let us see an example.

USE mydb
GO
DROP TABLE dbo.SalesOrderDetail
GO
SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId)
GO
SET STATISTICS IO ON
GO
SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail WHERE productid=707 AND
SalesOrderID=43680

The execution plan and the out put of IO statistics of the select statement are given below.

In the execution plan, you can see that ,50 percent of the query cost is contributed by the Key Lookup operation.In the output of the IO statistics , it clearly says SQL server performed 5 IO operation to fetch the single record.

Note that, the existing non clustered index have 229 pages and depth is 2( levels in the b tree structure).Seek operation on this index need to perform only 2 IO operation to complete the task.You can verify this using the DBCC IND command or refer the earlier post.

Let us assume that, this query(with different parameters ) is used very frequently from the application and you need to optimize it further.How we can do that ? The only way that we can optimize this query is by avoiding the Key lookup operation. For that we can modify our non clustered index and add the remaining two column (OrderQty and UnitPrice) which are not part of clustered index key or non clustered index key.

DROP INDEX ix_Productid ON dbo.SalesOrderDetail

GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId,OrderQty ,UnitPrice)
GO

SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail WHERE productid=707 AND SalesOrderID=43680

Now we were able to get rid of the Key lookup operation from the execution plan and to reduce the IO from 5 to 3.But if we look into the out put of DBCC IND of the modified non clustered index, we can see that , depth of the b tree is increased by one due to this change. As the index level is increased , the non clustered index has to to perform 3 IO to complete the operation. This will be worst, if we have more column in the select list and we added all those columns into the non clustered index key to avoid the key lookup operation.

Here comes the covering index to help us.Covering index help us to add non key column to leaf level of the non clustered index with very minimal possibility of increasing the depth of the b-tree structure. This can be achieved by adding include column in the CREATE INDEX statement.
An index that contains all information required to resolve the query is known as a Covering Index.When we create a nonclustered index to cover a query, we can include nonkey columns in the index definition to cover the columns in the query that are not used as primary search columns. Performance gains are achieved because the query optimizer can locate all the required column data within the index; the table or clustered index is not accessed.

DROP INDEX ix_Productid ON dbo.SalesOrderDetail
GO
CREATE UNIQUE NONCLUSTERED INDEX ix_Productid ON dbo.SalesOrderDetail(ProductId,SalesOrderId)
include(OrderQty ,UnitPrice)
GO
SELECT SalesOrderDetailid,productid,salesorderid,orderqty,unitprice FROM SalesOrderDetail
WHERE productid=707 AND SalesOrderID=43680

With this also, we were able to get rid of the key lookup operation and to reduce the IO operation to 2. The IO operation clearly says the the depth of the clustered index is two.
Let us see the output of the DBCC IND

SELECT index_id FROM sys.indexes WHERE name='ix_Productid' AND OBJECT_ID= OBJECT_ID('SalesOrderDetail')
GO
DBCC ind('mydb','SalesOrderDetail',2)

This returns 378 records and the root page is 7456 (Value of pagepid column of the record having max value for indexlevel column)

Let us see the root page and one leaf level page

DBCC traceon(3604)

GODBCC page ('mydb',1,7456,3)GODBCC page ('mydb',1,7328,3)

From the output we can see that, columns mentioned in the include clause are added into the leaf level pages with out making any changes in the non leaf level pages.

Include column are useful because we can refer the column that has a data type which can not be used in the index key.More over include columns are not counted in the 900 bytes or 16 key column limitation of index keys.We can include with any data types except text,ntext and image.Included column also support the computed column.

If you liked this post, do like my page on FaceBook

SQL Server : Part 7 : Non clustered index on non unique column

2013-03-24T19:27:00.000-07:00

In our earlier post, we have discussed about the non clustered index, but there we were always discussing about unique non clustered index to make the discussion simple.As we understood the general structure of the non clustered index, let us discuss the storage structure of a non clustered index on a non unique column.

In our last post, we have discussed how SQL server manage clustered key on a non unique column.In that post we learned that SQL server add 4 bytes value to all duplicate occurrence of the clustered key.In the same way, non clustered index add the cluster key in all level of the b tree to uniquely identify the records in the next level.In the case of clustered index, the uniquifier is added only to the duplicate occurrence.In the case of non clustered index , clustered key is added to all records if the uniqueness is not defined while creating the index. If the non clustered index is defined as unique, SQL server adds the clustered key only to the leaf level for the bookmark look up operation .

Let us see a sample .

SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)
GO
CREATE INDEX Ix_ProductId ON SalesOrderDetail(ProductId,Salesorderid)

A copy of Salesorderdetail table is created with unique clustered index on SalesOrderDetailId and a non clustered index on ProductId and SalesOrderId column.Note that, while creating the non clustered index , I have purposefully avoided the unique keywords even if the non clustered index key is unique.

DBCC ind('mydb','SalesOrderDetail',4)

DBCC IND returns 229 records and root page id is 8320. Let us see the output of DBCC page for the root page.

DBCC page ('mydb',1,8320,3)

In the below figure, I have combined the output of the root page and one of the leaf level page. If you look into the root page (the first part), you can see that the cluster key (SalesOrderDetailid) is added in the root page.If you go back to our earlier discussion on non clustered index on clustered table , you will not find the clustered key in the root level. it will be there only in the leaf level. There is no change in the leaf level page structure while defining non clustered index as unique or non unique.

Let us see what will happen if the table is on heap.

SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE INDEX Ix_ProductId ON SalesOrderDetailHeap (ProductId,Salesorderid)
GO
SELECT index_id FROM sys.indexes WHERE name='Ix_ProductId' AND
OBJECT_NAME(OBJECT_ID)='SalesOrderDetailHeap'
GO
DBCC ind('mydb','SalesOrderDetailHeap',2)
GO
DBCC page ('mydb',1,5352,3)

In the below figure, I have combined the output of the root page and one leaf level page.In the root page you can notice that Heap RID is added. If you go back our earlier discussion on non clustered index on heap, we can see the Heap RID only on the leaf level not in the root page. There is no change in the leaf level page structure while defining non clustered index as unique or non unique.

You might have noticed that ,in our above example, even if the non clustered key is unique , SQL server considered it as non unique as we did not mentioned the uniqueness while creating the non clustered index. Adding the clustered key (or  HEAP RID) in to all level of index might lead to increase the level of index (more IO) depends on the size of the clustered key.So it is important to consider the uniqueness of column while defining the non clustered index key in all possible situation.

If you liked this post, do like my page on FaceBook

SQL Server : Part 6 : Design consideration of Clustered Index

2013-03-21T18:42:00.002-07:00

In our earlier post, we have discussed about the structure and storage of clustered index. In this post we will discuss about the design consideration of clustered index.There are couple of points that need to be considered while selecting clustered index key.There is no hard rule in selecting the clustering key . It is best practices and guidelines and the internal knowledge will help us to select right clustering key.

Uniqueness

SQL server will allow you to create a clustered index on non unique column, but uniqueness is one of the most desirable attribute for any indexes especially for a clustered index.Even if SQL server allow to create clustered index on a non unique column , internally SQL server add 4 bytes value for all duplicate instance of clustered key and this 4 bytes variable length column is known as uniquiifier .In this case SQL server consider the clustered key as the combination of non unique column on which clustered index is defined and internally generated uniquifier column.This value will be stored where ever the clustered key to be stored. For example in the leaf level of a non clustered index defined on a clustered table.

Let us see an example.I am going to create a copy of SalesOrderDetail table and define clustered index on the productid column which has duplicate values.

Use MyDb
GO
SELECT * INTO dbo.SalesOrderDetailDupCI FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE CLUSTERED INDEX ix_SalesOrderDetailDupCI ON dbo.SalesOrderDetailDupCI(ProductId)

Now let us run the DBCC IND command

DBCC IND('mydb','SalesOrderDetailDupCI',1)

This returns me 1608 pages and the root page number is 3570(The value of PagePID column of the record having highest value for indexlevel column ) . Below is the output of DBCC Page command for the root page and one intermediate page

In the output, we can see one additional column called UNIQUIFIER which we did not seen when we created a unique clustered index in earlier post. When a clustered index created on a non unique column , SQL server add 4 bytes random value for all duplicate occurrence of clustered key. It will not generate the uniquifier value for the first occurrence. That is the reason there are 0 for uniquifier column for some records. So clustered key defined on the non unique column has an overhead of generating the uniquifier value and also widen the cluster index key.In our example we defined clustered index on a 4 bytes column but actual clustered key size become 8 bytes due to uniquifier.This combination has to replicated to leaf level of all non clustered index .The magnitude of the issue will increase when there is non clustered index defined on non unique column where this clustered index value need to be stored in the non leaf level pages also.(We will discuss about non clustered index on non unique column in later post).

If your table does not have a single unique key to define clustered index, try to make it unique by adding one or two narrow column to the clustering key. That will avoid the overhead of adding the uniquifier and reduce the bookmark look up operation as the non leaf level of non clustered index has more columns (The additional column added to clustered key to make it unique)

Static
Another desired property of the clustered index key is to be a Static. when we define clustered index on a non static column , that makes the update statement(updating the clustered index key) more costly as it has to move the record into different page to make sure the data is stored in the logical order of clustered index and leaf level of all non clustered index need to be updated. Let us see a sample

set statistics io on
GO

--Unique clustered index on SalesOrderDetailIdUPDATE SalesOrderDetail_StaticIndex SET ProductID =99 WHERE ProductID=707
GO--Nonunique clustered index on ProductIdUPDATE SalesOrderDetail SET ProductID =99 WHERE ProductID=707

In the below output you can see a huge change in IO for the second update statement.

Even in small table (Customer_id,Firstname.lastname) on which clustered index defined on a non static column (Lastname) and which has one non clustered index, any change in the clustered index key need to make changes in minimum of two pages.One data page and one leaf level page of non clustered index.

Size of the clustered index key

Size of the clustered index refers to the number of bytes requires to store the clustered index key.As the size of the clustered index increases, more IO required to fetch the records.This is happening because an index page can store lesser index row if the clustered index is wider. That increase number of pages in the intermediate level and depth of the indexes (Levels in the b tree structure) .For example, a table which contain millions of records might need only 3 level in b tree structure if the clustered index in defined on integer column. If we defined clustered index on wider column (say a uniqueidentifier column which need 16 bytes), the depth of index might increase to 4 (Level of index). Any clustered index seek requires 4 IO operation compared to 3( if the clustered index is narrow).

This issue propagate to non clustered index also as the clustered index keys are stored in the leaf level of all non clustered index as a pointer to the clustered index. If the non clustered index is defined on the non unique column , the clustered key need to be stored in non leaf level pages of the non clustered index . Which again might cause for more page in intermediate level and to increase the depth of the in non clustered index which in turn increase the IO operation in non clustered index seek/scan also. As the clustered index depth is increased to 4, each bookmark look up operation also need to do 4 IO operation.

Sequential

It is a best practice to define clustered index on a ever increasing(sequential) column. That is the reason we can commonly seen clustered index defined on identity column. Clustered index defined on a non sequential column cause for fragmentation . To read more on fragmentation refer this posts . A non sequential clustered index key force the SQL server to insert record in between to maintain the logical ordering of the data.This lead to page split which cause for external fragmentation and internal fragmentation.

Conclusion

We have discussed about the desired qualities of the clustered index key and the reason behind that.The above points that we discussed are general best practices to be considered while deciding the clustered index key.Apart from this, data access pattern also influence in deciding the clustered index key.If we do not have complete understand of the data access pattern , we might need to test the performance with different strategies.

If you liked this post, do like my page on FaceBook

SQL Server : Part 5 : Explaining Non clustered Index on Heap

2013-03-17T09:12:00.001-07:00

In the last post, we have discussed about non clustered index on a clustered table.In this post we will discuss about the non clustered index on a heap table.

Non clustered index can be created on clustered table as well as heap table.While creating a non clustered index on clustered table , clustered index key will act as a row pointer. In heap table ,the combination of file id , page number and slot number will act as a row pointer in the non clustered index.

Let us see a hands on example. We will create a copy of salesorderdetail table with a non clustered index on productid and salesorderid columns

SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetailHeap(ProductId,Salesorderid)

A pictorial representation of the b tree structure is given below

Let us run the DBCC IND command to find the root page of the index.

DBCC ind('mydb','SalesOrderDetailHeap',2)

This statement returns 289 records with 1 as maximum value in the indexlevel column. That means the index structure required one page in root node , 287 pages in leaf level and one page for IAM chain.The page number of record, having maximum value for indexlevel column (root page) is 1656. Let us see the root page.

DBCC traceon(3604)
GO
DBCC PAGE ('mydb',1,1656,3)

Below we can see a part of the output of DBCC page command. The structure of the output is same as the structure of root page of non clustered index on clustered table.

Fig 1

Now let us move to the page 1497 which is a leaf level page.

DBCC traceon(3604)
GO
DBCC PAGE ('mydb',1,1497,3)

Fig 2

In the leaf level of non clustered index on clustered table , we have noticed that , clustered key is added to leaf level pages along with nonclustered key.Here we do not have a clustered index. So SQL server added row identifier (8 bytes in size) which is a combination of fileid (2 bytes) ,pageid(4 bytes) and slot number(2 bytes). The row identifier can be found in the HeapRID column of Fig 2. From the above figure, it is clear that, the complete information of the record with productid=707 and salesorderid =49464 can be found at the location HeapRID 0x6813000001000600. Below query will help us to split the RID into FileId:PageId:SlotNo format

DECLARE @HeapRid BINARY(8)

SET @HeapRid = 0x6813000001000600SELECT 
     
       CONVERT (VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 6, 1)
                               + SUBSTRING(@HeapRid, 5, 1)))
     + ':'
     + CONVERT(VARCHAR(10),
                    CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)
                               + SUBSTRING(@HeapRid, 3, 1)
                               + SUBSTRING(@HeapRid, 2, 1)
                               + SUBSTRING(@HeapRid, 1, 1)))
     + ':'
          + CONVERT(VARCHAR(5),
                    CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)
                               + SUBSTRING(@HeapRid, 7, 1)))
                               AS 'Fileid:Pageid:slot'

The output of the query is 1:4968:6 which says File id =1, Pageid =4968 and slot no=6. Let us see the page number 4968

DBCC traceon(3604)

DBCC PAGE ('mydb',1,4968,3)

In the output, you can see all columns of record having productid=707 and salesordeid = 49464 at slot number 6.

Let us consider the below query and analyse how SQL sever fetch the data.

SET STATISTICS IO ON

GO
SELECT * FROM SalesOrderDetail WHERE productid=707 AND SalesOrderid=49464

SQL Server has to do two I/O operation to reach the leaf level of non clustered index and one I/O operation to fetch the remaining data using Heap RID from the heap structure.The execution plan of the query is given below

Even if we make changes in the query to fetch only the ProductId,SalesOrderid and SalesorderDetaildId columns, still SQL sever need to do Key lookup operation. This is because SalesOrderDetailid is not declared as clustered key and not available in the leaf level of the nonclustered index. To avoid the key lookup operation, we need to limit the select columns only to the non clustered key(ProductKey ,salesorderid).

If you liked this post, do like my page on FaceBook

SQL Server : Part 4 :Explaining the Non Clustered Index Structure

2013-03-14T18:37:00.000-07:00

A table can have only one clustered index as the data rows are stored in the order of the clustered key, but a table can have multiple non clustered indexes.We have discussed about clustered index structure in our earlier post . In this post let us try to understand the non clustered index structure.

Logical Representation of Non Clustered index

In a simple word , a non clustered index is a subset of a table. When we define a non clustered index, SQL server store the set of non clustered key in a different pages.Let us consider a table with four columns (PersonId(PK),PersonType,FirstName,LastName) and a non clustered index on that table.The actual table is stored in the order of personid column (Cluster index key).In the below figure will give you a pictorial representation of non clustered index. The non clustered index key column are stored apart from the actual table.If you look into the Non clustered index ,you can notice that, records are stored in the order of Firstname and lastname (Non Clustered index key) not as in the order of actual table.To understand easily , non clustered index can be considered as subset table of actual one.

Fig 1

Now let us assume that we have a task to find out all records whose first name is 'Michael'.If you tried to find out from the Actual table , we need to go through each record from top to bottom to check whether first name matches with our search string 'Michael' as the records are not ordered based on the firstname column. It will be more tedious task if we have thousands of records in that table. The task will be much easier if we look into the the Non Clustered index side as the first name and last name are alphabetically ordered.We can easily locate the records which has firstname as 'Michael' and we do not need go beyond that as we are sure that there will not be any more records with firstname as 'Michael'.

Now we know Firstname and lastname of the record. How do we get the values for other two column ? Let us make a change in the Non clustered index part by associating the PersonId column along with the non clustered index.

Fig 2

Now, once we locate the records , we can go back to the Actual Table using the PersonId (Cluster index key) to find the values of other columns and this operation is called bookmark lookups or RID lookup.

Clustered index v/s Non clustered index

Non clustered indexes have the same B-tree structure as clustered indexes.The non clustered index key will not make any change in the sort order of underlying table where as clustered index force the SQL server to store the underlying table in the order of cluster index key. The leaf level of clustered index are made up of data pages which contain the actual data of table where as the leaf level of non clustered index are made up of index pages.

Non clustered index can be defined on a heap table or clustered table.In the leaf level of nonclusterd index, each index row contain the nonclustered key value and a row locator.This locator point to a the data row in the clustered index or heap.The row locator in nonclusterd index rows are either a pointer to a row or a clustered index key for a row. If the table is a heap, which means it does not have a clustered index,the row locator is a pointer to the row.The pointer is built from the file identifier ,page number and slot number of the row on the page. The whole pointer is known as a Row ID(RID). If the table has a clustered index, the row locator is the clustered index key for the row.

Hand on

Let us create a unique non clustered index on the salesorderdetails table that we were working on the clustered index post. The table has a unique clustered index on salesorderdetailid column. Now we are defining a non clustered index using the below statement

CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetail(ProductId,Salesorderid)

A pictorial representation of the b-tree structure is given below

Now let us see how SQL server store the index. Let us see the output of DBCC IND command

DBCC IND('mydb','SalesOrderDetail',2)

The last parameter, 2 is the index id of the index Ix_ProductId. For the usage of DBCC IND refer this post.

The output returns me 229 records which include one IAM page and 228 index page.From this we can find the root page id by locating the record having highest value for IndexLevel column. Remember that index level increase from leaf level to root. In this case, the root level page id is 4688 with index level 1. That means, the b-tree structure of this index has only root and leaf level .There is no intermediate level in the b tree structure of this non clustered index. Let us see the page 4688.

DBCC traceon(3604)
GO
DBCC page('mydb',1,4688,3)

This returns the 228 records (228 leaf level index pages) .Part of the output is shown below.The output looks same as the clustered index root/intermediate page structure. The output can be read as , all the records where the combination of productid,salesorderid is less than or equal to (707,51151) can be found in child page 2288. All the records where the combination of productid,salesorderid is between (707,51151) and (707,55920) can be found in the child page 2289 and so on.

Fig 3

Let us see the page 2289

DBCC traceon(3604)

GO

DBCC page('mydb',1,2289,3)

This returns 539 records, all with product id 707.As this index use only two level, this is the leaf level of the b tree structure. Here you can notice that, there is no child page id but we have the value of the salesorderdetailid column (Clustered index key) which SQL server use do key or bookmark look up operation.

Fig 4

Let us see, how SQL server perform a select operation using this index.

SET STATISTICS IO ON
GO
SELECT * FROM SalesOrderDetail WHERE productid=707 AND SalesOrderid=51192

The execution plan will looks like below.You can see the Key lookup operation in the execution plan.

Fig 5

As the where condition exactly match with our non clustered index definition, SQL sever use that index to execute this query.As a first step SQL server reads the root page of b tree structure.The combination of our search criteria (707,51192) falls in to the second records (Refer Fig 3). So SQL server goes to the child page (Page id 2289).In that page, we can locate the record with the exact combination of (707,51192) with salesorderdetailid 37793. From here, SQL server do a key look up operation using the salesorderdetailid value. From the earlier post about clustered index, we know that , SQL server need to perform 3 I/O operation while searching on any clustered index key. So to complete this query, SQL server needs to do 5 I/O operation (2 in non clustered index and 3 for bookmark/key lookup operation in the clustered index) and that is the same you can find in the output of IO statistics.

To understand it better,let us consider non clustered index as subset table ( let us say table name Saleorderdetail_NC) of salesorderdetail table with columns productid,salesorderid and SalesorderDetailid and having combination ProductId and salesorderid as clustered index.Then the result of the above query can be obtained by combining the output of below two query.

SELECT * FROM SalesOrderDetail_nc WHERE productid=707 AND SalesOrderid=51192

GO

SELECT * FROM SalesOrderDetail WHERE SalesOrderDetailid=37793

As a last part let us see one more query

SELECT * FROM SalesOrderDetail WHERE productid=707

This query returns 3083 records and the search condition is matching with the non clustered index first column. Still SQL server will not use the non clustered index to execute the query. Please find below the execution plan of this query.

Fig 6

The reason behind that, if it use the non clustered index, it has to do a key lookup operation for 3083 records . That itself requires (3083X3) 9249 I/O operation . Instead of that, SQL sever can fetch the output by performing clustered index scan which need only 1501(Total number of pages required for clustered index b-tree structure) I/O operation.If we make slight changes in the query to select only Productid ,SalesOrderDetailid and SalesOrderId, SQL server use the non clustered index as it does not need to do the key lookup operation. The leaf level of the non clustered index have the values of these three column . Please find below the execution plan.

SELECT productid,salesorderdetailid,salesorderid FROM SalesOrderDetail WHERE productid=707

FIg 7

I know this was a bit lengthy post, but still I feel it will give you the clear picture of the non clustered index structure.

If you liked this post, do like my page on FaceBook

SQL Server : Index Part 3 :Explaining the Clustered Table Structure

2013-03-12T18:02:00.001-07:00

In SQL server, there are two types of table based on the storage.A table with clustered index is called Clustered Table and a table with out a clustered index is called Heap Table. In earlier post, we have discussed about the specialty and the storage structure of the heap table. In this post let us discuss about Clustered Table.

A table with clustered index is called clustered table.Clustered index stores the actual data in the order of clustering key using a b tree structure and the data can be stored only in one order.That is the reason SQL server restricted the number of cluster index to one on a table.Let us see a logical representation of b tree structure. I have done this logical representation based on the copy of SalesOrderDetail table from AdventureWorks2008 database.

USE mydb
GO
SELECT * INTO dbo.SalesOrderDetail FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetail ON dbo.SalesOrderDetail(SalesOrderDetailID)

Fig 1

This table has 121317 records. SQL server requires 3 level to store this data.Let us look into the Fig 1 and analyse the pages. On top level, you can see only one page which is called root page. In all B tree structure, there will be only one page in the root level which is the entry point to the tree structure.The root level is always the highest level . In our case root page has index level 2. The pages in the root level and intermediate levels are called index pages.In the index pages, SQL server stores the clustering key and entry point(page pointer) to the next level of B tree. The clustering key stored against child page id is the lowest value stored in the below level page(Child page). The maximum value of the clustering key on a specific child page can be obtained by looking into the next record. For example, in that root level first record (Salesorderdetailid =NULL,pageid=456) says, salesorderdetailid less than or equal to 29599 can be found in the page number 456. The next entry (Salesorderdetailid =29599 ,pageid=520) says, salesorderdetailid value between 29599 and 60372 can be found on page 520 and son on.The prevpage and nextpage values will work as doubly link list to connect the pages in that level. As there is only one page in the root level, the value of prevpage and nextpage will be always 0.

Let us move to the next level. we have four pages in this level. You can find the values in the nextpage and prevpage which links the pages in that level. This level is called intermediate level. The number of intermediate level and pages in the intermediate levels are depends on the size of the table and clustering key. A large table can have multiple intermediate level and a small table might not have any intermediate level.The values in this level can be read in the same way as we did in the root level. For example first entry in the first page of this level ( Salesorderdetailid =NULL,pageid=392) says , all record with salesorderdetailid less than and equal to 72 can be found in the page number 392.

Next level is the bottom level is called leaf level (index level 0). The pages in this level are called leaf pages or data pages. In this page you can find complete data(all column) of each record in the salesorderdetails table.In other words , the leaf level of the clustered index is where the actual data is stored.

Let us create a copy of SalesOrderDetail table without any indexes

USE mydb
GO
SELECT * INTO dbo.SalesOrderDetailHeap FROM AdventureWorks2008.Sales.SalesOrderDetail
GO

Now let us run the below set of query.Both will return the same record.We are more interested to see the IO part.

SET STATISTICS IO ON

SELECT * FROM SalesOrderDetail WHERE SalesOrderDetailID =75
GO
SELECT * FROM SalesOrderDetailheap WHERE SalesOrderDetailID =75

The IO stats result look like as below.The logical read on the table which has clustered index is negligible compared to other one.

Let us see how SQL server manage to fetch the record using clustered index with 3 logical reads.First of all we need to find the root node of the clustered index. The dbcc ind will help us to find that.

DBCC IND('mydb','SalesOrderDetail',1)

This is returning 1501 records.This include one IAM page , one index page and 1499 data pages.Part of the output is shown below

From the output ,we can see that page 186 (page type 2) is the root page and this page is the entry point to this table. Let us see the out put of DBCC page.

DBCC traceon(3604)

GO
DBCC page('mydb',1,186,3)

SQL server stores the lowest value of clustered key stored in the child page and its page id in the index pages.For example Page 456 will have information of all the records that have value less than or equal to 29599 for salesorderdetailid column . In the same way page 520 will have information of all records that have value between 29599 and 60372 for salesorderdetailid(29599 may be there in both pages) and so on.The value of salesorderdetailid column for the record that we are searching is less than 29599. So the information about that records can be found in the childpage 456.

Let us see what we can see inside the page 456

DBCC traceon(3604)

GO
DBCC page('mydb',1,456,3)

The output contains 401 records and below is part of the output. you can run the DBCC page command with option 1 (last parameter) to see the page header.There we can find the m_type=2 which means it is a index page.As we discussed above,the details of records that we are searching (Salesorderdetailsid=75) can be found in the child page 393.

Let us see what we can see in page number 393.

DBCC traceon(3604)

GO
DBCC page('mydb',1,393,3) with tableresults

From the page header section we can see that value of m_type is 1. So this page is data page and it is the leaf level of the index. On scrolling down the output we can see the details of the record that we are searching as given below.We have value for all columns of the record that we are searching as the leaf level of the clustered index is the actual data.

SQL server manage fetch the records from clustered index reading only three pages(Root page,one page from intermediate level and one page from level)

Storage Overhead :Let us run the DBCC ind command for both tables(SalesOrderDetail and SalesOrderDetailHeap)

DBCC IND('mydb','SalesOrderDetail',1)

DBCC IND('mydb','SalesOrderDetailHeap',1)

From the output, we can see that the table, that does not have clustered index need 1496 page where as the table with clustered index need 1501 pages. This extra 5 pages are used to store the intermediate and root level of the b tree structure.Considering the advantage that we have on the select (lesser IO), this is a negligible overhead.

If you liked this post, do like my page on FaceBook

SQL Server : Understanding the IAM Page

2013-03-07T18:45:00.001-08:00

In my earlier post, we have discussed about Data,GAM,SGAM and PFS pages. In this post, let us try to understand about the IAM (Index Allocation Map) page.

In SQL server 2005 and later, there are three types of allocation units.

IN_ROW_DATA (btree and heap) allocation unit
LOB_DATA allocation unit
ROW_OVERFLOW_DATA allocation unit

While discussing about GAM/SGAM pages, we have noticed that, a GAM page can track 4GB worth space and they are repeated in the 4GB intervel. An IAM page track the pages/extents allocation in GAM interval of a partition for specific allocation unit of a table. Let us try to make it more clear .

Let us create a table with three column having data type of varchar(3000) and one column with data type LOB. This will ensure three type allocation to this table.

USE mydb
GO

CREATE TABLE IAMTable(
   Id INT,
   data1 VARCHAR(3000),
   data2 VARCHAR(3000),
   data3 VARCHAR(3000),
   Lobdata NTEXT)

GO

Now let us insert a record into this table

INSERT INTO IAMTable VALUES (1,'A','B','C',N'Test')

Here we are inserting a record which will not generate row overflow. We will use the DBCC IND command to list the pages allocated to this table.

DBCC IND('mydb','IAMTable',1)

Fig 1

From the output it is clear that , SQL server allocated two IAM pages (page type 10) to this table to track the allocation of IN_ROW_DATA and LOB_DATA . As the size of existing record is not enough to create ROW_OVERFLOW_DATA, SQL server did not allocated a IAM page to track row_overflow allocation.

Let us try to insert a record which will force the SQL server to generate the row_overflow data.

DECLARE @data1 VARCHAR(3000)

SET @data1 = REPLICATE('A',3000)
INSERT INTO IAMTable VALUES (1,@data1,@data1,@data1,N'Test')
GO
DBCC IND('mydb','IAMTable',1)

Fig 2

Now the table has IAM pages for all three allocation units. If we have more partition on this table, there will be separate set of IAM pages for each partition. Below picture will give you a pictorial representation.

In short a heap/B tree structure can have minimum of one IAM page and maximum of (No. of partition X 3) IAM pages. If the tables grows further and pages allocated from different GAM interval, more IAM pages will be added.These IAM pages need to be linked together and this list is called IAM chain.

Now we have learned the usage of IAM page. Let us try to see what is there inside the IAM page.In the earlier post about fragmentation, we have discussed that, first eight pages of a table/index are single page allocation and will be allocated from mixed extents. From Fig 2 , we know that page now 126 (PagePID column) is an IAM page which track the the in_row allocation. Below is the a section of DBCC page command output.

DBCC traceon(3604)
GO
DBCC page('mydb',1,126,3)

In the IAM header section , we can see following field.

SequenceNumber : This is the position of the IAM page in the IAM chain. This increases by one for each page added to the IAM chain.
Status: Unused
Objectid : Unused
Indexid : Unused
Page_Count : Unused
Start_pg:This is the GAM interval that the page maps.It store the first page id in the mapped GAM interval.

Single Page allocation section: These are the first 8 pages allocated from the mixed extent.After the 8th page, SQL server allocate uniform extents. So these section is used only in the first IAM page of the chain.Page number 120 and 176 are allocated from mixed extent. This is the same information we have from Fig 2 (Page type column value =1)

Extent Allocation Section: This section will describe the extents allocated to the allocation unit.

This part has referenced from : http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/

Let us execute the below script 7 time . After that there will be 9 records in the table (Two records are inserted as part of earlier step)

DECLARE @data1 VARCHAR(3000)

SET @data1 = REPLICATE('A',3000)
INSERT INTO IAMTable VALUES (1,@data1,@data1,@data1,N'Test')

Now let us see how the IAM page looks like

DBCC traceon(3604)
GO
DBCC page('mydb',1,126,3)

All single page allocation are done and for the 9th record, SQL server allocated an extent which start from page no 192 to 199.

Note : I have learned a lot about internals from Paul S Randal blog . This is only an attempt to represent the way I understood.

If you liked this post, do like my page on FaceBook