Dan Guzman's Blog

Sliding Window Table Partitioning

2008-08-30T18:58:07-05:00:00

SQL Server table partitioning provides a great way to manage a time-based sliding window. By mapping each time period to an individual partition, old data can be efficiently purged or archived using a nearly instantaneous switch out of an entire partition. However, there are a couple of aspects of a time-base sliding window strategy that require some thought and planning.

RANGE LEFT or RIGHT

The RANGE LEFT or RIGHT partition function specification indicates which partition includes the exact match on the partition boundary. It is especially important to consider both the RANGE specification and boundary value when the partition function data type includes a time component (smalldatetime, datetime, datatime2 and datetimeoffset) because one usually wants all data for a given date in the same partition.

In the case of a RANGE LEFT, specify the latest time allowable for the data type and beware of implicit rounding. For example, a boundary value of ‘2008-08-30T23:59:59.999’ for a datetime partition function will get rounded up to ‘2008-08-31T00:00:00.000’ and result in data for midnight 2008-08-31 getting inserted into the right partition instead of the left. To avoid this rounding issue, specify ‘2008-08-30T23:59:59.997’ instead.

The following table shows examples that ensure ‘2008-08-31T00:00:00’ always ends up in the LEFT partition. The datetimeoffset examples assume UTC time zone (+00:00) is the desired date boundary but this can be adjusted as desired.

Partition Function Datatype	Range LEFT Boundary Specification
smalldatetime	‘2008-08-30T23:59:00’
datetime	‘2008-08-30T23:59:59.997’
datetime2(0)	‘2008-08-30T23:59:59’
datetime2(1)	‘2008-08-30T23:59:59.9
datetime2(2)	‘2008-08-30T23:59:59.99’
datetime2(3)	‘2008-08-30T23:59:59.999’
datetime2(4)	‘2008-08-30T23:59:59.9999’
datetime2(5)	‘2008-08-30T23:59:59.99999’
datetime2(6)	‘2008-08-30T23:59:59.999999’
datetime2(7)	‘2008-08-30T23:59:59.9999999’
datetime2	‘2008-08-30T23:59:59.9999999’
datetimeoffset(0)	‘2008-08-30T23:59:59+00:00’
datetimeoffset(1)	‘2008-08-30T23:59:59.9+00:00’
datetimeoffset(2)	‘2008-08-30T23:59:59.99+00:00’
datetimeoffset(3)	‘2008-08-30T23:59:59.999+00:00’
datetimeoffset(4)	‘2008-08-30T23:59:59.9999+00:00’
datetimeoffset(5)	‘2008-08-30T23:59:59.99999+00:00’
datetimeoffset(6)	‘2008-08-30T23:59:59.999999+00:00’
datetimeoffset(7)	‘2008-08-30T23:59:59.9999999+00:00’
datetimeoffset	‘2008-08-30T23:59:59.9999999+00:00’

With a RANGE RIGHT partition function, specify only the desired date (or specify date with time of midnight). This will ensure that all data for the specified date is in the same partition.

Avoid Data movement with SPLIT and MERGE

SPLIT and MERGE is very fast when the partitions are empty because no data movement is necessary. The goal is to plan sliding window partition maintenance accordingly. After the start of a new period, SWITCH out old data and remove the vacated empty partition with MERGE. SPLIT the last partition (empty) in anticipation of future data.

The choice of a RANGE LEFT or RIGHT partition also affects how one uses SWITCH, SPLIT and MERGE to maintain the sliding window.

Sliding a LEFT Partition Function Window

At the start of a new period with a LEFT partition function, partition 1 contains the old data, the second from last partition contains the current data and then last partition (empty) is for future data. The partition can be maintained as follows:

1) SWITCH out partition 1 to a staging table for archive/purge

2) MERGE empty partition 1 with non-empty partition 2 to form a new partition 1 (with data)

3) SPLIT last partition so that the last 2 partitions are now empty

4) Repeat steps 1 through 3 after the start of the next period

Sliding a RIGHT Partition Function Window

