Alexander Kuznetsov

Store your configuration settings as a hierarchy in a database.

Alexander Kuznetsov — Sun, 24 Aug 2008 21:37:00 GMT

Clearly there are many well known and proven ways to store and maintain the configuration settings of your applications. However, where your requirements have outgrown the ‘off the shelf’ methods, such as Active Directory, the relational database can...(read more)

Performance of inserts and IGNORE_DUP_KEY

Alexander Kuznetsov — Thu, 31 Jul 2008 02:39:00 GMT

If your data has a small percentage of duplicates, then IGNORE_DUP_KEY may speed up your inserts. For larger amounts of duplicates, IGNORE_DUP_KEY may slow them down significantly. I set up two tables, stripping down all the irrelevant details, as follows:...(read more)

CPU Overhead for Higher Isolation Levels

Alexander Kuznetsov — Sun, 13 Jul 2008 01:19:00 GMT

As we know, higher isolation levels acquire more locks and as such they use more CPU, but is the increase significant? In some case the increase is very noticeable, and here are a few benchmarks illustrating my point. Prerequisites: I created the following...(read more)

Congratulations, Denis!

Alexander Kuznetsov — Tue, 01 Jul 2008 17:37:00 GMT

Fun with DBCC CHECKIDENT

Alexander Kuznetsov — Thu, 26 Jun 2008 21:51:00 GMT

<Denis Gobo’s mode on> Suppose you have an empty table: SELECT COUNT (*) FROM SampleTable --- 0 What would be the result of the following query: DBCC CHECKIDENT ( 'SampleTable' , RESEED , 1 ) INSERT SampleTable ( j ) SELECT 1 SELECT SCOPE_IDENTITY...(read more)

Database Unit Testing: Impersonation

Alexander Kuznetsov — Wed, 18 Jun 2008 16:19:00 GMT

When you run NUnit/C# unit tests against your local instance, you are a dbo. As such, you are not getting any errors caused by missing permissions. However, you can impersonate another user, and run your unit tests in the context of that other user. The...(read more)

Unit Testing Tips: Reproduce a Lock Timeout

Alexander Kuznetsov — Fri, 30 May 2008 22:24:00 GMT

Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock...(read more)

Reuse Your Code with Table-Valued UDFs

Alexander Kuznetsov — Fri, 23 May 2008 17:26:00 GMT

Suppose you are using SQL Server 2005, and you want to truncate your DATETIME value, leaving only date part of it, as follows:

DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101')

Suppose you want to reuse this code and wrap it in a scalar UDF:

CREATE FUNCTION dbo.TruncatedDateTime(@d DATETIME)

RETURNS DATETIME

BEGIN

RETURN DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101')

END

It is well known that scalar UDFs may be slow, but how slow is this particular one, as compared to the expression which it wraps? That’s quite easy to figure out. I populated a table with one million dates from one of my production tables, as follows:

SELECT TOP 1000000 LastModifiedDate INTO dates

FROM data.MyProductionTable

And I ran these two queries:

SELECT COUNT(*) FROM(

SELECT DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101') AS d FROM dates

) t WHERE d>'20220203'

(snip)

SQL Server Execution Times:

CPU time = 343 ms, elapsed time = 347 ms.

SELECT COUNT(*) FROM(

SELECT dbo.TruncatedDateTime(LastModifiedDate) AS d FROM Dates

) t WHERE d>'20220203'

(snip)

SQL Server Execution Times:

CPU time = 26109 ms, elapsed time = 26651 ms.

Note that the UDF slowed the query down almost one thousand times, as expected. However, let us consider a better alternative – a table-valued UDF, as follows:

CREATE FUNCTION dbo.TableValuedTruncatedDateTime(@d DATETIME)

RETURNS TABLE

RETURN( SELECT DATEADD(day, DATEDIFF(day, '19010101', @d), '19010101') AS TruncatedDateTime);

SELECT COUNT(*) FROM(

SELECT TruncatedDateTime AS d FROM Dates CROSS APPLY dbo.TableValuedTruncatedDateTime(Dates.LastModifiedDate)

) t WHERE d>'20220203'

(snip)

SQL Server Execution Times:

