I am publishing this Blog on behalf of Hermann Daeubler, our SAP expert

Scenario:

Let’s assume we have a table consisting of five partitions and the one in the middle needs to  be dropped. In SQL Server we need the following steps to accomplish this task

a)      Create a non-partitioned target table with the same structure and indexes as the partitioned table

b)       Use the switch command to move the data from the middle partition to the new target table. Now the original partition is empty but still visible and the old boundary value isn’t removed

c)       Complete the task by altering the partition function by merging the empty partition

Problem:
The partition switch command is a metadata-only operation and is frequently used by customers
to add (assuming staging table has already been populated) or remove partition data almost instantaneously. This is a significant improvement over non-partitioned tables where these operations can take time proportional to the size of the data.

However, there are issues with partition merging where depending on the hardware and the size
of the partitions, the step “c)” can still take much longer than expected especially when merging partitions in the middle of a table.

For details on partitions, please refer to
http://msdn.microsoft.com/en-us/library/dd578580.aspx
http://msdn.microsoft.com/en-us/library/ms190787.aspx

 

Why partition merge is slow?

It is a common scenario to put partitions on different file groups. The decision was made to make the
partition function merge a deterministic function. This means that there is a fixed rule which defines
on which file group the merged partition will end up. As a result the merge will be always in the same
direction corresponding to the type (right or left). This approach has the downside that there is no
optimization related to the size of the partitions. In a scenario where the source partition has 10 million rows and the destination partition has only 1 million rows, SQL Server would therefore move
the 10 million rows of the source partition rather than moving the 1 million rows from the destination partition. The same would happen if the destination partition is completely empty as in
the sample above where one would like to drop a partition.

There is a workaround for this when the partitions to be merged are mapped to the same filegroup. In the example above where the intention is to drop a partition it is clear that the destination partition is always empty.  The workaround is to

·         Switch both partitions out – the source as well as the destination partition

·         Merge the partition function

·         Switch back in the source partition.

But this workaround has a side-effect. It temporarily removes data from the table and an application using this approach has to provide a proper cleanup or rollback in case something goes wrong

Solution with the changes in SQL Server 2008R2:

CU6 for SQL Server 2008 SP1 includes a fix for this potential issue if all partitions reside on the same
file group. It checks which partition has less rows and moves the data from this one during a partition
function merge. This also covers the special case where one partition is empty.
In case the performance of dropping partitions in the middle of a table is essential for an application
it’s necessary to place all partitions on one single file group to take advantage of the fix in CU6.

Results :

The screenshots below and the SQL Script further down use a simple example to show how the
workaround basically could be implemented. It also shows the difference after installing CU6 for
SQL Server 2008 SP1.

Figure 1 above shows that a test table was created with five partitions and five million rows in each
               partition.

Figure 2 above shows that the standard way to get rid of a partition took over 2 minutes on the test system.
               There were obviously millions of logical reads involved due to the move of the data during
               the partition function merge

Figure 3 above shows that afterwards boundary value 3 is missing as expected and four partitions are left

 

Figure 4 above shows the result of the workaround. Due to the additional switch more tables are involved.
               But overall we see just a few logical reads and the whole process is done within a second
               compared to over 2 minutes before

Figure 5 above shows the output of the standard method after installing CU6 for SQL Server 2008 SP1.
                There is only one switch necessary and it finishes like the workaround before in a second.

 

To run the SQL Script below, you need to follow the following steps in this sequence

·         First create the four procedures

·         Run the procedures one after the other as seen at the end

·          Create the test table as it’s necessary to specify how many rows should be generated.   It’s a  
 very basic algorithm which simply doubles the number of records 10 times.  A parameter of
 value 25000 results in 25 million rows – five million rows per partition.

 

-- Procedure to create a partitioned test table

