MS SQL Server : Nested Transaction and Partial Rollback of Transaction

Couple of days back, one of my colleague came to me and asked about partial rollback of a transaction.He was trying to do with nested transactions and it was throwing the  following error.

Msg 6401, Level 16, State 1, Line 1

Cannot roll back SaveTran. No transaction or savepoint of that name was found.

In this post let us go through the nested transactions (named transactions) and how to do partial rollback of transactions.

Nested Transactions

SQL server will allow you to start transaction inside the transaction which is called as nested transaction . Nested  transaction will allow to commit transaction individually but  will not allow to rollback individual transactions.In fact nested transaction is a myth in SQL server. Let us see below sample.

CREATE TABLE NestedTransaction (  Id INT)
GO
BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction  VALUES(1)
BEGIN TRAN InnerTxn
INSERT INTO NestedTransaction  VALUES(1)
ROLLBACK TRAN InnerTxn
SELECT @@TRANCOUNT

 

Above script will throw an error while trying to rollback the inner transaction and the transaction count is two which means that there are two open transactions in the current session. Try to execute the below two lines 

ROLLBACK TRAN OuterTxn
GO
SELECT @@TRANCOUNT

This will works perfectly and transaction count will be zero now. In short SQL server will allow you to rollback only the outer transaction and rollback of outer transaction will rollback all nested transactions and hence making the transaction count to zero.Let us see how commit work in this scenario.

BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction  VALUES(1)
BEGIN TRAN InnerTxn
SELECT @@TRANCOUNT 
INSERT INTO NestedTransaction  VALUES(2)
COMMIT TRAN InnerTxn
SELECT @@TRANCOUNT
ROLLBACK

This is more interesting. In the 4th line we can see that there are two open transaction and after that we are inserting a value 2 to our table. After committing the inner transaction, the transaction count reduced to one. Which says that the inner transaction is committed, but if you do a select query on our table after executing the last rollback statement ,there will not be any record in the table. In fact, there is no effect in commiting the inner transaction , but to commit nested transaction , we have to commit all nested transactions individually. In SQL server , inner transaction does not play any role. All locking of object also will be taken care  by the outer transaction. let us see below example.

CREATE TABLE NestedTransaction_1 ( Id INT)
GO
CREATE TABLE NestedTransaction_2 (   Id INT)CREATE TABLE NestedTransaction_3 (   Id INT)
GO
BEGIN TRAN OuterTxn
INSERT INTO NestedTransaction_1  VALUES(1)
BEGIN TRAN InnerTxn_1
INSERT INTO NestedTransaction_2  VALUES(1)
BEGIN TRAN InnerTxn_2
INSERT INTO NestedTransaction_3  VALUES(1)



Logically the locks on each table should be held by respective transactions but if you look into the TransactionName  column  in the output of  this query (Query to find the locking info), it will be outer transaction.  

if you ask me, what is the use of a nested transactions, I do not have a definitive answer.

Partial Rollback of Transactions

Partial rollback of transaction is possible by setting a save point inside a transaction using the Save Transaction command. Please find the sample script below.


CREATE TABLE PartialTxn( Name CHAR(10))
GO--Transaction Starting hereBEGIN TRAN
INSERT INTO PartialTxn VALUES('James')--Setting the savepointSAVE TRANSACTION Txn1INSERT INTO PartialTxn VALUES('George')SELECT * FROM PartialTxn ROLLBACK TRANSACTION Txn1COMMIT
SELECT * FROM PartialTxn

You can see that only one record exists in the table after the final commit. This can be implemented inside the procedure also. In case of multiple procedures are part of single transaction , you can rollback only the failed(due to some validation error)  procedure and can still commit the remaining data.   



If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

Windows 2008 Server : Moving Cluster Quorum Disk to New SAN

In my earlier post, I have explained how to move the MSDTC disk to new SAN. In this post we will go through the procedure to move the quorum drive to the new SAN. Follow the steps mentioned in the earlier post to add the new disk to the Available Storage Group.

In our environment the existing Quorum drive is Q and new designated drive is X. Once the new drive is available in the Available Storage Group, follow the steps given below:

• Open the cluster manager and select the cluster group in the left pane.
• In the right pane , you can see an option called More Actions. On clicking on that , a popup  menu will be opened as given below.

• Click on the first option, Configure Cluster Quorum Settings, which will open a screen as given below.

