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.

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:

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:

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.

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.

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.

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

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

execAudit.pCreateAuditTrigger'Sales','SalesTerritory'

and to drop the trigger, call

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

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.

/**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:

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*fromAudit.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.

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.

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:

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:

createtriggertrigCol1ondbo.tManyColumns forinsert,update,deleteasifUPDATE(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:

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

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:

selectSUBSTRING(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.

selectSUBSTRING(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:

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:

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:

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

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

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

select
a,
b from
Table1

open
csr

fetchnextfrom
csr into
@a,
@b

while@@fetch_status<>-1

begin

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

fetchnextfrom
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 innerjoin
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:

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

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.

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

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.

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.

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.

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.

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

--batch
1

declare
@batchsize int

set
@batchsize =
100

setrowcount
@batchsize

while
1=1

begin

delete
t1 where
a <
50000

if@@rowcount=
0

break;

end

setrowcount
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

deletetop(@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

setrowcount
@batchsize

delete
t1 where
a <
50000

setrowcount
0

--query
2

deletetop(@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:

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.

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) :).

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:

createtable
t101

(

a
int,

b
char(10)

)

go

insert
t101(a,
b)

select
1,''

union
allselect
2,''

union
allselect
3,''

union
allselect
4,''

union
allselect
5,''

union
allselect
6,''

union
allselect
7,''

union
allselect
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.

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.

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 =(selecttop
1 someval from
t102 where
a =
a orderbynewid())

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.

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:

ifobject_id('dbo.tDateTest')isnotnull

droptable
dbo.tDateTest

go

createtable
dbo.tDateTest

(

FromDate
datetimenotnull,

ToDate
datetimenotnull

)

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'

begintran

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

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'

begintran

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

ifobject_id('dbo.tDateTest')isnotnull

droptable
dbo.tDateTest

go

createtable
dbo.tDateTest

(

FromDate
datetimenotnull,

ToDate
datetimenotnull

)

go

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

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

ifobject_id('dbo.tDateTest')isnotnull

droptable
dbo.tDateTest

go

createtable
dbo.tDateTest

(

PK
intidentity(1,
1)primarykeyclustered,

FromDate
datetimenotnull,

ToDate
datetimenotnull

)

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.