Now that we have Unicode compression available in SQL Server 2008R2 as described in, let me take a simple example using AdventureWorksDW database to show you the additional compression that can be achieved on tables with one or more columns of type NCHAR or NVARCHAR.

use [AdventureWorksDW2008]

-- the table FactResellerSales is a FACT table with three columns of type

-- NVARCAHR types. Let us find the average length of each of these columns

select AVG( LEN(salesordernumber))

from FactResellerSales

 

This returns a value of 7. This represents number of character in the string and not the numnber of bytes.

                    

select AVG( LEN(CarrierTrackingNUmber))

from FactResellerSales

 

This returns a value of 12. This represents number of character in the string and not the numnber of bytes.

 

                    

select AVG( LEN(CustomerPONumber))

from FactResellerSales

 

This returns a value of 12. This represents number of character in the string and not the numnber of bytes.

Based on these computations, we have approximately 31 characters of NVARCHAR types. With Unicode compression, we will expect that the average length of the row will reduce further by 31 bytes (i.e. we will save 1 byte per UCS-2 representation). To measure this, we will attach the identical copy of AdventureWorks2008 database to two different instances of SQL Server, one instance of SQL 2008RTM and other instance of SQL 20088R2 and measure the average length of the row after applying the ROW compression. Before applying the ROW compression, let us measure the average length of the row in both instances using the following query

select avg_record_size_in_bytes

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

object_id('dbo.factresellersales'),

                           null, null, 'SAMPLED')

where object_name (object_id) like '%ResellerSales%' AND index_id =1

 

In both instances, the average length of the row comes out to be around 191 bytes. This should not surprise you as there are no changes to the uncompressed data. Now, let us execute the command to enable ROW compression in each of the instances using the following command

 

alter table  dbo.factresellersales rebuild 

 with (

    data_compression = ROW)

 

And, now let us find the average row length in each of the instances. Since Unicode compression is available as part of ROW compression, we will expect that the average length of the row will be around 31 bytes less in SQL2008R2. Let us run the following query again to find the average row length after the row compression

 

select avg_record_size_in_bytes

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

object_id('dbo.factresellersales'),

                           null, null, 'SAMPLED')

where object_name (object_id) like '%ResellerSales%' AND index_id =1

 

Here are the vallues returned

·         Average Row length in SQL2008 after row compression = 132 (around 30% compression)

·         Average Row length in SQL2008R2 after row compression = 100 (around 45% compression). This extra savings of 32 bytes is what we had expected.

 

As you can see that in this example,the unicode comrpession gave us extra 15% compression with ROW compression. We tried the Unicode compression with some in-house customer workloads and we observed the compression savings as shown in the table below. Of course, your savings will depend on your data and the schema but the key point to know is that space savings with Unicode compression can be significant if you consider tables in tera-byte range. In our testing we found that the additional impact on CPU is minimal.

ROW Compression	ROW with UNICODE
9%	43%
30%	53.2%
45%	64%
30%	45%

 

 

I enccourage you to download CTP2 verision (http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx)  of SQL2008R2 and see for yourself the kind of savings you can achieve with your workload.

 

Thanks

Sunil Agarwal

 

As I described in my previous blog, the data compression feature has been very successful. We do appreciate all the feedback that we have received as this provides us a way to know how customers have been using the feature and the challenges they are facing. Based on the feedback, we plan to improve our data compression offering in future releases.  Here is some of the feedack and the respective rationale. Please feel free to contact me for your suggestions on data compression.

(1)    Provide Unicode compression: For the uninitiated, the SQL Server stores columns of type NCHAR, VARCHAR, NTEXT and NVARCHAR(MAX) in UCS-2 format which takes 2 bytes per character even when 1 byte will do (e.g. ASCII data), a rather common case for locale used with USA (en-USA) and other major european languages. This leads to a waste of 1 byte per Unicode character.

(2)    Compress out of row LOB data: SQL Server compresses in-row LOB data but not if the LOB data is stored out of row. With the result many LOB heavy applications are unable to take full advantage of data compression. However, there are two workaround available. First, LOB data can be compressed at the application tier but this means (a) the application needs to be modified (b) the application cannot take advantage of search and partial update capabilities provided in SQL Engine. Second, use filestream feature to store LOB data on a compressed volume.  This is our recommended solution where applicable. Please refer to Books-Online for details on filestream feature

