SSIS Error – “To run a SSIS package outside of SQL Server Data Tools you must install…”

25 Jul cute-but-not-helpful

Background

On a Dev server I have used Visual Studio 2010 to build and test an SSIS package. Having completed all of my testing I have now created an Agent task with the SQL Server instance on the same box, that should execute the dtsx package.

However, when it attempts to execute it fails and I see the error message:

“To run a SSIS package outside of SQL Server Data Tools you must install Move file to Archive of Integration Services or higher.”

In this case ‘Move file to Archive’ is the name of a task within the SSIS package, which is of course, moving a file to an archive folder.

Not the most helpful error message.

Investigations

Running the installation of SQL server (or executing a Service Pack installation), up to the point where it shows what is already installed shows that the Integrated Services shared components are not installed:

SSIS_01

On this machine, although SQL server is installed, the shared components required for Integration Services are not. In this situation it is still possible to run very simple SSIS packages, because ‘dtsexec.exe’ is installed. This explains why I can see an Agent job that runs a package for running backups (created by the Maintenance Plan Wizard) but cannot execute my package.

Solution

Rerunning the installation confirms the absence of Integration Services components:

SSIS_02

I installed the required components and then confirmed their presence afterwards:

SSIS_03

Now the SSIS package executes successfully.

 

LDAP – Using SQL to get Active Directory details – Basic Examples

14 Jul

Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like to think it wasn’t but, that is how it looks. Continue reading

Partitioned Views

17 Dec PayCheck - from www.moviescreenshots.blogspot.com

Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.

What is a Partitioned View?

Where data has been partitioned horizontally (across several tables), a partitioned view can show a unified view of these tables and can also make any data selections only from the required table, without accessing all tables that the view comprises of. The tables can be in one database, several databases on the same server or on databases on linked servers.

Creating the Test Environment

For this, I have three databases:
TestDB and SecondTestDB, which are on the ‘main’ server.
ThirdTestdb which is on a linked server, named ‘ARCHIVEWAREHOUSE’.

Listing 1: Tables for ‘main’ server

USE TestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

USE SecondTestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

Listing 2: Tables for the linked server

USE ThirdTestDB;
GO

CREATE TABLE dbo.Warehouse1(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse1 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO


CREATE TABLE dbo.Warehouse2(
	WarehouseID			TINYINT NOT NULL,
	PartCode			VARCHAR(20) NOT NULL,
	Quantity			INT,
CONSTRAINT PK_Warehouse2 PRIMARY KEY CLUSTERED
(
	WarehouseID ASC,
	PartCode ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [Primary]  
) ON [Primary];

GO

Listing 3: Create test data on ‘main’ server

USE TestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 1, 'WIDG-001', 10),
		( 1, 'WIDG-005', 21),
		( 1, 'WIDG-007', 0),
		( 1, 'WIDG-008', 6),
		( 1, 'WIDG-009', 9);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 2, 'WIDG-001', 104),
		( 2, 'WIDG-015', 211),
		( 2, 'WIDG-017', 90),
		( 2, 'WIDG-048', 78),
		( 2, 'WIDG-079', 7);

GO

USE SecondTestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 3, 'WIDG-001', 5),
		( 3, 'WIDG-006', 77),
		( 3, 'WIDG-007', 67),
		( 3, 'WIDG-008', 9),
		( 3, 'WIDG-009', 24);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 4, 'WIDG-003', 22),
		( 4, 'WIDG-014', 85),
		( 4, 'WIDG-017', 45),
		( 4, 'WIDG-048', 6),
		( 4, 'WIDG-079', 89);

GO

Listing 4: Create Test Data on Linked Server

USE ThirdTestDB;
GO

INSERT INTO dbo.Warehouse1
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 5, 'WIDG-122', 10),
		( 5, 'WIDG-135', 21),
		( 5, 'WIDG-155', 0),
		( 5, 'WIDG-157', 6),
		( 5, 'WIDG-177', 9);

INSERT INTO dbo.Warehouse2
        ( WarehouseID, 
		  PartCode, 
		  Quantity )
VALUES  ( 6, 'WIDG-255', 104),
		( 6, 'WIDG-257', 211),
		( 6, 'WIDG-266', 90),
		( 6, 'WIDG-298', 78),
		( 6, 'WIDG-299', 7);

