SQL Server Storage Engine

AlwaysOn: Comparing Readable Secondary with the similar functionality available in DB2

2012-01-22T06:45:00Z

In my previous blogs, I had described how Readable Secondary functionality works SQL Server 2012. As you look at other database vendors, you will realize that they also provide the functionality to offload read workload to secondary or mirror. However, if you look closer, you will realize each vendor has significant differences on how this functionality is made available to customers. Here is one comparison that I have done between SQL Server and DB2.

Competitive study of Readable Secondary in DB2 9.7

DB2 9.7 HADR configuration supports ‘active’ standby server that can be used for query workload. From competition perspective, it is similar to Readable Secondary functionality. This report is based on DB2 9.7
documentation. For details, please refer to the link

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0054257.html

Key Feature	DB2	SQL Server 2012
Isolation Level Mapping: This is very relevant for SQL Server as the DB2 provides similar isolation levels	By default only support ‘Uncommitted Read’. An application that uses any other isolation level fails. You can however use configuration option DB2_STANDBY_ISO registry variable and set it to UR. In that case, all isolation levels are mapped to Uncommitted Read.	Provides consistent read by mapping all isolation levels to Snapshot Isolation.
DDL and Read Workload: What happens when DDL or maintenance operations are executed on the primary. What is the impact on the read workload on the secondary	When an HADR active standby database is replaying DDL log records or maintenance operations, the standby enters the replay-only window. When the standby is in the replay-only window, existing connections to the standby are terminated and new connections to the standby are blocked. DB2 recommends customers to run these operations during specific time window	No such restrictions. In many cases, the DDL may not get blocked due to concurrent read workload. In case of DDL getting blocked due to concurrent read-workload and the ‘recovery interval’ threshold is crossed, we generate an XEvent which can be used to kill the blocking readers. We expect this to be not that common, based on ‘recovery interval’ option.
Auto Statistics Creation: Since secondary is available for read-only access, how does it address making statistics available needed only for the queries running on the readable secondary? If statistics are not available, it can potentially increase the execution time by orders of magnitude.	Requires customers to manually create/update statistics. Since this is a DDL operation, it can only be done in ‘replay-only window’. This means that query plans will be sub-optimal until the statistics are made available. Note: DB2 document does not explicitly calls this out but references to statistics seem to imply this.	Allows auto-create/update of statistics on the readable secondary so that optimizer will always generate optimized query plans.
Auditing	Manually create the instance level auditing configuration on the standby server	Manually create the instance level audit specification on the readable secondary. .
Secondary falling behind:	Manually kill the read workload. It is not clear from DB2 documentation if they generate any event to notify customers	If REDO is slowing down, we will generate an XEvent when ‘recovery interval’ threshold is crossed. The customer can choose to kill the offending readers. If the slowdown is because of resources, the reader workload can be throttled using Resource Governor.
Temporary Objects	Created temporary tables (CGTTs) and declared temporary tables (DGTTs) are not supported on the standby.	No restrictions. The read workload is free to do any DDL in temporary database.
Data types: LOB, XML,	Not supported on the standby server	No restrictions. SQL Server will support LOBS, XML, FILESTREAM, and FTS
Backup on Secondary	Not supported	Supported

Thanks

Sunil Agarwal

AlwaysOn: I just enabled Readable Secondary but my query is blocked?

2011-12-22T23:23:00Z

When you connect to Secondary Replica, but it has not been enabled for read workload, you will get the following error under two situations

(1) You connect directly to one of the databases under availability group. It is denied because the database is not enabld for read workload.

(2) You connect to a non-AG database such as master database which succeeds and now you execute ‘use <db>’ command.

Msg 976, Level 14, State 1, Line 1

The target database, 'hadr_test', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

To connect to readable secondary database, you will need to enable the secondary replica for read workload. Once it is done, you can connect but there is one catch. Your queries will block until all active transactions active at the time of enabling readable secondary have been committed and processed on the secondary replica. This is needed to guarantee that row versions are available on the secondary replica before executing the query under snapshot isolation as all isolation levels are implicitly mapped to snapshot isolation. Here is one example to illustrate this

-- On primary replica, execute a transaction that updates all the rows in employee

-- but don't commit it

create table employee (name char (100), id int)

declare @i int

select @i = 0

while (@i < 10000)

begin

insert into employee values ('my name' + CONVERT(varchar(10), @i), 33)

select @i = @i + 1

end

begin tran

update employee set id = 1000

The transaction on the primary replica is still active. Now, enable secondary replica to accept read workload and issue the following query on the secondary replica. This query will get blocked

-- on secondary replica, connect to the secondary database, your connection will succeed but the

-- following query will block

select count(*) from employee

You can run query the DMV to see where the blocking is (in my example, the query above was issued on session_id 54). Note, while the queries on user data are blocked but readable secodnary still allows you to query DMVs otherwise you would not have been able to troubleshoot or understand what is going on.

select session_id, wait_type

from sys.dm_exec_requests where session_id = 54

The output is as follows. This wait stat corresponds to versioning transition.

If you now commit the transaction on the primary, the query will get unblocked once the commit log record has been processed by the secondary replica. We expect that enabling secondary replica is a very in-frequent and in a typical configuration, it would need to be done only once.

Thanks

Sunil Agarwal

AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

2011-12-22T23:00:00Z

In the previous blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-challenges-with-statistics-on-readonly-database-database-snapshot-and-secondary-replica.aspx we described that stale or missing statistics will potentially lead to a sub-optimal query plan and how it can impact the query performance on on read-only database, database snapshot and readable secondary. This blog describes how we have solved this in SQL 12 for all three flavors of databases transparently without any user intervention.

As mentioned earlier, the read-only database, by definition, allows no physical changes to any persistent information. Similarly, no physical changes can be done on a read-only access database (i.e. readable secondary) as it must follow the strict rule of exact physical replica of the primary database. So the question arises how can we allow creating/updating statistics on one or objects in read-only or read-accessible databases user databases? The short answer is that we cannot. However, we do recognize the fact that statistics on an object can be created and re-created using the data in the base object. We use this fact to create temporary statistics in TempDB. This change guarantees that up to date statistics are available on the secondary replica just like they are on the primary replica for the query optimizer. This guarantees similar optimized query plans as you would get on the primary database. The implication of creating temporary statistics is that these statistics can be lost if the SQL Server is re-started but this is not a data loss situation because, as we had noted earlier, these statistics can be recreated at a relatively low cost by querying the underlying objects.

Let us take couple of examples to illustrate how temporary stats work.

Example-1: Missing statistics: Key scenario here is that you execute a query on the secondary replica that requires statistics on a column but the statistics are missing because the same query or any other query that requires the statistics on the same column was never run on the primary replica.

create table T1 (c1 int, c2 int, c3 int)

-- insert into T1 1 row

insert into T1 values (-1, -1, -1)

-- query T1 and show that stats got created automatically on T1/c1

select * from T1 where c1 = 1

-- check the stats on table T1

select * from sys.stats where OBJECT_ID = object_id('T1')

Here is the output. Note, that it shows that statistics was created automatically on table with object_id as 59757167 which happen to represent table ‘T1’. Other interesting thing to note here is that there is a new column ‘is_temporary’ representing that this statistics is permanent implying that it was created on the primary replica. This statistics will survive the failover (assuming no data-loss) and restart.

If you query sys.stats table on the secondary replica, you will see the same results because any logged operation is automatically available on the secondary replica. If you want to know more details, you can execute the following

dbcc show_statistics('t1', 'c1')

The output shows the row count, column on which the statistics is available along with other information.

