SQL Server 2008 Snapshot Isolation Hands-on Lab
Table of Contents
SQL Server 2008: Snapshot Isolation ............................................................................................. 3 Objectives ................................................................................................................................. 3 Prerequisites ............................................................................................................................. 3 Lab Scenarios ........................................................................................................................... 4 Virtual PC Configuration and Setup ............................................................................................ 5 Using Virtual Machines ........................................................................................................... 5 The “Isolation” Lab Application .............................................................................................. 7 Tips on how to successfully complete the lab exercises .......................................................... 8 Part I: Pessimistic Locking ............................................................................................................. 9 Part 2: Activating Snapshot Isolation & Read Committed with Snapshot Isolation .................... 16 Part 3: Using Snapshot Isolation (SI)............................................................................................ 23 Part 4: Using Read Committed with Snapshot Isolation (RCSI) .................................................. 28 Part 5: Monitoring Snapshot Isolation & Read Committed with Snapshot Isolation ................... 31 Additional Snapshot Isolation Resources ..................................................................................... 35
Content created by
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 3 of 35
SQL Server 2008: Snapshot Isolation
The goal of these hands-on lab materials is to get an understanding of when to use one of the more advanced features of SQL Server 2008: Snapshot Isolation. The intent of these exercises is to provide you with best practices and implementation details for SQL Server 2008. As with all software development projects, your production environment may differ from this build and this environment. Be sure to design, implement, and test your final architecture extensively to minimize unexpected performance and data locking issues under concurrent workloads. For the latest details on SQL Server 2008, please visit http://www.microsoft.com/sql/2008/.
After completing these self-paced labs, you will be able to: Understand appropriate uses for the transaction isolation levels: Snapshot Isolation and Read Committed with Snapshot Isolation (RCSI) Understand pre-requisites required to configure databases to use Snapshot and RCSI isolation Understand the implications of activating Snapshot & RCSI isolation Describe the locking patterns caused by the interaction of transaction isolation levels Understand the difference between Optimistic and Pessimistic transaction isolation schemes Use a lab exercise application to generate interacting transactions and understand the exceptions raised by transaction interaction Use SQL Profiler to track commands being issued by the lab exercise application Use SQL Server Management Studio to examine system tables relating to Snapshot Isolation & RCSI Familiarity with Transactions and with ADO.NET 2.0 terminology desirable but not essential Some exposure to SQL Server Database Administration Experience with the SQL Server 2005 Tools or SQL Server 2008 Tools is very helpful but not required Familiarity with the Transact-SQL language is very helpful but not required Desire to sink your teeth into SQL Server 2008!
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 4 of 35
1. Pre-SQL Server 2005 – Pessimistic Locking This exercise is meant to introduce you to the lab application and to show you how to capture the SQL requests it makes. By showing you how pessimistic database locking works under the covers you will be able to contrast what you see with later exercises. This exercise also explains how pessimistic locking avoids update conflicts. Time for Completion: 15 minutes 2. Activating Snapshot Isolation & Read Committed with Snapshot Isolation This exercise is more focused on Database Administrators because typically they will be responsible for activating these database level features on the server. Some important considerations for making these changes to systems with longrunning transactions are explained and demonstrated. Time for Completion: 15 minutes 3. Using Snapshot Isolation (SI) This exercise shows how SI can be used to present a transactionally consistent view of the database without the wholesale locking typical of the Serializable isolation level. You will explore what happens when a data update conflict takes place and reviews the impact on application design. Time for Completion: 20 minutes 4. Using Read Committed with Snapshot Isolation (RCSI) Snapshot Isolation provides a transactionally consistent view of your application‟s database but requires application changes. RCSI changes the behavior of the default Read Committed isolation level. This exercise illustrates the differences between SI and RCSI and discusses scenario where RCSI should be approached with caution. Time for Completion: 10 minutes 5. Monitoring Snapshot Isolation & RCSI The lab ends with a final exercise that takes a look at the options available for monitoring the impact of SI/RCSI on your system: with a look at the Transaction Dynamic Management Views (DMV); and the System Monitor counters (Perfmon), that expose important metrics for Database Administrators. Time for Completion: 10 minutes
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 5 of 35
Virtual PC Configuration and Setup
This Virtual PC environment contains five already installed instances of SQL Server running on Windows 2003 Server SP2. The VPC machine name is S Q L H A V P C . The instances, versions and environments are described in the following table: SQL Server Service Name MSSQL$SQLDEV01 MSSQL$SQLDEV02 MSSQL$SQLEXPRESS MSSQL$SQLDEV03 SQL Server Version Edition SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition SQL Server 2005 build 9.00.3054.00 (SP2) Express Edition with Advanced Services SQL Server 2008 build 10.00.1075.23 (November 2007 CTP) Developer Edition
The SQL Express Edition is not the SQL Server 2008 November 2007 CTP version as the upgrade tool in that CTP did not fully support SQL Server Express instances. The accounts and passwords used in the following exercises are shown in the following table: Account Name Administrator SQLService sa Account Password Pass@word1 SQL!Service@05 SQL!Service@05 Account Usage Login account for VPC Startup account for all SQL Server services SQL Server System Administrator
Using Virtual Machines
Using Virtual Machines allows you to isolate and control an environment independently from your host operating system. Your host operating system can run any operating system that supports Virtual PC or Virtual Server. Then, within your Virtual Machine, you can run any “base” operating system desired. In the case of this self-paced lab series, the base operating system is Windows Server 2003 Enterprise Edition with Service Pack 2. A virtual machine has multiple components: a virtual hard drive (*.vhd) and a virtual machine configuration file (*.vmc). With Virtual PC or Virtual Server, you can open the vmc file in order to configure your virtual machine. The initial configuration of this vmc is that it expects 1GB of memory allocated to the virtual machine; this is memory in addition to what your host operating system needs. If you do not have more then 1.5GB of physical memory, reconfigure the virtual machine to use 512MB of memory instead.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 6 of 35 Once setup for your environment, start (or turn on) your virtual machine. Once started, you will see a login dialog. In order to access the login dialog within your virtual machine, there are special keystrokes required. The details for the login are provided in the following table: Tasks Login to your VPC image. Detailed Steps To access the login dialog on the VPC, use Right-Alt + Del and then login in as Administrator with a password of Pass@word1
Within Virtual PC or Virtual Server, most commands focus on using the Right-alt key. The Right-alt is the alt key immediately to the right of the spacebar. Command Description Login Full Screen/Return to Host (in VPC) Keystroke Combination Right-alt + delete Right-alt + enter
Customize the page file
The page file size has been reduced to 0 bytes in order to minimize the cost of expanding the .vhd (when you ran setup to install this VPC image). If you do not have 1.5-2GB of physical memory on your machine, you will need a page file. You have two options: Set the Page File to System Managed Size (recommended) Increase the size of the “custom” configuration
If desired, use the steps below to configure your Page File to a System Managed Size. Tasks Customize your Page File settings. Detailed Steps 1. 2. 3. 4. 5. 6. Start the Control Panel, System applet. From the Windows task bar, select the Start | Control Panel | System menu item. In the System Properties dialog, select the Advanced Tab. In the Performance section, click the Settings button. In the Performance Options dialog, select the Advanced Tab. In the Virtual Memory section, click the Change button. Select the System managed size radio button in the Virtual Memory dialog. This radio button is in the Paging file size for select drive section. NOTE: Drive C: will be selected as it is the only drive available to our VPC. Select Set. Click OK and continue to click OK as you exit the Control Panel, System dialogs.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 7 of 35
The “Isolation” Lab Application
This application is used to simulate concurrent usage of a database table. It is designed to illustrate the principles of row versioning, locking and blocking without requiring the user to follow complex T-SQL scripts. It is hoped that removing the student’s concentration the mechanics of complex, step-by-step execution of T-SQL scripts across three SQL Management Studio windows will allow full focus on understanding the core principles of optimistic locking. The lab exercises will refer to the various areas of the application using the terms below, qualified by “Left”, “Right” or “Locks area” (not visible below):
There are a number of principles that apply to the application: When a transaction is active the relevant status box (left and/or right) will be colored The application can make up two three connections to the database: “Left”, “Right” and “Admin” – the Admin connection is active when changing database options or viewing locks The User Interface simulates two users: “Left” and “Right”, this is done with two connections rather than two background threads, so command timeouts are used to return control to the student instead of allowing lock blocking to “hang” the application.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 8 of 35
For all exercises, the instance used is: Instance = (local)\SQLDEV01
To start the required resources needed for this lab and ensure that your VPC is not running unnecessary instances (and therefore wasting valuable resources), execute the following batch file: Tasks Start only the required instances for this exercise. Detailed Steps 1. Start My Computer From the Windows task bar, select Start | My Computer OR Press WindowsKey+E The WindowsKey does not exist on every keyboard. Often, when it does, it resides between the Ctrl and the Alt keys or sometimes next to a Fn key. The icon on the key is usually the Windows logo .
2. Navigate to the following directory: C:\AlwaysOn Labs\ 3. 4. Double-click StartSQL4Snapshot.cmd. After the command file has run you are ready to proceed to Exercise 1
Tips on how to successfully complete the lab exercises
For best results, read the Tasks column and its associated notes first. Then proceed to follow the detailed steps for step by step instructions on what to verify, review, execute, etc. Please check off steps as you complete them and execute steps in the order in which they are listed. As a best practice, read the entire step, think about what it is meant to accomplish and then execute it. There are numerous steps that you do NOT immediately execute and instead may have you review and/or alter what you might expect your normal behavior to be in order to show you a new feature or two. There are lots of notes along the way, all of which give you insight into special features, cool tips and often best practices. It‟s not a speed contest to finish first. Take it slow, review the steps and you‟ll get a lot more out of your workshop experience. Finally, as the complexity of the exercise increases, the need to follow steps in a certain order also increases. Some exercises may fail if steps are missed and worse yet, other exercises might require you to start over if/when steps are missed. Consider using a pen/pencil to mark off steps as you complete them. Additionally, consider taking a step back from each step to think about what you’re trying to accomplish. This will help to minimize errors and increase learning as you may be able to predict certain steps and/or think of additional things to test and learn!
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 9 of 35
Part I: Pessimistic Locking
Time for Completion: 15 minutes
In this scenario, you will be using an application designed to illustrate SQL Server‟s transaction data concurrency controls as implemented to the ANSI transaction isolation standard. Inside the application two often conflicting database sessions are run against a small table under varying transaction isolation levels. This application takes away the tedium of sequential execution of Transact-SQL commands while exposing the behavior of locks and isolation levels in SQL Server 2008. This first scenario focuses on “pessimistic locking” (where data collisions are assumed and locks taken to prevent conflicting updates) and the four standard isolation levels: 1. 2. 3. 4. Read Uncommitted (also known as “Dirty Read”) Read Committed (the default SQL Server transaction isolation level) Repeatable Read Serializable
These four isolation levels will be familiar to the SQL Server 2000 developers who are used to designing their applications to exploit this locking model, they will be less familiar to Oracle developers who use the “optimistic locking” model where transactions operate against a stable view of the database as of transaction start, and who must code to handle conflicting updates. By showing you how pessimistic database locking works under the covers you will be able to contrast this behavior with the optimistic locking exercises later in the lab. This exercise is also intended to introduce you to the Isolation lab application and to show you how to view the locks taken by transacted database operations, and to capture the SQL requests it makes. Tasks Launching the application: Connecting Left & Right Detailed Steps 1. Start My Computer From the Windows task bar, select Start | My Computer OR Press WindowsKey+E The WindowsKey does not exist on every keyboard. Often, when it does, it resides between the Ctrl and the Alt keys or sometimes next to a Fn key. The icon on the key is usually the Windows logo 1. 2. 3. .
Navigate to C:Always On Labs\Snapshot Isolation Lab and double-click Isolation.exe to activate the test application. In the Connect dialog review the settings, change the server name to (local)\SQLDEV01, and then click Save. In the Isolation application click Connect then select Connect Left, you should see Open in the Status box. Connect to the right side as well. Verify that Open is shown
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 10 of 35 Tasks Detailed Steps in both Status boxes prior to continuing.
You have opened two connections to the SQL Server, next you will use the View Locks option to examine the locks active to each connection In Isolation application click Options, in the menu select Show Locks. This will open the a new pane that displays the active locks in the database (see below) Both connections can be clearly identified under the Connection header. In this case the only locks are database-level schema locks held to reflect their connected status.
Viewing Locks and making an update within a transaction
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 11 of 35 Tasks Detailed Steps
In the left connection using Transaction Isolate Level spinner select Serializable then click Begin – the left Status box should turn blue and display “Begin” – blue indicates an active transaction. In the left connection click refresh to fill the grid with data
Click refresh in the Lock area to refresh the lock grid, you should see a view similar to:
Note the additional rows that now appear in the lock grid, all owned by the Left Connection. Even though it has only read data, the SQL Server lock manager must tag to protect the transaction from other transactions who might update the data, causing the view to change. The locks consist of an Intent-Shared (IS) lock on the table “SnapshotData”, an IS lock on the page, and then Shared (S) range locks to prevent the results of the select changing during the life of the transaction. An Intent-Shared lock means that at a lower level in the lock hierarchy (table to page to row/key), an actual Shared lock will be taken. For more information, see the SQL Server 2008 Books Online topics:
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 12 of 35 Tasks Detailed Steps Database Engine | Development | Querying and Changing Data Accessing and Changing Database Data Locking and Row Versioning Locking in the Database Engine o o o 5. Lock Granularity and Hierarchies Lock Modes Lock Compatibility (Database Engine)
In the left Datagrid Area, under Transaction Isolation Level change the RowData value for RowNumber 1 to 1 from 0. The Icon on the left of the grid will change to pending update, see below: this means that the grid has a
To apply the update either click on a different row or click refresh – this will cause the following statement to be executed by the SQL Server: UPDATE SnapshotData SET RowData = <your change> WHERE RowNumber = <the line in the grid>
In the Lock area click Refresh to see what has changed. You will see that under Mode the object and page level Intent-Shared (IS) locks have changed to Intent Exclusive (IX) – this is because one of the key locks has changed from Shared (S-S) to Exclusive (XX). This is for the row you have just updated – this will block any other transactions
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 13 of 35 Tasks Detailed Steps who try to read or update this row. 1. The left update has now locked the first row of data. Click Options, Show Locks to hide the Lock area. In SQL Server 2008, the application developer now has two choices: - Read the updated row and endure the lock wait (i.e. be “blocked”). This is a good choice if the blocking transaction will soon commit/rollback, as is typical in OLTP systems with small, short transactions. - Perform a “dirty read” by using the Read Uncommitted transaction isolation level or a NOLOCK locking hint on the query. This is a good choice if absolute accuracy isn‟t demanded, for example if data is being aggregated or averaged. However there is a risk that the “dirty” data will never be committed, potentially making any decisions based on the query results suspect. 2. In the right connection use Transaction Isolation Level spinner to select Read Committed and click Begin then click Refresh to populate the grid. In the right connection grid change the value for RowData in RowNumber 1 to 1 from 0, then apply the update by clicking Refresh. The read will be blocked (because the left connection has an Exclusive lock on that key range in the table) and without a timeout it would wait until the left transaction commits. However, the Isolation application has set a three second timeout, so you will see:
“Dirty Read” - View the in-flight “dirty” data using Read Uncommitted
In the dialog click OK. Then click Rollback on the right connection. In the right connection use the Transaction Isolation Level spinner to select Read Uncommited, click Begin then click refresh. This time the read completes and you can see the “dirty” value set but not yet committed by the left connection. Verify that your display matches the screenshot below (the left connection status may say „Refreshed‟ or „Pending‟).
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 14 of 35 Tasks Detailed Steps
In the right connection change RowData value for RowNumber 1 from 1 to 2, apply the change and as before this will cause a time out. This is because even though you we able to “dirty read” the updated data, the SQL Server transaction manager will not let you “dirty update” data – that would lead to data corruption and the inability to rollback to a consistent point on error.
In the dialog click OK then click Rollback in both the left and right connections, this will release all the table, page and row locks. Notes: You could repeat this exercise using Repeatable Read rather than Serializable – you will see different locking behavior, do you understand why? Reviewing the resources listed at the end of this lab will explain the ANSI trsansaction isolation levels and the various degrees of protection they offer against external data changes becoming visible within a scope of a transaction. Notes: The grids still contain a cached copy of the data; however it is no longer actively connected to the database server. If you press the green refresh buttons they will update within the context of an IMPLICIT transaction (defaulting to READ COMMITTED)
“Readers block Writers” - Read the table, blocking updaters with strong read locks
This final task in the exercise illustrates that read transactions can block writers. This is one of the key areas where an optimistic model provides relief – its strength is where there is a high volume of readers and a small number of non-conflicting writers, enabling both communities to get on with their work without interfering with each other. This capability is important because read transactions tend to be longer than update transactions – they perform actions like: counting rows; filtering and sorting; joining and denormalizing data; all taking longer than a quick in and out OLTP update (such as a seat booking on an airplane)
In the left connection use Transaction Isolation Level spinner to select Repeatable Read, click Begin then click refresh to read the data into the grid In the right connection click refresh to refresh the grid, and then change value of RowData for RowNumber 1 to 1 from 0 – when you apply your change, by clicking on another row, you will get another timeout. In the dialog click OK then click Options and select Show Locks verify that your screen is similar to:
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 15 of 35 Tasks Detailed Steps
The left reader has blocked the right writer because it has taken row level Shared (S) locks that prevent other transactions requesting stronger Exclusive (X) locks – this behavior can be especially hurtful to concurrency if long-running reads escalate from row lock to table locks, or even if they start with page level locks as writers can be locked out from the table until the read completes. Later in this lab you will explore Read Committed with Snapshot Isolation – this is a database level change to the default behavior, Read Committed, which is somewhat optimized for concurrent readers/writers as it acquires/releases locks during the transaction ( not just at the end of the transaction). 5. You are now ready to proceed with Part 2. At this time do not close the Isolation application.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 16 of 35
Part 2: Activating Snapshot Isolation & Read Committed with Snapshot Isolation
In this exercise you will explore the rules around activation of Snapshot Isolation and Read Committed with Snapshot Isolation (RCSI). This is an important topic for DBAs to understand as there are different restrictions placed on each activation that may influence the deployment of this technology. “I only do this once, don‟t I?” – this is a good question, and the answer is that it depends. Some systems will just test the new options, find that their impact is acceptable and deploy them into production. Some other systems may find that only one or other of the options is relevant. This is especially true of RCSI, which changes default behavior, and should not be used within a 3rd party application without verifying that the vendor supports its activation, whereas Snapshot Isolation can be enabled and used safely for reporting against a 3 rd party application with changing its default behavior. A third class of systems might want to avoid the overhead of row versioning during peak update periods, it is these applications that will need to toggle the value on or off and who will need to understand how this is done without disrupting service. Both options are constrained by varying degrees of activity from other user sessions in the database, and are exposed to being blocked by long running transactions. This lab explains how these impacts can be mitigated. This exercise focuses on the mechanics of activating each option, the impact of activation is discussed in the final monitoring exercise. These impacts are: Increased I/O to manage row versions Increased tempdb usage Changes to default behavior affecting applications that rely on pessimistic locking to implement ordered queues.
Tasks Start SQL Server Profiler to track the T-SQL executed by the application
Detailed Steps 1. 2. From the Windows taskbar select : Start | All Programs | Microsoft SQL Server 2008 | Performance Tools | SQL Server Profiler In SQL Server Profiler start a default trace: On the Task bar click File and select New Trace. In the Connect to Server dialog ensure the following settings then click Connect: Server type: Database Engine Server name: (local)\SQLDEV01 Authentication: Windows Authentication 3. In the Trace Properties dialog ensure that Standard (default) is selected then click Run.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 17 of 35 4. If Isolation application is still running you should see:
Note the three connections Left, Right (& Admin, only if you are showing lock status) You will use SQL Server Profiler to examine what is being sent to the SQL Server by the Isolation application. 5. Return to Isolation application. For both connections click Rollback. Hide the Lock area by clicking Options, Show Locks. Finally disconnect the right connection by clicking Connect then selecting Connect Right Inside Isolation application click Options and select Refresh Database Status Return to SQL Server Profiler you will see two statement batch starting/completed, containing the following statements, if helpful increase the size of the TextData column. SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'SILabDB' SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = N'SILabDB' The results of these queries are used to update the Options UI. The first returns an integer with four possible values, the second returns a Boolean true/false. You will explore these settings in this exercise. Read Committed with Snapshot Isolation activation rules 1. 2. Return to Isolation application, verify that the left connection is still active then click refresh to refresh the grid. Click Options then click RCSI Enabled. You will see the following timeout.
(This is because the Isolation lab application chooses to set a timeout rather than wait for command completion, without this timeout the request would hang)
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 18 of 35
Enabling RCSI requires that the enabling connection be the only connection in the database, and the ALTER DATABASE (shown below, shown in SQL Server Profiler) will wait for other connections to terminate before it runs.
One option to avoid the timeout is to use the following syntax: ALTER DATABASE SILabDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE This command will immediately terminate existing connections and hence won‟t timeout/get blocked by user connections. The obvious drawback is that all the users‟ connections are terminated! An alternative to frequently toggling this database option and causing terminated connections (e.g. to run periodic reports) is to use a Database Snapshot and run the report against that. A Database Snapshot provides a static pointin-time view of a database – see the Database Snapshots Lab on this DVD for more information. If you run the ALTER DATABASE command the Isolation Lab application will need to be restarted. Snapshot Isolation activation rules 1. In Isolation application, click Options and check status of Snapshot Isolation Enabled, make sure it is active. If it is necessary to activate it, the SQL Server Profiler screenshot below shows the activation command.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 19 of 35
ALTER DATABASE SILabDB SET ALLOW_SNAPSHOT_ISOLATION The above command activates the Snapshot Isolation infrastructure and starts the row versioning process – as data changes the previous version is copied to tempdb, and the old data is made available to any transactions that start before the changes to the row are committed. 2. Return to Isolation application and in the Options menu turn off Snapshot Isolation Enabled – you are going to try to activate Snapshot Isolation while other transactions are active. In the left connection, use Transaction Isolation Level spinner to select Read Committed then click Begin and Refresh. As before change the value of RowData in RowNumber 1 from 0 to 1, then click on another row. Verify that your display matches the screenshot below. You have created a pending update transaction.
Now reactivate Snapshot Isolation, click Options and select Snapshot Isolation Enabled The request will timeout, see below – blocked by the pending update. (Again, the Isolation application sets a short timeout to avoid the UI being blocked by the pending update and the request to enable Snapshot Isolation.)
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 20 of 35
Click OK to continue SQL Server prevents the immediate transition while there are pending updates because until all pending updates commit/rollback the version store in tempdb would be unable to show the previous versions of each updated row. Unlike Read Committed with Snapshot Isolation, which only supports a binary Off/On state, Snapshot Isolation also supports a “Pending Off” and a “Pending On” state, which you will explore below. Enabling Read Committed with Snapshot Isolation 1. In Isolation application, Rollback the left connection and then disconnect by clicking Connect and click Connect Left. Make sure that the Lock area is also closed. At this time you should have no pending transactions or connected sessions to the SILabDB database. Note that SQL Server Profiler can be left active if desired. 2. Use the Options menu to toggle RCSI Enabled on and off. As there are no longer any connections to the database you should not see any timeout errors. By doing this you are simply changing the state between on and off. If you have problems (or see a timeout dialog) make sure that SQL Server Management Studio isn‟t connected to the SILabDB database as any active connections will impact you. SI pending activation /deactivation 1. 2. In Isolation application, use the Options menu to ensure that Snapshot Isolation Enabled is not checked (uncheck it if necessary). In Isolation application, click Connect and select Connect Left. Select Read Committed with the Transaction Isolation Level spinner then click Begin, click Refresh to populate the grid. As before change the RowData value for RowNumber 1 from 0 to 1, then click on a different row, you have now created a pending update transaction. Start SQL Server Management Studio by double-clicking the shortcut on the desktop. In the Connect to Server dialog verify the following settings and click Connect: Server type: Database Engine Server name: (local)\SQLDEV01 Authentication: Windows Authentication 4. In SQL Server Management Studio click File, select Open and click File. In the Open File dialog navigate to C:\AlwaysOn Labs\Snapshot Isolation Lab\Additional Scripts\Enable Snapshot Isolation.sql. In the Connect to Database Engine dialog click Connect You should see a script similar to: --- Script to turn On Snapshot Isolation
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 21 of 35 -ALTER DATABASE SILabDB SET ALLOW_SNAPSHOT_ISOLATION ON 5. Execute the script – it should hang (as depicted below) as it is blocked by the active update transaction in the Isolation application that you initiated in step 2.
The default ALTER DATABASE command waits for all in-flight update transactions to rollback or commit. Add the WITH ROLLBACK IMMEDIATE option to force rollback (remember to consider the impact on long-running update transactions that will be rolled back – in some conditions it could be faster to wait). Event though the ALTER DATABASE command remains executing the SQL Server has switched on the infrastructure for retaining row revisions, this takes immediate effect for all new update transactions, if this didn‟t happen SQL Server would have to fail new update requests until the ALTER DATABASE command completes, which would disrupt normal application operation. The pending status affects requests for new Snapshot Isolation transactions 6. In Isolation application click Options, review the dialog box that is opened and click OK, the dialog box will be discussed later, in the Options menu select Refresh Database Status. SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'SILabDB' SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = N'SILabDB' If the “snaphot_isolation_state” is 0/1 (Off/On) then the isolation application‟s UI is updated; if it is 3/4 (Pending Off/Pending On) then a message will pop up, in this case:
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 22 of 35
Similar behavior is seen when disabling Snapshot Isolation:
Both of these messages were generated by Isolation application, with text taken from SQL Server Books Online. After reviewing the contents of the dialog box click OK prior to continuing. 7. In Isolation application click Connect then Connect Right. On the right connection select Snapshot from the Transaction Isolation Level spinner then click Begin then Refresh, (doing this will attempt to initiate the transfer - all SQL Server transactions only kick into action once data is accessed). Verify the following dialog appears then click OK:
This behavior means that the DBA should plan carefully when enabling/disabling Snapshot Isolation – you might choose to only enable SI for overnight batch, if you cannot force your applications to use reasonable timeouts (or, unlike the Isolation application, not hold open transactions across non-time-limited user input) then use the ROLLBACK IMMEDIATE option to prevent issues when activating the SI infrastructure. 8. In Isolation application click Rollback in both left and right connections to rollback pending transactions. Return to SQL Server Management Studio – the hanging ALTER DATABASE query should complete. In Isolation application ensure that Snapshot Isolation Enabled is highlighted in the Options menu. It may be necessary to click Refresh Database Status to see this.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 23 of 35
Part 3: Using Snapshot Isolation (SI)
You have refreshed your understanding of “pessimistic locking”; you know the dangers of a dirty read; you know how to enable Snapshot Isolation, now you‟ll see how it adds another tool to your database workshop. You will see how it works, where default behavior doesn‟t change and how an update conflict is generated. Snapshot Isolation is used when you require a stable view of a database for the duration of a transaction. Examples might be a suite of financial reports where the numbers on the last report must tally with those on the very first. In SQL Server 2000 you might have implemented these reports against a read-only replica of your system to prevent changes without disrupting production activity, or done these reports overnight, shutting down the online application. However in these times of fast-moving, 24x7, global applications these techniques become harder and harder to implement and retain up to date data for your customers. Enter Snapshot Isolation. Once again the Isolation application is used to simulate two concurrent users in the database, and to examine their impacts on each other. You‟ll see that the lock footprint has completely changed, even though both have a “serialized” view of the database. You‟ll see how an update blocks as usual, with one twist – a conflict is raised when one session updates & commits a change that the other session then also tries to modify.
Tasks Activate Snapshot Isolation
Detailed Steps 1. 2. If open, close SQL Server Management Studio. In Isolation application Rollback both left and right connection to close any in-flight transactions, and then close both the left and right connections in the Connect menu. In the Options menu ensure that Snapshot Isolation Enabled is selected and that RCSI Enabled is deactivated. Connect to both left and right connections. On the left connection select Read Committed using Transaction Isolation Level spinner. Click Begin then click refresh, as before change the RowData value for RowNumber 1 to 1 from 0 then click on another row to apply the edit– there is now an in-flight update on the left, verify that your screen matches the screenshot prior to continuing.
SI is not a default
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 24 of 35 Tasks Detailed Steps
On the right, click Refresh this will attempt reload the grid. The implicit transaction will try and read the table with the default Read Committed isolation level, but the pending update will block it and the command will timeout:
Turning on Snapshot Isolation has not altered the default behavior of the database – this is an important difference between Snapshot Isolation and Read Committed with Snapshot Isolation that you will explore in the next exercise. Using Mixed Isolation Levels 1. On the right connection use Transaction Isolation Level spinner to select Snapshot and click Begin then click Refresh to populate the grid. Turn on the Lock area by clicking Options and selecting Show Locks from the menu. Verify that your screen matches the screenshot below:
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 25 of 35 Tasks Detailed Steps
Note that the right sees the old data (even though it is actively locked by the left connection). Furthermore it has not taken any data locks – this is because it is guaranteed a “serialized” view of the database. SQL Server can supply the appropriate row states from the version store (in tempdb), so there is no need for locks to ensure the same data can be read again. 2. In the right connection change the RowData value for RowNumber 1 from 0 to 1, then click on another row to apply the change, note that the following popup appears:
The Snapshot Isolation level does not allow multiple edits to the same data – in this case it follows the same locking semantics of any other transaction isolation level. 3. Click OK on the popup then Rollback both left and right connections to end both transactions. The next task illustrates what happens when two snapshot transactions compete (“conflict”) for the same data.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 26 of 35 Tasks Snapshot Isolation data update conflicts Detailed Steps 1. In the Isolation application use Transaction Isolation Level spinner to select Snapshot for both the left and right connections, and then click Begin on both connections, click Refresh on both left and right connection as well as the Locks area. Verify that your screen is similar to the screenshot below.
Snapshot Isolation transactions (like all SQL Server transactions) don‟t start until they touch data – if you issued a BEGIN TRANSACTION in your session and then updated & committed multiple changes in another session, before you accessed any data in your session then you would see the changes made by the other session even though your transaction began earlier… 2. In the left connection change the RowData value for RowNumber 1 from 0 to 1. Then click Refresh on both connections. This non-blocking behavior is snapshot isolation in action – on the right you are seeing the value before the change (a Read Uncommitted transaction would have seen the inflight data, all other ANSI levels would have been blocked by the update). 3. In the right connection edit the RowData value for RowNumber 1 so that it matches the value of the left connection. Then click another row. Note that you have been blocked and that the application has timed out. On the left connection, click Commit to commit your change to the database. On the right connection click Refresh note that it has returned the value for RowData back to the value before you manually changed it. Also note that the value is different from the left connection as the committed data (left connection) is newer than the change you made to the right connection. In the right connection attempt to make the same change to RowData value for RowNumber 1 as you attempted before, note that when you click on another row that
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 27 of 35 Tasks Detailed Steps the transaction is aborted and rolled back. The dialog is shown below.
Optimistic locking schemes have to perform conflict detection to protect two “serial” transactions from each other – otherwise the latest update would win, losing the intermediate committed state. This is a common flaw in applications designed to use client cached “batch optimistic” locking that don‟t check that the row hasn‟t changed before writing back. Transaction rollback is an expensive operation, log data might need to be rewound to undo the rest of the changes made before the conflict. This is the primary reason for ensuring that your applications make wise decisions about the transaction isolation strategy they use. If update conflicts are likely then using Snapshot Isolation may be the wrong choice, especially if you have long running update transactions that might risk being rolled back. This is especially important to handle in your application, when the application updates data it should be prepared to fail gracefully and restart the transaction. For this reason Snapshot Isolation is not recommended for transactions that update a lot of data. 7. On the right connection, click Refresh – you should now see the newly committed data.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 28 of 35
Part 4: Using Read Committed with Snapshot Isolation (RCSI)
In the last exercise you explored Snapshot Isolation and saw that it requires changes to existing applications before it can be exploited (the isolation level must be explicitly chosen). In this scenario, you will enable RCSI and note its affects on the default behavior of the database. Unlike Snapshot Isolation which provides a transactionally consistent view of a database as of the start of the transaction, RCSI provides statement level stability. This means it is possible for two invocations of the same select within the same Read Committed transaction to get different results. The upside of this is that row versions do not have to be retained for the life of the transaction, this can have a positive impact on tempdb size and version chain length and hence performance over that seen by a long running Snapshot Isolation transaction in a system with high numbers of updates.
Tasks Activate Read Committed with Snapshot Isolation
Detailed Steps 1. 2. 3. If open, close SQL Server Management Studio. In Isolation application close left and right connections also close the Locks area. In the Options menu ensure that RCSI Enabled is selected. The application issues the following command to the SQL Server: ALTER DATABASE SILabDB SET READ_COMMITTED_SNAPSHOT ON If you see errors from Isolation application when enabling the RSCI infrastructure make sure that you have no unexpected connections in the database.
Default behavior changes
In Isolation application Connect to both left and right sides On the left connection select Read Committed from Transaction Isolation Level spinner then click Begin and refresh. Edit the RowData value for RowNumber 1. Click on another row to apply the edit – there is now an in-flight update on the left.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 29 of 35 Tasks Detailed Steps
On the right connection click Refresh to read data within an implicit read committed transaction note that you are not blocked This new behavior is also known as “statement level snapshot isolation” (because the serialized, stable view of the data only applies to the life of the statement that has read the data – the next statement is not guaranteed the same results)
Open the Locks area, click Option select Show Locks. You‟ll see that the locks taken by the left connection would normally have blocked all but a READ UNCOMMITTED transaction.
Because this behavior “comes for free” (no application changes are required to get nonblocking/blocked reads) it is very tempting just to turn it on, however that could be a mistake: - You need to ensure your tempdb has: - enough space (to support the version store) - enough I/O performance (to support row versioning traffic) - If you are running a third party application you need to ask the vendor if they support
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 30 of 35 Tasks Detailed Steps this mode - Both home-grown and third party applications developed by programmers who fully understand pessimistic locking can have problems in this mode, especially if they rely on being blocked to support queues and ordered data access. 5. To confirm this behavior change with an explicit transaction: In the right connection, using the Transaction Isolation Level spinner select Read Committed then click Begin. Click Refresh note that the data shown is that prior to the change you made in the left connection. Rollback both connections prior to continuing.
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 31 of 35
Part 5: Monitoring Snapshot Isolation & Read Committed with Snapshot Isolation
This is the final exercise in this lab and is designed to help the Operational DBA understand the range of counters and measures available to them to monitor the health of a system with Snapshot Isolation and/or Read Committed with Snapshot Isolation enabled. Enabling row versioning functionality comes at the price of maintaining the version store – this is stored inside tempdb. SQL Server exposes a number of System Monitor counters as well as Dynamic Management Views that you can use to explore and manage the version store, these can be used in union with the standard views and performance counters to track space and performance. You will first examine and run two queries that use Transaction Dynamic Management Views to provide data about long running (and hence potentially resource-draining in terms of the number of row versions that must be kept active) transactions, as well as transactions that are traversing long row version chains to get to the data appropriate for their “version of reality”. Finally the last task focuses on the System Monitor counters available to the Operational DBA. These can easily be consumed by tools like Microsoft Operations Manager to alert on thresholds such as over-large version store sizes, dangerous shortages in tempdb space or over long transactions. The power of SQL Server is in the combination of these metrics with others. Transactions slow? It could be a tempdb bottleneck – SQL Server has the counters and data to enable you to work out what is going on and hopefully fix it before problems are seen by the system‟s users. For all tasks, the instance used is: Primary instance = (local)\SQLDEV01
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 32 of 35
Tasks Using the Transaction Dynamic Management Views
Detailed Steps 1. 2. 3. 4. In Isolation application, if open, close both left and right connections as well as the Locks area. In Options menu verify that Snapshot Isolation Enabled is selected In Isolation application Connect both left and right connections. For both connections select Snapshot from Transaction Isolation Level spinner. For both connections click Begin and then click Refresh. Verify that your screen looks like the screenshot below.
Using the shortcut on the desktop start SQL Server Management Studio in the Connect to Server dialog verify the following settings then click Connect: Server type: Database Engine Server name: (local)\SQLDEV01 Authentication: Windows Authentication
On the menu bar click File select Open and click File in the Open File dialog navigate to C:\AlwaysOn Labs\Snapshot Isolation Lab\Additional Scripts\Monitor Snapshot Isolation.sql and click Open. In the Connect to Database Engine dialog click Connect Set your database to SILabDB and select and Execute the first select statement: -- 1- Top 10 Oldest Snapshot Transactions SELECT TOP 10 atxs.transaction_begin_time, atx.transaction_id, atxs.[name] FROM sys.dm_tran_active_snapshot_database_transactions AS atx INNER JOIN sys.dm_tran_active_transactions as atxs ON atx.transaction_id = atxs.transaction_id WHERE atx.is_snapshot = 1 ORDER BY atxs.transaction_begin_time ASC
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 33 of 35 Tasks Detailed Steps This query can be used to give you a sense of how long transactions are active in your application. The older the transaction the more version store that must be reserved to retain a serialized view of the database – as each row changes its old value must be placed into the version store for other transactions to use. The version store is only cleaned up when there are no longer any active transactions that might need that version of the row. Execute the second select statement: -- 2- Top 10 Most Expensive Snapshot Transactions SELECT TOP 10 atxs.transaction_begin_time, atx.* FROM sys.dm_tran_active_snapshot_database_transactions AS atx INNER JOIN sys.dm_tran_active_transactions as atxs ON atx.transaction_id = atxs.transaction_id WHERE atx.is_snapshot = 1 ORDER BY atx.max_version_chain_traversed DESC This query identifies transactions that are relying on long traversal (both average & maximum) – they are having to work through multiple versions of a row to get to the one that is appropriate for their age. There is a CPU and I/O implication of long chains that should make this a red flag if average chains are more than a couple of versions deep (and the transaction is accessing a large amount of data) All of the above Dynamic Management Views are documented in SQL Server 2008 Books Online, and provide detailed information that enable you to monitor the impact of SI/RCSI on your transactions. Using System Monitor (PerfMon) 1. The previous task focused on the Dynamic Management Views (DMVs), another option for monitoring system state is the System Monitor (aka PerfMon) , this is a valuable tool if you want to integrate SI/RCSI metrics into a tool like Microsoft Operations Manager. Double-click Windows Explorer shortcut on the desktop and navigate to C:\AlwaysOn Labs\Snapshot Isolation Lab\Performance directory then double-click Snapshot Isolation Counters.msc file to activate System Monitor with some preselected counts
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 34 of 35 Tasks Detailed Steps
These (database transaction) counters enable the monitoring of the size of the version store and of free space in tempdb – both counters are very important to avoid infrastructure issues caused by activating the SI or RCSI new options. For an explanation of these counters, see the SQL Server 2008 Books Online section: Database Engine | Operations | Monitoring Monitoring Resource Usage (System Monitor) Using SQL Server Objects SQL Server, Transaction Object
SQL Server 2008: Snapshot Isolation Microsoft Hands-on Labs Page 35 of 35
Additional Snapshot Isolation Resources
MSDN Whitepaper: SQL Server 2005 Beta 2 Snapshot Isolation by Kimberly L. Tripp, President and Founder, SQLskills.com http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp MSDN Technical Note: Using Snapshot Isolation by MSDN Team (Rated 8.75/9) http://msdn2.microsoft.com/en-us/library/tcbchxcb.aspx MSDN Webcast: A Primer to Proper SQL Server Development (Part 6 of 10): Mixed Workloads, Secondary Databases Wait States Locking and Isolation (Level 200) by Kimberly L. Tripp, President and Founder, SQLskills.com http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032282207&EventCategory =5&culture=en-US&CountryCode=US TechNet Webcast: Managing Concurrency in SQL Server 2005 (Part 1 of 2): Minimize Blocking with New Snapshot Based Isolation Levels (Level 200) presented by Sunil Agarwal, Program Manager, SQL Server, Microsoft Corporation, October 2005 http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032282056&EventCategory =4&culture=en-US&CountryCode=US TechNet Webcast: Managing Concurrency in SQL Server 2005 (Part 2 of 2): Planning for and Troubleshooting Snapshot Isolation and Row Level Versioning (Level 200) presented by Kalen Delaney, CFO and Principal Mentor, Solid Quality Learning, October 2005 http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032282337&EventCategory =5&culture=en-US&CountryCode=US Microsoft Research Paper: A Critique of ANSI SQL Isolation Levels by Hal Berenson; Phil
Bernstein; Jim Gray; Jim Melton; Elizabeth O'Neil; Patrick O'Neil
http://research.microsoft.com/research/pubs/view.aspx?tr_id=5 Research Paper: A Read-Only Transaction Anomaly Under Snapshot Isolation by Alan Fekete, Elizabeth O'Neil, and Patrick O'Neil http://www.cs.umb.edu/~poneil/ROAnom.pdf