The other day I ran into a question on msdn:Tempdb Deallocation (dm_db_session_space_usage). A person with the pseudonym DontPageMeBro noticed an odd behavior in his SQL Server installation: The sum of the reported usage of tempdb across all sessions was often bigger than tempdb itself. Something is clearly off here, so I figured it was worth digging a little deeper.

TempDb Usage

First let us look at how to get information about tempdb usage. SQL Server provides two DMVs that return information about the space used in tempdb:

sys.dm_db_session_space_usage

sys.dm_db_task_space_usage

sys.dm_db_task_space_usage reports the amount of pages taken and released by a task currently running. If a request uses parallelism you might see more than one row for that request returned by this DMV. After each task finishes, it's numbers get rolled up into the sys.dm_db_session_space_usage. So here you can see how much space in tempdb a request used in total, but this information is only available after the request is completed.

Both DMVs report the number of pages in four columns:

user_objects_alloc_page_count, 
user_objects_dealloc_page_count, 
internal_objects_alloc_page_count, 
internal_objects_dealloc_page_count

So, to get the current user object space allocation in tempdb for a given session, you need to subtract the "user_objects_dealloc_page_count" from the "user_objects_alloc_page_count" in both DMVs and then SUM all those values together. The following query shows how to do that for user as well as for internal space used:

SELECT  session_id ,
        SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) CurrentUserPages,
        SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) CurrentInternalPages
FROM    ( SELECT  session_id ,
                  user_objects_alloc_page_count ,
                  user_objects_dealloc_page_count ,
                  internal_objects_alloc_page_count ,
                  internal_objects_dealloc_page_count
          FROM    sys.dm_db_session_space_usage
          UNION ALL
          SELECT  session_id ,
                  user_objects_alloc_page_count ,
                  user_objects_dealloc_page_count ,
                  internal_objects_alloc_page_count ,
                  internal_objects_dealloc_page_count
          FROM    sys.dm_db_task_space_usage
        ) X
GROUP BY X.session_id
ORDER BY X.session_id ASC ;

DontPageMeBro is dealing with the problem that some users create large #temptables and then leave the session open without dropping those tables. If you have a lot of users like that your tempdb might grow to a significant size. Because it is always advisable to have tempdb on very fast storage, this problem can mean a significant cost to the business, so it makes sense to follow up and see how big the problem really is. The sys.dm_db_session_space_usage can give us that information. Every time there is a session that has more allocated than deallocated pages, that session is holding on to a temporary object. If the last request for that session has finished a while ago, that means, that space is not actually in use and those objects should have been dropped.

Counting Inaccuracies

So far the theory. However, as DontPageMeBro noticed, if a #temptable is big it seems that SQL Server sometimes forgets to count the page deallocation. So, even though the #temptable is gone, the pages are still being counted as in use by the session that created the table.

To understand what is going on here, we need to look at how SQL Server handles page deallocations after dropping an object.

Each Table consists of at least one allocation_unit. An allocation_unit contains for example all the data pages for a given table or index. There might also be additional allocation_units holding special pages for e.g. row overflow data. Every index in turn has it's own set of allocation_units.

When dropping an object, all allocation_units belonging to that object are deallocated and all their pages are marked as free. This can take quite some time for big objects and usually this cleanup work is done synchronously, which means that the session issuing the drop request has to wait. However if the allocation unit contains more than 1024 pages, this cleanup work is deferred. The allocation_unit is marked as "DROPPED" and the link to the table is removed at the time the drop statement executes. The actual work of freeing up all those pages is then done by a cleanup process later on. This process is the same in all databases, not only tempdb.

Lets look at an example to demonstrate this:

CREATE TABLE #temp
  (
    id INT IDENTITY ,
    name VARCHAR(100) 
  );
GO
SELECT  allocation_unit_id
INTO    #aus
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN tempdb.sys.system_internals_partitions p ON u.container_id = p.partition_id
WHERE   p.object_id = OBJECT_ID('tempdb..#temp');
GO
INSERT  INTO #temp
        SELECT TOP ( 328168 )
                'FillText'
        FROM    sys.system_internals_partition_columns a ,
                sys.system_internals_partition_columns;
GO
SELECT  'A' A , *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = @@SPID;

SELECT  'A' A , u.* , p.rows
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN tempdb.sys.system_internals_partitions p ON u.container_id = p.partition_id
        JOIN #aus t ON u.allocation_unit_id = t.allocation_unit_id;
GO
DROP TABLE #temp;
GO
SELECT  'B' B , *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = @@SPID;

SELECT  'B' B , u.*
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN #aus t ON u.allocation_unit_id = t.allocation_unit_id;
IF ( @@ROWCOUNT > 0 ) 
  BEGIN
    WAITFOR DELAY '00:00:06' ;
    SELECT  'C' C , *
    FROM    sys.dm_db_session_space_usage
    WHERE   session_id = @@SPID;
    
    SELECT  'C' C , *
    FROM    tempdb.sys.system_internals_allocation_units u
            JOIN #aus t ON u.allocation_unit_id = t.allocation_unit_id;
  END ;