Now, let us execute the following query on the secondary replica and check the statistics

-- query on column C2 to show TEMPSTATS

select * from t1 where c2= -1

--show the stats on the secondary

select * from sys.stats WHERE OBJECT_ID = OBJECT_ID('t1')

Here is the output. Note, a new temporary statistics got created with the name ‘_WA_Sys_00000002_239E4DCF_readonly_database_statistics’ with the ‘is_temporary’ flag indicating that it is temporary. The statistics was automatically created because the optimizer needed it. The name of the statistics is appended with the suffix ‘_readonly_database_statistics’. This is done so that this name does not clash with other automatically created statistics on the primary replica. SQL Server 2012 prevents creating any statistics with this suffix. There is an outside chance that a permanent statistics already exists with this name (assuming someone explicitly created a statistics with this name) before database was upgraded to SQL2012. In that case, the temporary statistics creation will fail but your query will still succeed but the optimizer will not have the statistics as needed for optimization.

The temporary statistics are stored in TempDB and each statistics typically takes 8K (1 page) of storage. You can always query sys.stats table to find out all temporary statistics and estimate the storage space taken in the temporary database. Other point to note is that statistics created as part of auto-stats use data sampling so the creation of these statistics is fast and does not depend on the size of the table. One drawback is that if the data for the column is skewed then the statistics based on the sampled data are not very accurate. In such cases, if you want, you can create the statistics on the primary replica without sampling and then it will be available on the secondary replica. The temporary statistics are lost when the secondary replica is either restarted or when primary replica fails over.

Example-2: Stale Statistics: Key scenario is that the statistics on a column was either explicitly or automatically created but has become stale due to DML operations. The statistics stay stale because there was no query run on the primary that required this statistics since the last update. Now, if we run a query on the secondary replica, this statistics will get updated automatically so that the optimizer can use it for optimization. Let us take the previous example where we have a table T1 with 1 row and permanent statistics on column C1 and temporary statistics on C2.

-- insert 10000 rows on the primary to make the statistics stale

declare @i int = 0

while (@i < 10000)

begin

insert into t1 values (@i, @i + 1000, @i + 10000)

set @i = @i + 1

end

Now, we execute the following query on the secondary

-- Query on the secondary so that stats are updated with TEMPSTATS

select c2 from t1 where c1 = 100

Here is the output of the statistics. Note that the statistics ‘_WA_Sys_00000001_239E4DCF’ got updated and is now marked as ‘is_temporary’ being 1. This implies that SQL Server has created a temporary statistics. This does not mean that the permanent statistics got lost. It is still available but SQL Server knows that there is an updated version on this statistics in TempDB and it will use that.

Let us see the full details of this statistics

dbcc show_statistics('t1', '_WA_Sys_00000001_239E4DCF')

The output is as follows showing that there are now 10001 rows and this statistics in on column C1

If you restart your secondary replica, the temporary statistics will be lost and you will see the following output. In fact, if you query the statistics on the primary, you will see the same output as well.

You may wonder what happens if the statistics on C1 get updated on the primary replica. Let us repeat this experiment by running the following query on the primary

-- query t1 and show that stats got created created automatically on t1/c1

select * from t1 where c1 > 1000

This query will cause the statistics on column C1 to get updated and when it flows to secondary replica, the statistics will be marked as permanent and now the optimizer will use the permanent statistics as it is the latest statistics available on column C1.

In summary, to illustrate various interactions, let us take a table T1 with three columns C1, C2, and C3. For this discussion, it does not matter what the column type is. We will use C1_prim and C1_sec to represent statistics on column C1 that got created on primary and secondary replica respectively

The following table summarizes various interactions

Action	Primary Replica	Secondary Replica
Query on Secondary with predicate on C1		C1_sec gets created
Query on Primary with predicate on C1	C1_prim gets created	C1_prim is created on when the log for the statistics is processed. At this time, both C1_sec and C1_prim exist on secondary replica but the C1_prim is latest and optimizer will use it. At this time C1_sec is not useful and user can explicitly drop it.
Memory pressure forces T1 out of cache		C1_sec is removed from the cache but it still persists in TempDB
Insert bunch of rows in T1 such that auto-stat threshold is crossed. Now query on the secondary with predicate on C1		C1_sec gets refreshed.
Query on Primary with predicate on C2	C2_prim gets created	C2_prim is created on when the log for the statistics is processed.
Insert bunch of rows in T1 such that auto-stat threshold is crossed. Now query on the secondary with predicate on C2		C2_sec gets created. At this time, both C2_sec and C2_prim exist on secondary replica but the C2_sec is latest and optimizer will use it.
Do a DDL operation on table T1		Cached metadata for T1 is deleted and as part of it C1_sec and C2_sec gets dropped.

The same mechanism works on read-only database and database snapshot.

Thanks

Sunil Agarwal

AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica

2011-12-22T22:32:00Z

I am sure you all have dealt with situations when the statistics on one or more columns is either missing or not up-to date. When user submits a query for execution, the SQL Server goes through logical (simplifying or restructuring a query) and physical optimization that considers various query plans based on statistical information and then picks the one that has the least estimated cost. The key to physical optimization is the accurate estimation of data distribution (i.e. statistical information) of the input tables and intermediate results (e.g. output of JOIN operator). When optimizing a query, the optimizer generates, the statistical information on one or more tables/indexes, if not available and auto-stats has not been explicitly disabled by the user. It uses statistics generate the optimized query plan. It is not recommended to generate a query plan without the statistics because you may end up with a sub-optimal plan that can take many orders of magnitude longer to execute. There are customer reported cases where a good query plan allows a query execution to complete in minutes while a bad query plan can take hours.

The challenge for readable secondary is that that statistical information cannot be created or updated as this will amount to modifying the data on the secondary replica in the context of a read-only access. One common question that comes up in the context of readable secondary is ‘if the statistics created on the primary replica are automatically available on the secondary replica, why do I care about creating/updating statistics on the secondary?’ While it is true that any statistics that is created on the primary replica is automatically available on the secondary replica however the queries that you will run on the secondary replica are, in all likelihood, very different than the ones you run on the primary replica. For this reason, the statistics may either be missing or possibly stale when a query is run on the secondary replica. Interestingly, this issue is not limited to readable secondary. You can experience the same issues on a database snapshot or to a lesser degree on databases that are marked as read-only. The reason I say is to a lesser degree for read-only databases is because, in theory, a customer can mark a database read-only after creating all possible statistical information and since the data never changes, there will never be stale statistics. However, in reality, it is hard to predict what read-only workload will be executed which can lead to poor execution plan due to missing statistical information.

Example: Here is one example that illustrates this issue using a Read-Only database

create database test

use test

create table t1 (c1 int, c2 int, c3 char (50))

create nonclustered index t1_nci on t1(c1)

-- show statistics

select * from sys.stats where object_id = object_id('t1')

If you look at the stats, it will show 1 statistics that got created as part of creating an index. Note, it shows that there are 0 rows as you would expect.

-- show the statistics

dbcc show_statistics('t1', 't1_nci')

Now, let us insert 1 row into the table

--insert 1 row

insert into t1 values (1,1,'hello')

-- show the statistics

dbcc show_statistics('t1', 't1_nci')

You will notice that the above statistics does not show that a new row has been inserted. The reason is that statistics are not automatically updated when one or more rows are inserted. The statistics are updated either using an explicit command to update them or automatically if needed by optimizer assuming the compute threshold has been crossed. Let us now do a query that requires statistics on the T1.C1 and then re-check the statistics as follows

--do a simple select and show the query plan