The RIGHT partition maintenance strategy is slightly different that the LEFT strategy. The difference is that partition 2 rather than partition 1 contains the old data at the start of a new period. However, just like the LEFT strategy, the second from last partition contains the current data and then last partition (empty) is for future data. The partition can be maintained as follows:

1) SWITCH out partition 2 to a staging table for archive/purge

2) MERGE empty partition 1 with empty partition 2 to form a new partition 1 (empty)

3) SPLIT last partition so the last 2 partitions are now empty

4) Repeat steps 1 through 3 after the start of the next period

Automating the Sliding Window Maintenance

In my next post, I’ll share a script that will help automate sliding window maintenance.

Are you a DBA Monkey?

2008-08-01T17:56:28-05:00:00

We have a number of de facto standards in our shop, such as always placing data and indexes on separate filegroups. I asked why we bothered with the separate filegroups since the underlying files were often on the same physical disks anyway. No one really knew why. The practice had been followed before the current DBAs joined the organization and has continued.

This reminded me of a parable my boss told me several years ago. Five monkeys are placed in a cage with a step ladder leading to a bunch of bananas suspended from the top. The first monkey rushes up the ladder eagerly only to get forcibly knocked off by a stream of water from fire hose when he reaches the top. The other monkeys are also drenched with the ice cold water. After recovering from the shock, another monkey climbs the ladder with the same result. Eventually, each monkey tries to reach the bananas and learns the futility and unpleasantness of the effort. Any monkey that even tries to climb the ladder is promptly tackled by his peers.

Now attrition begins. One of the original monkeys is removed and replaced with a new monkey. He looks at the bananas and back at the other monkeys, wondering why they ignore such a tasty treat. He starts up the ladder and is immediately pulled off and beaten by the other monkeys. After a couple of attempts, the new monkey learns to avoid the ladder. Another original monkey is replaced with a new monkey. When he goes for the bananas, he also gets tackled by the other monkeys, including the one that’s never been soaked. The same process is repeated; original monkeys are replaced and new monkeys quickly learn to avoid the ladder despite the prize at the top. The last original monkey is eventually replaced.

We now have a group of monkeys who’ve never been soaked by the fire hose, won’t climb the ladder and won’t allow others to climb it either. It doesn’t matter if the fire hose is removed or how many generations of monkeys follow. They don’t know why they do what they do.

Like the monkeys, institutional memory causes us to follow practices mindlessly and be slow to adapt to changes in our environment. Going back to my data/index filegroup question, there may have once been a good reason for the separate filegroups. Perhaps the separate groups improved performance of a specific application workload by isolating random and sequential I/O on local disk storage. Our current environment is quite different, however. Physical disks on the current SAN storage are often shared with other hosts/LUNs so placing data and indexes on different filegroups no longer isolates the I/O. Furthermore, the separate filegroup approach was since generalized to apply to all databases regardless of whether or not it made sense for a given application workload. We continue the data/index filegroup practice because the reasoning behind the separate filegroups is not known or understood.

It may be easier to robotically continue with the status quo but I think we need to understand exactly why a given filegroup approach might be advantageous so that we can choose what is best for the situation at hand. For example, I/O Performance of our high transaction OLTP databases is best achieved by balancing the heavy random I/O workload over as many disks as possible because this maximizes the disk transfers/sec rate. I don't think segregating data and index files is appropriate for our OLTP applications because it artificially caps the maximum I/O rate and can actually introduce an unnecessary I/O bottleneck.

In contrast, a single filegroup for both data and indexes distributes I/O proportionally according to the size of the underlying files with less complexity and administration than the separate filegroup approach. In my experience, this single user-defined filegroup strategy works well for both OLTP and mixed workloads where sequential I/O cannot be accurately predicted. Note that we need to work closely with the SAN storage team to meet performance objectives regardless of the filegroup strategy we choose since the physical implementation is outside our control.