CPU time = 344 ms, elapsed time = 347 ms.

Note that the query using the table-valued UDF runs just as fast as the original one. As you have seen, CROSS APPLY allowed you to reuse your date-truncating code without taking a performance hit.

A Great Advice on Coding Predicates in Outer Joins

Alexander Kuznetsov — Thu, 15 May 2008 17:19:00 GMT

Yet another very common error is when someone puts a wrong condition in a WHERE clause and that essentially renders an outer join into an inner join. A great advice on coding predicates in outer joins comes from a DB2 expert Terry Purcell.

"Meet the experts: Terry Purcell on coding predicates in outer joins: A comparison of simple outer join constructs"

http://www.ibm.com/developerworks/db2/library/techarticle/purcell/0112purcell.html

Enjoy!

Be Aware of These Loopholes in Your Referential Integrity

Alexander Kuznetsov — Tue, 13 May 2008 22:17:00 GMT

I am going to discuss several potential problems with referential integrity, because they keep coming up in the newsgroups.

Parent and Child Tables Are in Different Databases.

Although you cannot use a foreign key in this situation, there are workarounds – you can use either triggers or UDFs wrapped in check constraints. Either way, your data integrity is not completely watertight: if the database with your parent table crashes and you restore it from a backup, you may easily end up with orphans.

Parent-Child Relationship Is Enforced by Triggers.

There are quite a few situations when triggers do not fire, such as:

· A table is dropped.

· A table is truncated.

· Settings for nested and/or recursive triggers prevent a trigger from firing.

Also a trigger may be just incorrect. Either way, you may end up with orphans in your database.

Parent-Child Relationship Is Enforced by UDFs Wrapped in Check Constraints.

Such “constraints” do not prevent from deleting parent rows. Again, you may end up with orphans in your database.

In most cases the easiest and simplest way to enforce a parent-child relationship is to use a built-in tool – do not reinvent the wheel, have both tables in one database and use a foreign key.

When Index Covering Prevents Deadlocks

Alexander Kuznetsov — Sun, 04 May 2008 03:08:00 GMT

Suppose that a select has just navigated all the way down a non-clustered index and is trying to lookup a row in the clustered index. Suppose that at the same time an update has just modified that row and tries to modify the corresponding entry in the non-clustered index. This situation will end up in a deadlock. However, if the non-clustered index covers the select query, once the select has navigated to the leaf level of that index, there is no need to perform a bookmark lookup. As such, this select will not embrace in a deadlock. (Note that situations like this are intermittent – they happen from time to time, but not every time you run your select.

To reproduce this quite common situation, I created the following table and index:

CREATE TABLE [dbo].[Tickets](

[TicketId] [int] NOT NULL,

[AssignedTo] [int] NOT NULL,

[Priority] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Status] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[LotsOfComments] [char](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED

(

[TicketId] ASC

)

) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NCI_Tickets_AssignedTo] ON [dbo].[Tickets]

(

[AssignedTo] ASC

)

I populated the table with 10K rows, and simultaneously ran the following update:

UPDATE Data.Tickets SET AssignedTo = 1 WHERE TicketId = 1

SET NOCOUNT ON

DECLARE @i INT, @Status VARCHAR(10), @TicketId INT

SET @i = 0

WHILE @i < 100000 BEGIN

SELECT @i = @i + 1

-- because originally AssignedTo = 1 for this row,

-- it will toggle 0,1,0,1,0,1,0,1

UPDATE Data.Tickets SET AssignedTo = 1 - AssignedTo WHERE TicketId = 1

END

And I ran the following select in another tab of SSMS:

DECLARE @i INT, @Status VARCHAR(10), @AssignedTo INT

SET @i = 0

WHILE @i < 1000000 BEGIN

SELECT @i = @i + 1, @Status = Status

FROM Data.Tickets WHERE AssignedTo = 1

END

Every time I run these two scripts, the select consistently becomes a deadlock victim in just a few seconds. Let us use index covering:

DROP INDEX [Data].[Tickets].[Tickets_AssignedTo]

CREATE NONCLUSTERED INDEX [Tickets_AssignedTo] ON [Data].[Tickets]