select c2 from t1 where c1 between 1 and 20

-- show the statistics

dbcc show_statistics('t1', 't1_nci')

Here is the output. You will notice that the statistics now shows that there is now 1 row

Another interesting thing to note here is to see the query plan. Note, that optimizer chooses a table scan because it has determined that if there is only 1 row, it is cheaper to scan the table instead of going through the non-clustered index.

So far so good. Now, let us insert 10,000 rows into table T1 and then mark the database as ReadOnly.

declare @i int = 0

while (@i < 10000)

begin

insert into t1 values (@i, @i+100000, 'sunil')

set @i = @i + 1

end

ALTER DATABASE [test] SET READ_ONLY WITH NO_WAIT

Now let us do the query and see the statistics.

--do a simple select and show the query plan

select c2 from t1 where c1 between 1 and 20

-- show the statistics

dbcc show_statistics('t1', 't1_nci')

The statistics still shows that there is only 1 row even though we had inserted 10000 rows before marking the database as read-only. The reason is that before the database was marked read-only, we did not issue any query that would have required statistics on column C1 therefore the statistics could not be updated.

If you look at the query plan, it still shows the table scan. Clearly, this would be very inefficient plan as it will require a table scan checking each row that meets the search predicate. This is the key problem with read-only database or for that matter with database snapshot for if the statistics were either stale, as in the case here, or missing, optimizer will not be able to generate an efficient plan. The only recourse will be mark the database as R/W and update/create the statistics before marking it read-only but it is not really practical. You could get away with this limitation if you knew in advance all possible queries that will be run against this read-only database or you create the statistics on all columns of all tables. As you can see that this can take significant time for large databases computing statistics that may never be needed. This becomes even more impractical for database snapshot.

Just for the kicks, let us now mark the database as R/W and re-issue the same query and look at the query plan. As you might have guessed, in this case, the optimizer chose the non-clustered index.

Now that you understand the issue, you will be happy to know that you will never run into stale or missing statistics when running queries on readable secondary. For details, please refer to http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-making-upto-date-statistics-available-on-readable-secondary-read-only-database-and-database-snapshot.aspx. As an added bonus, we have applied the same solution for Read-Only database and database snapshot independent of whether these databases are used in the context of AlwaysOn or not.

Thanks

Sunil Agarwal

AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation

2011-12-22T22:07:00Z

In my previous blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-minimizing-blocking-of-redo-thread-when-running-reporting-workload-on-secondary-replica.aspx, I described how Readable Secondary eliminates potential REDO thread blocking for DML work running on the primary replica by mapping all isolation levels used in the reporting workloads to Snapshot Isolation. While it was essential that we eliminate REDO thread blocking with concurrent DML on secondary replica, it comes at some overheads as described in this blog.

First thing to understand is that if you do not enable secondary replica for read workload, there is no Snapshot Isolation mapping needed and therefore no additional overhead. Things get interesting when you enable the secondary replica for read workload. You may recall that both Snapshot Isolation and RCSI are based on row versioning. When a row is modified, its previous version is saved in the version store backed by TempDB and a 14 byte pointer is set from the modified row to the versioned row. If the data row gets modified again, the process is repeated. The key point to note here is that newly inserted or modified data rows will have a 14 byte overhead. There are 4 possible scenarios to consider here

Secondary replica is not enabled for read workload: As shown in the figure below, there is no additional overhead on the primary replica.

Secondary replica is enabled for read workload: As you will note in the figure below, a 14 byte overhead is added to the row on the primary and secondary replica. Additionally, a row version is generated by REDO thread on the secondary replica as it processes the UPDATE/DELETE operation from the primary. There are two interesting points to note here. First, the row version is only generated on the secondary replica. Reason is simple. Since the RCSI or SI has not been enabled on the primary replica, there is really no need to have row versions created there. This also means that apart from adding 14 byte overhead, there is not additional impact (i.e. row version generation and TempDB overhead) on the primary replica. Second, we need to add 14 byte overhead to the new/modified rows on the primary because the primary and secondary replicas must be physically identical.

Primary replica already has RCSI or SI enabled but no readable secondary: This case is a bit simpler because in this case, the 14 bytes versioning overhead is already added to the data rows independent of the status of secondary replica. As shown in the picture below, if secondary replica is not enabled for read workload, there is still a 14 byte overhead on the rows on secondary replica as it must, but there is not row version generation because the read workload has not been enabled

Primary replica already has RCSI or SI enabled with readable secondary: This case is similar to previous configuration except that row versions will need to be generated on the secondary replica as well.

There is no change or additional guidance for managing and provisioning for row versioning in this configuration compared to what we recommend for Snapshot Isolation or RCSI. Please refer to Books Online for details of SI and RCSI.

Thanks

Sunil Agarwal

AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica

2011-12-22T21:32:00Z

In earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-on-the-primary-workload-when-you-run-reporting-workload-on-the-secondary-replica.aspx, I discussed the impact on RTO in case the REDO thread gets blocked. While designing this feature, it was one of the key usability constraints we had (i.e. to eliminate REDO blocking for common usage scenario). For the un-initiated, here is the problem

Primary Replica	Secondary Replica
	Set Isolation level Repeatable Read
	Begin Tran
	Select T1.C1 from T1 where T1.C2 = 10 -- this takes a S lock on the qualifying data rows
Begin Tran
Update T1 set T1.C3 = <some-value> Where T1.C2 = 10 --This statement will not block because the -- S lock is held on the secondary replica, not on -- primary replica
Commit	REDO applies the UPDATE. To do this update, it needs to acquire an X lock on qualifying rows but it will get blocked because the read transaction holds a S lock on the row

As you can guess, this will be a common scenario and we need to ensure that REDO thread is not blocked. A blocked REDO thread can compromise the RTO and/or can prevent cleaning up the log on the primary replica. To avoid this blocking completely, all transaction isolation levels used in an application on the secondary replica are mapped transparently to Snapshot Isolation as shown in the table below including the side effects without requiring any changes to the reporting application.

Isolation Level	Mapped To	Impact on workload
Read Uncommitted (RU)	Snapshot Isolation (SI)	None
Read Committed (RC)	Snapshot Isolation (SI)	None. Since SI eliminates blocking, if the read-only application depends on blocking, it will not work as expected. However, we do not think that read-only application that will depend on blocking. If it does, then part of the application logic depends on a DML activity somewhere which by definition means that it is not a read-only application.
Repeatable Read (RR)	Snapshot Isolation (SI)	SI is a higher isolation level than RR in the sense that it eliminates phantoms. We don’t think a read-only application will depend on phantoms. If it does, then part of the application logic depends on a DML activity somewhere which by definition means that it is not a read-only application.
Snapshot Isolation (SI)	Snapshot Isolation (SI)	None
Serializable (SR)	Snapshot Isolation (SI)	Transactions running under SI are not guaranteed to be serializable in its most general form. However, for read-only workload, SI offers the same Serializable guarantee.

You may ask how about locking hints? Well, all locking hints are ignored. While transparent mapping to Snapshot Isolation level should work for most reporting workloads, we recommend you to test your workload before deploying in production.

One concern that you will have is that is the impact of mapping reporting workload to Snapshot Isolation on the primary replica and also how does it change the space usage in TempDB? This is the topic of my next blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-impact-of-mapping-reporting-workload-to-snapshot-isolation-on-readable-secondary.aspx. While mapping reporting workload to snapshot isolation eliminates blocking between DML workload as applied by the REDO thread on the secondary replica and the read or reporting workload, it does not eliminate the potential blocking of REDO thread when it is executing a DDL operation. I will discuss this in more details in a subsequent blog.