We also have databases containing large amounts of historical data. In that case, we can use filegroups to place infrequently accessed older data on read-only filegroups with the files on relatively slow inexpensive storage. Often-queried recent data can then be stored on the more expensive fast storage devices via a read-write filegroup. The key here is that the additional maintenance and administration complexity is justified by the hardware savings. Similarly, our large reporting databases can benefit from a customized filegroup strategy to facilitate backup/recovery or improve performance by isolating random and sequential I/O for specialized (and predictable) workloads.

Like many of the choices we make, there is no single filegroup strategy that is optimal for all cases. It’s important to understand the rationale behind a given approach so that one can choose the best one for the current situation and create new strategies when needed. A key differentiator between average DBA and a good one is a thorough knowledge of why practices are followed. Don’t be a monkey.

Blocking is not Deadlocking

2008-07-26T10:41:32-05:00:00

After interviewing several candidates for a database position, I was surprised to find that many didn’t know the difference between a block and deadlock. Many used the terms interchangeably because they thought both were synonymous. Although missing this interview question didn’t necessarily disqualify those candidates, it certainly didn’t help their chances. I decided I’d contrast blocking and deadlocking here to help those who might not grasp the difference.

Blocking

Blocking is a necessary side effect of using locks to control concurrent resource access. A lock is either compatible or incompatible with other locks depending on the lock mode. Before a lock is granted, SQL Server first checks to see if an incompatible lock on the same resource exists. If not, the lock request is granted and execution proceeds. When the requested lock mode is incompatible with an existing lock mode on the same resource, the requesting session is blocked and waits until:

1) The blocking session releases the lock

2) The LOCK_TIMEOUT threshold of the blocked session is reached (indefinite by default)

3) The blocked client cancels the query