CREATE PROCEDURE create_partition_function_merge_test_tables ( @nr_rows INT )
AS
BEGIN

    DECLARE @counter int

    SET NOCOUNT ON

    IF EXISTS( select * from sys.objects
    WHERE name ='partition_function_merge_test_table' and type = 'U' )
        DROP TABLE partition_function_merge_test_table

    IF EXISTS( select * from sys.partition_schemes
    WHERE name = 'PARTITION_FUNCTION_MERGE_TEST_SCHEME' )   
        DROP PARTITION SCHEME [PARTITION_FUNCTION_MERGE_TEST_SCHEME]      

    IF EXISTS( select * from sys.partition_functions
    WHERE name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' )   
        DROP PARTITION FUNCTION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION]

 

    CREATE PARTITION FUNCTION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION] ( int )
    AS
    RANGE RIGHT FOR VALUES ( 1,2,3,4,5 )   

    CREATE PARTITION SCHEME [PARTITION_FUNCTION_MERGE_TEST_SCHEME]
    AS PARTITION [PARTITION_FUNCTION_MERGE_TEST_FUNCTION]
    ALL TO ( [PRIMARY] )

   
    CREATE TABLE partition_function_merge_test_table
    (
        key_partkey  int NOT NULL,
        key_col1     int,
        key_col2     int,
        data_col1    char(15),
        data_col2    char(15),

      data_col3    char(15)
     ) ON [PARTITION_FUNCTION_MERGE_TEST_SCHEME] ( key_partkey )

     CREATE CLUSTERED INDEX PFM_IDX1 on partition_function_merge_test_table ( key_partkey )

      SET @counter = @nr_rows

     WHILE ( @counter > 0 )
     BEGIN

         INSERT INTO partition_function_merge_test_table VALUES
         ( @counter%5,@counter,@counter,
         'Testdata Col1','Testdata Col2','Testdata Col3' )

         SET @counter = @counter – 1
     END

 

     SET @counter = 10

     WHILE ( @counter > 0 )

     BEGIN

         INSERT INTO partition_function_merge_test_table
         SELECT * from partition_function_merge_test_table

         SET @counter = @counter – 1
     END

 

     CREATE INDEX PFM_IDX2 on partition_function_merge_test_table ( key_col1 )
     CREATE INDEX PFM_IDX3 on partition_function_merge_test_table ( key_col2 )
     CHECKPOINT

END
GO       

 -- Procedure to drop the partition in the middle via switch and merge command

 CREATE PROCEDURE drop_partition_3_slow
AS
BEGIN

    IF ( OBJECT_ID('temp_partition') IS NOT NULL )
    BEGIN
        DROP TABLE temp_partition;
    END

 

    CREATE TABLE temp_partition
    (   key_partkey  int NOT NULL,
        key_col1     int,
        key_col2     int,
        data_col1    char(15),
        data_col2    char(15),
        data_col3    char(15)
    )

 

    CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition ( key_partkey )
    CREATE INDEX PFM_IDX2 on temp_partition ( key_col1 )
    CREATE INDEX PFM_IDX3 on temp_partition ( key_col2 )

    SET STATISTICS IO ON

    ALTER TABLE partition_function_merge_test_table switch PARTITION 3 TO temp_partition
    ALTER PARTITION FUNCTION PARTITION_FUNCTION_MERGE_TEST_FUNCTION ()
    MERGE RANGE (3)

    SET STATISTICS IO OFF

END
GO

 

 -- Procedure to drop the partition in the middle via switch and merge command.
-- This time it will also switch out the "neighbor" before the merge and switch
-- it back in after the merge. This will avoid an unnecessary copy of the rows
-- in the neighbor partition