Thanks

Sunil Agarwal

AlwaysOn: Impact on the primary workload when you run reporting workload on the secondary replica

2011-12-22T21:23:00Z

The primary goal of AlwaysOn technology is to provide High Availability for Tier-1 workloads. The ability to leverage secondary replica(s) to offload reporting workloads and database/transaction log backups is useful, but only if it does not compromise High Availability. This is one of the very common concern/question that I have heard from many of you. In this context, let us evaluate the impact of Active Secondary on the primary replica and workload and what you can do to minimize its impact.

Transaction response time: When a transaction is run on the primary replica, its transaction log records are written to log buffer and at the same time are sent to log pool to be sent to the secondary replica (for now, we will consider only 1 secondary replica but the same logic holds for multiple replicas) as shown in the picture below. If the secondary replica is configured in async mode, there is no impact on the transaction response time on the primary. However, if the secondary replica is configured in ‘sync’ mode, then the primary replica only commits a transaction when the ‘commit’ log record has been hardened on the secondary replica (it sends an acknowledgement to primary replica). A delay in the acknowledgement from the secondary replica will add to the latency of the transaction. The question that needs to be answered if read workload running on the secondary replica can impact the ACK for the transaction commit? If you look at the secondary replica in the picture below, there are essentially two background threads, one receives the log record over the network and the other hardens that log record. SQL Server gives priority to background threads over user threads (including the ones that are running read workload). This means that at least from CPU perspective, read workload cannot ‘delay’ the ACK. Other possibility is the IO bottleneck caused by read workload that can slow down the transaction log write but this can only happen if data and transaction log share the same physical disk. In most production deployments, transaction log disk is not shared with data disk(s) so it is a non-issue. Having said that, a network bottleneck can add to the latency of the transaction but then it is unrelated to read workload. As we will see later in this article, if REDO thread is not able to keep with the transaction log generated, it can potentially lead to log to grow which can to the transaction latency. In a well configured and managed system, it is unlikely that the read workload on the primary will add to the transactional latency.

Transaction throughput: Just like the case of transactional response time discussed earlier, there should not be any impact on the transactional throughput with readable secondary except for the unlikely case when either REDO thread is slowed due to concurrent read workload or blocked which can cause transaction log to grow or run out of space. We will discuss when and how a REDO thread can get blocked and what you can do about it.
Recovery Point Objective (RPO): RPO refers to data loss in case the primary replica fails over to secondary replica. If the secondary replica chosen as the new primary is configured with ‘Sync’ mode, there cannot be any data loss regardless of the fact that readable secondary has been enabled or not. You may ask what happens if the secondary replica was set in ‘Async’. Interestingly, in this case, there will be no additional impact on the RPO (yes, you can lose data as it is possible if you failover to an ‘Async’ replica) due to running reporting workload on secondary. The reason is that the reporting workload does not interfere with the log transfer path from primary replica to log buffer and its hardening on the secondary replica assuming you log files are mapped to their own storage.
Recovery Time Objective (RTO): The interesting case for us to know here is the time needed to bring the database ONLINE after a fail over to the secondary replica. When the database restarts after a failover, the REDO thread needs to apply the transaction log records from its current position to the end of the transaction log. The more REDO thread has fallen behind in applying the transaction, the longer (i.e. RTO will be larger) it will take to bring the database ONLINE. Now here, the read workload can potentially impact the RTO negatively for the following two reasons

o As part of applying log records on the secondary replica, REDO thread reads log records from the log disk and then for each log record, it access the data page(s) to apply the log record. The page access can cause a physical IO if the page is not already in the buffer pool. Now, if read workload is IO bound, it will compete for IO resources with REDO thread thereby slowing it down.

o REDO thread can get blocked by read workload. A blocked REDO cannot apply any log record until it is unblocked which can lead to unbounded impact on the RTO. As described in http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/alwayson-minimizing-blocking-of-redo-thread-when-running-reporting-workload-on-secondary-replica.aspx, the design center of readable secondary has eliminated blocking in common scenario and in the event there is blocking, it provides users tell-tale signs both in the dash-board and in the form of XEvent to warn the user of the situation so that a corrective action can be taken

With this, I hope that I have answered most common concerns. I look forward to your questions and suggestions

Thanks

Sunil Agarwal

AlwaysOn: Readable Secondary and data latency

2011-12-22T19:58:00Z

One question that I often get asked is if there will any data latency if you are using 'Sync' replica for read workload. The short answer is yes. Here is the blog that explains it in detail.

While the reporting workload running on the secondary replica gets to access the data as it is changing on the primary but there is some data latency, typically of the order of seconds. If your reporting or read work load cannot tolerate data latency then there is no choice but to run it on the primary replica. Most customers whom I interacted with indicated that their reporting workload can indeed tolerate data latency of the order of minutes. This is good news because as offloading reporting workload to secondary replica allows customers to leverage their investment in High Availability configuration and deliver better performance to both the primary and read workloads.

While this is well and good, but there is some misconception that if you enable readable secondary on a ‘sync’ replica, then there is no data latency. This confusion is rooted in the fact that ‘sync’ replica must send an ACK to primary replica for the ‘commit’ log record. Yes, it is true that ‘sync’ replica guarantees that there is no data loss (i.e. RPO = 0) but it does not guarantee, at least not in SQL-12, that REDO thread running on the secondary replica will apply commit log record before the ACK is sent to the primary. In fact, the REDO thread runs asynchronously to the log hardening logic. So there is indeed some data latency. You may wonder if this data latency is more when you have configured the secondary replica in ‘Async’ mode. This is a little tricky question to answer. If the network between primary/secondary replicas is not able to keep up (i.e. not enough bandwidth) with the transaction log traffic, the ‘Async’ replica can fall further behind leading to higher data latency. In ‘sync’ replica case, the insufficient network bandwidth does not cause higher data latency on the secondary but it can slow down the transaction response time/throughput on the primary.

Another dimension to data latency is when you have configured two or more secondary replicas for read workload. The primary replica sends log records to each of the secondary replicas independently. Within each secondary replica, the log records are applied by REDO thread asynchronously. As you can imagine, this means that each replica is at different point-in-time, though very likely to be close to each other with the result the same query executed on two different readable secondary may return different result. Note, this is not any different than transaction replication with multiple subscribers. Each subscriber will have data at different point-in-time.

The more interesting question here is what happens if you have scaled out your reporting applications across multiple readable secondary? Let us take an example where AlwaysOn is configured with three replicas on node N1, N2, and N3 and you have two reporting applications App1 and App2. The primary replica in on node N1 while the readable secondary are on node N2 and N3. As indicated earlier, the node N2 and N3 can be at different point-in-time. In this configuration, there are two interesting cases as follows

Case-1: You scale out your reporting workload by running App1 on node N2 and App2 on node N3. In this case, App1 does not see the data on node N3 therefore not impacted by the fact that the data on N3 could be at different point-in-time. Similar case holds for App2 as well. The down side of this configuration is, for example, if App2 cannot offload work to node N3 if it hits temporary spike in the workload.
Case-2: You scale out both App1 and App2 on node N2 and N3. In other words, each application accesses data both no node N2 and N3 as shown in the figure below for App1. Here all connections for App1 get distributed to node N2 and N3.

It is good to know that you have both options available to you for scale out your read workload. I look forward to your experience with offloading read workload to the secondary replica and any feedback to improve the product in future. Happy offloading read workload to the secondary replica

Thanks
Sunil Agarwal

AlwaysOn: Why there are two options to enable a secondary replica for read workload?