(3)    Improve column prefix compression: Currently PAGE compression provides column prefix compression but for some workloads the repeating words occur in the middle or towards the end of the column value which does not qualify for prefix compression. This reduces the data compression savings that can be achieved. It is not to say that these customers have not benefited from data compression but clearly they could have achieved even more compression savings.

(4)    Enable estimate data compression savings computations in lower SKUs: Currently, you need to run sp_estimate_compression_savings stored procedure on EE or Dev edition. Many customers who are running on standard SKU would like to know what additional space savings they can get with data compression before they invest into a EE edition. Current work around is to use Dev edition to estimate the space savings which requires customers to install Dev Edition and restore the production database. It would be nice if we could just estimate the compression savings in the lower editions.

On this note, I am very pleased to announce that we have enhanced the data compression feature to include Unicode compression for NCHAR and NVARCHAR datatypes. The Unicode compression will be available as part of ROW compression in SQL Server 2008R2 release. You can down load the CTP2 version http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx  to play with it. In my next blogs, I will go into more details with Unicode compression.

Thanks

Sunil Agarwal

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences, I often hear of following suggestions

1.   Provide a way to control how much TempDB space can be allocated by various applications on an instance of SQL Server. Clearly, this will provide a very good way to isolate applications from misbehaving ones. In this case, if an application exceeds its limit, it may come to a stop even if there was space on TempDB. To address this, the SQL Server can possibly provide some alternatives like to allow space allocation if the TempDB is not in-use by other aplications and then do force deallocations when pressure from other applications mount.

 

2.   Provide multiple TempDBs and then assign different TempDBs to different applications. In my opinionm if SQL Server could do (1) well, then this may not be as use useful.

These suggestions are well taken but unfortunately SQL Server does not support this functionality today. So you wonder what you can do. Well, the SQL Server exposes a way using DMVs to identify TempDB space allocations by currently executing queries. If you identify that the TempDB space is running awfully low, you can use this new way to identify currently executing requests. May be some user ran an adhoc query that took significant space in TempDB. You, as an administrator, can then make the decision if you need to kill one or more of these queries to get back the space in TempDB.

Let me illustrate this with an example. I will use two large (actually not so large) tables and then join them using a hash join. You may recall that during hash join, one of the tables in hashed in memory and is backed by persistence in TempDB.

create table t1 (c1 int primary key, c2 int, c3 char(8000))

go

 

create table t2  (C4 int, c5 char(8000))

go

 

 

 

declare @i int

select @i = 0

while (@i < 6000)

begin

insert into t1 values (@i, @i + 1000, 'hello')

insert into t2 values (@i,'there')

set @i = @i + 1

end

 

 

-- now let us clean the buffer pool so that this

-- query takes some time to complete and allows us

-- to monitor the TempDB space usage

dbcc freeproccache

DBCC DROPCLEANBUFFERS

 

-- Now run the query. Note, I have used a hash-join hint

select c1, c5

from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4

order by c2

Now in another session, I will run the following DMV query

-- This DMV query shows currently executing tasks and

-- tempdb space usage

-- Once you have isolated the task(s) that are generating lots

-- of internal object allocations,

-- you can even find out which TSQL statement and its query plan

-- for detailed analysis

 

select top 10

t1.session_id,

t1.request_id,