CREATE PROCEDURE drop_partition_3_fast
AS
BEGIN

    IF ( OBJECT_ID('temp_partition') IS NOT NULL )
    BEGIN
        DROP TABLE temp_partition;
    END

    IF ( OBJECT_ID('temp_partition2') IS NOT NULL )
    BEGIN
        DROP TABLE temp_partition2;
    END

 

    CREATE TABLE temp_partition
    (   key_partkey  int NOT NULL,
        key_col1     int,
        key_col2     int,
        data_col1    char(15),
        data_col2    char(15),
        data_col3    char(15)
    )

    CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition ( key_partkey )
    CREATE INDEX PFM_IDX2 on temp_partition ( key_col1 )
    CREATE INDEX PFM_IDX3 on temp_partition ( key_col2 )

 

    CREATE TABLE temp_partition2
    (   key_partkey  int NOT NULL,
        key_col1     int,
        key_col2     int,
        data_col1    char(15),
        data_col2    char(15),
        data_col3    char(15)
    )

    CREATE CLUSTERED INDEX PFM_IDX1 on temp_partition2 ( key_partkey )
    CREATE INDEX PFM_IDX2 on temp_partition2 ( key_col1 )
    CREATE INDEX PFM_IDX3 on temp_partition2 ( key_col2 )

    SET STATISTICS IO ON

    ALTER TABLE partition_function_merge_test_table switch PARTITION 3 TO temp_partition
    ALTER TABLE partition_function_merge_test_table switch PARTITION 4 TO temp_partition2

    ALTER PARTITION FUNCTION PARTITION_FUNCTION_MERGE_TEST_FUNCTION ()
   MERGE RANGE (3)

    ALTER TABLE temp_partition2 ADD CONSTRAINT partCheck_constr CHECK ( key_partkey = 3 )
    ALTER TABLE temp_partition2 switch TO partition_function_merge_test_table PARTITION 3

    SET STATISTICS IO OFF

END
GO

 -- Procedure to list the partitions of the test table

CREATE PROCEDURE list_partitions
AS
BEGIN

 

    DECLARE @nr_partitions int
    DECLARE @counter int
    DECLARE @partrows int
    DECLARE @boundary_value char(10)

     SET @nr_partitions = ( SELECT COUNT(*) FROM sys.partition_range_values prv,
         sys.partition_functions pf  WHERE pf.function_id = prv.function_id  AND  
        pf.name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' )

    PRINT 'Partitions for table partition_function_merge_test_table :'

    PRINT ''

    PRINT 'nr | boundary value | # rows in partition'

    SET @counter = 1

    WHILE ( @counter <= @nr_partitions )
    BEGIN

        SET @boundary_value = CONVERT(CHAR(10),

        (SELECT value FROM sys.partition_range_values prv, sys.partition_functions pf

        WHERE pf.function_id = prv.function_id AND pf.name = 'PARTITION_FUNCTION_MERGE_TEST_FUNCTION' AND

        boundary_id = @counter ))

 

        SET @partrows=(SELECT p.rows FROM sys.partitions p, sys.objects o
        WHERE p.object_id = o.object_id AND o.name = 'partition_function_merge_test_table' AND
        p.partition_number = @counter AND index_id IN (0,1) )

        PRINT ' ' + rtrim(convert(char(5),@counter)) + ' |' +
              '              ' + rtrim(@boundary_value) + ' |' +
              '           ' + convert(char(20), @partrows)

        SET @counter = @counter + 1

    END
END
GO

 

 -- Repro to show the performance impact of different ways to drop a partition.
-- The procedure to drop the partition will turn "statistics io" on to prove
-- by the number of logical reads on the test table that in the first case
-- data will be moved whereas in the second case it won't.
-- This will change by installing CU6 for SQL Server 2008 SP1 which fixes this
-- behavior. While the runtime of the workaround was always the same the standard
-- approach to drop a partition in the middle of a table improved from over 2 minutes
-- to 1 second after installing CU6 for SQL Server 2008 SP1 – as shown on the
-- screenshots above. The difference is of course dependent on the hardware
-- configuration and might be even more or also less on other systems.

 

 execute create_partition_function_merge_test_tables 25000
execute list_partitions
execute drop_partition_3_slow
execute list_partitions

 execute create_partition_function_merge_test_tables 25000
execute list_partitions
execute drop_partition_3_fast
execute list_partitions

 

When you compress an object (index or table or partition), there are two components of space savings that you get. First component is fragmentaton (i.e. the original object might have been fragmented). The object gets degragmented as part of doing compression so you get some space savings. Second component is actual data compression savings. Many customers have asked the question 'Is there a way to know how much savings do I get from each of the two components?".

Interestingly, starting with SQL2008R2, you indeed have this capability. Here is one example where I create a table t1 and then simulate fragmentation by updating a column. Once the table is fragemented, I can use the sp_estimate_data_compression stored procedure to determine how much space savings I can get by defragmenting it.

drop table t1

go

 

-- Create the table

create table t1 (c1 int, c2 char(100), c3 varchar (300))

go

 

-- create a clustered index 

create clustered index t1_ci on t1(c1)

 

-- load 10000 rows. Note,  that for column C3, I am storing full-values that

-- take all 300 characters

declare @i int

select @i = 0

 

while (@i < 10000)

begin

     insert into t1 values (@i, 'hello', replicate ('1', 300))

     select @i = @i + 1

end

 

-- Now let us measure the average length of the row. It comes to 417 bytes.

-- This means that there are 19 rows per page

 

select avg_record_size_in_bytes

from sys.dm_db_index_physical_stats (db_id('TEMPDB'),

object_id('T1'),

                   null, null, 'SAMPLED')

where object_name (object_id) like 't1' AND index_id =1

 

 

-- check the space usage. This shows the space usage of 4296 KB

sp_spaceused t1

 

-- This update will cause fragmentation because I am changing the column C3 from 300

-- chars to 50 chars. With the result, the average row length

-- will now become 167 but the SQL Server will still keep 19 rows/page which

-- leaves more that 50% unused space on each page

update t1 set c3 = REPLICATE ('2', 50)

 

-- check the space usage. It shows that the allocated or reserved space

-- has not changed

  sp_spaceused t1

 

-- estimate how much space you can save by defragmentation. Note

-- here I am executing the stored procedure on an 'uncompressed' table

-- with target compression setting is 'NONE',(i.e. no compression). 

                                   

exec sp_estimate_data_compression_savings 'dbo',

                     'T1',1, NULL, 'none'

 

-- This stored proc returns

 

object_name    schema_name index_id    partition_number

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

T1              dbo          1           1               

 

 

size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB)

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