2011-12-22T18:26:00Z

In the previous blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2011/12/22/AlwaysOn-setting-up-readable-seconary-replica.aspx I mentioned that there are two options to configure secondary replica for running read workload. The first option ‘Read-intent-only’ is used to provide a directive to AlwaysOn secondary replica to accept connections that have the property ApplicationIntent=ReadOnly set. The word ‘intent’ is important here as there is no application check made to guarantee that there are no DDL/DML operations in the application connecting with ‘ReadOnly’ but an assumption is made that customer will only connect read workloads. If you connect an R/W application using false claim ApplicationIntent=ReadOnly, the application will connect successfully but will fail on the first DML/DDL thereby leading to a surprise to the customer.

Specifically, the benefits of ‘Read_Intent Only’ are

Ensuring primary workload is not impacted: The key scenario here is that you have setup a primary replica and one or more secondary replicas. You want to only allow R/W workload to run on the primary replica and prevent any reporting workload to connect to primary to guarantee that the performance of primary workload is not compromised. If primary is set up not to accept connection for read-only workload, then all connections initiated by read-only applications with option (i.e. ApplicationIntent=ReadOnly) to primary node will fail. Read-Only-Routing http://msdn.microsoft.com/en-us/library/gg471494(v=SQL.110).aspx uses ‘ReadOnly’ application intent to route the read workload to the readable secondary transparently without requiring user to know which secondary replica allows read workload.
Allowing older clients to work in AlwaysOn configuration without any changes: One key scenario of AlwaysOn is to allow DBMirror failover partner based clients/applications to work seamlessly in AlwaysOn configuration. The failover partner based protocol works because the connection to SQL Server only succeeds on the primary. When configuring AlwaysOn, you can set secondary replica to disallow connections or to allow connections with ApplicationIntent=ReadOnly. If you have configured allows customers to upgrade to AlwaysOn without any changes to existing DBMirror clients while still allowing new clients to take advantage of readable secondary using option (i.e. ApplicationIntent=ReadOnly). Customer can choose to migrate the failover based client applications to take full advantage of AlwaysOn functionality like multiple replicas at a later point.
Eliminate surprises: If an R/W application connects to readable secondary by mistake choosing to connect to the physical node instead of using VNN, the connection will succeed but the application will error out when the DML or DDL operation is executed. This will lead to a surprise to customer if s/he did not know that the application was actually connected to a secondary replica. This can happen if application connected to the primary node using physical node-name, not with VNN, and now there was a failover and the primary/secondary replicas switched roles. Read-Intent option will eliminate such surprises as presumably the R/W application would not have set ApplicationIntent=ReadOnly on the connection string. In fact, for R/W there is no need to set ApplicationIntent. Let us take an example. Suppose you have 2 node AlwaysOn configuration with N1 as primary and N2 as secondary. An R/W workload explicitly connects, without using VNN, to N1. Now a fail over happens and N1 now becomes the secondary but the application does not know about it. It connects to N1 thinking it is a primary leading to a surprise later on when it attempts a DDL or Insert/Delete/Update operation.

Note, you can only specify option (i.e. ApplicationIntent=ReadOnly) with new clients shipped as part of SQL12. If you have an application that uses older client, then you will need to use ‘Yes’ option for the secondary replica to allow read workloads. With this setting, secondary replica will allow newer clients as well.

Thanks

Sunil Agarwal

AlwaysOn: Setting up Readable Seconary Replica

2011-12-22T18:16:00Z

The easiest way to create an availability group and adding replicas is to use the availability group wizard. You can invoke the wizard by right-clicking on 'Availability Groups'. The picture of the explorer window below shows that I have created 1 availability group 'StockPro' with two replicas 'SUNILA03-6PLHSI' (primary) and 'SUNILA03-YLZO1U' (secondary). The availability group StockPro contains one database 'hadr_test' but you can have more databases if you want. For now, let us work with this simple example

While working with the wizard, you will be presented a choice to enable one or more secondary replicas for read work load as shown below in the picture. You can choose either ‘Yes’ or ‘Read-intent only’ to get the ability to run the read workload on the secondary replica. In the picture below, I have chosen ‘Yes’ option for replica running on instance ‘SUNILA03-YLZO1U’. A later blog <link> describes the difference between the two options.

Once you have configured the availability group, you can connect to the secondary replica either explicitly or using VNN and run your reporting workload. I will talk about VNN in a later blog.

Thanks

Sunil Agarwal

AlwaysOn: Value Proposition of Readable Secondary

2011-12-22T17:34:00Z

This series of blogs is targeting AlwaysON Readable Secondary. Please refer to http://blogs.msdn.com/b/sqlalwayson/archive/tags/availability+group/for other blogs in the related area

Readable Secondary is part of AlwaysOn suite of functionality available in SQL12 release. It allows customers to leverage their investment in High Availability hardware for offloading read workload such as reporting to one or more secondary replicas. Offloading reporting workload to secondary replica frees up resources on the primary node for primary application workload to achieve higher throughput and at the same time allowing resources on secondary replica to reporting workload to deliver higher performance. So it is a win-win situation both for primary and reporting workload.

Before we proceed further, it will be useful to look at the technology choices available to SQL customers today to offload read workload. This will provide us a good insight into the unique value proposition of Readable Secondary.

SQL Server 2008 offers following four High Availability choices to customers

Failover Clustering: This is shared disk environment and since only one node can have access to the database at one time, the second node cannot be used offloading read workload. The customers can increase the utilization of the HA hardware by deploying Active-Active cluster but not for offloading the read workload.
Database Mirroring: It is used for HA but it also provides read access to the data by creating a database snapshot on the mirror. The read workload can then run against the database snapshot. While many customers have used database snapshot for running reporting workload but it has some challenges as follows

The name of the database in database snapshot is different so if your application has hard coded database name, it will require modifications.
It is a static snapshot of the database. If an application needs to access the data that has changed since, a new database snapshot needs to be created with a new name unless you drop the old database snapshot. In other words, customers want accessing data in near real-time but it is hard, if not impossible, to achieve using database snapshot. However, if a reporting workload processes, for example, last week’s data, you can create a database snapshot on the weekly boundary and run your reporting workload.
Performance: Database snapshot employs copy on-write technology. When a database page is first modified after creating a database snapshot, a copy of the page made. This can significant overhead if there are multiple database snapshot. Also, during query processing it randomizes IO

Log Shipping: Log shipping can be thought of as manual database mirroring with the fail over time dependent on how frequently log backups are done and restored on the log shipped node. DBAs can open the Log Shipping destination for read workload with the added limitation that log backup cannot be restored while the Log Shipping destination is open for read workload. This can put DBAs into a fix as they need to choose between HA and the read workload. If they reduce the frequency of the log backup restores, it will increase the RTO (recovery time objective) and if the increase the frequency, they will need kill all currently running queries. This does not work for many customers especially if they have long running queries as they may never run to completion due to HA constraints. One can get around this by adding one more log shipping destination (i.e. more hardware and software license cost) where log backups can be restored at a lesser frequency assuming that we will be able to keep up with the log.
Replication: This is commonly deployed solution for HA and for offloading read workload. One of the key benefit of replication is that customer can create reporting workload specific indexes on the subscriber node. The challenges here are (a) may require schema changes (e.g. unique key) (b) not very suitable for high transaction throughput.

Readable Secondary addresses the challenges outlined with previous High Availability solutions with the exception of the ability to create reporting workload specific index(s) similar to the ones allowed by transaction replication. Readable Secondary allows read workload to run concurrently with the recovery thread that is applying the transactional logs from the primary. This allows reporting workload to access the data changes “live” as they occur on the primary replica and subsequently applied by the REDO thread on the secondary replica. The reporting workload is run without any changes as it is the same database on secondary replica as it was on the primary.