4) The blocked client times out (30-second default in ADO/ADO.NET, technically the same as #3)

The SQL Server Books online is an excellent resource for a more detailed description of lock modes, compatibility and related information so I won’t repeat that information here. I strongly recommend that serious database professionals peruse the Locking in the Database Engine topic of the SQL Server Books Online, especially the following subtopics:

· SQL Server 2005 Books Online: Lock Modes

· SQL Server 2005 Books Online: Lock Compatibility

· Lock Granularity and Hierarchies

Deadlocking

A deadlock is basically a special blocking scenario where 2 sessions are waiting on each other (directly or indirectly). Neither can proceed so both will wait indefinitely unless a timeout or intervention occurs. Unlike a normal blocking scenario, SQL Server will intervene when a deadlock situation is detected and cancel one of the transactions involved. The locks of the cancelled transaction are then released so the other blocked session can proceed. SQL Server chooses the transaction that is the least expensive to rollback as the deadlock victim by default. If SET DEADLOCK_PRIORITY has been issued, SQL Server chooses the one with the lowest priority as the victim.

A deadlock always starts as a normal block with one session waiting while the other continues. It is only when the running session is later blocked by the waiting session that the deadlock occurs. The scenario is easily illustrated by executing queries from 2 different SQL Server Management Studio query windows. First, create a table with insert test data:

USE tempdb;

CREATE TABLE dbo.DeadlockExample

(

DeadlockExampleKey int NOT NULL

CONSTRAINT PKDeadlockExample PRIMARY KEY CLUSTERED

);

INSERT INTO dbo.DeadlockExample (DeadlockExampleKey) VALUES(1);

INSERT INTO dbo.DeadlockExample (DeadlockExampleKey) VALUES(2);

Run the following from window 1:

USE tempdb;

BEGIN TRAN;

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 3

WHERE DeadlockExampleKey = 1;

Then run this script from window 2:

USE tempdb;

BEGIN TRAN;

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 4

WHERE DeadlockExampleKey = 2;

--this UPDATE will be blocked by session 1

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 5

WHERE DeadlockExampleKey = 1;

You can view session and lock info during this blocking episode by executing sp_who2 and sp_lock from a new SSMS window or you can use the Activity Monitor from the SSMS Object Explorer GUI. A query against the sys.dm_tran_locks DMV and sys.partitions catalog view will also show the table involved in this blocking episode:

SELECT

OBJECT_NAME(p.object_id), *

FROM sys.dm_tran_locks AS dtl

JOIN sys.partitions AS p ON

dtl.resource_associated_entity_id = p.hobt_id;

Finally, run the UPDATE below from window 1:

UPDATE dbo.DeadlockExample

SET DeadlockExampleKey = 6

WHERE DeadlockExampleKey = 2;

This will cause a deadlock between the sessions because session 1 is waiting for key 2 (exclusively by session 2) and session 2 is waiting for key 1 (exclusively locked by session 1). SQL Server will choose one of the sessions as a victim and that session will get rolled back and receive error 1205:

Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

See the Deadlocking topic Books Online for more information on deadlocks as well as information on diagnostic tools.

Performance and Concurrency

2008-06-28T09:15:33-05:00:00

Performance and concurrency go hand-in-hand. In fact, these are more closely tied that many DBAs and developers realize. I’ll underscore the important relationship between performance and concurrency in this last article of my concurrency series and explain why performance tuning can fix blocking problems in addition to improving response times.

Why Performance Affects Concurrency

A bad query execution plan touches much more data than necessary and also consumes inordinate CPU and disk resources. The result is that more locks are acquired than needed and these are held for longer durations when concurrent queries complete for conflicting locks, CPU and disk resources. This can lead to a downward spiral of hardware bottlenecks, long-duration locks, blocking and deadlocks. I’ve seen some companies even throw hardware at such a problem rather than address the underlying cause. Although faster hardware is one way to reduce lock duration deadlock likelihood and improve overall performance, this is most certainly not the best approach.

Keep in mind that unnecessarily high resource utilization manifests itself as a performance problem only in severe cases. An occasional query that scans thousands of rows when only a few are needed might barely be noticeable in Performance Monitor. However, the amount of rows touched greatly increases the likelihood that the query will block (or be blocked by) other concurrent queries. It is often only after the problem escalates into a severe blocking incident that a DBA becomes aware that the latent problem exists.

Tuning addresses blocking and deadlocking problems for a number of reasons. Most importantly, index tuning provides an efficient path to data so minimal data needs to be locked while minimal CPU and disk resources are used. Query tuning ensures that queries are formulated to achieve the desired result efficiently and that expressions are sargable. Best Practices in server configuration and maintenance, such as data and log files on different drives and keeping stats up-to-date, help transactions and queries run as quickly as possible. Well-tuned databases consume minimal hardware resources so more queries can be processed concurrently and short-term blocking is not even noticeable.

I strongly advocate examining query execution plans (ctrl-L in SSMS) before promoting stored procedures and queries to production. Check to ensure that scans are done only when appropriate for the task at hand and indexes are used efficiently (seeks). Even if query response time is adequate, a suboptimal plan can lead to avoidable concurrency problems. Proactively examining execution plans is a great way to maximize both performance and concurrency.

Concurrency Model Confusion II

2008-06-07T14:49:43-05:00:00

In the first post of this series, I reviewed concurrency concepts and highlighted differences in concurrency terminology from a database and application perspective. I’ll now discuss how applications can leverage SQL Server features to meet concurrency objectives.

SQL Server Concurrency Terminology

Optimistic concurrency control inside the SQL Server 2005 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control. Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.

I don’t personally like calling SQL Server concurrency control methods either optimistic or pessimistic. I prefer to instead view concurrency control objectives from the application perspective and choose the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL Server optimistic row versioning should not be used solely because the application uses optimistic concurrency. A better choice for a well-tuned, highly transactional application might very well be default pessimistic SQL Server concurrency control behavior.

Row Versioning and Concurrency

Row versioning isolation levels can improve concurrency because committed data can be read from the version store instead of acquiring locks on data read. All update and delete statements store pre-update versions of changed data in the tempdb version store once either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is turned on. SQL Server then returns these pre-update row versions to applications reading data in the READ_COMMITTED (with READ_COMMITTED_SNAPSHOT database option on) or SNAPSHOT transaction isolation levels. The main difference between row versioning in these isolation levels is that READ_COMMITTED is single-statement level and SNAPSHOT is transaction level (multi-statement). See Row Versioning-based Isolation Levels in the Database Engine in the Books Online for details.

A DBA can sometimes address blocking/deadlocking concurrency problems unilaterally by simply turning on the READ_COMMITTED_SNAPSHOT database option because statement-level row versioning is transparent to applications running in the default READ_COMMITTED isolation level. However, this shouldn’t be done indiscriminately because row versioning has an associated cost in terms of disk I/O, storage, CPU and DBA management. One can even make concurrency problems worse by enabling row versioning unless there are sufficient server resources to handle the additional work introduced by row versioning. See the Overhead of Row Versioning post in the SQL Server Storage team blog for more info on I/O and storage overhead.

SNAPSHOT isolation level row versioning can also improve concurrency. However, SNAPSHOT isolation is a different paradigm than most SQL Server developers are accustomed to. With SNAPSHOT isolation, applications have a consistent view of the database from the time the transaction starts so developers need to consider the ramifications of retrieving stale data and perhaps delegating the task of checking for changed data in an optimistic model to the database engine. I elaborate more on SNAPSHOT later.

Optimistic Concurrency from an Application Perspective

SQL Server applications have traditionally implemented optimistic concurrency by detecting conflicts when data are saved. This is done by either comparing either the original/current values of the target columns or a rowversion column. The READ_COMMITTED example below can run with or without row versioning enabled.

--retrieve latest values when user clicks edit

SET TRANSACTION ISOLATION LEVEL READ_COMMITTED;

SELECT

ContactName,

ContactTitle

FROM dbo.Suppliers

WHERE SupplierID = @SupplierID;

--check for conflicts when user clicks save

UPDATE dbo.Suppliers

SET

ContactName = @NewContactName,

ContactTitle = @NewContactTitle

WHERE

SupplierID = @SupplierID AND

ContactName = @OriginalContactName AND

ContactTitle = @OriginalContactTitle;

IF @@ROWCOUNT = 0 --a zero rowcount indicates data was deleted or changed

BEGIN

RAISERROR ('Contact information was changed by another user', 16, 1);

END;

The above optimistic concurrency technique requires no persistent database connection. Locks are held only for the short duration of the initial SELECT (and only when READ_COMMITTED_SNAPSHOT is off) and again during the subsequent update. No persistent database connection is needed because no explicit transaction is used. This same method can be used with or without the READ_COMMITTED_SNAPSHOT database option turned on.

Applications can also leverage SQL Server’s SNAPSHOT transaction isolation level to implement optimistic concurrency. In the following example, a transaction is started and persistent database connection is maintained until the COMMIT:

--retrieve latest values when user clicks edit

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SET XACT_ABORT ON;

BEGIN TRAN;

SELECT

ContactName,

ContactTitle

FROM dbo.Suppliers

WHERE SupplierID = @SupplierID;

--no need to check for changed data when user clicks save

UPDATE dbo.Suppliers

SET

ContactName = @NewContactName,

ContactTitle = @NewContactTitle

WHERE

SupplierID = @SupplierID;

COMMIT;

Unlike the READ_COMMITED method, the UPDATE statement in the SNAPSHOT level doesn’t need to check for changed data. SQL Server will raise an exception during the UPDATE if data was changed by another session since the start of the transaction. In this SNAPSHOT example, error 3960 is raised if the Supplier row changed since the transaction started:

Msg 3960, Level 16, State 2, Line 13

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Suppliers' directly or indirectly in database 'Northwind' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

The persistent database connection requirement limits SNAPSHOT isolation scalability so it shouldn’t be used as a general means of implementing optimistic concurrency. It wouldn’t be practical to maintain open transactions for thousands of concurrent web user sessions or keep transactions open indefinitely while waiting for user response. However, SNAPSHOT is a viable alternative to the SERIALIZABLE transaction isolation level. Applications that would otherwise require SERIALIZABLE might consider SNAPSHOT alternative to improve concurrency.