4480                                      1720     

 

-- To verify this, I rebuild the index and see the space consumption             

alter index t1_ci on t1 rebuild

 

-- check the space usage. It shows that the table now takes 1744KB

  sp_spaceused t1

 

In SQL2008RTM, the stored procedure did not compute the space savings if the source and target compression settings were same. In otherwords, the stored proc would have returned the following instead

 

size_with_current_compression_setting(KB) size_with_requested_compression_setting(KB)

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

4480                                      4480 

 

 

Interestingly, you can use it to get tell-tale signs to know if ROW compressed table already takes advantage of unicode compression in SQL2008R2. Let us say customer was using ROW compression in SQL2008 and now upgrades to SQL10.5, s/he can use to the sp_estimate_data_compression_savings stored proc by specifying target compression to be ROW as well. Note, in this case, the compression savings will include the potential savings from data defragmentation as well so one can’t easily deduce that the savings are from Unicode compressions or due to both. However, customer can  get some clues by referring to the sys.dm_db_index_physical_stats DMV to see if the object in question was fragmented or not.

 

Thanks

Sunil

 

In my previous blog, I had mentioned that unicode compression will be available as part of next SQL Server release named SQL 2008R2. You can down load the CTP2 version http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx  to play with it. This is a new exciting addition to the data compression offering. 

Let me describe the problem and how it has been addressed in SQL Server.

Problem: As more and more businesses developing global customer base, applications are being developed/modified to use unicode based data types such as NCHAR and NVARCHAR. SQL Server uses UCS-2 encoding scheme that takes two bytes of storage regardless of the locale. For example, in ASCII character set when stored as NCHAR, each character only needs 1 byte of storage but it is stored using 2 bytes with the significant byte being 0. In fact most European languages need only 1 byte of storage. When an application is either converted or written to user unicode based data types, it can, depending on the size of strings, increase the storage requirements significantly. 