In the subsequent blogs I will describe how to setup readable secondary, configure application connectivity and ensuring predictable performance both for primary and secondary workloads

Thanks

Sunil Agarwal

SQL Databases on File Shares - It's time to reconsider the scenario.

2011-10-18T23:06:10Z

For those who have been around databases for any length of time, the idea of putting a database that you care about from either a reliability or performance perspective on an (SMB – Server Message Block) file share seems like a crazy idea, but recent developments have made SMB-based file shares a very viable platform for production SQL Server databases with some very interesting advantages.

Historically, the perspective has been:

File shares are slow.
The connections to the share may be unreliable.
The storage behind the file share may be flaky.
SMB consumes large amounts of CPU if you can get it running fast enough.

Over the past few years, all of these conditions have changed, and in particular the work which has been done on the 2.2 revision to the SMB protocol has produced some stunning results.

So let’s look at these one by one:

File Shares are slow

There are two components to this one:

The raw speed of Ethernet vs Fibrechannel and the speed/efficiency of the SMB protocol.

The transport layer has seen a very significant improvement in recent years. Where at one point Ethernet was orders of magnitude slower than Fibrechannel, this is no longer the case. Current commodity Ethernet is running up to 10 gigabit with 40 gigabit being tested, and on the near horizon. This will put Ethernet on par with Fibrechannel from a bit-rate perspective, and the projections are that the two technologies will leap-frog each other from here out with neither one being a clear leader.

On the protocol front, the original SMB 1.x protocol was chatty, inefficient, and slow. Over the last couple of years, the Windows file server team, while developing the 2.2 version of the protocol, has been using SQL Server, with a TPCC workload, as one of the performance benchmarks.

The benchmark configuration was to take a fibre-attached array, connect it to a server and run TPCC.
Then add an identical server connected to the first with a single 1Gb link, and run the TPCC database on the new server with the original server functioning as a file server.

When they started, TPCC over a file server ran at ~ 25% the speed of direct-attach storage. The team discovered several performance problems in the stack, but one particular bug on the client side made a stunning difference. The current results are that TPCC running over an SMB link as described above performs at 97% of the speed of direct-attach. That is a stunning result, and one which is not limited to Windows file servers since the fix is on the client side.

So now, we have an SMB implementation running at speeds comparable to a fibre-attached array.

Connections to the share may be unreliable

Again, there are multiple parts to this one. One aspect of this is that the underlying networking hardware has gotten very much more reliable in recent years. Consumers and enterprises alike just wouldn’t put up with flaky network connections these days. The popularity of FCoE (Fibre Channel over Ethernet) is an indicator of how much confidence of Ethernet as a storage transport has grown.

The second aspect to this one again comes back to the work done in the 2.2 version of the SMB protocol.
With this version, SMB has a number of resiliency features built in. If a link was to momentarily drop, in the past the connection would be lost and the file handle broken. With the 2.2 version of the protocol, the link is automatically re-established and the application never sees the event other than a momentary stall in outstanding IO.

If we take the configuration a step further, the file server itself can be clustered, and now has the capability to failover a share from one file server to the other without losing handle state. To clarify, SQL Server running an active workload can have the file server hosting the database files fail over, planned or unplanned, and SQL sees only a momentary drop in IO rates.

The storage behind the file server may be flaky or unreliable.

While it is always possible to put together an unreliable server, the tools now exist to incorporate very sophisticated reliability features right in the box. Particularly with the advent of Windows 8 features recently announced, we have a pretty good toolset native in the OS. We can create pools of storage which can be dynamically expanded. Pools can be assigned a variety of RAID levels. Many of the features which were previously only available in Fibre-attached arrays are now available with direct-attached storage on a Windows File Server. When you add in the capability for failover and scale-out clustering, the reliability becomes very impressive.

SMB consumes large amounts of CPU if you can get it going fast enough.

This is actually a painful aspect of Ethernet which has hurt iSCSI as well as SMB and other protocols.

A recent transport development is the rDMA transport, which enables data to flow directly from the network wire into user space, without being copied through kernel memory buffers.
This produces a huge reduction in CPU utilization at high data rates. How much?
I’ve seen an Infiniband-based SMB connection sustaining > 3 gigaBYTES per second, while consuming around 7% CPU, using SQLIO 512K writes as a workload. We’ve seen prototype units performing at twice that rate in the lab.

Additional benefits

Now that we’ve discussed why the factors which previously were blockers no longer are, let’s discuss some of the additional benefits:

Manageability

Consider the steps required for a DBA to move a database from one server to another:

SAN	SMB
Take database offline/detach	Take database offline/detach
File request to remap LUNs	Database is attached to new server using UNC path
Meet with storage admin	Database is brought online
LUNs are unmapped from original server
LUNs are mapped to new server
LUNs get discovered and mounted on new server
Database is attached to new server
Database is brought online

Additionally, you have one set of tools for configuring storage, as opposed to separate tooling for each SAN vendor which you use.

Cost

Cost is always a concern, and with the capabilities which this platform brings to bear, we can accomplish what previously has required a much more expensive solution, for a fraction of the cost without sacrificing performance, reliability or manageability.

Example

As one example of the whole package, one reference configuration for a SQL deployment had originally been configured with Infiniband for communications, and several small SAN arrays – one per server in a rack. By converting that configuration to a single clustered file server, the total cost of the solution was dropped dramatically: ~$50,000 in FibreChannel hardware was saved, and the cost savings in moving from multiple FC arrays to the clustered fileserver was very substantial.
The kicker though was that the performance of the solution was better than the original configuration, as previously it had bottlenecked on the
Storage Processors in the arrays.

So, the overall cost is substantially lower, the required features are delivered, and the performance is improved.

What’s not to like?

SQL Server Team is hiring

2011-05-17T21:16:27Z

Hello to you all,

The SQL Server team is hiring arcoss all test, dev and PM discipline. If you or someone you know is interested, please do apply for the job using the link with job postings below. Look forward to hearing from some of you.

Thanks

Sunil Agarwal

"SQL Server is one of the most important products at Microsoft and is the core on which our future server and storage technology will be built. Over the past decade we have experienced tremendous growth making us one of the largest businesses at Microsoft -- and this is still only the beginning. The importance of data continues to grow. Most applications being built today are data-centric. The ability to efficiently store, query, and share data at a low cost with built-in high-availability and flexible compute/storage capacity is a core competitive advantage for today's businesses.

The resurgence of Cloud computing is driving a major disruption to technology that is bringing broad changes to the technology landscape. With the current SQL Azure database-as-a-service offering, the SQL Server group is well positioned to become the dominant database-platform-as-a-service player. Competition is fierce and the time frames are aggressive. SQL Server is uniquely positioned to provide a friction-free and compatible database experience spanning both on-premise and cloud platforms. Our products will be offered as regular box software and also as software-as-a-service through our SQL Azure service. This choice creates a significant competitive advantage as customers can retain their existing skill-set and extend existing applications over SQL Azure to reap the benefits of the cloud platform. If you are excited about being part of this revolution that not only brings full-fledged database technology to the cloud, but also enables new kinds of data-centric application paradigms, then this is just the place for you. See www.microsoft.com/windowsazure/sqlazure for more details on the SQL Azure platform.