• Select Next button , which lead to to Quorum configuration settings screen.Select appropriate setting based on your environment . The default is the second option and that might suits to almost all environments.

• On clicking next , disk selection page will open. Select the appropriate disk. In our case we have to select the X drive.You can expand the disk to see the drive letter. 

• After selecting the appropriate disk, click on Next which will lead to Confirmation page and on clicking next from the confirmation page , the quorum will be moved to new disk. Now you can see a folder Cluster in the new drive (X). The old drive will be available in the Available Storage Group. 

Moving Quorum is completely an online operation and does not required any down time. If you really want to keep the drive letter Q for the Quorum drive , we can do it in two ways. 

• Unassign the drive letter Q from the  old drive and change the drive letter of new quorum drive(X) to Q , but you will get a warning message and I did not proceed with this as I do not want to take any risk with our cluster environment.
• The second method is , change the drive letter of the old disk to any available drive letter. For example Y. Follow the steps mentioned earlier to move the quorum to  Y drive. Change the drive letter X to Q. Again follow the same step to move to Q drive which is the new quorum drive.

I followed the above steps to move the quorum disk to new SAN and it worked well.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

SQL Server: Measuring The Index Fragmentation

In my previous post , I have explained about the different types of fragmentation and how it will affect the query performance. In this post we will discuss about measuring the index fragmentation.

Measuring Internal Fragmentation 

Internal fragmentation  is all about the fullness of the page and it is measured using the         sys.dm_db_index_physical_stats function with DETAILED mode. The avg_page_space_used_in_percent column in the output gives the internal fragmentation of the index. Below query list all the indexes which have more than 10 pages and page fullness is less than 85 percent.

EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @DefaultFillFactor INT
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value  END  FROM @Fillfactor 

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  stats.avg_page_space_used_in_percent,
  CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'DETAILED') AS stats,
        sys.objects AS o,
        sys.schemas AS s,
        sys.indexes AS iWHERE
        o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND     stats.avg_page_space_used_in_percent<= 85 AND stats.page_count >= 10 AND        stats.index_id > 0 ORDER BY  stats.avg_page_space_used_in_percent ASC, stats.page_count DESC

I have used the where condition to fetch  only the indexes which  have more than 10 pages  and page fullness is less than 85 percentage. This is  based on   my environment and some best practices documentations.Low values for avg_page_space_used_in_percent and higher  value for PageCount together will affect the performance of the system. The value of avg_page_space_used_in_percent will be  low due to various reasons

• Due to Page split and deleting records: In this scenario we have to REBUILD or REORGANIZE  the indexes. If the fragmentation is reported in the non leaf level , REBUILD is required to reduce the fragmentation.
• Due to fill factor setting : A wrong setting  of fill factor value of the index might cause the internal fragmentation.If the internal fragmentation is due the fill factor setting, we have to REBUILD the index with new fill factor value.
• Due to record size : Some time size of the record might account for internal fragmentation. For example let us assume that size of one record is 3000 bytes and page can hold only two record. The third record can not be fitted into a page as the remaining free space in the page is less than 3000 bytes. In this scenario each page will have empty space of 2060 bytes. To get rid of the fragmentation due to the size of the record , we might need to redesign the table or has to do a vertical partitioning of the table.

Measuring External Fragmentation 

External fragmentation also measured using the sys.dm_db_index_physical_stats function with LIMITED mode ,but we will be using the avg_fragmentation_in_percent from the result to measure the external fragmentation. With LIMITED mode it will give the fragmentation of the leaf level. To get the fragmentation of non leaf level, it  should be executed  with DETAILED or SAMPLE mode. A fragment is a continuous allocation of pages.For example if an index has 150 pages and pages are allocated from 1 to 50, 55 to 60 ,65 to 120 and 140 to 180.Each of these sequences are called as fragment and we can say that this index has four fragment.


EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
G
ODECLARE @DefaultFillFactor INT
DECLARE @Fillfactor TABLE(Name VARCHAR(100),Minimum INT ,Maximum INT,config_value INT ,run_value INT)
INSERT INTO @Fillfactor EXEC sp_configure 'fill factor (%)'     
SELECT @DefaultFillFactor  = CASE WHEN run_value=0 THEN 100 ELSE  run_value END  FROM @Fillfactor 