GO

Six tables spread across three databases, all with identical structure. The data is spread across these tables, with the value of ‘WarehouseID’ controlling which table the row is stored within. At this point there is no control to prevent a row being inserted into the ‘wrong’ table, but that will be developed as the example continues.

Listing 5: Create View on ‘main’ server

USE TestDB;
GO

CREATE VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
);
GO

Using the view

Setting STATISTICS IO ON and showing the actual execution plan, shows that in its current structure the view will access all tables, regardless of whether or not a filter is applied.

Figure 1: SELECT results without a filter

Partititioned_Views_02

Figure 2: Statistics and query plan without a filter

Partititioned_Views_01

Figure 3: SELECT results with a filter

Partititioned_Views_03

Figure 4: Statistics and query plan with a filter

Partititioned_Views_04

With this configuration, this is just a normal view and makes no distinction between the various tables it is accessing. Figure 2 and Figure 4 show no difference in the tables accessed, with or without the filter being applied. At this stage this is not a partitioned view.
To change this to a partitioned view actually requires changes to the source tables.

Creating the partitioned view

With the data structure used here, the column ‘WarehouseID’ is being used to signify which table the various rows should be in. To ensure that this actually works a constraint needs to be applied to the column. Once the constraint is used, if the view is filtered using the column that has the constraint then it will only use the appropriate table, based upon the filter value.

Listing 6: Create the constraint on the ‘main’ server

USE TestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 1);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 2);
GO

USE SecondTestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 3);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 4);
GO

Listing 7: Create the constraint on the linked server

USE ThirdTestDB;
GO

ALTER TABLE dbo.Warehouse1
ADD CONSTRAINT C_Warehouse1 CHECK (WarehouseID = 5);
GO

ALTER TABLE dbo.Warehouse2
ADD CONSTRAINT C_Warehouse2 CHECK (WarehouseID = 6);
GO

Repeating the queries used earlier shows that the view focuses resources on the required table only.

Figure 5: Statistics and query plan with a filter and constraints created

Partititioned_Views_06

The work required by the query has now been reduced substantially.

Of course, to some degree it must still be accessing the other table – to check the metadata if nothing else. This can easily be proved by taking ‘SecondTestDB’ offline and querying the view again.

Figure 6: Querying the view with a database offline

Partititioned_Views_05

Even though no rows are required from the table in the database ‘SecondTestDB’ an error has been raised from the view, showing that tables it does not need to select from still need to be available.

Adding a table with a different structure

Because this is a view, it is possible to add a table to this that does not have an identical structure. However, it must have the same column for the constraint, otherwise the view will access all of the tables that it has been constructed from, ignoring the constraint control created by Listings 6 and 7.

Listing 7: Adding a table with a different format to TestDB

USE [TestDB]
GO