Joining SQL Server is a fantastic opportunity to work with some of the best engineers at Microsoft tackling challenging problems in a product that is a key driver to Microsoft's business. SQL Server fosters a supportive and productive work environment. SQL Server is known for providing growth avenues for developers to reach the highest levels. The team culture values engineering quality, technical excellence, and grass roots innovation. Every day you will be working on features that are used by millions of customers to power their businesses.

The SQL Server team is looking to hire a range of Individual Contributors and Leads across levels, disciplines and focus areas. If this interests you, please check out the job listing at here, and apply to the positions that match your passion and experience."

Data Compression and Fill Factor

2011-05-12T19:20:00Z

Recently, I was asked this question "If I have a compressed index and I rebuild it with fill factor 70%, do I actually get any fill factor? Or does compression take away the empty space?". The answer to this question is 'yes'. In fact all index options listed below available with data compression (both for ROW and PAGE)

<relational_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
Here is a simple script to show the 'fill factor" experiment
create table t1 (c1 int, c2 char (1000))
go

-- load 10000 rows
declare @i int
select @i = 0
while (@i < 10000)
begin
insert into t1 values (@i, REPLICATE ('1', 500))
set @i = @i + 1
end

-- create index with 40% fill factor
create clustered index ci on t1 (c1) with (fillfactor = 40)

-- run this DMV query to get the info on fill factor, number of pages
select max_record_size_in_bytes,page_count, avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats (db_id('foo'), object_id('foo.t1'), null, null, 'DETAILED')
where object_name (object_id) like 't1'

max_record_size_in_bytes                page_count           avg_page_space_used_in_percent
------------------------                 --------------------         ------------------------------
1011                                       2500                         50.0370644922165

-- enable ROW compression.Note, it will maintain the fill factor or 40%
alter index ci on t1 rebuild
with (
data_compression = ROW)

-- The output below shows almost ½ the page couns but fill factor is around 40%

max_record_size_in_bytes               page_count           avg_page_space_used_in_percent
------------------------                --------------------         -----------------------------
511                                               1430                 44.2960588089943

-- enable PAGE compression. Note, it will maintain the fill factor or 40%
alter index ci on t1 rebuild
with (
data_compression = PAGE)

max_record_size_in_bytes                      page_count           avg_page_space_used_in_percent
------------------------                         --------------------         ------------------------------
9                                                           42                   38.6835680751174

Thanks
Sunil Agarwal

Concurrency Series: My application was running fine yesterday but why is it blocking today?

2011-02-20T22:40:52Z

Please refer to http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-my-application-was-running-fine-yesterday-buy-why-is-it-blocking-today.aspx for details. Cross-referencing here

Thanks

Sunil Agarwal

Concurrency Series: Minimizing blocking between updaters

2011-02-20T22:39:19Z

Please refer to http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-minimizing-blocking-between-updaters.aspx for details. Cross referencing my post.

Thanks

Sunil Agarwal

Concurrency Series: Why do I get blocked when no one has locked the row(s) being queried?

2011-02-20T22:37:00Z

Please refer to http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-why-do-i-get-block-when-no-one-has-locked-the-row-s-being-queried.aspx for details

Thanks

Sunil Agarwal

Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?

2011-02-20T22:36:19Z

I posted this in SQLCAT blog site. This post provides reference to http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-why-do-i-get-blocking-when-i-use-read-uncommitted-isolation-level-or-use-nolock-hint.aspx

Thanks

Sunil Agarwal

Concurrency Series: Basics of Transaction Isolation Levels

2011-02-20T22:34:22Z

Please refer to the blog http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

Thanks

Sunil Agarwal

Concurrency Series: Common Concurrency Issues with Applications

2010-06-04T20:13:00Z

As part of my role as a program manager in SQL Server Engine, one of the areas I am reponsible for is the concurrency related features in SQL Server. If you have been involved with database applications as a develeoper or as a DBA in keeping the application running, I am sure you will agree that blocking is one of the most interesting and challenging problems. I have seen cases where an application was running fine yesterday but it starts blocking suddenly. The culprits can be many such as lock escalation or query plan changes or just increased seasonal user workload.

If you look at the concurrency theory, it is actually quite simple for most part. SQL Standard defines 4 isolation levels (read uncommitted, read committed, repeatable read and serializable). While SQL Standard does not dictate how these isolation levels need to be implemented, but most common implementation is to build them using 'Locks'. SQL Server provides you a choice using either the lock based or row versioning based (non blocking) implementation for the 'default' read committed isolation level.

Each vendor defines different lock types and corresponding lock compatibility matrix. The locking modes/compatibility matrix is intutive in most cases. For example, it is easy to understand that two transactions that need to modify the same row need to be serialized (i.e. one transaction must complete before other is allowed to modify the row). You may ask 'oh really?' what if one transaction is modifying column1 and other transaction is modifying column2, we should allow them to proceeed? This can be possible if locking granularity at a column level is supported but that won't come for free (i.e. takes more resources and complexity). As you can guess, there is trade off betweek concurrrency and resource consumption. If you use lower locking granularity, you will get more concurrency but with more overhead and possibly more deadlocks.

Before I get buried in these details, I want to spell out the modest goal of my subsequent blog(s) on this topic. I want to take some very simple examples to illustrate the common blocking issues I have seen with customers and suggest some ways how these can be addressed. I will try to propose solutions, when possible, that don't require any application changes. But clearly, this is not always possible. Hopefully once we are done with the series of blogs on concurrency, I will consolidate the learning into some general guidelines.

Stay tuned

Thanks

Sunil Agawal

Minimal logging and MERGE statement

2010-06-03T17:23:00Z

In my earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx, we discussued how we can get minimal logging using TF-610 when using TSQL INSERT statement. Interestingly, you can get minimal logging when using MERGE statement as well. Here is a simple example to illustrate it.

use bulktest
go

alter database bulktest set recovery SIMPLE

-- enable the trace flag 610
dbcc traceon (610, -1)

-- create the staging table
create table t_staging (c1 int, c2 int, c3 char (100), c4 char(1000))
go

-- load 10000 rows into the staging table
declare @i int
select @i = 0
while (@i < 10000)
begin
insert into t_staging values (@i, @i+10000, 'indexkey', 'hello')
select @i= @i + 1
end

-- create the target table with clustered index
DROP TABLE t_target
GO

create table t_target (c1 int, c2 int, c3 char(100), c4 char(1000))
go

create clustered index ci on t_target(c1)
go

-- clean up the log
while @@trancount > 0 rollback
checkpoint

-- execute a merge statement under a transaction
BEGIN TRAN
MERGE INTO t_target AS Target using t_staging as Source
ON Target.c1 = Source.c1
when matched then
    update set c4 = source.c4
when not matched by target then
     insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;

-- Now look at the top 20 log records
select top 20 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName
from fn_dblog(null, null)
where allocunitname='dbo.t_target.ci'
order by [Log Record Length] Desc

-- here is the output

You can see that after the first data page worth of rows, the rest of the inserts are minimally logged. The same can be done if the target table was a HEAP but in this case, you will need to use TABLOCK hint as shown here

MERGE INTO t_target with (TABLOCK) AS Target using t_staging as Source
ON Target.c1 = Source.c1
when matched then
update set c4 = source.c4
when not matched by target then
insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;

This will work even when you have non-empty target table when the inserted rows go to newly allocated pages.

Thanks

Sunil Agarwal

Backup compression and Virtual Device Interface (VDI)

2010-02-21T23:07:00Z

People often ask if they can get native SQL Server backup compression using VDI interface. The simple answer is 'yes' but there are few things you need to be aware of as described below. You can get details on VDI by clicking link-to-VDI-Specifications.