Solution: SQL Server implements unicode compression using SCSU (Simple Compression Schecme for Unicode Data), a standard compression scheme for Unicode data (http://unicode.org/reports/tr6/). The following table shows the compression achieved with SCSU and compares it with UTF-8 as both DB2/Oracle have implemented UTF-8 encoding for unicode data. You will notice that for English (i.e. ASCII) we are compressing 50% or in other words, we will take 1 byte instead of 2. Same is true for German locale.  On the other hand for Korean locale, SCSU scheme takes 2 bytes so there is no compression savings. The compression achieved using SCSU is very comparable to UTF-8.

Locale	SCSU	UTF-8
English	0.5	0.5
Japanese	.85	1.0
Korean	1.0	1.0
Turkish	.52	.53
German	.5	.5
Vietnamese	0.61	0.68
Hindi	0.5	1.0

 

The Unicode compression is enabled or disabled as part of ROW or PAGE compression. The implications are

1.        The customers who are already using ROW or PAGE compression will get the benefit of unicode compression but it will require them to rebuild the index or table after the upgrade to SQL 2008R2. The interesting thing is that the Unicode compression will kick in when a new row inserted or an existing Unicode value is modified even if the corresponding compressed index or the table is not immediately rebuilt after the upgrade.

2.        Existing scripts used for data compression can be used without modifications

 

If NCHAR or NVARCHAR string does provide any compression savings with unicode compression, like the example with korean locale, unicode compression is not applied. For this case, the compression overhead is paid at the time of compression to know if we can get unicode compression savings but not when the value is read. Like before no application changes are needed to take advantage of unicode compression.

 

At this time, the SQL Server will not support Unicode compression for NVARCHAR (MAX) type, including in-row values or for NTEXT. This is something we will consider in the future. In my next blog, I will provide an example using AdventureWorksDW database to show you the compression benefits achieved using new improved ROW compression.

Thanks

Sunil Agarwal

It has been a while since I blogged about data compression so I thought it will be good to provide an update on data compression usage within SQL Server community. I am happy to say that the Data compression feature has been a tremendous success in SQL Server 2008 with customers.  Many customers have been able to reduce the size of their database significantly leading to reduction in the cost of hardware and storage management. The table below shows the actual space savings that some of the customers have achieved with ROW and PAGE compression in their production environment. Each row in the table below represents a different customer. Couple of key observations; First, the space savings can be huge. For example, one customer has been able to achieve 81% space savings with PAGE compression. Second, not surprisingly, the PAGE compression provides better compression at the cost of additional CPU cycles. As I had emphasized in my earlier blogs, the space savings achieved will depend on the schema and the data distribution.

Data Compression Space Savings	Notes
70%	PAGE. Data Warehouse application.
40%	PAGE. OLTP Web application.
62%	PAGE. DW application.
38%, 21%	PAGE, ROW.
80%, 50%	PAGE, ROW.
52%	PAGE.
50%, 15%	PAGE, ROW.
81%	PAGE. ERP application.
35%	PAGE.

 

Now, this may all sound good, but one question that keep coming back to me is 'Well space savings is alright, but how does data compression impact the performance of the workload?'. I agree we can all create best/worst cases to make our point but the real answer is know how data compression has impacted the real production applications. The table below shows the performance impact of data compression (the green color shows the improvement in the performance while the red color shows the performance degradation).  Each row in the table below represents a different customer.

Performance impact	Notes
5%	PAGE compression. OLTP Web application. Large volume of transactions.
40%-60%	PAGE compression. Large sequential range queries. A DW Application (IO intensive)
1%	PAGE compression. 500 users, 1500 Transactions / sec. OLTP with some reporting queries.
11%	PAGE compression. A lot of insert, update and delete activity which leads to increase in CPU usage. A better choice would have been to go with ROW compression.
2% - 3%	PAGE compression. OLTP Application.
3%	PAGE compression. ERP application – small transactions.

 I am hoping that this has provided some excitement to customers on the sideline. I would like to encourage trying out the feature and seeing for yourself if this is right for your environment.  I will also encourage you to read some of the white papers listed below

http://www.unisys.com/eprise/main/admin/corporate/doc/41371394.pdf

http://h71028.www7.hp.com/ERC/downloads/4AA1-8766ENW.pdf?jumpid=reg_R1002_USEN

http://www.microsoft.com/casestudies/casestudy.aspx?casestudyid=4000002956

http://www.microsoft.com/downloads/details.aspx?FamilyID=b5bb47a4-5ece-4a2a-a9b5-5435264f627d&DisplayLang=en

http://sqlcat.com/whitepapers/archive/2009/05/29/data-compression-strategy-capacity-planning-and-best-practices.aspx

Thanks

Sunil Agarwal

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In previous blogs, I discussed how to identify and troubleshoot IO and allocation bottleneck in TempDB. In this blog, I will describe how to indentify DDL bottleneck in TempDB and to troubleshoot it.

It will be useful to understand why DDL bottleneck is most commonly related to TempDB and not in user databases. Well, if you think about user database(s), they are created as part of application design. Once the application is tested and deployed, the schema in user databases does not change except may be to add/drop indexes or when the application is upgraded to a newer version. TempDB on the other hand is quite different and is used as a scratch pad both by user applications to store intermediate data in # and ## tables as well as table variables and also by SQL Server as part of processing queries. Please refer to the http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx  for some background. Now, let us take a simple case where an application creates a #table containing 10 columns in a stored procedure. When this stored procedure is invoked, the SQL Server creates this #table by inserting one row in system table that stores meta-information about tables and 10 rows, each representing 1 column the #table, in the system table storing meta-information about column. There may be other system tables affected, for example when you create index(s) on the #table, but it is clear that at minimum, it will require 11 rows to be inserted into system tables at create time and then these rows to be deleted when the stored procedure terminates. Now for moments assume that this table is not cached and this stored procedure is executed thousands of times by concurrent threads. It will cause a large number of inserts/deletes in system tables in TempDB. I hope that this provides you a good understanding why and how the DDL contention, i.e. blocking when accessing system tables, can happen inside TempDB. Note, it is possible that an application may create another ‘user’ database as a scratch pad. In that case, this database can potentially incur DDL bottleneck but then you may wonder why create a scratch database when TempDB provides optimized logging and easy manageability.

Now let us discuss how to detect DDL contention and what you can do to minimize it.

Diagnosing DDL Contention

As I indicated in the previous paragraph, the DDL contention refers to the contention in pages belonging to system tables. For this specific case, we need to look at PAGELATCH contention in system tables within TempDB. You can use the following query to identify it

select  session_id, wait_duration_ms, resource_description

from sys.dm_os_waiting_tasks

where wait_type like 'PAGE%LATCH_%' and

    resource_description like '2:%'

You may recall that this query is very similar, in fact it is identical, to the DMV query to detect allocation bottleneck. The difference however is that you will need to look at pages other than GAM, SGAM and PFS that are incurring PAGELATCH waits and see, using undocumented DBCC PAGE command, if these pages indeed belong to system table(s) in TempDB. This will show you that you are indeed incurring DDL bottleneck. Now for any performance metrics, you will need to compare this number with baseline when the workload was performing normally and see if you are incurring excessive DDL bottleneck.

You can also look at the following PERFMON counters to see if you are encountering significant change in these numbers. Note, internal objects in TempDB don’t cause DDL contention.

·         Temp Tables Creation Rate The number of temporary table or variables created/sec.

·         Temp Tables For Destruction The number of temporary tables or variables waiting to be destroyed by cleanup system thread.

 

Troubleshooting:

OK, now you have determined that you indeed are encountering DDL bottleneck, what can you do? Clearly, the goal is to minimize creation/destruction of user objects (i.e. #, ## and table variables). Unfortunately, there are no easy fixes. My suggestions are

(1)        Check if the user objects are indeed getting cached. Please refer to  http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx for details on detecting user object caching.

(2)        Look at query plans for queries that are creating/destroying user objects in TempDB and see if you can do something to minimize it. For example, a #table may be created in a loop and maybe it is possible to create it outside the loop.

 

Thanks

Sunil Agarwal

I hope my earlier blogs on TempDB (http://blogs.msdn.com/sqlserverstorageengine/archive/tags/TempDB/default.aspx)  have given you a good background on the objects contained in TempDB and its configuration and have deployed your workload in production and it runs for ever without any glitches. Oh, really? Well, don’t we all wish it was so but as you may have already experienced, perhaps one time too many, that the performance/configuration problems do happen. In fact, most DBAs/Administrators spend 20-30% of the time in troubleshooting the issues. With that context, let us look into key issues with TempDB in production and steps to troubleshoot them.

TempDB is more vulnerable because it is a shared resource and any misbehaving application or adhoc query can probably cause extreme resource issues in TempDB thereby affecting other applications running on the same instance. Here is a list of 4 common issues that you may encounter with TempDB

·         IO bottleneck

·         Allocation Contention

·         DDL Contention

·         Running out of space

I will cover IO bottleneck in this blog and others in my next blogs with the emphasis on how to detect that this indeed is happening and the steps that you can take to address.

IO Bottleneck: Now, the IO bottleneck is nothing specific to TempDB. Any database can incur IO bottleneck if the IO bandwidth is not configured to meet the workload demands. It is possible, and in fact likely, that your workload has changed overtime such the initial configuration is not able to handle it.

When a customer hits an IO bottleneck, it is tempting to conclude that the IO subsystem needs to be upgrades. While this may in fact be the final solution, but it should not be the first and the only step. Here are the recommended actions

1.   Check if you have memory pressure: If you don’t have sufficient physical memory allocated to the SQL Server process, you may incur additional IO that could be avoided. As a first step, you need to make sure you don’t have memory bottleneck by looking at the following counters

a.   Buffer Cache Hit ratio

b.   Page Life Expectancy

c.   Checkpoint pages/sec

d.   Lazywrites/sec

 

I will also advise you to look at DBCC Memorystatus command that gives details on how the memory is being used. You may want to refer to http://support.microsoft.com/kb/907877

 

2.   Identify queries that are generating the most IOs: you can run the following DMV query to identify it. The output of the query shows the top 10 queries (the SQL text and the query plan handle) that are generating the most IOs across all its executions. Agreed that the IOs include IOs in TempDB and also in other databases, but it is a good place to start. You can investigate these queries to identify any bad query plan (perhaps an index was not chosen) and/or possibly look into rewriting the queries that minimize IOs.

 

SELECT TOP 10

    (total_logical_reads/execution_count) AS

                                 avg_logical_reads,

    (total_logical_writes/execution_count) AS

                                 avg_logical_writes,

    (total_physical_reads/execution_count)

                                 AS avg_phys_reads,

    execution_count,

    statement_start_offset as stmt_start_offset,

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

        (CASE WHEN statement_end_offset = -1

            THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

                ELSE statement_end_offset

            END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

         plan_handle

FROM sys.dm_exec_query_stats 

ORDER BY

  (total_logical_reads + total_logical_writes) DESC

 

3.   Once you feel satisfied that you have configured your memory correctly and the query plans are ok, then it makes sense to add more IO bandwidth for TempDB. Now, here is one catch. Say, you add 1 new file to TempDB on a faster disk. Guess what happens? Due to proportional fill algorithm, the access to new file will be skewed in its favor and this may in fact lead to more IO and allocation bottleneck. The recommended action is to add the new file(s) with the same size as existing files and then restart the SQL Server. This will guarantee that the IOs are distributed across all files. Other alternative will be to move all the files in the TempDB to a different disk subsystem.

 

Thanks

Sunil Agarwal

Allocation bottleneck refers to contention in the system pages that store allocation structures. There are three types of pages that store allocation structures as follows

·         PFS (Page Free Space): it tracks the following key information about each page. It uses 1 byte/page and each PFS page stores status for 8088 pages.

o   Free space available

o   If it is allocated or not

o   Has ghost records (when a row is deleted, it is marked as ghost)

·         GAM (Global Allocation Map): Tracks if a uniform extent is free or not. A bit ‘1’ indicates that extent is available for allocation. Each GAM page tracks 64000 extents or 4GB

·         SGAM (Shared Allocation Map): Tracks if an extent is mixed extent or not. A bit ‘1’ indicates that it is a mixed extent and has one or more free pages. Mixed extents are used to allocate first 7 pages of an object. If number of pages in an object is 8 or larges, its pages are allocated from uniform extents. Each SGAM covers 64000 extents or 4 GB

So for example, if you have a database file of size 16GB, it will have 4 GAM pages (each GAM covers 4GB), 4 SGAM pages and 256 PFS pages. It is worth mentioning that each file has its own PFS, GAM and SGAM pages.

Now, let us look at a high level what happens during allocation. A page allocation is done when an object is first created or as part of inserting the row that requires a new page to be allocated. Please note, my intent in the following description is not to describe the allocation algorithm of SQL Server (in fact, the allocation mechanism is much more complex and possibly widely different) but to give you an idea how you can get contention in allocation structures.

·         SQL Server looks at the target extent if there are any pages available for allocation by examining the PFS page under SH latch. If a page is found, it is allocated and the PFS information is updated under UPDATE latch. For HEAPs, but not for Btree, the SQL Server will look into other allocated pages with enough free space to store the new row and then updates the new free space information by taking the UPDATE latch on PFS page.

·         If no such page is found, the SQL Server looks at GAM page under SH latch to find a free uniform extent (assuming the object already has > 8 pages). If no such extent is found, then it looks at the next GAM page and so on. When a free extent is found, the SQL Server takes an UPDATE latch on the GAM page and updates the information.

·         If the object has <= 8 pages, a page from mixed extent needs to be allocated. The SQL Server looks at SGAM page under SH latch and if a mixed extent with one or more free page is found, the page is allocated and the SGAM page is updated by taking the UPDATE latch

Similarly, when page is deallocated or an object is dropped or when a row is deleted (for HEAPs), the allocation structures need to be updated. As you can now imagine, if there is significant allocation/deallocation activity in a SQL Server database, multiple threads may need to wait to acquire X or SH latch in non-conflicting mode which can lead to allocation bottleneck.

Starting with SQL Server 2005, the caching mechanism for objects in TempDB has been improved significantly which will reduce the allocation contention incurred by your workload. you can address SGAM bottleneck by enabling TF-1118. Please refer to Managing TempDB in SQL Server: TempDB Configuration.

 

Thanks

Sunil Agarwal

Now that we know a few things about Version Store, let us now look into its structure to understand how it stores rows from different tables/indexes with different schema. Interestingly, you don’t need to look far and the answer is available when you examine the DMV sys.dm_tran_version_store.

This DMV shows the full logical structure of the version store.  There are two key points to note. First, the version store consists of 8K pages just like data or index pages. These pages exist in the buffer pool and can be flushed to the disk, in this case to the TempDB, under memory pressure. The rows on the version store follow the same rules as any data/index row. Second, the row versions are stored as their full ‘binary’ image, like the way it is stored in the data page’, in the version store.  The binary image is broken into ‘first-part’ and ‘second-part’ which is then combined internally by the SQL Server to interpret it just like it would interpret the actual data/index row according to the schema. This makes the row version storage independent of the owning schema of the object. So a given version store page can have rows from multiple tables and indexes and in fact they can be from any database under SQL Server instance. In other words, the version store is shared among all databases in the SQL Server instance. Just like the pages of a table or an index may need to be kicked out of buffer pool under memory pressure so are the pages of version store. For this reason the version store is backed by persistence in TempDB.

If you look at the fields in the sys.dm_tran_version_store DMV, you will note that the version row stores many other attributes like database-id, row-length etc which is not part of the original data/index row. So you may wonder how does the version of a row with a size of 8060 (max allowed length of a row in SQL Server) stored in the version store as the version store ‘row’ stores many other attributes as described below? Well, the answer is that the data row is broken into 2 rows physically on the version store page but it does not show as such in the DMV because it is ‘virtual’ and shows it as 1 big row.

Here is an example of the contents of version store where transaction with XSN 57 has updated three different rows while a transaction with XSN 58 has updated only 1 row. Note, if a transaction updates the same row multiple times, there is only 1 version created because for other transactions it looks like the first transaction is holding a X lock.

transaction_sequence_num version_sequence_num database_id

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

57                      1                    9          

57                      2                    9          

57                      3                    9          

58                      1                    9          

 

rowset_id            status min_length_in_bytes

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

72057594038321152    0      12                

72057594038321152    0      12                

72057594038321152    0      12                

72057594038386688    0      16                

 

record_length_first_part_in_bytes

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

29                              

29                              

29                              

33                               

 

record_image_first_part                                            

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

0x50000C0073000000010000000200FCB000000001000000270000000000       

0x50000C0073000000020000000200FCB000000001000100270000000000       

0x50000C0073000000030000000200FCB000000001000200270000000000       

0x500010000100000002000000030000000300F800000000000000002E0000000000

 

record_length_second_part_in_bytes    record_image_second_part

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

0                                  NULL

0                                  NULL

0                                  NULL

0                                  NULL

 

In the next blog, I will describe how the pages in the version store are grouped together and the subsequent garbage collection and gotchas

 

Thanks

Sunil Agarwal