CREATE TABLE [dbo].[Warehouse3](
	[WarehouseName] VARCHAR(10) NOT NULL,
	[WarehouseID] [tinyint] NOT NULL,
	[ProductCode_1] [varchar](20) NOT NULL,
	[ProductCode_2] [varchar](20) NOT NULL,
	[Quantity_In] [int] NOT NULL,
	[Quantity_Out] INT NOT NULL,
 CONSTRAINT [PK_Warehouse3] PRIMARY KEY CLUSTERED 
(
	[WarehouseName] ASC,
	[ProductCode_1] ASC,
	[ProductCode_2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[Warehouse3]  WITH CHECK ADD  CONSTRAINT [C_Warehouse3] CHECK  (([WarehouseID]=(7))) ---WarehouseID has to be present for the partitiion functionality
GO

Listing 8: Alter View

USE TestDB;
GO

ALTER VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM ARCHIVEWAREHOUSE.ThirdTestDB.dbo.Warehouse2
UNION ALL
SELECT [WarehouseID],
	   ltrim(RTRIM(ProductCode_1)) + ltrim(RTRIM(ProductCode_2)) AS [PartCode],
	   [Quantity_In] - [Quantity_Out] AS [Quantity]
FROM dbo.Warehouse3
);
GO

Listing 9: Add test data to ‘Warehouse3’ table

USE [TestDB]
GO

INSERT INTO [dbo].[Warehouse3]
           ([WarehouseName],[WarehouseID]
           ,[ProductCode_1]
           ,[ProductCode_2]
           ,[Quantity_In]
           ,[Quantity_Out])
     VALUES
           ('Seven'
		   ,7
           ,'ABCD'
           ,'EFGH'
           ,10
           ,3)
GO

With the addition of the ‘Warehouse3’ table, the partitioning behaviour still works, because the column ‘WarehouseID’ and the associated constraint are in the new table. The SELECT within the view has transformed the appearance of the data from that table to match the other tables.

Figure 7: View results with different structured source table

Partititioned_Views_07

So, the tables don’t have to be identical, when selecting data – as long as the tables have the same constraint and can be structured within the view to match the other tables (because of the ‘UNION ALL’).

Updating data via the view

To simplify this exercise, the remote server will be removed from the view, as well as the table that has a different structure (as detailed in Listing 7).

Listing 10: Alter view to one server with identical table structures

USE TestDB;
GO

ALTER VIEW StockLevels AS (
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse1
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM dbo.Warehouse2
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse1 
UNION ALL
SELECT WarehouseID, 
	   PartCode, 
	   Quantity
FROM SecondTestDB.dbo.Warehouse2 
);
GO

This view now accesses the ‘Warehouse1’ and ‘Warehouse2’ tables from ‘TestDB’ and ‘SecondTestDB’. Twenty rows of data across four tables.

Figure 8: Altered view results

Partititioned_Views_08

Inserting a row of data via the view means that we aren’t concerned with selecting the ‘correct’ table (based upon the WarehouseID constraint). The view will insert into the correct table, based upon the value assigned to the column with the constraint.

Listing 11: Insert via the view

USE [TestDB]
GO

INSERT INTO [dbo].[StockLevels]
           ([WarehouseID]
           ,[PartCode]
           ,[Quantity])
     VALUES
           (2
           ,'WIDG_1234'
           ,33)
GO

Figure 9: SELECT to show the inserted row

Partititioned_Views_09

As shown in Figure 9, the row of data has been inserted into the table ‘Warehouse2’ of ‘TestDB’, which is the table with the constraint that checks for ‘WarehouseID’ having a value of 2.

Listing 12: UPDATE via the view

UPDATE [dbo].[StockLevels]
SET Quantity = 30
WHERE WarehouseID = 2
  AND Partcode = 'WIDG-079';

Figure 10: SELECT to show the updated view

Partititioned_Views_10

UPDATE and DELETE work in the same way.

Behind the scenes, one large difference between using SELECT on a partitioned view and INSERT/UPDATE/DELETE, is that the query accesses all of the tables – as shown in the query plan.

Figure 11: Query plan for a DELETE from a partitioned view

Partititioned_Views_11

The savings in processing when using SELECT are not evident when using INSERT/UPDATE/DELETE, although it removes the decision-making about which source table requires the action.

Forwarding Pointers and Fragmentation on a Heap Table

3 Dec heap

A heap is a table that has no clustered index – it may have non-clustered indexes but without a clustered index it is a heap.
When a row is updated within the heap, if the new data size means a row cannot be stored in the original location, SQL Server moves the row to another location and leaves a pointer (called a forwarding record) to show where it has been moved to.

Continue reading

Replication Logging

20 Nov Monitor

At SQLPASS 2015 I attended a presentation by Kendal Van Dyke, in which he told us that it was possible to log the actions of Replication to an output file.

This was news to me, so I just had to have a closer look.

The Setup

This will use the same Publication that I created in my earlier articles (Subscription and Distributor), which has been running on my test instances ever since.

The Commands

Replication is controlled via Agent Jobs and this is where the output commands are used. Every job involved in Replication (Log Reader, Queue Reader and Distributor) can make use of them.

-Output [Filename] is the name of the text file that the output will be written to. If the file does not exist then it will be created, otherwise it will be appended to.

-OutputVerboseLevel [0|1|2] is the level of output required, with 0 being the lowest and 2 the highest.

Implementation

Implementing this is quite easy – located the Agent jobs for the Publication of interest and add the commands to end of the job’s command line.

The appropriate Log Reader Agent and Queue Reader Agent can be found via the Replication Monitor. Open Replication Monitor, from any of the Publisher, Distributor or Subscriber servers and locate the ‘Agents’ tab from the appropriate Publication. From the ‘Agent types’ drop-down select either ‘Log Reader Agent’ or ‘Queue Reader Agent’, then right-click on the entry and select ‘Properties’.

replication_output_01

This will show the same control that selecting the job under SSMS ‘SQL Server Agent/Jobs’ and ‘Properties’ of the appropriate Job would have shown, but removes any doubt you have located the correct Log Reader Agent.

Of course, if you know the job involved then you can just locate each from the SSMS ‘SQL Server Agent/Jobs’ list on the appropriate servers and update them directly. In my Pull Subscription I have to locate the Distributor Job myself within SSMS – it doesn’t appear as an option via Replication Monitor.

Once the parameters have been added the jobs need to be stopped and started again, so the new command-line can be read.

The Output

With OutputVerboseLevel set to 0 only basic details are written to the text files – various settings of the publication, server names, job names and suchlike. The Distributor produces these details every time it runs, so even this can mean quite a large document given enough time. This is useful for getting error messages with basic history details.

OutputVerboseLevel of 1 also shows statistics, in addition to connection details and other information. This of course increase the output from any job using this setting.

OutputVerboseLevel of 2 is the most detailed level of output. This shows the stored procedure calls, batch details, connections, disconnections and so on. It is very useful for a short period of time but quickly fills drive space. Use this when you need a great deal of detail for a short period of time.

Monitoring Versioning

11 Sep

Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB.
The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced.

The downside is that the resources required for data modifications are increased, because the data requires a version creating before modification and pointers need to be generated to ensure the correct versions are in use. Retrieving the correct version also incurs an additional overhead, because the version store is a linked list and therefore a number of rows may need to be traversed before finding the data required.

Because TempDB contains the row versions for all databases on an instance it needs to have enough space to accommodate these. Should TempDB run out of space then an attempt will be made to shrink the version store. This can result in error 3967, where a transaction that has yet to generate a version has been rolled back, or error 3966 where a transaction cannot read the row version. Write operations will continue, but error 3959 will be written to the error log because the write could not generate a row version.

So it really pays to ensure there is enough space for TempDB.

There are several ways to see the impact of using a version store – Performance Monitor and DMVs.

Basic Example
In this example I’m using three perfmon counters, all located under the instance of the appropriate SQL server –
Transactions\Version Generation rate (KB/s),
Transactions\Version cleanup rate (KB/s) and
Transactions\Version store size (KB).

On the instance used in this example the MSDB database has row-versioning enabled because it has snapshot isolation set. Therefore there is always some background activity in the version store.

VersionStore_02

Approximately every minute an automatic job executes which cleans out entries from the version store that are no longer required and is monitored under ‘Version cleanup rate (KB/s)’.
Similarly, the two DMVs ‘sys.dm_tran_top_version_generators’ and ‘sys.dm_tran_version_store’ show a small amount of activity within the version store.

VersionStore_02

Because select queries don’t generate row versions, executing a simple update against a test table is all that is required to demonstrate the potential impact upon the TempDB.

USE IsolationLevels;

SET ROWCOUNT 100000;

UPDATE dbo.test4
SET Test_Value = 1;

A spike can be seen in the version generation and an increase in the size of the version store.

VersionStore_03

In addition, there are a number of entries reported by the DMVs, filtered on the database in question.

VersionStore_04

The cleanup process (the green line in the following screenshot) then removes any entries from the version store that are too old to be of use.

VersionStore_05

And in this example, the DMVs return no results after the cleanup.

VersionStore_06

Now I’m going to loop the update and run a select query, which also loops. This will increase the number of versions created and acquired, showing the impact via Perfom and the DMVs.

The update

WHILE 1 =1 
BEGIN
	UPDATE dbo.test4
	SET Test_Value = Test_Value + 1
		WHERE Index_Column IN ('A', 'B')
		  AND ID < 100000;

	WAITFOR DELAY '00:00:01'
END

Will run until terminated, generating a large number of versions.

The select will run constantly too, showing the data it is reading.

WHILE 1 = 1

BEGIN

	SELECT *
	FROM dbo.Test4
	WHERE Index_Column IN ('A', 'B')
	  AND ID < 100000
	ORDER BY id ASC;


END

Perfmon shows a gradual increase in the version store size, although the rate of creation is low (due to the WAITFOR DELAY).
When the cleanup executes it does not remove all of the versions, because the SELECT statement is using some of those versions.

VersionStore_07

In addition, the DMVs return a larger number of rows and takes longer to do so. This extra time can be excessive where the version store has become large.

VersionStore_08

Conclusion

Optimistic locking can reduce the overhead of locking and blocking, but it incurs its own overheads which need to be considered.

Further Reading

Understanding Row Versioning-Based Isolation Levels
https://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

Row Versioning Resource Usage
https://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions)
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/01/managing-tempdb-in-sql-server-tempdb-basics-version-store-growth-and-removing-stale-row-versions.aspx

Transaction Related Dynamic Management Views and Functions
https://msdn.microsoft.com/en-us/library/ms178621.aspx

Read Committed Snapshot – Another Optimistic Flavour

26 Aug http://www.lovethispic.com/image/13760/rainbow-ice-cream

In a previous article (here) I described how the isolation level read committed works, with some examples. There is also an optimistic version of this – read committed snapshot, which uses row versions.
The behaviour of read committed and read committed snapshot is similar and repeating the examples used previously will show the similarities and the differences.

First of all, the database needs to be configured for snapshot isolation.

ALTER DATABASE IsolationLevels
SET READ_COMMITTED_SNAPSHOT ON;

As with the article on read committed, there are 5,000,000 rows with the ‘ID’ column starting from 10 and a value of 1 in ‘Test_Value’ for each row.

Read_Committed_snapshot_01

This code is executed again and as soon as it starts the row with ‘ID’ of 10 is moved to the end of the index, in another tab.

UPDATE dbo.Test2
SET id = id + 6000000
WHERE id = 10;

This time the result is slightly different. The result shows that move of the row with ‘ID’ 10 to 6000010 has not affected the first SELECT. This is because it used a snapshot of the data, taken when the statement started (not when the transaction started), so the move had no impact upon this version of the data.

Read_Committed_snapshot_02

Similarly, moving the last row in the index to the start of the index does not have the impact it did with read committed isolation, for the same reason. Having reset the data and executed the appropriate UPDATE does not have the same affect, because it processed a snapshot of the data.

UPDATE dbo.Test2
SET id = 9
WHERE id = 5000009;

Read_Committed_snapshot_03

For the same reason, dirty reads cannot happen with read committed snapshot, because it is using as snapshot of the data taken when the statement started.

A smaller demonstration
In the read committed isolation level article, an UPDATE was executed to cause a lock, thereby blocking a subsequent SELECT.
Trying the same demonstration here results in different behaviour.

1000 rows, with an ‘ID’ column and ‘Test_Value’ column matching in values.

Read_Committed_snapshot_07

Executing an UPDATE in a separate window without a COMMIT/ROLLBACK will lock the selected row.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET Test_Value = 1000
WHERE id = 50;

And rerunning the query with the two SELECT statements would wait for the release of the lock from the UPDATE, if this were read committed isolation level.

Read_Committed_snapshot_06

However, with read committed snapshot the row with the ‘ID’ of 50 is unchanged, and the SELECT was not blocked, because a snapshot was used by the transaction.

A different demonstration, to show that each SELECT within a transaction uses a different snapshot.
Two identical SELECT queries, separated by a 10 second delay.

Read_Committed_snapshot_04

Before the WAITFOR command completes an UPDATE is executed that will move a row from the start of the index to the end.

BEGIN TRANSACTION;

UPDATE dbo.Test2
SET ID = 1001
WHERE ID = 10;

COMMIT TRANSACTION

Looking at completed initial transaction, the moved row is shown in the second SELECT but not in the first, so the second SELECT read a new snapshot, showing the moved data.

Read_Committed_snapshot_05

So, as with read committed isolation, multiple queries within the same transaction may return different results, even when the criteria are identical.

Follow

Get every new post delivered to your Inbox.