SELECT
DB_NAME() AS DBname,
QUOTENAME(s.name) AS CchemaName,
QUOTENAME(o.name) AS TableName,
i.name AS IndexName,
stats.Index_type_desc AS IndexType,
stats.page_count AS [PageCount],
stats.partition_number AS PartitionNumber,
CASE WHEN i.fill_factor>0 THEN i.fill_factor ELSE @DefaultFillFactor END  AS [Fill Factor],
  stats.avg_fragmentation_in_percent,stats.fragment_count,
  CASE WHEN stats.index_level =0 THEN 'Leaf Level' ELSE 'Nonleaf Level' END AS IndexLevel
FROM
        sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL , NULL, 'LIMITED') AS stats,
        sys.objects AS o,
        sys.schemas AS s,
        sys.indexes AS iWHERE
        o.OBJECT_ID = stats.OBJECT_ID AND s.schema_id = o.schema_id       AND     i.OBJECT_ID = stats.OBJECT_ID AND i.index_id = stats.index_idAND     stats.avg_fragmentation_in_percent>= 20 AND stats.page_count >= 1000ORDER BY  stats.avg_fragmentation_in_percent DESC,stats.page_count DESC




In this query ,  I have used a where condition to fetch indexes which have fragmentation  greater than 20 percent and have minimum of 1000 pages.  

avg_fragmentation_in_percent can have higher value due to various reasons :

• SQL server storage engine allocates pages from mixed extent to a table or index till the page count reaches eight.Once the page count reaches to eight SQL server storage engine starts assigning full uniform extents to the index. So there is a possibility of having higher fragmentation for small table and rebuilding indexes might increase the fragmentation.For example, let us assume that an index has 7 pages and these pages are allocated from two mixed extent, while rebuilding the index there is possibility of allocating pages from more than 2 extents and maximum of seven extents which in turn increase the fragmentation. 
• Even the pages are allocated from uniform extent , there is possibility of fragmentation. When the size of index grow , it need more pages in the non leaf level also.If last page allocated to leaf level is 250 and to accommodate more row in the leaf lever index structure might need a page in index level 1, then SQL server storage engine allocate page 251 to the index level 1 which create fragment in the leaf level.
• Other common reason  is the page split  due to the DML operations . This I have explained well in my previous post.Rebuild/Reorganize  index may not be effective to fix fragmentation happened due to the fist two reason, but it can reduce the fragmentation caused by the page split or delete operation. 
• In our environment we follow the index maintenance  as given below:
• 20 to 40 percentage of fragmentation is handled with reorganizing the index.
• All index which has more 40 percentage fragmentation will considered for rebuild
• Index which has less than 1000 pages will be ignored by the index maintenance logic.
• Index which has more than 50K pages and fragmentation between 10 and 20 will also be considered for Reorganize.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

SQL server: Deciphering Wait resource

When I was working with couple of frequent blocking issue in our live server, I have noticed different types of wait resources which are not meaningful unless they mapped to a table or an index. In this post I will explain how to map the wait resources to table/index.

Key Wait Resource 

Below is a sample of blocked process report with Key as wait resource .

The first part of the key wait resource is the database id and second part is called as Hobt_Id. Hobt is an acronym for Heap Or B Tree. The hobt_id can be mapped to sys.indexes and sys.objects through sys.partitions. Below script will map the key wait resource to corresponding index and table.

SELECT 
 o.name AS TableName, 
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID 
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID  AND p.index_id = i.index_id 
WHERE p.hobt_id = 72057594040811520

Page Wait Resource 

Below is a sample of blocked process report with Page as wait resource .

As everyone knows, the first part of the wait resource is the database id (68)  , second part is file id (1)  and third part is the page number(492478). To find out the object id associated with this page follow the below steps.

SELECT DB_NAME(68)

Select the database based on the output above statement. Then execute the below statement

DBCC traceon (3604)

GO
DBCC page (68, 1, 492478) --Database_id,file_id,page_id


This will give a result as given below.

The object id can be mapped to a table using  the system function  object_name()

Object Wait Resource 

Below is a sample of blocked process report with Object as wait resource .

Here also the first part of the wait resource is the database id(68) ,second part is the object id and third part is known as lock partition id. The object id part can be mapped to an object using the object_name() system function . The lock partition id is not very useful in troubleshooting the blocking issue.This will have positive value only when the server has more than 16 CPU. I will explain more about the lock partition in my  future post.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba