This blog will no longer be updated.

New content is available on my new blog

Piotr Rodak

Good practices - database programming, unit testing

Jason Brimhall wrote today on his blog that new book, Defensive Database Programming, written by Alex Kuznetsov (blog) is coming to bookstores. Alex writes about various techniques that make your code safer to run. SQL injection is not the only one vulnerability the code may be exposed to. Some other include inconsistent search patterns, unsupported character sets, locale settings, issues that may occur during high concurrency conditions, logic that breaks when certain conditions are not met. The book covers these issues and provides examples how to write code to diminish chances of failure. Another good thing is that it is free, and you can download it from RedGate’s page.

Database code is often written ad hoc, by programmers who do not necessarily have in-depth knowledge of the database engine. There is poor error handling, careless usage of data types, resulting in implicit conversions at run time, cursors and loops and many other flavours. Apart from this, the code is very often tested only in a very rudimentary way, for a few (at most) parameter combinations and developers rarely take edge conditions into account.

It is quite easy to improve stored procedure coding standards by following just a few simple ideas. I find these most important:

  • Parameter validation – ensure that your procedure or batch operates on parameters within expected ranges. If certain value must not be null or empty string, check for it before you apply it into a query, you’ll save some resources. Of course this doesn’t mean that you shouldn’t have proper check constraints on your columns.
  • Error handling – along with the parameter validation it is a good practice to throw an error if some validation fails. If you write a procedure that adds new contact to database, it doesn’t make sense if its last name is empty. If you throw an error saying ‘Contact’s name must not be empty’, it will be much easier to debug and fix the problem if it occurs.
  • Testing – write unit tests that will ensure that your code works. Since there is no reasonable and easy way to write tests in T-SQL, you can use higher level framework, like NUnit for .NET. I find it sufficient in many cases to write a few tests straight in the T-SQL, wrapped in begin tran .. rollback blocks. These tests verify that the procedure works in expected way for parameters. These tests do not mimic exactly unit testing frameworks and do not have the Assert functionality. They let you though see at first glance if your code works for given set of parameters or not.
  • Proper naming of constraints and indexes – it helps a lot if you can associate name of the constraint with particular table without having to shuffle through sys.objects view.

Let’s have a look at little example.

Let’s assume that we have table that defines some feeds we handle in the system. Feeds can have lots of properties, but for our example I will use only FeedName, FeedCategory which describes category of feed and FeedType, which defines how the feed is processed.

First approach to the code as follows:

   1: create table FeedCategory
   2: (
   3:     CategoryId int identity(1, 1) not null primary key,
   4:     CategoryName varchar(128)
   5: )
   6: go
   7: create table Feed
   8: (
   9:     FeedId int identity(1, 1) not null primary key,
  10:     FeedName varchar(128) not null check (len(FeedName) > 0),
  11:     FeedCategory int not null references FeedCategory(CategoryId),
  12:     FeedType char(2)
  13: )
  14: go
  15: create procedure AddNewFeed
  16: (
  17:     @FeedName varchar(128),
  18:     @FeedCategoryName varchar(128),
  19:     @FeedType char(2)
  20: )
  21: as
  22: begin
  23:     declare @CategoryId int
  24:     select @CategoryId = CategoryId from FeedCategory where CategoryName = @FeedCategoryName
  25:  
  26:     insert Feed(FeedName, FeedCategory, FeedType)
  27:     values (@FeedName, @CategoryId, @FeedType)
  28: end
  29:  
  30: go

Now it' looks kind of OK, let’s write a test.

First test that I will write is to check what happens if the FeedName is invalid:

   1: begin tran
   2:     raiserror('Add new feed, empty feed name', 10, 1) with nowait;
   3:     insert FeedCategory (CategoryName) values('Maintenance feeds')
   4:     exec AddNewFeed @FeedName = '', @FeedCategoryName = 'Maintenance feeds', @FeedType='FS'
   5: rollback
   6: go

As you see, tests are built in simple way. All test code is wrapped in begin tran – rollback block. First line after the beginning of transaction outputs test description, so you can see which test fails or succeeds. If you run the above test, an error message is returned:

 

   1: Add new feed, empty feed name
   2:  
   3: (1 row(s) affected)
   4: Msg 547, Level 16, State 0, Procedure AddNewFeed, Line 12
   5: The INSERT statement conflicted with the CHECK constraint "CK__Feed__FeedName__2F650636". The conflict occurred in database "testdb", table "dbo.Feed", column 'FeedName'.
   6: The statement has been terminated.

I don’t like this error message. Look how ugly the name of the CHECK constraint is. Let’s have a look what can be done about this.

If you change the name of the constraint to something more meaningful, like in the following code,

   1: create table Feed
   2: (
   3:     FeedId int identity(1, 1) not null primary key,
   4:     FeedName varchar(128) not null,
   5:     FeedCategory int not null references FeedCategory(CategoryId),
   6:     FeedType char(2),
   7:     constraint CHK_Feed_FeedNameNotEmpty check (len(FeedName) > 0)
   8: )

you will get better error message:

   1: Add new feed, empty feed name
   2:  
   3: (1 row(s) affected)
   4: Msg 547, Level 16, State 0, Procedure AddNewFeed, Line 12
   5: The INSERT statement conflicted with the CHECK constraint "CHK_Feed_FeedNameNotEmpty". The conflict occurred in database "testdb", table "dbo.Feed", column 'FeedName'.
   6: The statement has been terminated.

Well, this looks better, but in my opinion, it is still lacking elegance.

To fix this, you can modify the procedure:

   1: create procedure AddNewFeed
   2: (
   3:     @FeedName varchar(128),
   4:     @FeedCategoryName varchar(128),
   5:     @FeedType char(2)
   6: )
   7: as
   8: begin
   9:     begin try
  10:         if isnull(@FeedName, '') = ''
  11:             raiserror('Feed name must not be empty', 16, 1)
  12:  
  13:         declare @CategoryId int
  14:         select @CategoryId = CategoryId from FeedCategory where CategoryName = @FeedCategoryName
  15:  
  16:         insert Feed(FeedName, FeedCategory, FeedType)
  17:         values (@FeedName, @CategoryId, @FeedType)
  18:     end try
  19:     begin catch
  20:         declare @err varchar(2000)
  21:         set @err = 'Exception in '  + error_procedure() + ', error line '+convert(varchar, error_line())+': ' + error_message()
  22:         raiserror(@err, 16, 1)
  23:     end catch
  24: end
 

Now the error message looks like this:

   1: Add new feed, empty feed name
   2:  
   3: (1 row(s) affected)
   4: Msg 50000, Level 16, State 1, Procedure AddNewFeed, Line 23
   5: Exception in AddNewFeed, error line 12: Feed name must not be empty
Ok, this looks much better in my opinion. We can say that test 1 has passed by properly failing the transaction and returning user friendly message. If you want to mimic Assert functionality of NUnit you will have to slightly modify the test code:
 
   1: --assert like test
   2: begin tran
   3:     begin try
   4:         raiserror('-- ================= --', 10, 1) with nowait;
   5:         raiserror('Test 1: Add new feed, empty feed name', 10, 1) with nowait;
   6:         insert FeedCategory (CategoryName) values('Maintenance feeds')
   7:         exec AddNewFeed @FeedName = '', @FeedCategoryName = 'Maintenance feeds', @FeedType='FS'
   8:         raiserror('FAIL', 16, 1) --Assert.Fail. We shouldn't be here.
   9:     end try
  10:     begin catch
  11:         declare @err varchar(2000)
  12:         set @err = error_message()
  13:         if @err like '%Feed name must not be empty%' --is it expected exception?
  14:             raiserror('Test succeeded.', 10, 1) with nowait;
  15:         else
  16:             raiserror('Test failed: %s', 16, 1, @err);
  17:     end catch
  18: rollback
 
OK. So far, we have one test covered, that checks if the feed name is correct. As you noticed, the AddNewFeed procedure accepts also category name and performs lookup in the FeedCategory to retrieve the CategoryId. It is sometimes better to perform lookups, especially if the procedure is going to be called by support directly from management studio. This procedure is meant to be part of API to manage feeds, so it has to be user friendly to some extent. Fiddling with identifiers is not very convenient and error prone.
I will skip the discussion about naming of the foreign key constraint here, but let’s have a look at the code that handles failed lookups. If you don’t have these lines in the procedure, insert will fail because of foreign key constraint, but as before, more friendly message is going to be appreciated by your users.
   1: create procedure AddNewFeed
   2: (
   3:     @FeedName varchar(128),
   4:     @FeedCategoryName varchar(128),
   5:     @FeedType char(2)
   6: )
   7: as
   8: begin
   9:     begin try
  10:         if isnull(@FeedName, '') = ''
  11:             raiserror('Feed name must not be empty', 16, 1)
  12:  
  13:         declare @CategoryId int
  14:         select @CategoryId = CategoryId from FeedCategory where CategoryName = @FeedCategoryName
  15:  
  16:         if @CategoryId is null
  17:             raiserror('Invalid feed category name', 16, 1)
  18:         
  19:         insert Feed(FeedName, FeedCategory, FeedType)
  20:         values (@FeedName, @CategoryId, @FeedType)
  21:     end try
  22:     begin catch
  23:         declare @err varchar(2000)
  24:         set @err = 'Exception in '  + error_procedure() + ', error line '+convert(varchar, error_line())+': ' + error_message()
  25:         raiserror(@err, 16, 1)
  13:         if @err like '%Invalid feed category name%' --is it expected exception?
  26:     end catch
  27: end

Please notice lines 16 and 17 in this snippet. I detect the fact that particular category name could not be found in FeedCategory table and throw appropriate error.

One of aspects of unit testing is that each unit test should test only one condition. We could modify Test 1 to cover invalid category name situation, but we will write new test, so both cases are logically separated.

   1:  
   2: begin tran
   3:     begin try
   4:         raiserror('-- ================= --', 10, 1) with nowait;
   5:         raiserror('Test 2: Add new feed, invalid category name', 10, 1) with nowait;
   6:         insert FeedCategory (CategoryName) values('Maintenance feeds') --insert category
   7:         exec AddNewFeed @FeedName = 'Feed 1', @FeedCategoryName = 'No such category', @FeedType='FS'
   8:         raiserror('FAIL', 16, 1) --Assert.Fail. We shouldn't be here.
   9:     end try
  10:     begin catch
  11:         declare @err varchar(2000)
  12:         set @err = error_message()
  13:         if @err like '%Invalid feed category name%' --is it expected exception?
  14:             raiserror('Test succeeded.', 10, 1) with nowait;
  15:         else
  16:             raiserror('Test failed: %s', 16, 1, @err);
  17:     end catch
  18: rollback

As you see in the line 13, I changed the expected error message to test invalid category name. If this exception is thrown, test succeeds.

   1: -- ================= --
   2: Test 2: Add new feed, invalid category name
   3:  
   4: (1 row(s) affected)
   5: Test succeeded.
I will leave adding test for valid FeedType values to you, I hope that this post gives you an idea how easy it is to improve quality of code without spending too much time. These unit tests are pretty simple, but you can use them to perform basic validation of logic in your stored procedures. You are not likely to make your users smile, but your DBAs should be happier people if you follow these simple ideas.
 
 
 
 
 

The penultimate audit trigger framework

So, it’s time to see what I came up with after some time of playing with COLUMNS_UPDATED() and bitmasks. The first part of this miniseries describes the mechanics of the encoding which columns are updated within DML operation.

The task I was faced with was to prepare an audit framework that will be fairly easy to use. The audited tables were to be the ones directly modified by user applications, not the ones heavily used by batch or ETL processes. The framework consists of several tables and procedures. All these objects belong to Audit schema, to separate them from other objects in database. This means that you will have to either modify the framework script or create Audit schema in your database.

Internet is full of dreams of generic audit triggers. Developers look for magic trigger that will know how to handle table structure and will be easy to set up, and they will not have to modify code of the trigger for each table separately. Is it possible? Yes, but unfortunately, generic trigger approach has its drawbacks. Code that is too generic, will have to perform many operations at the time when the trigger is fired, what will affect performance. I decided to write a framework that will create triggers in generic way, but they will be crafted for particular tables they are created on.

I will describe how the framework works later in this post, lets for now list objects that the framework consists of:

  • Audit.tAuditGroup – table that stores data related to particular DML operations, like table schema and name, user friendly name (for reporting purposes), change date and credentials of the principal who caused the trigger to filre
  • Audit.tAuditDetails – This table stores primary key information for the changed rows and old and new values for all columns belonging to the audited table.
  • Audit.vAudit – view that joins the above tables providing comprehensive information about audited data.
  • Audit.tIgnoreColumns – table where you can specify column names that you don’t want to be audited – because there may be no point of auditing them. I added two names, UpdateDate and RowID as an example, but in general, if you leave this table empty, all columns will be audited.
  • Audit.tUserFriendlyLabels – Sometimes it is nice to have some meaningful or at least less cryptic names when a report is displayed. This table allows for mapping audited table names to some more user friendly labels, so you can display them as ‘Account value modification’ rather than dbo.tAccount. This table also has a DML trigger attached to insert and update events that outputs message reminding that trigger has to be regenerated to reflect the changes. I will explain this a bit later.
  • Audit.ptrig_GenericAudit – this procedure is called from every trigger generated by Audit framework. It takes a few parameters and requires existence of two tables, #ins and #del, which are populated from inserted and deleted pseudo-tables.
  • Audit.pCreateAuditTrigger – this is procedure to create audit trigger on a table. You pass table schema and name to the procedure, and name of the trigger is derived from these parameters.
  • Audit.pDropAuditTrigger – this procedure removes trigger from a table with specified schema and name.
  • Audit.vAuditTriggers – this view simply lists all audit triggers defined in the database. It may be useful to create a quick report with all audited tables without having to dig through scripted code.

How it works?

I decided to move as much as possible of work to the creation stage of the trigger. The procedure Audit.pCreateAuditTrigger gathers some metadata information about the table and generates actual trigger code. This dynamic code includes call to the Audit.ptrig_GenericAudit procedure, and static information is passed to it in parameters. This static information includes fragment of SQL used in join clause between deleted and inserted tables and primary key information.

select	@JoinClause = coalesce(@JoinClause + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME,
			-- Get primary key select for insert - T-SQL version
			@PrimaryKey = coalesce(@PrimaryKey + ' and ','') + '[' + c.COLUMN_NAME + ']' +
			case when cols.CHARACTER_MAXIMUM_LENGTH is not null then
			' = ''''''''''''+convert(varchar(1000), coalesce(q.I_' + c.COLUMN_NAME + ', q.D_' + c.COLUMN_NAME + '))+''''''''''''' 
			when cols.DATETIME_PRECISION is not null then
			' = ''''+convert(varchar(23), coalesce(q.I_' + c.COLUMN_NAME +', q.D_' + c.COLUMN_NAME + '), 121)+''''' 
			else
			' = ''''+convert(varchar(1000),coalesce(q.I_' + c.COLUMN_NAME +', q.D_' + c.COLUMN_NAME + '))+''''' 
			end 
	from	INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
			on c.TABLE_NAME = pk.TABLE_NAME and c.TABLE_SCHEMA = pk.TABLE_SCHEMA and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
			inner join INFORMATION_SCHEMA.COLUMNS cols on cols.TABLE_NAME = c.TABLE_NAME 
						and cols.TABLE_SCHEMA = c.TABLE_SCHEMA and cols.COLUMN_NAME = c.COLUMN_NAME
	where 	pk.TABLE_NAME = @TableName and pk.TABLE_SCHEMA = @TableSchemaName
		and	CONSTRAINT_TYPE = 'PRIMARY KEY'

Initially I thought it might be useful to store the primary key information as XML fragment, but eventually I decided to code it as fragment of WHERE clause – all columns belonging to the primary key are joined by AND operator.

So to create a trigger on a table, you simply call

exec Audit.pCreateAuditTrigger 'Sales', 'SalesTerritory'

and to drop the trigger, call

exec Audit.pDropAuditTrigger 'Sales', 'SalesTerritory'

The above code creates trigger [Sales].[trgAudit_SalesTerritory] on the table Sales.SalesTerritory. When you look at the code of the generated trigger, you will see that the primary key fragment doesn’t contain any particular values but rather cryptic column references, and the reason is, that this fragment itself is used by dynamic SQL code in Audit.ptrig_GenericAudit.

exec Audit.ptrig_GenericAudit @UserName, @TableSchemaName, @TableName, @TableObjectId, 
@OperationType, @UTCUpdateDate, @ColumnsUpdated, 
' on i.TerritoryID = d.TerritoryID', 
'[TerritoryID] = ''+convert(varchar(1000),coalesce(q.I_TerritoryID, q.D_TerritoryID))+'''
, 'Sales territory'
 

Parameters passed to Audit.ptrig_GenericAudit are as follow:

  • UserName - contains name of the principal who triggered the operation,
  • TableSchemaName – name of the schema audited table is in
  • TableName – name of the table
  • TableObjectId – object id of the audited table
  • Operation – single character code for insert, update or delete
  • UTCUpdateDate – UTC date and time of the operation
  • ColumnsUpdated – bitmask of the updated columns
  • JoinClauseFragment – fragment of the dynamic sql that joins inserted and deleted tables on primary key columns
  • PrimaryKeyFragment – fragment of sql that allows for identification of the audited row
  • FriendlyName – label to display on a report, if there is need.

The framework supports composite primary keys. The resulting generated primary key code is converted to varchar(1000). If you need to have nvarchar data in the key, you have to modify the code to take this into account and modify the PK column in Audit.tAuditDetails. If your primary key can’t fit in 1000 characters, you can also change length of the PK column, but probably also think about design of the table – such long keys are usually sign that something went wrong during the design stage and you should consider shorter surrogate keys.

Let’s have a look how the Audit.ptrig_GenericAudit looks like:


	create procedure Audit.ptrig_GenericAudit
	(	@UserName sysname,
		@TableSchemaName sysname,
		@TableName sysname,
		@TableObjectId int, --id of the table in sys.objects
		@Operation char(1),
		@UTCUpdateDate datetime,
		@ColumnsUpdated varbinary(100), 
		@JoinClauseFragment nvarchar(3000), --fragment of SQL to append to the dynamic SQL to join inserted and deleted
		@PrimaryKeyFragment nvarchar(3000), --fragment that defines primary key. Values are retrieved in dynamic SQL.,
		@FriendlyName nvarchar(255) --user friendly name to appear on the screen
	)
	as
	begin
		/** 20100413 Piotr Rodak:	This procedure implements generic auditing code. It is called from triggers		***/
		/**						Procedure requires two temporary tables #ins and #del to exist. These have be	***/
		/**						populated with inserted and deleted pseudotables data in triggers before the	***/
		/**						procedure is called																***/

	declare @bit int ,
			@field int ,
			@maxfield int ,
			@char int ,
			@fieldname nvarchar(128) ,
			@sql nvarchar(max), 
			@selectList nvarchar(max), --script fragment with column names in the first part of unpivot query
			@crossapplyList nvarchar(max), --fragment for cross apply part of the unpivot query
			@ErrorMsg varchar(200), --error message that can be passed from internal sql
			@params nvarchar(200) -- common parameters of the query
		declare @ChangeId bigint
		
		
		--initialize parameters definition	
		set @params = '@ChangeId bigint, @ErrorMsg varchar(600) output'

		--select @ColumnsUpdated
		begin try
			--get new id for the change
/**1**/		insert Audit.tAuditGroup(Operation, TableSchema, TableName, Label, UTCUpdateDate, UserName)
			values (@Operation, @TableSchemaName, @TableName, @FriendlyName, @UTCUpdateDate, @UserName)
			set @ChangeId = scope_identity()
	
			;with column_updated_bytesCTE --divide bitmask into bytes. this query requires number table Admin.tTally in the database
			as
			(
				select number ByteNumber, convert(binary(1), 
				substring(@ColumnsUpdated, number, 1)) [ByteValue] 
				from Admin.tTally
				where number <= datalength(@ColumnsUpdated)
			),
			columnsCTE as --return columns belonging to table @TableObjectId, calculate appropriate bit masks
			(
				select column_id, [name] column_name, ByteNumber, ByteValue, 
						power(2, (a.column_id - 1) % 8) BitMask
				from sys.columns a inner join 
					column_updated_bytesCTE b 
						on ((a.column_id - 1) / 8) + 1 = b.ByteNumber
				where a.object_id = @TableObjectId
			)
			,
			updatedColumnIdsCTE as --get updated column ids, remove columns we don't want to audit (non core)
			(
				select c.column_id, convert(nvarchar(128), column_name) column_name, 
				case when ByteValue & BitMask > 0 then N'1' else N'0' end IsColumnChanged
					from columnsCTE c
					--where ByteValue & BitMask > 0 --commented out because even unchanged columns have to be audited
				where column_name not in 
					(select ColumnName from Audit.tIgnoreColumns where TableSchema is null 
						union all
					select ColumnName from Audit.tIgnoreColumns 
						where TableSchema = @TableSchemaName and TableName = @TableName )
			
			)
/**2**/			select 
				@selectList = coalesce(@selectList + N', ', N'') +
					N'convert(sql_variant, d.['  + column_name + ']) [D_' + column_name + N'], ' +
					N'convert(sql_variant, i.['  + column_name + ']) [I_' + column_name + N'], ' + 
					IsColumnChanged + N' [IsColumnChanged_' + column_name + N']' + nchar(10),
				
				@crossapplyList = coalesce(@crossapplyList + N' union all ' + nchar(10), '') + 
				N'select ''' + column_name + N''', [D_' + column_name + N']' + N', [I_' + column_name + N'], ' + N'[IsColumnChanged_' + column_name + N']'
			from updatedColumnIdsCTE 
			
			

			if(@@rowcount > 0)
			begin
				--print '@PrimaryKeyFragment= ' + @PrimaryKeyFragment
				
/**3**/				set @selectList = @selectList + 
					N'from #ins i full outer join #del d' + @JoinClauseFragment + N') q' + nchar(10) +
					N'cross apply ' 
				set @crossapplyList = N'( ' + nchar(10) + @crossApplyList + nchar(10) + N') unp(ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged)'
				
				select @sql = 
					N'begin try ' + nchar(10) + 
					N'insert Audit.tAuditDetails (ChangeID, PK, FieldName, OldValue, NewValue, IsColumnChanged)' + nchar(10) +
					N'select @ChangeId, PK, ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged' + nchar(10) +
					N'from ( select ''' + @PrimaryKeyFragment + N''' [PK], ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged from '+ + nchar(10) + 
					N'( select ' + nchar(10) +
					@selectList + nchar(10) +
					@crossapplyList + nchar(10) + N') outerQry' + nchar(10) +
					N'end try' + nchar(10) + 
					N'begin catch' + nchar(10) + 
						N'set @ErrorMsg = ''Audit error: '' + error_message() + '' ('' + convert(varchar, error_number()) + '')''' + nchar(10) +
						N'print ''dsql Error: '' + @ErrorMsg' + nchar(10) +
					N'end catch'
				--print @sql
				exec sp_executesql @sql, @params, @ChangeId, @ErrorMsg output
				if @ErrorMsg is not null --rethrow error if message not empty
					raiserror(@ErrorMsg, 16, 1)
			end

		end try
		begin catch
			declare @err nvarchar(1000)
			set @err = N'Procedure ' + error_procedure() + N': Error occured ('+ convert(nvarchar, error_number()) + N'): ' + error_message()
			raiserror(@err, 16, 1) with nowait;
		end catch
	end

There are three important stages of execution in this procedure.

/**1**/ – The DML operation is recorded and its id is retrieved to @ChangeId variable. This value is used subsequently in the ‘details’ table. After this insert, bitmap processing already discussed in my previous post takes place.

/**2**/ – The COLUMNS_UPDATED bitmap calculations result with data I can use to generate list of changed columns. This is place where you can decide if you want to save only changed columns, or rather everything. In my case, I store everything, and I have flag IsColumnUpdated that indicates if a column was updated during the DML operation. Please note that even if columns value hasn’t changed, but it was in the SET part of the UPDATE query, this flag will be set to 1. This means that you may want to additionally filter audit data to remove columns whose values didn’t change from the report.

/**3**/ – In this part of the procedure final sql code is put together. As you may notice (you can uncomment print @sql statement to see the generated code) I use cross apply operator in the query. The reason is that my initial approach proved to be quite slow and depended on the number of processed columns. I begun with the code posted by Nigel Rivett some time ago. The code worked, but I didn’t like the idea of having a loop in the trigger, and also the bitmap calculations are based on ordinal_position column. The ordinal_position works only if you don’t remove any columns from the table (unless you remove last column each time). So I changed the loop code to set-based code, which did more or less the same job. As you may notice, the code joins inserted and deleted tables for each of the columns. if you have 5 columns, it’s OK. If you have 50 – it is less than OK. I came across the performance issue and looked for a solution for a while. I realized that to improve performance I need to join the inserted and deleted tables rather once per operation than for every column. So unpivot was the answer. This is not a very well known table operator, and this is a pity, because it can be really useful in many cases. I wonder, why there is practically no documentation about it in Books Online.

The problem with unpivot operator is that if the rotated columns contain null values, they are removed from the resulting rowset. There is an excellent discussion of this operator published some time ago by Brad Schultz: part 1 and part 2. It turns out that unpivot can be successfully replaced with cross apply, which gives you ability to include nulls to the resulting rowset.

I uncommented the print statement in the generated code and ran the update statement:

update Sales.SalesTerritory set [Name] = 'Northwest 1' where TerritoryId = 1

The generated code is as follows:

begin try 
insert Audit.tAuditDetails (ChangeID, PK, FieldName, OldValue, NewValue, IsColumnChanged)
select @ChangeId, PK, ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged
from ( select '[TerritoryID] = '+convert(varchar(1000),coalesce(q.I_TerritoryID, q.D_TerritoryID))+'' [PK], ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged from 
( select 
convert(sql_variant, d.[TerritoryID]) [D_TerritoryID], convert(sql_variant, i.[TerritoryID]) [I_TerritoryID], 0 [IsColumnChanged_TerritoryID]
, convert(sql_variant, d.[Name]) [D_Name], convert(sql_variant, i.[Name]) [I_Name], 1 [IsColumnChanged_Name]
, convert(sql_variant, d.[CountryRegionCode]) [D_CountryRegionCode], convert(sql_variant, i.[CountryRegionCode]) [I_CountryRegionCode], 0 [IsColumnChanged_CountryRegionCode]
, convert(sql_variant, d.[Group]) [D_Group], convert(sql_variant, i.[Group]) [I_Group], 0 [IsColumnChanged_Group]
, convert(sql_variant, d.[SalesYTD]) [D_SalesYTD], convert(sql_variant, i.[SalesYTD]) [I_SalesYTD], 0 [IsColumnChanged_SalesYTD]
, convert(sql_variant, d.[SalesLastYear]) [D_SalesLastYear], convert(sql_variant, i.[SalesLastYear]) [I_SalesLastYear], 0 [IsColumnChanged_SalesLastYear]
, convert(sql_variant, d.[CostYTD]) [D_CostYTD], convert(sql_variant, i.[CostYTD]) [I_CostYTD], 0 [IsColumnChanged_CostYTD]
, convert(sql_variant, d.[CostLastYear]) [D_CostLastYear], convert(sql_variant, i.[CostLastYear]) [I_CostLastYear], 0 [IsColumnChanged_CostLastYear]
, convert(sql_variant, d.[rowguid]) [D_rowguid], convert(sql_variant, i.[rowguid]) [I_rowguid], 0 [IsColumnChanged_rowguid]
, convert(sql_variant, d.[ModifiedDate]) [D_ModifiedDate], convert(sql_variant, i.[ModifiedDate]) [I_ModifiedDate], 0 [IsColumnChanged_ModifiedDate]
from #ins i full outer join #del d on i.TerritoryID = d.TerritoryID) q
cross apply 
( 
select 'TerritoryID', [D_TerritoryID], [I_TerritoryID], [IsColumnChanged_TerritoryID] union all 
select 'Name', [D_Name], [I_Name], [IsColumnChanged_Name] union all 
select 'CountryRegionCode', [D_CountryRegionCode], [I_CountryRegionCode], [IsColumnChanged_CountryRegionCode] union all 
select 'Group', [D_Group], [I_Group], [IsColumnChanged_Group] union all 
select 'SalesYTD', [D_SalesYTD], [I_SalesYTD], [IsColumnChanged_SalesYTD] union all 
select 'SalesLastYear', [D_SalesLastYear], [I_SalesLastYear], [IsColumnChanged_SalesLastYear] union all 
select 'CostYTD', [D_CostYTD], [I_CostYTD], [IsColumnChanged_CostYTD] union all 
select 'CostLastYear', [D_CostLastYear], [I_CostLastYear], [IsColumnChanged_CostLastYear] union all 
select 'rowguid', [D_rowguid], [I_rowguid], [IsColumnChanged_rowguid] union all 
select 'ModifiedDate', [D_ModifiedDate], [I_ModifiedDate], [IsColumnChanged_ModifiedDate]
) unp(ColumnName, OldColumnValue, NewColumnValue, IsColumnChanged)
) outerQry
end try
begin catch
set @ErrorMsg = 'Audit error: ' + error_message() + ' (' + convert(varchar, error_number()) + ')'
print 'dsql Error: ' + @ErrorMsg
end catch

As you see, the generated code joins deleted and inserted tables (copied to respective temporary tables) only once per query, and cross apply operator is used to unpivot the columns to name value pairs. The result of the update is in the following table:

select * from Audit.vAudit

 

ChangeId Operation TableSchema TableName Label PK FieldName OldValue NewValue IsColumnChanged UTCUpdateDate UserName
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 TerritoryID 1 1 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 Name Northwest Northwest 1 1 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 CountryRegionCode US US 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 Group North America North America 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 SalesYTD 5767341.9752 5767341.9752 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 SalesLastYear 3298694.4938 3298694.4938 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 CostYTD 0.00 0.00 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 CostLastYear 0.00 0.00 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 rowguid 43689A10-E30B-497F-B0DE-11DE20267FF7 43689A10-E30B-497F-B0DE-11DE20267FF7 0 2010-05-10 22:22:38.247 Amilo\Rogas
1 U Sales SalesTerritory Sales territory [TerritoryID] = 1 ModifiedDate 1998-06-01 00:00:00.000 1998-06-01 00:00:00.000 0 2010-05-10 22:22:38.247 Amilo\Rogas

As you can see all column values are stored in the audit table. You can easily change this if you choose to do so, but in my case, this was the requirement. To help identify changed columns, there is IsColumnChang

ed column in the view. Mind though, that if you run update statement that touches a column within the SET clause, it will be reported as changed even if there was no actual change of the value.

TODO – the framework doesn’t support LOB types, as there is no explicit conversion to sql_variant data type. If you need LOB types to be audited, you can convert them to corresponding “short” types, like varchar(8000) and nvarchar(4000), but you may lose some data with such conversion. The other option would be to create separate LOB audit columns to keep these values.

 

I attach script that installs the framework, you can download it using this link.

Be careful when running it twice, because it removes audit tables and any information that is stored in them.

 

This would conclude this post and the audit framework miniseries. Please feel free to add any comments you have below.

 

COLUMNS_UPDATED() for audit triggers

In SQL Server 2005, triggers are pretty much the only option if you want to audit changes to a table. There are many ways you can decide to store the change information. You may decide to store every changed row as a whole, either in a history table or as xml in audit table. The former case requires having a history table with exactly same schema as the audited table, the latter makes data retrieval and management of the table a bit tricky. Both approaches also suffer from the tendency to consume big amounts of space, especially for wide tables. There is usually no sense of storing unchanged data, in 2 copies for update operations (from deleted and inserted psuedotables). You can also choose the third approach, to store only changed data in the audit table. This post focuses on methods of identifying columns changed by the DML statements. Let’s start with creating a sample table.


if exists(select 1 from sys.tables where object_id = object_id('[dbo].[tManyColumns]')
drop table [dbo].[tManyColumns]

GO

create
table [dbo].[tManyColumns]
(
[c1] [int] NOT NULL,
[c2] [int] NOT NULL,
[c3] [int] NULL,
[c4] [int] NULL,
[c5] [int] NULL,
[c6] [int] NULL,
[c7] [int] NULL,
[c8] [int] NULL,
[c9] [int] NULL,
[c10] [int] NULL,
[c12] [int] NULL,
[c13] [int] NULL,
[c14] [int] NULL,
[c15] [int] NULL,
[c16] [int] NULL,
[c17] [int] NULL,
[c18] [int] NULL,
[c19] [int] NULL,
constraint [PK1] primary key clustered
(
[c1] ASC,
[c2] ASC
)
)
ON [PRIMARY]

go
--populate the table

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN
TRANSACTION;
INSERT INTO [dbo].[tManyColumns]([c1], [c2], [c3], [c4], [c5], [c6], [c7],
[c8], [c9], [c10], [c12], [c13], [c14], [c15], [c16], [c17], [c18], [c19])
SELECT 1, 3, 32, 14, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, 88, NULL, NULL UNION ALL
SELECT
1, 4, 32, 14, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL,
NULL, NULL, 88, NULL, NULL UNION ALL
SELECT
2, 1, 32, 14, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, 11, 88, NULL, NULL
COMMIT
;
RAISERROR (N'[dbo].[tManyColumns]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO
--check contents of the table
select * from dbo.tManyColumns

go

Please note that script with INSERT statements was generated by excellent SSMS Tools Pack.

Triggers in SQL Server are fired once for data change operation (insert, update, delete), not for every row. This is important to know, many developers assume that trigger is called for every row within the updated set. Interestingly, if there are no rows updated, trigger is fired anyway (for example update testtbl where 1 = 0). How do you know a trigger was fired for particular operation? You have two choices here.
You can create a separate trigger for each of the operations:

create trigger trigDel on dbo.tManyColumns for delete 
as
print 'DELETE trigger'

GO

create
trigger trigUpd on dbo.tManyColumns for update
as
print 'UPDATE trigger'

GO

create
trigger trigIns on dbo.tManyColumns for insert
as
print 'INSERT trigger'

This approach while clearly separates particular actions, also has tendency to multiply practically the same code. So most implementations I have seen use other approach. You can test inserted and deleted pseudo-tables available in trigger for presence of data. In case of DELETE, there will be no rows in inserted table and for INSERT, deleted table will be empty. For UPDATE, both tables will be populated, inserted with new data and deleted with old data. I never realized this before, but you shouldn't assume that the trigger is fired for DELETE because there are no rows in inserted, because it may be fired for UPDATE where no rows match the criteria - and both inserted and deleted tables will be empty.
How to know which columns have been changed?
SQL Server provides two methods of finding out which columns have been updated during particular operation:
UPDATE( column ) - This function returns true if particular column is updated, false otherwise.
COLUMNS_UPDATED() - This function returns one or more bytes that contain bitmaps representing updated (and not updated) columns.

So, you can implement a trigger that will react to particular column update more or less like this:

create trigger trigCol1 on dbo.tManyColumns for insert, update, delete 
as
if UPDATE (c3)
print 'Hey, column c3 has been just updated!'


Of course, it's up to your imagination, (or your project requirements) what you can do with this information. You can audit change of this column, you can trigger some actions that have to happen when this column is updated, eventually you can calculate some other values (possibly in other tables) based on new value of the column. You can also rollback the transaction if the new value of column doesn't meet business or integrity requirements.

 

The drawback of this method is that you have to check condition for every column and you have to deal with column names and for a hundred columns in a table it is really not a feasible option. This is where COLUMNS_UPDATED functions comes in rescue.
The COLUMNS_UPDATED returns a bitmap where bits set to 1 indicate changed columns, while these set to 0 mean that the columns were not changed. It is worth to mention here that 'changed' doesn't mean that they values were changed, it means that columns where on the left side of assignment operator in update statement. So this statement
update testtbl set col2 = col2
will set the bit mask for the column col2 to 1 even though the actual column's value hasn't changed.

The COLUMNS_UPDATED function returns varbinary type string (is there better word?). Its length depends on the number of columns that are defined in the table. Each byte will contain mask for eight consecutive columns, and the last byte will keep mask for the remaining columns. For up to 8 columns there is going to be 1 byte, for 12 columns 2 bytes, for 17 columns - 3 bytes.
If you look at the above numbers, you can see, that a formula for the number of bytes is ((@n - 1) / 8 + 1), where @n is max column_id in the table.

select  
((7 - 1) / 8 + 1) [bytes for 7 columns],
((12 - 1) / 8 + 1) [bytes for 12 columns],
((17 - 1) / 8 + 1) [bytes for 17 columns]

You can get all columns from information_schema.columns view, but this view will not return column_id, but ordinal position for these columns. To get column_id values you can use sys.columns table:

select name, column_id from sys.columns where object_id = object_id('testtbl')

Alternatively you can use COLUMNPROPERTY function:

select ordinal_position, column_name, 
COLUMNPROPERTY(object_id(table_name), column_name, 'ColumnId') column_id
from information_schema.columns

The COLUMNS_UPDATED return value is curiously arranged in a rather counter-intuitive way, at least this was my first impression. The value is as I said divided into bytes, and each byte contains bit mask for eight consecutive columns. The catch is that bytes are ordered left to right:

1stbyte->2ndbyte->3rdbyte..., while in bytes, bits are ordered right to left, with least significant bit on the right (this is standard binary number representation).

You end up with something like this:
[{8}, {7}, {6}, {5}, {4}, {3}, {2}, {1}][{16}, {15}, {14}, {13}, {12}, {11}, {10}, {9}][{24}, {23}, {22}, {21}, {20}, {19}, {18}, {17}]
The square brackets represent bytes, numbers in curly braces represent column_id values.

What can you do with this bitmap? If you want to check if particular column was updated, first you have to select the byte relevant for this column and then check if the flag for the column is set to 1 or 0. This sounds complicated, but it isn’t.

You can use SUBSTRING function to select only one byte of the mask, like this:

select SUBSTRING(COLUMNS_UPDATED(), 2, 1) 

The above statement returns second byte in the mask. Of course, you wouldn’t want to work with hardcoded values, but the formula for the byte number of a particular column is same as in case of calculating length of the whole mask: just take column_id of the column, subtract 1, divide by 8 and add 1 to the result.

select SUBSTRING(COLUMNS_UPDATED(), ((@col_id - 1) / 8 + 1), 1)

Now, we have our byte, we need to extract the bit value for the column. To calculate position of the bit within selected byte, you have to subtract 1 from the column_id, modulo divide the result by 8 and add 1 to the result of the division.

@bit_position = (@col_id-1)%8 + 1

The modulo operation returns the reminder of the division. So for col_id 5 it will be 5, for 12 it will be 4, for 17 the operation will return 1:

select 
(5-1)%8 + 1 [5th bit in 1st byte],
(12-1)%8 + 1 [4th bit on 2nd byte],
(17-1)%8 + 1 [1st bit in 3rd byte]

Now, when we have position, we can calculate mask that we will use in turn to extract the bit indicating if the column was updated or not. There is another formula, and this is the last one in this post: to get the mask value, you have to raise 2 to the power equal to bit position minus 1. So the formula looks like this:

@mask = power(2, @bit_position – 1)

If we replace @bit_position with the formula for the bit position, we get

@mask = power(2, (@col_id-1)%8 + 1 – 1) = power(2, (@col_id-1)%8)

select 
(5-1)%8 + 1 [5th bit in 1st byte],
power(2, (5-1)%8) [mask for the bit],
(12-1)%8 + 1 [4th bit on 2nd byte],
power(2, (12-1)%8) [mask for the bit],
(17-1)%8 + 1 [1st bit in 3rd byte],
power(2, (17-1)%8) [mask for the bit]

Ok, let’s wrap it up. We know all the formulas required to get updated column information from the bitmap, how to use this in a trigger? Many developers use in this case a loop that calculates the byte, mask and retrieves name of the updated column. I am not going to do this.

Some time ago I wrote about numbers table, and you can find there links to clever and very useful implementations of this technique. You can also apply this technique to COLUMNS_UPDATED bitmap. Here’s one approach:


if exists(select 1 from sys.triggers where parent_id = object_id('[dbo].[tManyColumns]')
and name = 'trg1')
drop trigger trg1

go

create
trigger trg1 on [dbo].[tManyColumns]
for insert, update, delete
as
begin
declare @table_id int

select @table_id = parent_id from sys.triggers where object_id = @@procid

select columns_updated() [columns_updated string]

;with column_updated_bytesCTE --divide bitmask into bytes. this query requires number table Admin.tTally in the database
as
(
select number ByteNumber, convert(binary(1),
substring(columns_updated(), number, 1)) [ByteValue]
from Admin.tTally
where number <= datalength(columns_updated())
),
columnsCTE as --return columns belonging to table @TableObjectId, calculate appropriate bit masks
(
select column_id, [name] column_name, ByteNumber, ByteValue,
power(2, (((a.column_id - 1 ) % 8) + 1) - 1) BitMask
from sys.columns a inner join
column_updated_bytesCTE b
on ((a.column_id - 1) / 8) + 1 = b.ByteNumber
where a.object_id = @table_id
)
select *,
case when ByteValue & BitMask > 0 then 'yes' else 'no' end [Is column updated?] from columnsCTE
end

go

This small example returns list of columns belonging to the table and indicates which columns have been updated. It also returns the value of columns_updated bitmask, so you can check that particular bytes in it are equal to ByteValue returned by the last query in the trigger. Here’s how you can test the trigger:

update dbo.tManyColumns set c10 = 1 where c1 = 1 and c2 = 4

Here’s output you will see in the results pane:

columns_updated string
--------------------------
0x000200

(1 row(s) affected)

column_id   column_name     ByteNumber  ByteValue BitMask     Is column updated?
----------- --------------- ----------- --------- ----------- ------------------
1           c1              1           0x00      1           no
2           c2              1           0x00      2           no
3           c3              1           0x00      4           no
4           c4              1           0x00      8           no
5           c5              1           0x00      16          no
6           c6              1           0x00      32          no
7           c7              1           0x00      64          no
8           c8              1           0x00      128         no
9           c9              2           0x02      1           no
10          c10             2           0x02      2           yes
12          c12             2           0x02      8           no
13          c13             2           0x02      16          no
14          c14             2           0x02      32          no
15          c15             2           0x02      64          no
16          c16             2           0x02      128         no
17          c17             3           0x00      1           no
18          c18             3           0x00      2           no
19          c19             3           0x00      4           no

(18 row(s) affected)

As you see, column C10, which has column_id belonging to the second byte is correctly indicated as updated. The ByteValue for this byte is 0x02, which corresponds to second position of the column (from the right) within the byte. In the first resultset returned by the trigger you can see that columns_updated bitmap consists of three bytes, and second byte (from the left) is 0x02.

Given this information, it is pretty easy to generate dynamic code that will insert changed data to an audit table. However, this is topic for another post. Stay tuned :)

Coding standards

This post will be about coding standards. There are countless articles and blog posts related to this topic, so I know this post will not be too revealing. Yet I would like to mention a few things I came across during my work with the T-SQL code.

Naming convention - there are many of them obviously. Too bad if all of them are used in the same database, and sometimes even in the same stored procedure. It is not uncommon to see something like

create procedure dbo.Proc1

(

@ParamId int

)

as

begin

declare @variable_id int

--...

end



As you see, we have both CamelCase and 'underscore' notation here. Wouldn't it be nicer if we had only one? I am not saying which one, but you should agree upon naming convention before you start development.

Cursors - oh yeah, we all know that they are bad. But do we? I have seen code like this

declare @a int, @b int

declare csr cursor for

select a, b from Table1

open csr

fetch next from csr into @a, @b

while @@fetch_status <> -1

begin

update Table2 set ColB = @b where ColA = @a

fetch next from csr into @a, @b

end

close csr

deallocate csr



So as you see, we select rows from Table1 and one by one (RBAR) issue an update on Table2 using variables read from cursor as keys. How about this?

update Table2 set ColB = b

from Table1 inner join Table2 on Table1.a = Table2.ColA


In these two lines you solve nicely clumsiness (and potentially performance issues) of the first code sample.



Default parameters - something I noticed recently, when I needed to figure out if a procedure is going to be called without parameters or not:

create procedure dbo.ProcessSomethingForItem

@ItemName varchar(20) = '%'

as

select ColA, ColB into #temp from Items where ItemName = @ItemName

...

I am not sure if this is a typo or not - if I should fix the code to support wildcard search or change it to throw error if no rows are returned? Obviously '%' is not a name of any of items in the table. What is the reason of calling this proc in a way that will not process any rows?  
If we remove default value from the parameter, we will at least know that something is wrong when someone implements parameterless call to this procedure. Otherwise higher code will work seamlesly, no exceptions, but nothing done too. Definitely not what customers wish. Having said that, parameters with default values are definitely useful and should be used where needed, but they shouldn't be treated as shortcuts to facilitate testing.
Even worse scenario is if we code the parameter to default to an existing ItemName. Then, if for some reason higher code will call procedure without parameters, procedure will process data for ItemName - another rather tricky bug to trace down.

Comments - I see tons of code that modifies data in very extensive ways, but there is no explanation whatsoever about what is the business meaning of particular transformations. I am sure that a DBA or a new developer would appreciate a lot if a series of update statements, 10 lines each would be accompanied by a line or two of commentary, about what the statements do and why. Would be nice, wouldn't it?

Prefixing columns in views - this is real pain.. If you have a view with 150 columns and 20 tables joined every possible way in FROM clause and no prefixes in columns in SELECT part... It takes so much time to dig down the tables and other views to find the origin of a particular column. Wouldn't it be nice to have a feature in SSMS that would either enforce usage of prefixes or at least issue a warning during object creation? After all, some rudimentary checks are performed and the engine knows which columns are from which table. I recommend that developers must prefix columns in multitable select statements and I would impose this practice during code reviews.

This list is definitely not finished. I am sure you came across many little annoying things, like indenation, spaces vs tabs, lowercase vs UPPERCASE etc. I will update this post if I find especially outstanding and annoying (bad) practice.

An internal error occurred on the report server - no disk space for database.

Last week was pretty hectic for me. I was developing SSRS report that was to be deployed to our QA, UAT and production environments. The report consists of many sections, which I had to implement as tables rather than subreports, because SSRS for SQL Server 2005 has many constraints, being unable to display dynamic headers and footers from subreports one of them. Having about 15 rather complicated tables on one design area is not a very comfortable way of work. The whole IDE is slow and tends to crash, so it is important to save your work every now and then and use a source control system to keep history of changes.

Yet I was able to make progress and elements of the report started to fit in, resembling the desired outcome.
I installed the reporting server and deployed the report. It was rather smooth process. I worked on new versions of the report and deployed them as they were ready so our BAs could test them and look for bugs in data and layout.
Yesterday around 6pm I was ready to deploy most recent version of the report and then head home. You can imagine that I was rather unimpressed when I got message:

An internal error occurred on the report server. See the error log for more details

I would appreciate more detailed information to be returned to someone who deploys the report, by the way.

I looked into the error log and found basically nothing.

<Header>
  <Product>Microsoft SQL Server Reporting Services Version 9.00.4035.00</Product>
  <Locale>en-US</Locale>
  <TimeZone>Eastern Standard Time</TimeZone>
  <Path>C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles\ReportServerWebApp__02_22_2010_10_37_33.log</Path>

  <SystemName>USWMWEMDSQ01</SystemName>
  <OSName>Microsoft Windows NT 5.2.3790 Service Pack 2</OSName>
  <OSVersion>5.2.3790.131072</OSVersion>
</Header>
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing ReportBuilderTrustLevel to '0'  as specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing MaxScheduleWait to default value of '1' second(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing DatabaseQueryTimeout to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing ProcessRecycleOptions to default value of '0'  because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing RunningRequestsScavengerCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing RunningRequestsDbCycle to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing RunningRequestsAge to default value of '30' second(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing CleanupCycleMinutes to default value of '10' minute(s) because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing WatsonFlags to default value of '1064'  because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing WatsonDumpOnExceptions to default value of 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'  because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to default value of 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'  because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing SecureConnectionLevel to default value of '1'  because it was not specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing DisplayErrorLink to 'True'  as specified in Configuration file.
w3wp!library!8!2/22/2010-10:37:33:: i INFO: Initializing WebServiceUseFileShareStorage to default value of 'False'  because it was not specified in Configuration file.


This was the most recent log file, and there were no error messages in it as you see. I noticed though that the file was modified about half an hour earlier than I started to receive error. I searched the web trying to find something likely to be similar to my case but I couldn't. I remoted to the app server and checked once more the settings of the Reports and ReportsServer applications in IIS. They looked alright. Still, the fact that I had no entries in the log file for my error was suspicious and I decided to restart web server and the application pool the reporting applications were using. And bingo, the log file was refreshed. This time it contained the reason of the problem with the deployment.


w3wp!library!1!02/22/2010-14:42:04:: Call to CreateFolderAction(TEST Data Sources, /).
w3wp!library!1!02/22/2010-14:42:04:: i INFO: Catalog SQL Server Edition = Enterprise
w3wp!library!1!02/22/2010-14:42:05:: w WARN: Transaction rollback was not executed connection is invalid
w3wp!library!1!02/22/2010-14:42:05:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ;

 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Could not allocate space for object 'dbo.Catalog'.'IX_Parent' in database 'MyReports' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery()
   at Microsoft.ReportingServices.Library.DBInterface.CreateObject(Guid id, String shortName, String fullPath, String parentPath, Guid parentId, ItemType objectType, Byte[] objectContent, Guid intermediateSnapshotID, Guid link, String linkPath, ItemProperties objectProperties, String parameters, Byte[] createdBySid, String createdByName, DateTime creationDate, DateTime modificationDate, String mimeType)
   at Microsoft.ReportingServices.Library.DBInterface.CreateObject(Guid id, String shortName, String fullPath, String parentPath, Guid parentId, ItemType objectType, Byte[] objectContent, Guid intermediateSnapshotID, Guid link, String linkPath, ItemProperties objectProperties, String parameters, String createdBy, DateTime creationDate, DateTime modificationDate, String mimeType)
   at Microsoft.ReportingServices.Library.CatalogItem.Create()
   at Microsoft.ReportingServices.Library.CreateItemAction`1.CreateNew(CatalogItem itemToCreate, CatalogItemContext parentContext)
   at Microsoft.ReportingServices.Library.CreateItemAction`1.PerformActionNow()
   at Microsoft.ReportingServices.Library.RSSoapAction`1.Execute()
   
Wow. This was not expected. I realized that I had never thought about looking at the setup of the report server databases, and they were created automatically when I was installing and configuring reporting services. And it turned out that they were created according to model database presets, and in the default folder of the database server. Unfortunately, this folder happened to be on the drive that is not supposed to contain any databases. Other activity was filling it up gradually and yesterday there was no more space for reporting databases. Deployment writes quite a lot of information into the database and of course this requires growth of data and log files. Oh well. Tomorrow I will move the databases to appropriate disks. For now, I freed up some space on the drive and I was able to deploy the reports without any problems.

Lesson learned, I guess.

Kayleigh

It's been about 25 years since I've heard Misplaced Childhood or the first time in my life. There was time when I could sing the whole album along with Fish.
Thankfully karaoke and youtube weren't as popular in Poland in 1985 as they are now, or I could've ended up in a lip sync hit like these two chaps:
http://www.youtube.com/watch?v=tMI7h78mDMA

I learned a lot of english words from lyrics, sometimes to great joy of my english speaking friends. Yet I just realized today, what 'dancing in stilletos in the snow' actually meant. I always felt that it had to be pretty romantic, enough to mention it in a song. I never knew at that time though what those stilettos might be. Then, many years later, I learned the word and the meaning, but it's only today when it hit me and eventually I can paint the whole image of that dance in my mind. And it is a 'frozen' image, though still romantic.

So, I had a revelation, you might say.

Posted by Piotr Rodak | 1 comment(s)
Filed under:

relative query cost not accurate when using set rowcount

When tuning performance of a query, it is quite common to compare different variants of the query in the same batch and compare the execution plans to see if changes that you made actually work for better or not. Often you would compare just relative cost of execution plan of a query within the batch. I lived for a long time with innocent assumption that the relative cost is accurate and reliable. As Gail Shaw showed in her blog, this assumption doesn't hold if you use scalar functions within a query. I just found out that this is true also for certain type of queries that you would use in specific scenarios.


Let's assume that you have a huge table that is heavily used by OLTP system. Yet you have to delete significant amount of data from it with as small impact for the user as possible. If you just execute delete from table with some key value, it may happen that the amount of affected rows will trigger lock escalation to table level. This will obviously prevent users from inserting or even reading data from the table.
The remedy for such scenarion is to delete rows in batches.
Let's see how we can do it. Here is the table we are going to use.

create table t1(a int identity(1, 1) primary key clustered, b char(10) default('a'))


I will populate it with 100 000 rows:

set rowcount 100000

insert t1 (b)

select 'a'

from master.sys.objects a, master.sys.objects b, master.sys.objects c

set rowcount 0

select count(*) [before batch 1] from t1



Fist approach to delete rows in batches from this table can be as follows:

--batch 1

declare @batchsize int

set @batchsize = 100

set rowcount @batchsize

while 1=1

begin

delete t1 where a < 50000

if @@rowcount = 0

break;

end

set rowcount 0



As you see, we can modify the size of the batch here to adjust it to the size of the table and required degree of concurrency.
The other option is to use rowcount setting, similar to the approach we used to populate the table:

declare @batchsize int

set @batchsize = 100

while @batchsize <> 0

begin

delete top (@batchsize) from t1 where a < 50000

set @batchsize = @@rowcount

end



The query and the loop looks a bit simpler. Which approach is better?  

To be honest both techniques give similar results, at least for the sample data. I didn't have time to run tests for bigger amounts of data, but if you find this interesting, I attach the script so you can check it out for yourself.

More interesting are the execution plans. I removed while loops from above queries and executed them with "Include Actual Execution Plan" option on.

declare @batchsize int

set @batchsize = 100


--query 1

set rowcount @batchsize

delete t1 where a < 50000

set rowcount 0

--query 2

delete top (@batchsize) from t1 where a < 50000


The set rowcount query shows plainly delete from the clustered index. The select top() query shows plan that is more complicated, involving clustered index seek and Top operator. Yet SQL Server shows that the plan with select top() is much more efficient!


 

Why? I checked IO statistics and both queries show the same number of reads:

Table 't1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 't1'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If you look though at the estimated and actual numbers of rows that optimizer processed, you will see immediate difference

 

 

 


The optimizer doesn't know that set rowcount @batchsize is in effect and estimates that all rows will have to be processed. The select top() query is parsed by optimizer and it knows that we want only 100 rows, so it estimates that the cost of IO and memory will be much less than in case of set rowcount query. Yet actual numbers of rows are the same, which may explain why my tests show no particular difference between both approaches.

This leads me to conclusion that the relative difference between both plans, as you see on the first picture, is mostly caused by inaccurate estimation of amount of data the engine will have to read and modify. And if this is the case, I think this is worth to keep in mind that it may be that stale statistics may affect your plan comparisons too. You can recognize stale statistics amongst the others by significant differences between estimated and actual numbers of rows in execution plans.

I attach the scripts that I used for tests here: testbatchdelete.zip

like in the old times

My little brother got recently an old 50mm Pentax-M 1:2 lens. While this lens is not known for the best image quality amongst Pentax prime 50mm lenses, it gave me first opportunity to take pictures 'the old way' on my K10D. This is quite nostalgic feeling to have to focus and set aperture manually like in the old '80s with famous soviet camera Zenith. My brother has also some old lenses with M42 mount. I bought adapter allowing me to connect them to my camera and indeed, it works. I find though that 50mm is the most interesting.

This is one of pictures i took with it. It is quite hard to get focus properly, at least for me, but when I succeed, the satisfaction is much more rewarding than when I use 'intelligent' lens.

By the way, winter in Poland looks beautiful, provided you have heating (it can be -20 C here or less) :).

 

Posted by Piotr Rodak | with no comments
Filed under:

updating column with a random value

I have been working recently on load testing of our ETL. One of the factors that determines amount of transfered data is a lookup table that is joined to the source. I populated this table with aproppriate values, and then realized that I also need some values in second column, and they were null. Let's say the table looks like that:

create table t101

(

a int,

b char(10)

)

go

insert t101(a, b)

select 1, ''

union all select 2, ''

union all select 3, ''

union all select 4, ''

union all select 5, ''

union all select 6, ''

union all select 7, ''

union all select 8, ''

go

Luckily I had another table that contained the values I needed. The problem was only, there were much less rows in the second table than in the first one. There is also no relation between both tables.

 

create table t102

(

someval char(10)

)

 

go

insert t102(someval)

select 'val 01'

union all select 'val 02'

union all select 'val 03'

union all select 'val 04'

union all select 'val 05'

union all select 'val 06'

union all select 'val 07'

union all select 'val 08'

union all select 'val 09'

go

Happily I wrote a simple update:

update t101 set b = (select top 1 someval from t102 order by newid())

I ran the query and then selected from the t101:

select * from t101

And the results are..

a    b
1    val 06   
2    val 06   
3    val 06   
4    val 06   
5    val 06   
6    val 06   
7    val 06   
8    val 06     

I was surprised, to say the least. Since I had something other to do, I left this as it was, letting my 'background threads' do the thinking. Of course, I figured out what was going on when I was walking home, wading in snow slush and struggling with wind.

The problem with above subquery is that it is not correlated. This means, its execution doesn't depend on the row the query produces output for. So the optimizer chooses to execute the query only once and apply the result to every row of the outer query. It is a random result, but only one for the whole set.

Ok, so what to do then? No, don't use cursor.

declare c cursor for select a from t101
declare @a int
open c
fetch next from c into @a
while @@fetch_status = 0
begin
        update t101 set b = (select top 1 someval from t102 order by newid())
        where a = @a
        fetch next from c into @a
end
close c
deallocate c

Seriously, don't use cursor.

You have to find a way to correlate the query even if there is no relationship between the data. I thought about two ways, but I am sure there may be more.

First option is to return value which would be a result of concatenation of value from table 2 and value from current row of table 1.:

update t101 set b = (select top 1 someval + case when b is null then '' else '' end from t102 order by newid())

As you see, I don't add effectively anything to someval. This query works, but is kind of too elaborate and complicated to write. So there is another option for you - add a filter that will always return true.

update t101 set b = (select top 1 someval from t102 where a = a order by newid())

 

As you see, as long as a value is not null, the subquery will return a result. And it will be a new result for each processed row.

I attach the code so you can play with this idea a bit more, maybe you will find simpler ways of generating random data than these above - if you do, please let me know.

 Testing update random value.zip

 

 

 

 

updlock, holdlock and deadlocks

We have a process that manages sliding window over a number of tables in our reporting database. This process is triggered by first call to our reporting ETL. There is table that contains two dates that are important for the partitioning and business requirements. The table is build as the following example:

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

FromDate datetime not null,

ToDate datetime not null

)


The table contains only one row:

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')


 
It is important that the code is called only once and other processes do not overlap, as this would lead to incorrect partition range and missing data in the interface views.

Just a few days we had a production issue, and then another one, next day - deadlocks. That surprised me to say the least, because I thought I had secured myself against this problem. The code looked like this:

declare @newFrom datetime, @newTo datetime, @oldFrom datetime

set @newFrom = '20090115'

set @newTo = '20090114'


begin tran

--######### original approach

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)


while @oldFrom < @newFrom

begin

set @oldFrom = dateadd(day, 1, @oldFrom)

update dbo.tDateTest set FromDate = @newFrom, ToDate = @newTo

--some other processing here..

end

commit

select * from dbo.tDateTest


Query 1, Original approach.

As you see I added the updlock and holdlock hints to the select statement. This technique effectively changes isolation level of the transaction to serializable, by acquiring update lock at the time select statement is executed and holding it to the end of the transaction.
Well, it turned out that when multiple processes execute the above code, there may be a situation when a deadlock occurs.
At the beginning, I had trouble to actually reproduce the behavior. Fiddling with waitfor statement and multiple windows proved to be rather cumbersome. Then I remembered that Adam Machanic's little utility SQLQueryStress can do exactly what I want - call some code many times from different threads. And indeed, when I set up the utility and pasted above code (picture below), I encountered deadlocks exactly as in the production.



I turned on trace flag 1222 which writes detailed deadlock information to the error log to see what actually is going on.
dbcc traceon(1222, -1)
The -1 parameter indicates that you want to set the status flag globally for all connections to the server. If it is not provided, the flag applies only to the current connection.

After I had enabled the flag, I pasted query 1 to the SQLQueryStress util. I configured the tool to run the code 40 times in two threads. During the run errors were indicated and when I clicked the ellipsis (...) button I could verify the errors were indeed deadlocks. So I opened the SQL Server error log and got the following output from it:

deadlock-list
deadlock victim=process1cdf68c58
process-list
process id=process79a72e8 taskpriority=0 logused=0 waitresource=OBJECT: 15:1622453004:0  waittime=1812 ownerId=4097956687 transactionname=user_transaction lasttranstarted=2009-12-21T13:56:52.703 XDES=0x8031d0c0 lockMode=X schedulerid=1 kpid=1856 status=suspended spid=117 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-12-21T13:56:52.703 lastbatchcompleted=2009-12-21T13:56:52.563 clientapp=.Net SqlClient Data Provider hostname=MYHOST hostpid=16244 loginname=testlogin isolationlevel=read committed (2) xactid=4097956687 currentdb=15 lockTimeout=4294967295 clientoption1=671096864 clientoption2=128056
executionStack
frame procname=adhoc line=7 stmtstart=336 stmtend=480 sqlhandle=0x020000000144d3314c9019b950c94aa9630febde7d049222
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
inputbuf
declare @newFrom datetime<c/> @newTo datetime<c/> @oldFrom datetime
set @newFrom = '20090115'
set @newTo = '20090114'
begin tran
--#########        original approach
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
while @oldFrom < @newFrom
begin
set @oldFrom = dateadd(day<c/> 1<c/> @oldFrom)
update dbo.tDateTest set FromDate = @newFrom<c/> ToDate = @newTo
#NAME?
end
commit
select * from dbo.tDateTest
process id=process1cdf68c58 taskpriority=0 logused=0 waitresource=OBJECT: 15:1622453004:0  waittime=1828 ownerId=4097956685 transactionname=user_transaction lasttranstarted=2009-12-21T13:56:52.703 XDES=0x35527f6d0 lockMode=X schedulerid=3 kpid=3324 status=suspended spid=118 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2009-12-21T13:56:52.703 lastbatchcompleted=2009-12-21T13:56:52.533 clientapp=.Net SqlClient Data Provider hostname=MYHOST hostpid=16244 loginname=testlogin isolationlevel=read committed (2) xactid=4097956685 currentdb=15 lockTimeout=4294967295 clientoption1=671096864 clientoption2=128056
executionStack
frame procname=adhoc line=7 stmtstart=336 stmtend=480 sqlhandle=0x020000000144d3314c9019b950c94aa9630febde7d049222
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
inputbuf
declare @newFrom datetime<c/> @newTo datetime<c/> @oldFrom datetime
set @newFrom = '20090115'
set @newTo = '20090114'
begin tran
--#########        original approach
select @oldFrom = FromDate from dbo.tDateTest with(updlock<c/> holdlock)
while @oldFrom < @newFrom
begin
set @oldFrom = dateadd(day<c/> 1<c/> @oldFrom)
update dbo.tDateTest set FromDate = @newFrom<c/> ToDate = @newTo
#NAME?
end
commit
select * from dbo.tDateTest
resource-list
objectlock lockPartition=0 objid=1622453004 subresource=FULL dbid=15 objectname=Database_Perf.dbo.tDateTest id=lockea708480 mode=IX associatedObjectId=1622453004
owner-list
owner id=process79a72e8 mode=IX
owner id=process1cdf68c58 mode=IX
waiter-list
waiter id=process1cdf68c58 mode=X requestType=convert
waiter id=process79a72e8 mode=X requestType=convert



There a couple of facts to be noticed here. As you see in the second line od the output, process process1cdf68c58 has been chosen as victim of the deadlock. The lines beginning with >process id< contain also information about what object was deadlocked. This is the waitresource part: =OBJECT: 15:1622453004:0. The identifier tells you that the deadlocked resource is an object (table), in database with db_id() 15, then there is id of the object within this database and eventually id of the index on the table. The index id 0 indicates that this is heap - either there there is no clustered index defined or it was not chosen to run the query.
By the end of the output in the resource-list section you can also see the name of the table and the types of locks that were in place before the deadlock occured. As you see, both processes (owner id lines) had intent exclusive (IX) lock on the table and both of them decided to convert the lockx to exclusive (X)) and since these locks (IX and X) are not compatible, deadlock occured and one of the processes was killed. There is a MSDN article here with overview of the locks compatibility.

To say the least, I was pretty surprised. I never thought this would occur, although when I think of this, the deadlocking is pretty reasonable. I just made (uneducated) assumption, that when there is only one row in a table, the enginge will get straight to this row and (updlock, holdlock) will work as planned. I was wrong as it turns out.

Anyway, I needed to fix the problem.

1. The first idea was to replace the updlock with tablockx and this resolved the deadlocking issue.
Note that the TABLOCKX hint causes the engine to take an exclusive lock on the whole table. Use it with caution, as if you have really concurrent environment, this hint can affect performance seriously. In my case, this is not an issue, so I am OK with this approach. Here is the query that I used to resolve the problem:

declare @newFrom datetime, @newTo datetime, @oldFrom datetime

set @newFrom = '20090115'

set @newTo = '20090114'


begin tran

--############ tablockx approach

select @oldFrom = FromDate from dbo.tDateTest with(tablockx, holdlock)


while @oldFrom < @newFrom

begin

set @oldFrom = dateadd(day, 1, @oldFrom)

update dbo.tDateTest set FromDate = @newFrom, ToDate = @newTo

--some other processing here..

end

commit

select * from dbo.tDateTest



This would indicate that updlock has different path of execution than tablockx and there is transition state from intent lock to exclusive lock that is prone to deadlock issues if exclusive table lock is not explicitly requested. Please note that while I accepted this approach as the solution, it may not be the best idea if you have heavily accessed table with many rows.


Since I was at it, I decided to try several various approaches and see if they work. Here are some approaches I tried:
2. I thought that maybe deadlocking is caused by query without where clause narrowing the set to one row (yeah, there is one row there anyway, but maybe optimizer chooses different optimization technique?). I changed the select statement to the following:

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)

where FromDate = '20090102'



Obviously, because there is no index on the table, the select statement produces table scan in execution plan, so there is not a big change to the plan produced by query without where clause. The deadlocks are still there, too.

3. Next idea I had was to create a nonclustered index on the table and use it to select rows from it.

--add nonclustered index

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

FromDate datetime not null,

ToDate datetime not null

)

go

create index ix1 on dbo.tDateTest(FromDate)

go

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')

go



Now, this query

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)



uses index scan because optimizer chooses to use index instead of table to retrieve all required data. What is interesting - there are no deadlocks here. So it looks like index scan is somewhat safer in this case than table scan, in terms of deadlocking.

4. I added new variable to the script and changed the query so it has to use in some way a table.
The query looks now like this:

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock)


Because there is no where clause, the optimizer chose to use table scan and bypass the index altogether. This takes us to the square one and the deadlocking issue.

5. I wondered what would happen if I enforced use of the index in the query:

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock, index=ix1)



This query produces plan that contains index scan and RID lookup on the table. And there are no deadlocks interestingly.

6. So, next approach is to use index and search for the only row in the table using WHERE clause.

select @oldFrom = FromDate, @oldTo = ToDate from dbo.tDateTest with(updlock, holdlock, index=ix1) where FromDate = '20090102'



Since the optimizer goes directly to the searched row, the locking seems to be less prone to deadlocking.

7. The last change I checked was to modify the table and add surrogate clustered primary key:

--change definition of the table - add clustered key

if object_id('dbo.tDateTest') is not null

drop table dbo.tDateTest

go

create table dbo.tDateTest

(

PK int identity(1, 1) primary key clustered,

FromDate datetime not null,

ToDate datetime not null

)

go

insert dbo.tDateTest(FromDate, ToDate) values('20090102', '20090101')


go



Now, the query was modified to seek for the row with PK = 1,

select @oldFrom = FromDate from dbo.tDateTest with(updlock, holdlock)

where PK = 1



Since this is a Clustered Index seek in execution plan, there is no deadlocking as well.


In summary, this is another example that it is better to have a clustered index than not, even on small single-row tables. It is surprising that table-scan is more susceptible to deadlocks than index scan as idea number 5 showed.
I suppose these findings may be inaccurate if there are more rows in the table or in the index. I haven't tested such scenario yet, but definitely SQLStressQuery utility is coming to my toolbelt.

It is worth to mention, that there is another way of serializing execution of your code. There is a, somewhat not well known procedure sp_getapplock which, together with sp_releaseapplock allows for creating critical section around your code withouth applying locks to the table. I checked this approach as well and obviously it prevents deadlocks from occur. This is pretty interesting option in certain situations in my opinion.


The code I used for testing is available here: testing updlock, holdlock hint.zip, and the SQLStressQuery utility is here, please have your go if you find it interesting and have some time to spend.
More Posts Next page »