t1.task_alloc,

     t1.task_dealloc, 

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

          (CASE WHEN statement_end_offset = -1

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

                   ELSE statement_end_offset

              END - t2.statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

 (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

 

from      (Select session_id, request_id,

sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,

sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc

       from sys.dm_db_task_space_usage

       group by session_id, request_id) as t1,

       sys.dm_exec_requests as t2

where t1.session_id = t2.session_id and

(t1.request_id = t2.request_id) and

      t1.session_id > 50

order by t1.task_alloc DESC

 

Here is the sample output of the query for my workload. I have simplified it by shortening the long DMV query and just put a symbolic name where XML show plan appears. This output shows that the query with hash-join is causing the most allocations in TempDB. Though in this case, we do know about the workload but you can run the above DMV query on any SQL Server without any knowlede of the workload and it can show you the top consumers (batches currently executing) of the space in TempDB. You can also take a look at the query plan to see what is causing the allocations in TempDB.

session_id request_id           task_alloc           task_dealloc

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

51          52                  6016                 1112

52          0                   0                    0

 

select c1, c5  from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4  order by c2

select top 10  t1.session_id,   t1.request_id … (THE DMV query)

 

 

<XML-showplan fpr the first query>

<XML-showplan fpr the DMV query>

 

 

Thanks

Sunil Agarwal

This blog continues the discussion on the common issues in TempDB that you may need to troubleshoot. In the blog http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/05/tempdb-monitoring-and-troubleshooting-io-bottleneck.aspx, we discussed how to identify and troubleshoot IO bottleneck in TempDB. In this blog, I will describe how to indentify allocation bottleneck and to troubleshoot it.

As you may recall, the allocation bottleneck is caused when allocation structures are accessed by concurrent threads in conflicting modes. Please refer to http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/what-is-allocation-bottleneck.aspx for details. In this case, the concurrent thread(s) will need to wait to acquire the pagelatch thereby slowing the operation. The good thing is that the pages containing allocation structures (GAM, SGAM, PFS) are well known and have fixed page numbers in each file. For example, in file-id 1, the allocation pages IDs are

·         PFS – 1

·         GAM – 2

·         SGAM - 3

A PFS page will appear every 8088 pages in a file. The GAM and SGAM will appear every 511232 pages and similarly in other files.

Diagnosing:

You can run the following DMV query to find any latch waits that occur in allocation pages

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:%'

Since the database id of TempDB is 2, the search argument ‘2.%’ represents any page in TempDB across any file. If this page happens to be GAM, SGAM or PFS, it will represent allocation bottleneck. Note, in a concurrent application, some blocking is expected so you will need to baseline the allocation waits when your application is performing normally. Only when the waits exceed the baseline significantly, it signals that you are incurring allocation bottleneck.

Troubleshooting:

SQL Server recommends the following

·         Create atleast as many files of equal size as there are COREs/CPUs for SQL Server process. The rationale is that at a given time, the number of concurrent threads is <= number of COREs/CPUs. Don’t confuse this with the number of active sessions/batches. 

·         Enable TF-1118 if you are encountering bottleneck in SGAM structures.

·         If you are still encountering allocation bottleneck, you will need to look at your application and see which query plans are creating/dropping objects in TempDB and if these objects are being cached and take corrective action when possible. Most IT shops have very limited choice here as they don’t own the application code.

Here I want to point out one seemingly harmless step to solve allocation bottleneck that can in fact make it worse. Say you are encountering allocation bottleneck and you decide to add one more file to the mix hoping that allocation will spread further. Well, if you recall proportional fill methodology; all new allocations will favor the newer file because it has the most free space. So suddenly, the allocation problem becomes even worse. It is a good practice to restart SQL Server when you add a new file, clearly it needs to be of the same size as other files, to TempDB.

 

Thanks

Sunil Agarwal

 

In my previous blogs, I described the types of objects in TempDB and how they are managed. I hope that it provided you with a good working knowledge of TempDB. Now the next question is how do I configure the TempDB for my production workload? In this context, there are three common questions as follows:

1.   Where should I create TempDB?

2.   What should be the size of TempDB?

3.   Single file vs multiple file?

Let us tackle each of these questions in Order.

More often than not, customer may not realize that the cause of the slowdown in the workload is because the TempDB is on the slower device. This can happen as workload changes over time or the TempDB was configured on a slower device. The performance of your TempDB is critical to your workload as, I had indicated in my earlier blogs, the SQL Server uses TempDB to store intermediate results as part of executing a query, for example to create a hash table or to sort as a result of order by. So what should you do? Well, it is not any different than what you will need to for your user databases. You need to measure the IO bandwidth needed to meet the demands of your workload. Since the persistence requirements of TempDB are different (i.e. no REDO needed), you may want to consider creating TempDB on its own spindles. In fact, you may even consider using RAM Disk to achieve better performance. Please refer to the KB article http://support.microsoft.com/kb/917047

Unlike user databases where you can probably estimate the growth rate of tables over time based on the workload, estimating the size of TempDB is non-trivial. The size of TempDB can be affected for example by a change in query plans, version store when there is a long running transaction and so on. The best way to estimate the size of TempDB is by running your workload in a test environment. Once you have a good estimate of the size of TempDB, use Alter Database command to set its size with a safety factor  that you feel is appropriate. Never, I mean never, let the TempDB grow to its steady state size through auto-grow. You should only use auto-grow as a last resort but not as a strategy. Also, remember that the TempDB is created every time you restart a SQL Server but its size is set to either default of Model database or the size you had set using Alter Database command (the recommended option)

·         Don’t rely on auto-grow to manage file sizes. You must set the appropriate size of TempDB using Alter Database command. Here are the pitfalls of auto-grow

o   Auto-grow causes a pause during processing when you can least afford it

o   Less of an issue with instant file initialization

o   Auto-grow leads to physical fragmentation

SQL Server recommends that you create as many files as there are COREs or CPUs to minimize allocation bottlenecks. Yes, it is true that starting with SQL Server 2005, there is better caching of TempDB objects but there are cases when the caching does not work and even with caching, the size of temp objects grows beyond 1 page (# tables) or 1 extent (internal objects), the SQL Server can potentially incur the allocation bottleneck. Please refer to the allocation-bottleneck for details on what causes allocation bottleneck.  Before we proceed further, you may wonder why we talk about allocation contention in the context of TempDB and why not in the context of other databases. Clearly, the allocation contention can happen even in user database. The main reason is that the objects in TempDB are created/destroyed all the time to process customer workload which leads to order of magnitude more allocation contention.

To minimize allocation bottleneck, here is the recommendation

·         Spread TempDB across atleast as many equal sized files as there are COREs or CPUs. Since allocation in SQL Server is done using proportional fill, the allocation will be evenly distributed and so is the access/manipulation of the allocation structures across all files. Note, you can always have more files than COREs but you may not see much improvement. One of the questions that people often ask is if they should create multiple files on the same physical spindle or multiple?  The allocation bottleneck can be reduced by creating multiple files on single or multiple spindles. Creating files across multiple spindles will help increase the IO bandwidth but will have no additional impact on allocation bottleneck.

·         If you are still encountering allocation bottleneck, you may want to consider enabling TF-1118. Under this trace flag SQL Server allocates full extents to each TempDB object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in TempDB. This trace flag has been available since SQL Server 2000. With improvements in TempDB object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. If your workload is not encountering SGAM contention, then enabling TF-1118 will not help. Also, this TF is instance wide and impacts the user databases as well.

Thanks

Sunil Agarwal

In the previous blog http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/31/managing-tempdb-in-sql-server-tempdb-basics-version-store-logical-structure.aspx , I described the logical/physical structure of a row in version store.  A natural question that comes up is how these pages are grouped together and how does SQL Server garbage collect these rows. You may recall that if you need to DELETE a large number of rows from a table, an efficient option is to truncate or even drop the table rather than deleting one row at a time. Similarly, the SQL Server has to delete 1 row at a time. It can be quite expensive. So a better option is to DELETE bunch of rows together. For this reason, the SQL Server groups the row versions in a physical unit called ‘Append-Only store’. All the versioned rows in an Append-Only store are garbage collected together.  Alternatively, you can think of Append-Only store as an internal table, but it is not really a table like #table, and this table is dropped once SQL Server determines that it does not need the row versions in it. You can see that this operation is much more efficient compared to deleting 1 versioned row at a time. A new ‘Append-Only Store’ is created very minute, if needed (i.e. if there is no transactional activity that generates a row version, the Append-Only store will not be created).  The version store represents the aggregation of all active ‘Append-Only’ stores.

Let us now work through an example to illustrate the functioning of the version store. For simplicity, let us assume that the transactional activity is such that a new Append-Only store is being created every minute.  Let us say that Append-Only store AS1 was created at time T and the two subsequent Append-Only stores AS2 and AS3 were created at time (T+1) and (T+2) respectively.  You may now wonder how SQL Server chooses a specific Append-Only store to “insert” the row version. Well, the SQL Server associates an Append-Only store to a transaction at its start. So for example, for transactions that start between T and (T+1), the version store is stored in AS1. Similarly, for transactions that start between (T+1) and (T+2), the row versions are stored in AS2. Note, a transaction may be long running say 5 minutes and can be generating row versions throughout its life time, but still all the row versions it creates go to the Append-Only store that was assigned to it in the begin of the transaction.  You can also imagine that in its life-time, each Append-Only store will have different sizes depending upon the row versions generated by associated transactions. One important point that I did not mention earlier is the “insert” into the Append-Only store are not logged. Why? Well if SQL Server has to fail, all active transactions will be rolled back anyways so the SQL Server does not need the “state” of version store as of the time when SQL Server stopped.

Let us now look at when it is safe to remove an Append-Only store. To remove an Append-Only store, the SQL Server must guarantee that no transaction will need to look at the row versions it contains. For the above example, to remove Append-Only store AS1, the following conditions must be met

1.    All transactions that started between T and (T + 1) have completed. Let us say that these transactions completed at time T2.

2.    All transactions that started between (T + 1) and T2 and needed row versions (i.e. the transactions running with RCSI or SI) from AS1 have completed.

3.    All Append-Only stores created before AS1 have been removed

 

Here are some key observations (a) typically, the transactions commit in the order they were started so this scheme of deleting Append-Only store works well. (b) if you have a long running transaction, it will prevent deleting Append-Only stores created since the start of the transaction. This can cause version store to grow and subsequently the  TempDB may go out of space. While this may sound alarming but it is similar to when a long running transaction can cause the transaction log to fill up. The key difference here is that the impact of versioning is wider as it can impact all databases that need to create row versions. Typically a long running transaction indicates a poor application design. SQL Server provides tools to detect the version store growth. I will cover this in troubleshooting section in later blogs.

On another note, you may wonder how the version store is managed for ONLINE index which can potentially take an order of magnitude more time than a transaction. The SQL Server gets around this issue by ‘internally’ creating another version store, distinct from the one I just described, for ONLINE index build containing the row versions of the table on which the index is being built.

This concludes my discussion on the version store. I will cover the troubleshooting of version store as part of overall troubleshooting of TempDB. Hope this provided you with some deeper understanding and I look forward to your comments. I also want to take this opportunity to wish you all a very happy new year.

 

Thanks

Sunil Agarwal

In order to understand the version store, let me start with an example. I will use a database  that has RCSI (read committed snapshot) and SI (Snapshot Isolation) enabled as it provides more controlled environment to manage versions.

 

When a database is enabled for RCSI/SI, any update of a row will create a row version. This version stays in the version store as long as it is needed. Now, that is a tricky statement. How does SQL Server know when to reclaim the version? Some other questions that may cross your mind are: Is not reclaiming (i.e. deleting) a row version expensive? Does SQL reclaim row versions one at a time or a group of them? Each user table has different schema (i.e. different number of columns and their types) so does the SQL Server store the row versions from each table separately? What is the overhead of row versioning?

 

Let me first create a database and enable SI/RCSI.

 

create database snapshottest

go

 

-- Setting database for snapshot based isolations. AS you can

-- see that enabling SI/RCSI is a simple DDL operation.

alter database snapshottest set read_committed_snapshot ON

go

 

alter database snapshottest set allow_snapshot_isolation ON

go

 

-- check the snapshot state of the database and it will

-- show that both SI and RCSI have been enabled.

select  is_read_committed_snapshot_on,

   snapshot_isolation_state_desc,

        snapshot_isolation_state

from sys.databases

where name='snapshottest'

go

 

--create a table with each row > 1000 bytes

create table  t_version (c1 int, c2 char(1000))

go

 

--Load 50 rows. Note,I start the transaction but did not

--commit it yet.

Begin tran

declare @i int

select @i = 0

while (@i < 50)

begin

insert into t_version values (@i, 'hello')

set @i = @i + 1

end

 

Now you can use the following DMV to look at the version store. You will notice that it does not show any rows in the version store. This is the first observation I want you to make. The INSERT operation does not cause a row version to be generated because there is really no previous version of the row being inserted. There is a special when an insert causes a row version to be created but I will cover that later to keep this topic simple. This means that when you do Bulk Import into a SI/RCSI enabled database, SQL Server is not creating any row versions.

 

select COUNT(*) from sys.dm_tran_version_store

-- Now commit the transaction

commit

 

 Now let us update all the 50 rows in the table. Now, since the row is being updated, SQL Server will copy the older version of the row into the version store. So, we will expect 50 row versions

 -- update all the rows in the table

update t_version set c2 ='test10'

 

-- the following statement returns the count of versioned row.

-- And, for the case here, it will return 50

select COUNT(*) from sys.dm_tran_version_store

 

Note, that the UPDATE statement was not run in an explicit transaction so the above statement was the only statement in the “implicit” transaction. Also, since there was no other concurrent transactions that would be interested in the row versions just created, these versions are garbage collected by SQL Server. If you run the following query after minute or so, you will realize that row versions have been garbage collected.

 

-- the following statement returns 0 if run after a minute or so

select COUNT(*) from sys.dm_tran_version_store

 

Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed.  In the worst case, if you have a long running transaction that has either created the row version or needs it, the version row cannot be removed and this can cause version store to grow and it can potentially use up all the space in TempDB just like a long running transaction can cause transaction log to fill up. I will describe this in more details later.

 

Just like UPDATE, when a row is deleted, a row version is created. So both DELETE and UPDATE operations lead to row versions.

 

In the next blog, I will describe the version store layout.

 

Thanks

Sunil Agarwal