Within the VDI protocol, the backup application specifies a TSQL backup command. This means that the backup application can use any TSQL command line options including the option 'WITH COMPRESSION'. In this case, the SQL Server will send a pre-compressed (using SQL native compression algorithm) data stream. However, if the server default is set to always compress, the SQL Server will not send compressed data stream to VDI initiated backup unless the application specifically requests it. The reason for this logic is that some backup applications depend on reading and interpreting the backup stream, and if SQL Server sends compressed bytes, it can potentially break the backup application. For example, there are 3rd partly backup tools that allow extracting an object (i.e. a table) from the backup and if the backup is compressed in proprietary format, the tool will not be able to extract the object. On the other hand, if the backup application explicitly requests the backup compression, then we know that the application is expecting an opaque, compressed stream. Secondarily, some backup applications do their own compression and may prefer to use their own algorithm. That’s perfectly OK too.

Thanks

Sunil Agarwal and Kevin Farlee

Performance improvement by orders of magnitude when merging partitions in SQL Server 2008R2

2010-02-03T23:38:00Z

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 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

Improvement in minimizing lockhash key collisions in SQL Server 2008R2 and its impact on concurrency

2010-01-18T20:52:00Z

Another improved functionality in SQL Server 2008 R2

I am posting this on behalf of Juergen Thomas who has been with SQL Server PM team from 12+ years and is an expert in SAP.

Juergen>> In this article I’d like to talk about another improvement we made to SQL Server 2008 R2. The improvement pretty much is not noticed since it is buried deep into the SQL Server Engine. There also is nothing to tune about it. It is about the hash key algorithm which is used by SQL Server in its Lock Manager. So let’s step and explain what SQL Server does and what impact this change in SQL Server 2008 R2 has.

How does the locking work in SQL Server?

Unlike some other database vendors, there is a logical component to SQL Server’s Lock Manager. SQL Server uses a lockhash value to represent a lock on the lock structure in the SQL Server Lock Manager instead of using the physical description to a row, page or a table. The lockhash value then is kept in memory. This design was driven by major considerations like:

· No locking information to be stored on the page containing the resource. This eliminates additional IO or any space penalty on the page due to locking.

· Since the key to a row could be as large as 900 bytes, using the real key values would have inflicted larger memory consumption. Especially with applications running long transactions and holding hundreds of thousands of locks. Therefore one needed to seek for a possibility to have a lock value which would not exceed a few bytes and fixed in size for better memory management

The solution to this problem was found when designing SQL server 7.0 in 1996 and 1997 by using the key of the row and apply a hash algorithm to it which then results in a 6 byte long lockhash value. This value is stored as resource description. Added to this is HoBT ID (B-Tree ID). If an another row in the same B-Tree needs to be locked, the hash value for the key of the row gets calculated and then compared to the hash values already stored as granted or waiting locks in order to see whether a lock on this row already exists. This mechanism worked sufficiently well for many years

Issues appearing on the horizon

Using a hash algorithm to calculate a value out of keys does have one small disadvantage:

· Depending upon the # of rows, structure of the primary key, the data distribution and the complexity of the hashing algorithm, one can get hash collisions. For example, one calculated lockhash value can lock more than one row within a B-Tree.

How can we see what the hash key value for a lock held on a key is?

Let’s demonstrate this with an example.

CREATE TABLE test

(a VARCHAR(3) NOT NULL, b VARCHAR(8) NOT NULL, c VARCHAR(5) NOT NULL,

d integer NOT NULL)

GO

CREATE UNIQUE CLUSTERED INDEX ucl ON test(a,b,c)

GO

begin transaction

INSERT test VALUES('150','00001082','00345',1)

Now let’s perform this query:

select resource_type,resource_database_id, resource_description, resource_associated_entity_id,request_mode, request_type, request_status from sys.dm_tran_locks where resource_type = 'KEY'

The result will look like:

resource_type	resource_database_id	resource_description	resource_associated_entity_id	request_mode	request_type	request_status
KEY	5	(5c017ccf0cbf)	72057594038976512	X	LOCK	GRANT

As resource_description you can see the value our hash key algorithm did calculate out of the input of the three key values ‘150’, ‘00001082’ and ‘00345’. The column ‘resource_associated_entity_id’ is the id of the B-Tree. It also finds usage in the system views sys.partitions and sys.allocation_units and can be used to get to the object_id or the name of the table the B-Tree belongs to.

Now let’s insert another row into the very same table with another Query Window. We’ll try to insert with this command:

begin transaction

INSERT test VALUES('150','00024855','00012',4)

To our surprise the insert of this row seems to be blocked and doesn’t come back (please note that the transaction of the first Query Window with the first row inserted still is open). Executing the same query against sys.dm_tran_locks gives us this result:

resource_type	resource_database_id	resource_description	resource_associated_entity_id	request_mode	request_type	request_status
KEY	5	(5c017ccf0cbf)	72057594038845440	X	LOCK	GRANT
KEY	5	(5c017ccf0cbf)	72057594038845440	X	LOCK	WAIT

As this shows we are basically blocked with inserting the second row. The X-Lock could not be acquired because the hash algorithm used did calculate the very same hash value for the 3 key column values of the second row to be inserted.

This issue demonstrates an undesired side effect of using hash algorithms to calculate lock values. However fortunately in most customer workloads, we rarely encountered this issue with customers. One of the reasons certainly is that the row colliding under the same hash value usually are far apart. E.g. if we lock a row of a general ledger table out of the current month in the company code of the corporation and there is a collision with a row of last year in the company code of one of the small subsidiaries, there will be no practical effect of such a hash collision. However, where we encountered a negative side effect of this was in large data loads. There it is all about getting tables of sometimes Terabyte in size loaded as fast as possible. Therefore one often splits up the loads in the export phase already. Importing those packages now in parallel (sometimes up to two dozen packages against one table) we did encounter blocking locks as well as deadlocks between those load streams.

How does SQL Server 2008 R2 resolve this issue?

In SQL Server 2008 R2, the hashing algorithm calculating the lockhash value was rewritten. The goals to make it more complex on the one side to avoid collisions without compromising the performance. We analyzed a lot of ISV customer database plus some other other customer databases to come up with a hash algorithm which used comparable CPU cycles but produced a better distribution of hashed values. In all the cases that we tested, we found a dramatic reduction of hash key collisions. For example, in 3 billion row tables where we had more than hundred thousand collisions with the old algorithm. Also, in many cases, we eliminated the hash collisions completely. With all the test data we used, the probability to get hash collisions is further lowered by a factor of 15,000 using the new hashing algorithm. After more than extensive tests for months and months, the new hash algorithm got released with the November CTP of SQL Server 2008 R2. For the example above, we don’t see any hash collision anymore with SQL Server 2008 R2 as the table below proves. This is not to say that collision will not occur but it will be very unlikely.

Result of running of performing this query against scenario described above:

select resource_type,resource_database_id, resource_description, resource_associated_entity_id,request_mode, request_type, request_status from sys.dm_tran_locks where resource_type = 'KEY'

resource_type	resource_database_id	resource_description	resource_associated_entity_id	request_mode	request_type	request_status
KEY	5	(4b729e3b9a50)	72057594038845440	X	LOCK	GRANT
KEY	5	(7bd75c434bae)	72057594038845440	X	LOCK	GRANT

As one can see, in the case of SQL Server 2008 R2 (November CTP) the second insert didn’t get blocked anymore since the lockhash value in the column resource_description differs.

Changes to sp_estimate_data_compression_savings stored procedure in SQL2008R2

2009-09-16T22:53:00Z

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

-- Create the table

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

-- 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