GO

DROP TABLE #aus;

This script creates the table #temp, fills it with a few thousand rows and then drops it again. If you run this script you will get the following output back:

The first result-set shows that the current session has 1019 pages currently reserved in tempdb. The second result-set tells us that the table #temp contains 328168 rows that are stored in a single allocation unit of type "IN_ROW_DATA" with 1018 pages.

The third and forth result-set were captured after the table was dropped. Here we can see that 1017 pages were deallocated in tempdb for the current connection and that the allocation_unit is now gone. (That not all pages of #temp show as being dropped in the count is due to another optimization that is not part of today's discussion.)

If we change the number of rows inserted (line 14 in above script) from 328168 to 328169 we get this output instead:

This time the allocation_unit has 1026 pages, so we would expect the deferred drop to take place. Result-set 4 now shows, directly after the drop table took place, that the allocation_unit is still there, but it's type changed to "DROPPED". The link to the table it once belonged to is also removed, as is visible in the container_id column.

If the above script sees that the allocation_unit still exists after the drop (see line 37) it waits for a few seconds and then looks again. In result-set 6 you can now see that the allocation_unit got removed. However the current session still shows 1027 allocated and no deallocated pages (result-set 5).

Cleaning Up

To see the cleanup process in action you can use the following script:

CREATE TABLE #temp
  (
    id INT IDENTITY ,
    name VARCHAR(100) 
  );
GO
SELECT  allocation_unit_id
INTO    #aus
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN tempdb.sys.system_internals_partitions p ON u.container_id = p.partition_id
WHERE   p.object_id = OBJECT_ID('tempdb..#temp');
GO
INSERT  INTO #temp
        SELECT TOP ( 328169 )
                'FillText'
        FROM    sys.system_internals_partition_columns a ,
                sys.system_internals_partition_columns;
GO
SELECT  'A' A , *
FROM    sys.dm_db_session_space_usage
WHERE   session_id = @@SPID;

SELECT  'A' A , u.* , p.rows
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN tempdb.sys.system_internals_partitions p ON u.container_id = p.partition_id
        JOIN #aus t ON u.allocation_unit_id = t.allocation_unit_id;
GO
DROP TABLE #temp;

SELECT  IDENTITY(INT,1,1) Id,SYSDATETIME()now, u.allocation_unit_id , data_pages , type_desc
INTO    #siau
FROM    tempdb.sys.system_internals_allocation_units u
        JOIN #aus t ON u.allocation_unit_id = t.allocation_unit_id ;

SELECT IDENTITY(INT,1,1) Id,SYSDATETIME()now, *
INTO #ssu
 FROM  sys.dm_db_session_space_usage 
WHERE user_objects_dealloc_page_count>0 OR session_id = @@SPID
ORDER BY session_id;

SELECT IDENTITY(INT,1,1) Id,SYSDATETIME()now, tsu.*,r.status,r.command
INTO #tsu
 FROM  sys.dm_db_task_space_usage tsu
 JOIN sys.dm_exec_requests r
 ON tsu.session_id = r.session_id
WHERE user_objects_dealloc_page_count>0
ORDER BY session_id;

DECLARE @AuId BIGINT = (SELECT TOP(1) allocation_unit_id FROM #aus);
DECLARE @Stop DATETIME2 = DATEADD(SECOND,10,SYSDATETIME());
WHILE (SYSDATETIME()<@Stop)
BEGIN
  INSERT   INTO    #siau
  SELECT  SYSDATETIME()now, u.allocation_unit_id , data_pages , type_desc
  FROM    tempdb.sys.system_internals_allocation_units u
  WHERE u.allocation_unit_id = @AuId;

  INSERT INTO #ssu
  SELECT SYSDATETIME()now, *
   FROM  sys.dm_db_session_space_usage 
  WHERE user_objects_dealloc_page_count>0 OR session_id = @@SPID
  ORDER BY session_id;

  INSERT INTO #tsu
  SELECT SYSDATETIME()now, tsu.*,r.status,r.command
   FROM  sys.dm_db_task_space_usage tsu
   JOIN sys.dm_exec_requests r
   ON tsu.session_id = r.session_id
   AND tsu.request_id = r.request_id
  WHERE user_objects_dealloc_page_count>0
  ORDER BY session_id;
END

SELECT MIN(now) MinNow,MAX(now) MaxNow,type_desc,data_pages,allocation_unit_id
FROM #siau
GROUP BY type_desc,data_pages,allocation_unit_id
ORDER BY MIN(Id) ASC;

SELECT MIN(now) MinNow,MAX(now) MaxNow,session_id,database_id,user_objects_alloc_page_count,user_objects_dealloc_page_count,internal_objects_alloc_page_count,internal_objects_dealloc_page_count 
FROM #ssu 
GROUP BY session_id,database_id,user_objects_alloc_page_count,user_objects_dealloc_page_count,internal_objects_alloc_page_count,internal_objects_dealloc_page_count
ORDER BY session_id ASC, MIN(Id) ASC;

SELECT MIN(now) MinNow,MAX(now) MaxNow,status,command,session_id,request_id,exec_context_id,database_id,user_objects_alloc_page_count,user_objects_dealloc_page_count,internal_objects_alloc_page_count,internal_objects_dealloc_page_count
FROM #tsu 
GROUP BY status,command,session_id,request_id,exec_context_id,database_id,user_objects_alloc_page_count,user_objects_dealloc_page_count,internal_objects_alloc_page_count,internal_objects_dealloc_page_count
ORDER BY session_id ASC, MIN(Id) ASC;

GO
DROP TABLE #ssu;
DROP TABLE #tsu;
DROP TABLE #aus;
DROP TABLE #siau;

This script again creates the same table #temp that was used by the previous script, fills it and drops it. After dropping the table it records for ten seconds information from the allocation_unit DMV and the two space_usage DMVs. After those ten seconds it produces this output:

The first two result-sets show us the same information about the table that we have seen before. The other three result-sets contain grouped information of the three recorded DMVs. Each is grouped by all columns but the time of capture. That means, each row shows when those values where visible first and last, so you can see the progression of events.

The third result-set tells us that right after dropping the table the allocation_unit was marked as dropped. It at this point also shows a size of 0 pages. Just a little under a second later, at 17:18:06.1417576, the allocation_unit was seen last. At 17:18:06.1387573, just barely a millisecond after dropping the table, session 8 suddenly appears and starts to deallocate pages – pages it clearly did not allocate – until it, at 17:18:06.1417576, has deallocated all 1025 pages that belonged to our allocation_unit. That is exactly the same time when the allocation_unit itself was seen last. That this process started right after dropping the table is a coincidence and you might see several seconds pass before it starts, when you try this out yourself.

During the whole time of the recording from the table drop to ten seconds later, the information returned by sys.dm_db_session_space_usage DMV did not change at all. So, while the cleanup process can be seen to deallocate pages, that information never gets rolled up into the session space DMV. The session that originally created the table also never gets updated.

Versions

I have tested this in the following versions of SQL Server:

Microsoft SQL Server 2005 - 9.00.5057.00 (X64)
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Microsoft SQL Server 2012 RC0 - 11.0.1750.32 (X64)

Conclusion

I consider this behavior a bug in SQL Server. As the last example showed, the allocation_unit gets cleaned up by a background process. At that point the link to the original table and with it the originating session is gone, so the background process cannot update the session space usage information. However, right at the time of the drop, when the allocation_unit is marked as DROPPED, it also shows a size of 0 pages. At this point the task space usage information for the request executing the drop should have been updated to also reflect the drop. The pages could at the same time appear in the allocated column of one of the "TASK MANAGER" requests that execute the background cleanup tasks to make sure that the total of all allocated and deallocated pages in those two DMVs always matches the current state of tempdb.

I created a Connect item with Microsoft for this behavior here:. If you agree that this should be addressed, please go there and vote for it.

About two weeks ago I wrote about how to get the content of the entire database into an XML document. Today Daniel commented, that he would like the XML to be in a compacter format. In this post I am going to explain how to get there.

While WordPress™ conveniently ate the example XML Daniel tried to post, I will assume for this article that he was going for this format:

<tables>
  <table name="[dbo].[T1366]">
    <row id="1" c1="1366" c2="2732" c3="4098" c4="5464" c5="6830" c6="8196" />
    <row id="2" c1="1366" c2="2732" c3="4098" c4="5464" c5="6830" c6="8196" />
  </table>
  <table name="[dbo].[T127]">
    <row id="1" c1="127" c2="254" c3="381" c4="508" c5="635" c6="762" c7="889" />
    <row id="2" c1="127" c2="254" c3="381" c4="508" c5="635" c6="762" c7="889" />
  </table>
</tables>

Instead of all column values being sub-nodes of their <row> nodes they are now attributes of an empty <row> node. Also the <data> node between <table> and <row> is now missing, as it was kind of superfluous. And while I was changing the code anyway, I went ahead and added the previously missing root node as <tables>.

So how did I get there?

First let us look at the missing root node, which turns out to be the simplest of all the changes. When you select rows for a table specifying the FOR XML PATH('NodeName') directive, every row is going to be represented as a node with the passed in name. Those nodes are just hanging together without an enclosing root node. To make the output a valid XML document you can just specify the ROOT clause next to the PATH clause like this:

  SELECT * 
    FROM dbo.Table
  FOR XML PATH('rowNodeName'),ROOT('rootNodeName'),TYPE

The TYPE directive tells SQL Server to return the XML as a value with the XML type. If you leave it out the XML gets returned as a string, which can create problems if you want to continue to work with it in T-SQL.

The next step was, to get rid of the <data> node. When you specify an XML value in a select statement that is in turn using FOR XML PATH, that value is wrapped in a node with the value's column name. The generated SQL in my original solution looked like this:

SELECT * FROM(
SELECT '[dbo].[t1]' [@name],(SELECT * FROM dbo.t1 FOR XML PATH('row'),TYPE) data
UNION ALL
SELECT '[dbo].[t2]' [@name],(SELECT * FROM dbo.t2 FOR XML PATH('row'),TYPE) data
)X FOR XML PATH('table'),TYPE;

The data column in the UNION ALL query contains XML values, so the outer SELECT * ... FOR XML PATH('table') is going to wrap each one into a <data> node like this:

<table name="[dbo].[t1]">
  <data>
    <row>
      <id>1</id>
      <c1>1</c1>
    </row>
    <row>
      <id>2</id>
      <c1>1</c1>
    </row>
  </data>
</table>
<table name="[dbo].[t2]">
  <data>
    <row>
      <id>1</id>
      <c1>2</c1>
      <c2>4</c2>
    </row>
    <row>
      <id>2</id>
      <c1>2</c1>
      <c2>4</c2>
    </row>
  </data>
</table>

To remove the additional node we can use the fact that SQL Sever will inline the value of a column that does not have a name. So we need to select the data column without specifying its name. To do that we will use a trick:

SELECT [@name],(SELECT data) FROM(
SELECT '[dbo].[t1]' [@name],(SELECT * FROM dbo.t1 FOR XML PATH('row'),TYPE) data
UNION ALL
SELECT '[dbo].[t2]' [@name],(SELECT * FROM dbo.t2 FOR XML PATH('row'),TYPE) data
)X FOR XML PATH('table'),TYPE;

By wrapping the data column in its own SELECT it loses its name property for the outer SELECT statement. The output it generates now looks like this:

<table name="[dbo].[t1]">
  <row>
    <id>1</id>
    <c1>1</c1>
  </row>
  <row>
    <id>2</id>
    <c1>1</c1>
  </row>
</table>
<table name="[dbo].[t2]">
  <row>
    <id>1</id>
    <c1>2</c1>
    <c2>4</c2>
  </row>
  <row>
    <id>2</id>
    <c1>2</c1>
    <c2>4</c2>
  </row>
</table>

The last thing we need to accomplish is to make the column values attributes of each <row> node instead of sub-nodes. If you specify a column name with a leading "@" sign, FOR XML PATH will make that value an attribute with that name (without the "@"):

SELECT id AS [@id] FROM dbo.t1 FOR XML PATH('row'),TYPE;

The output of above statement looks like this:

<row id="1" />
<row id="2" />

So instead of a SELECT * FROM for each table, we need to list all columns in the format shown above. To get this specially formatted column list we are going to use XML concatenation one more time:

SELECT STUFF((SELECT ','+QUOTENAME(name)+' AS '+QUOTENAME('@'+name) 
                              FROM sys.columns c 
                             WHERE c.object_id = OBJECT_ID('dbo.t2')
                             ORDER BY column_id 
                            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'');

This produces the desired list:

[id] AS [@id],[c1] AS [@c1],[c2] AS [@c2]

With that we have all the parts collected to create the new "compact" solution. The last step is to put it all together:

DECLARE @cmd NVARCHAR(MAX) ;
SET @cmd = 'SELECT [@name],(SELECT data) FROM('
    + STUFF(
(SELECT ' UNION ALL SELECT ''' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
        + '.' + QUOTENAME(t.name) + ''' [@name],' + '(SELECT '+c.ColList+' FROM '
        + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(t.name)
        + ' FOR XML PATH(''row''),TYPE) data'
 FROM   sys.tables t
 CROSS APPLY (SELECT STUFF((SELECT ','+QUOTENAME(name)+' AS '+QUOTENAME('@'+name) 
                              FROM sys.columns c 
                             WHERE c.object_id = t.object_id
                             ORDER BY column_id 
                            FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
             )c(ColList)
    FOR     XML PATH('') ,
                TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
    + ')X FOR XML PATH(''table''),ROOT(''tables''),TYPE;' ;

EXEC(@cmd) ;

This produces the desired output that was shown all the way at the beginning of this article. The size of the resulting XML document for my example database went from about 1.4 MB for the original solution to now about 0.5 MB – a significant improvement.

]]> http://sqlity.net/en/626/selecting-the-entire-database-as-xml-string-2/feed/ 4 http://sqlity.net/en/626/selecting-the-entire-database-as-xml-string-2/ http://feedproxy.google.com/~r/sqlitynet/~3/m7p0XluTc3M/ http://sqlity.net/en/609/the-performance-impact-of-forwarded-records-on-table-scans/#comments Tue, 31 Jan 2012 16:00:18 +0000 Sebastian http://sqlity.net/en/?p=609 more »]]>

Forwarded records are a mechanism that SQL Server employs to reduce the amount of maintenance work for indexes on a table without a clustered index. During an update of a record in a table that does not have a clustered index, an increase in size might prevent the record to fit on its current page. If that happens, a forwarding pointer is created in the current page and the record is moved to a new location.

It is well known that such forwarded records will cause each RID-Lookup operation that encounters one to have to read a second page. If you are doing a filtered index scan followed by an RID-Lookup this can potentially double the amount of work necessary to retrieve all rows.

What is less well known is the fact, that such forwarded records can significantly impact the performance of a simple table scan as well. In this article I will show you why this happens.

Terminology

To explain the way SQL Server handles forwarded records during a table scan, I am first going to rehash some storage basics.

SQL Server knows two ways to organize the data in a table on disk. One is the Clustered Index and the other one the Heap. In a Clustered Index data is organized in a B+Tree structure allowing for fast access based on the Clustered Index Key. In a Heap on the other hand the data is not sorted or linked in any way. Any new row is inserted into the table in any free space big enough to hold the row.

In both cases the smallest unit of storage and access is a Page. Every piece of data SQL Server needs to store on disk is organized in blocks called pages. Each page is exactly 8192 Bytes in size. The only exception to this rule are the log files. Log data is stored in a structure called virtual log file and those are not part of today's discussion.

Indexes created on a Heap use a physical address to point from the index record to the underlying data record. This address consists of the file number, the number of the page that contains the record within that file and the slot number inside that page.

Forwarded Records

When a record with variable length data type columns is updated, its size might increase. If the page it is stored in is filled already, the updated record might need to be moved to a new page. If that happens, all pointers in any index on that table addressing the moved row now point to the wrong (old) place. There are two ways to handle this situation: Update all indexes with the new location or put a special record in the old place that "forwards" to the new location.

Going through all existing indexes can be extensively resource intensive. For that reason SQL Server is using the second option to put a forwarding record in the old place.

Such a forwarding record links to the new physical address of the actual data. The new record also contains a pointer back to the forwarding record. This back-pointer allows the forwarding record to get updated in case the data record has to move again or in case it gets deleted which causes the forwarding record to be deleted as well. Updating the forwarding records in cases of repeated data record moves prevents long forwarding chains. Instead there is always only at max on hop.

In the case of an RID-Lookup SQL Server has to follow those forwarding records to get to the actual data record. This causes an additional (logical) page read for each forwarding record encountered.

During a table scan on the other hand each page will be read anyway so SQL Server could just ignore all forwarding records. That however could cause reading inconsistencies. Rows that are updated after the scan started and before it finishes could be missed entirely by the scan, if the record gets moved to a page that was scanned already. For that reason SQL Server follows each forwarding record immediately. This not only causes an additional read right there, is also turns the sequential read of all pages in allocation order into a succession of random reads, hurting performance potentially even more.

Demonstration

To demonstrate this behavior let us set up two tables dbo.ForwardSmall and dbo.NoForwardSmall as well as a Procedure dbo.ChangeRow to help with the creation of forwarded records:

IF OBJECT_ID('dbo.ForwardSmall') IS NOT NULL DROP TABLE dbo.ForwardSmall;
CREATE TABLE dbo.ForwardSmall(Id INT ,F VARCHAR(8000))
GO
IF OBJECT_ID('dbo.NoForwardSmall') IS NOT NULL DROP TABLE dbo.NoForwardSmall;
CREATE TABLE dbo.NoForwardSmall(Id INT ,F VARCHAR(8000))
GO

IF OBJECT_ID('dbo.ChangeRow') IS NOT NULL DROP PROCEDURE dbo.ChangeRow;
GO
CREATE PROCEDURE dbo.ChangeRow
@Id INT,
@Size INT
AS
BEGIN
  SET NOCOUNT ON;
  MERGE dbo.ForwardSmall fs
  USING (SELECT @Id,REPLICATE('X',@Size)) x(Id,F)
  ON fs.Id = x.Id
  WHEN MATCHED THEN
    UPDATE SET fs.F = x.F
  WHEN NOT MATCHED THEN
    INSERT (Id,F)VALUES(x.Id,x.F);
END
GO

The procedure will take an Id parameter as well as a size parameter. It uses to merge command to either insert or update the record with the given Id value. The F column will be valued with a string of length @Size.

The following script will create 16 rows in the table dbo.ForwardSmall and then update those records to create forwarded records.

EXEC dbo.ChangeRow 11,900;
EXEC dbo.ChangeRow 12,900;
EXEC dbo.ChangeRow 13,900;
EXEC dbo.ChangeRow 14,900;
EXEC dbo.ChangeRow 15,900;
EXEC dbo.ChangeRow 16,900;
EXEC dbo.ChangeRow 17,900;
EXEC dbo.ChangeRow 18,900;

EXEC dbo.ChangeRow 21,900;
EXEC dbo.ChangeRow 22,900;
EXEC dbo.ChangeRow 23,900;
EXEC dbo.ChangeRow 24,900;
EXEC dbo.ChangeRow 25,900;
EXEC dbo.ChangeRow 26,900;
EXEC dbo.ChangeRow 27,900;
EXEC dbo.ChangeRow 28,900;
--------------------------
EXEC dbo.ChangeRow 27,0;
EXEC dbo.ChangeRow 28,0;
EXEC dbo.ChangeRow 18,1800;
EXEC dbo.ChangeRow 28,900;
EXEC dbo.ChangeRow 18,900;

EXEC dbo.ChangeRow 26,0;
EXEC dbo.ChangeRow 17,1800;
EXEC dbo.ChangeRow 27,900;
EXEC dbo.ChangeRow 17,900;

EXEC dbo.ChangeRow 25,0;
EXEC dbo.ChangeRow 16,1800;
EXEC dbo.ChangeRow 26,900;
EXEC dbo.ChangeRow 16,900;

EXEC dbo.ChangeRow 24,0;
EXEC dbo.ChangeRow 15,1800;
EXEC dbo.ChangeRow 25,900;
EXEC dbo.ChangeRow 15,900;


EXEC dbo.ChangeRow 23,0;
EXEC dbo.ChangeRow 14,1800;
EXEC dbo.ChangeRow 24,900;
EXEC dbo.ChangeRow 14,900;

EXEC dbo.ChangeRow 22,0;
EXEC dbo.ChangeRow 13,1800;
EXEC dbo.ChangeRow 23,900;
EXEC dbo.ChangeRow 13,900;

EXEC dbo.ChangeRow 21,0;
EXEC dbo.ChangeRow 12,1800;
EXEC dbo.ChangeRow 22,1499;
EXEC dbo.ChangeRow 12,780;

EXEC dbo.ChangeRow 11,910;

EXEC dbo.ChangeRow 22,900;
EXEC dbo.ChangeRow 21,910;

Each record starts out with ~950 bytes, so the 16 records will fit into two pages. From there the records are updated in a way so that each of them ends up on the other page with a forwarding record in its old place.

Now lets put the exact same data into the dbo.NoForwardSmall table:

INSERT INTO dbo.NoForwardSmall
SELECT * FROM dbo.ForwardSmall;

This again requires two pages, but this time no forwarded records are created.

To confirm, we can use the following query using the sys.dm_db_index_physical_stats DMF:

SELECT OBJECT_SCHEMA_NAME(object_id)+'.'+OBJECT_NAME(object_id) Tbl,index_type_desc,alloc_unit_type_desc,page_count,record_count,forwarded_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'detailed') ps
WHERE object_id IN (OBJECT_ID('dbo.ForwardSmall'),OBJECT_ID('dbo.NoForwardSmall'));

This query returns the number of pages, the total number of records and the number of forwarded records for each table:

Tbl	index_type_desc	alloc_unit_type_desc	page_count	record_count	forwarded_record_count
dbo.ForwardSmall	HEAP	IN_ROW_DATA	2	32	16
dbo.NoForwardSmall	HEAP	IN_ROW_DATA	2	16	0

Now let us look at what happens during a table scan:

SET STATISTICS IO ON;
SELECT * FROM dbo.NoForwardSmall;
GO
SELECT * FROM dbo.ForwardSmall;
SET STATISTICS IO OFF;

This produces the following output:

(16 row(s) affected)
Table 'NoForwardSmall'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(16 row(s) affected)
Table 'ForwardSmall'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Scanning the dbo.NoForwardSmall table caused 2 logical reads - one for each page of the table. Scanning the dbo.ForwardSmall table however caused 18 logical reads. That is almost ten times more. Each of the 16 records caused an additional page read.

Conclusion

This article showed that SQL Server follows each forwarding record it encounters right away. This not only happens in the case of an RID-Lookup but also in the case of a table scan. These additional reads can present a significant performance impact. If you have a decently sized table with variable length columns and with regular update activity, add this behavior to the list of reasons why the table really should have a clustered index.

Transaction Management

The question where in the call stack a transaction should be managed, is similar to the one where errors should be handled. There are several strong reasons to push the transaction management as high up the stack as possible. The most obvious reason is the lack of support for nested transactions in SQL Server.

SQL Server does allow to nest BEGIN TRANSACTION ... COMMIT blocks within each other. However, only the outermost layer actually opens and closes a transaction. In the inner layers SQL Server just maintains a counter; only the outermost COMMIT has any effect on the data.

With that structure in place, SQL Server has no way to do a partial rollback, so if a rollback is requested, everything is rolled back to the beginning of the outer most transaction.

If you have code, that does some data changes followed by a procedure call followed by other data changes, all within a transaction, and the procedure decides to execute a rollback, the rest of the data changes after the procedure call will execute outside of any transaction. Among other catastrophies this can cause inconsistencies in your data and it will prevent another rollback later.

SQL Server realizes this to be a mayor issue. It raises an error anytime the transaction count before and after the execution of a procedure do not match. You can try this out with this code snippet:

IF OBJECT_ID('dbo.DoRollback') IS NOT NULL DROP PROCEDURE dbo.DoRollback;
GO
CREATE PROCEDURE dbo.DoRollback
AS
BEGIN
  BEGIN TRAN
  ROLLBACK;
  RETURN 0;
END;
GO

RAISERROR('Calling dbo.DoRollback outside of transaction',0,1)WITH NOWAIT;
EXEC dbo.DoRollback; -- No Error

GO

RAISERROR('Calling dbo.DoRollback inside of transaction',0,1)WITH NOWAIT;
BEGIN TRAN;
EXEC dbo.DoRollback; -- Error

Executing the procedure after starting a transaction causes an error like this:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

That error however does not prevent execution of additional code. The following example demonstrates that by calling the above procedure within another procedure:

IF OBJECT_ID('dbo.CallDoRollback') IS NOT NULL DROP PROCEDURE dbo.CallDoRollback;
GO
CREATE PROCEDURE dbo.CallDoRollback
AS
BEGIN
  BEGIN TRAN;
  EXEC dbo.DoRollback;
  RAISERROR('--Inside dbo.CallDoRollback after calling dbo.DoRollback',0,1)WITH NOWAIT;
  IF(@@TRANCOUNT>0)ROLLBACK;
  RETURN 0;
END;
GO

RAISERROR('Calling dbo.CallDoRollback',0,1)WITH NOWAIT;
EXEC dbo.CallDoRollback;

So the conclusion here is, that a stored procedure should never execute a rollback. Instead it should raise an error to signal to the outer layer that a situation occurred that requires some mediation like a rollback.

Partial Rollbacks

Every once in while you need to code a procedure that needs to undo its own actions independent of the circumstances it was called in. That can be achieved with a partial rollback.

While SQL Server does not support nested transactions, it allows you to set a save-point within an open transaction. After setting a safe-point you can then rollback all changes that occurred after it. The DoCorrectRollback procedure demonstrates how you can use that functionality in your procedures.

IF OBJECT_ID('dbo.DoCorrectRollback') IS NOT NULL DROP PROCEDURE dbo.DoCorrectRollback;
GO
CREATE PROCEDURE dbo.DoCorrectRollback
AS
BEGIN
  DECLARE @ErrorStatus INT; SET @ErrorStatus = 0;
  DECLARE @TranName VARCHAR(32);SET @TranName = REPLACE((CAST(NEWID() AS VARCHAR(36))),'-','');

  BEGIN TRAN;
  RAISERROR('Setting savepoint: %s',0,1,@TranName)WITH NOWAIT;
  SAVE TRAN @TranName;
 
  BEGIN TRY
    EXEC dbo.DoError;
  END TRY
  BEGIN CATCH
    DECLARE @msg NVARCHAR(MAX);SET @msg = ERROR_MESSAGE();
    RAISERROR('Caught this error: %s',0,1,@msg)WITH NOWAIT;
    SET @ErrorStatus = -1
  END CATCH;

  IF(@ErrorStatus != 0)
  BEGIN
    RAISERROR('Rolling back to savepoint: %s',0,1,@TranName)WITH NOWAIT;
    ROLLBACK TRANSACTION @TranName;
  END;
  
  COMMIT;
  
  RETURN 0;
END;
GO

RAISERROR('Calling dbo.DoCorrectRollback outside of transaction',0,1)WITH NOWAIT;
EXEC dbo.DoCorrectRollback;

GO

RAISERROR('Calling dbo.DoCorrectRollback inside of transaction',0,1)WITH NOWAIT;
BEGIN TRAN
  EXEC dbo.DoCorrectRollback;
COMMIT;
GO

The procedure first executes a BEGIN TRANSACTION to make sure that there is an active transaction. (Remember, this does not have any effect inside a preexisting transaction.) Next, the procedure sets a save-point. A save-point requires a unique name that can be up to 32 characters long. The procedure uses the NEWID() function to get this name. NEWID() returns 36 characters of which 4 are a '-', so by removing them we end up with the required 32 characters.

After this transaction setup section the actual code is executed inside a TRY...CATCH block. If an error is thrown, the error handling code causes a ROLLBACK to be executed. The ROLLBACK TRANSACTION statement takes the save-point name as parameter, which causes the rollback to undo only actions that happened after the save-point was set - the beginning of the procedure in our case.

This ROLLBACK to the previously set save-point does not change the open transaction counter at all, so before the procedure returns it needs to execute a commit to bring the transaction counter back to the state it was in at the beginning of the procedure. This step has to be taken independently of the ROLLBACK being executed or not. It should therefore be the last statement of the procedure.

This allows the procedure to be called inside and outside of a transaction without causing adverse effects by executing a rollback.

Limitations

SQL Server allows to set several save-points within one active transaction as long as they have unique names. If a name is reused the existing save-point is moved to the current position and no error is thrown, so be sure to use unique names as shown in the example.

Another limitation is, that save-points cannot be used in distributed transactions spanning more than one SQL Server instance. If you try to set a save-point in a distributed transaction an error is thrown.

Lastly, the example given does not notify the outer layers about the existence of a problem. This should certainly be part of the design of your procedures. There are several ways to achieve this, for example by raising an error after the rollback step that contains the reason for the rollback.

In this session we will examine one of SQL Servers' most fundamental yet most important functions - how does SQL Server store data on disk and how does it find it again when executing a query. Using a combination of demos, lecture and discussions we will begin with the structure of the data on disk by delving into the world of HoBTs and Extends, RIDs and Keys and especially Pages. From there we will tackle topics concerning the retrieval of data such as Index levels, Index Scans and Seeks and Lookups.

Downloads

• Slides
• TableInfo & IndexInfo

Other Resources

• SQL Sentry Plan Explorer
• Microsoft® SQL Server® 2008 Internals (Pro - Developer)

I thought that request was an excellent way to practice my XML skills so I spend a little time to come up with a solution.

For testing purposes I first created a few tables in an empty database:

DECLARE @cmd NVARCHAR(MAX) ;
SET @cmd = ( SELECT 'CREATE TABLE T' + CAST(n AS NVARCHAR(MAX))
                    + '(id INT IDENTITY(1,1)'
                    + (SELECT   ',c' + CAST(c.n AS NVARCHAR(MAX))
                                + ' INT DEFAULT ' + CAST(c.n AS NVARCHAR(MAX))
                                + '*' + CAST(t.n AS NVARCHAR(MAX))
                       FROM     dbo.GetNums(( t.n - 1 ) % 10 + 1) c
             FOR    XML PATH('') ,
                        TYPE).value('.', 'NVARCHAR(MAX)') + ');INSERT INTO T'
                    + CAST(n AS NVARCHAR(MAX)) + ' DEFAULT VALUES;'
             FROM   dbo.GetNums (3000) t
FOR XML PATH('') ,
        TYPE).value('.', 'NVARCHAR(MAX)') ;

EXEC(@cmd) ;

This snippet uses Itzik's GetNums function to generate 3000 CREATE TABLE and INSERT statements.

With the table in place I was able to come up with this solution to the original question:

DECLARE @cmd NVARCHAR(MAX) ;
SET @cmd = 'SELECT * FROM('
    + STUFF(
(SELECT ' UNION ALL SELECT ''' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
        + '.' + QUOTENAME(name) + ''' [@name],' + '(SELECT * FROM '
        + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name)
        + ' FOR XML PATH(''row''),TYPE) data'
 FROM   sys.tables
    FOR     XML PATH('') ,
                TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '')
    + ')X FOR XML PATH(''table''),TYPE;' ;

EXEC(@cmd) ;

The script builds a single statement. For each table it selects the table name in a column with the name [@name] and the content of that table as XML (using a sub-select) in a column with the name data. All these SELECTs are "UNION ALL"ed together and formated into the final XML document with an additional outer SELECT FOR XML.

If you are only after some of the tables, you can add a where clause in row 8 of the above snippet.

The resulting XML has this format:

<table name="[dbo].[T1628]">
  <data>
    <row>
      <id>1</id>
      <c1>1628</c1>
      <c2>3256</c2>
      <c3>4884</c3>
      <c4>6512</c4>
      <c5>8140</c5>
      <c6>9768</c6>
      <c7>11396</c7>
      <c8>13024</c8>
    </row>
  </data>
</table>
<table name="[dbo].[T2102]">
  <data>
    <row>
      <id>1</id>
      <c1>2102</c1>
      <c2>4204</c2>
    </row>
  </data>
</table>

For each table there is a <table> tag that has a name attribut with the table name. It contains a <data> tag that in turn contains one <row> tag for every row in the table.

The only thing missing to make this valid XML is a root tag, but that should be easy to add.]]> http://sqlity.net/en/577/selecting-the-entire-database-as-xml-string/feed/ 3 http://sqlity.net/en/577/selecting-the-entire-database-as-xml-string/