(

[AssignedTo], Status

)

Now both scripts complete without deadlocks, although they usually run simultaneously for more than 30 seconds on my laptop.

As you have seen, in this particular case index covering completely eliminated deadlocks. Of course, real life situations are usually much more complex than this simplistic one.

PS As Uri pointed out, it is necessary to compare the outcome for different isolation levels. So, if I up the isolation level to repeatable read or to serializable, I am still getting deadlocks. Uri was right, the TABLOCK hint prevents deadlocks but slows the execution down significantly. Also there are no deadlocks under snapshot isolation - we could guess that much ;).

Not all UDFs are bad for performance.

Alexander Kuznetsov — Mon, 21 Apr 2008 22:43:00 GMT

There is a popular misconception that UDFs have adverse effect on performance. As a blanket statement, this is simply not true. In fact, inline table-valued UDFs are actually macros – the optimizer is very well capable rewriting queries involving them as well as optimizing them. However, scalar UDFs are usually very slow. I will provide a short example.

Prerequisites

Here is the script to create and populate the tables:

CREATE TABLE States(Code CHAR(2), [Name] VARCHAR(40), CONSTRAINT PK_States PRIMARY KEY(Code))

INSERT States(Code, [Name]) VALUES('IL', 'Illinois')

INSERT States(Code, [Name]) VALUES('WI', 'Wisconsin')

INSERT States(Code, [Name]) VALUES('IA', 'Iowa')

INSERT States(Code, [Name]) VALUES('IN', 'Indiana')

INSERT States(Code, [Name]) VALUES('MI', 'Michigan')

CREATE TABLE Observations(ID INT NOT NULL, StateCode CHAR(2), CONSTRAINT PK_Observations PRIMARY KEY(ID))

SET NOCOUNT ON

DECLARE @i INT

SET @i=0

WHILE @i<100000 BEGIN

SET @i = @i + 1

INSERT Observations(ID, StateCode)

SELECT @i, CASE WHEN @i % 5 = 0 THEN 'IL'

WHEN @i % 5 = 1 THEN 'IA'

WHEN @i % 5 = 2 THEN 'WI'

WHEN @i % 5 = 3 THEN 'IA'

WHEN @i % 5 = 4 THEN 'MI'

END

When a query involving a UDF is rewritten as an outer join.

Consider the following query:

SELECT o.ID, s.[name] AS StateName

INTO dbo.ObservationsWithStateNames_Join

FROM dbo.Observations o LEFT OUTER JOIN dbo.States s ON o.StateCode = s.Code

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Observations'. Scan count 1, logical reads 188, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'States'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 187 ms, elapsed time = 188 ms.

And compare it to a query involving an inline table valued UDF:

CREATE FUNCTION dbo.GetStateName_Inline(@StateCode CHAR(2))

RETURNS TABLE

RETURN(SELECT [Name] FROM dbo.States WHERE Code = @StateCode);

SELECT ID, (SELECT [name] FROM dbo.GetStateName_Inline(StateCode)) AS StateName

INTO dbo.ObservationsWithStateNames_Inline

FROM dbo.Observations

Both its execution plan and its execution costs are the same – the optimizer has rewritten it as an outer join. Don’t underestimate the power of the optimizer!

A query involving a scalar UDF is much slower.

Here is a scalar UDF:

CREATE FUNCTION dbo.GetStateName(@StateCode CHAR(2))

RETURNS VARCHAR(40)

BEGIN

DECLARE @ret VARCHAR(40)

SET @ret = (SELECT [Name] FROM dbo.States WHERE Code = @StateCode)

RETURN @ret

END

Clearly the query using this UDF provides the same results but it has a different execution plan and it is dramatically slower:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 3 ms.

Table 'Worktable'. Scan count 1, logical reads 202930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Observations'. Scan count 1, logical reads 188, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 11890 ms, elapsed time = 38585 ms.

As you have seen, the optimizer can rewrite and optimize queries involving inline table valued UDFs. On the other hand, queries involving scalar UDFs are not rewritten by the optimizer – the execution of the last query includes one function call per row, which is very slow.

Also thank you Peter and Adam for setting me up as a blogger on this wonderful site!