Michael Zilberstein SQL Server Blog

So long and thanks for all the fish

mz1313 — Sat, 01 Mar 2008 13:16:00 GMT

This is my last post on SQLJunkies. I'm moving to sqlblog.com - welcome to my new blog. It's empty meanwhile but not for long. Since nice part of my RSS feeds subscription belongs to sqlblog.com bloggers (Kalen Delaney, Adam Machanic, Denis Gobo, Linchi Shea etc), I was more than happy to receive an offer to move there. So... see you in my new blog.

Cursor

mz1313 — Sat, 02 Feb 2008 17:59:00 GMT

Cursors are powerful - for good and for bad. In hands of a programmer that doesn't understand it cursors can be painful. Real-life example of cursor that never returns....(read more)

RIP: Ken Henderson

mz1313 — Thu, 31 Jan 2008 11:34:00 GMT

RIP: Ken Henderson Ken Henderson passed away last Sunday...(read more)

Storage engine and locking - nondeterministic behavior

mz1313 — Wed, 25 Jul 2007 09:43:00 GMT

This post (or maybe series of 2 posts) is inspired by short discussion in one of the Israely SQL Server user group meetings; Ami Levin - SQL Server MVP - mentioned that when you query first row of the page, SQL Server goes not only to the page itself but also to the previous page because the same value can be in the end of the previous page also. Ami said that he himself had found it together with Itizk Ben-Gan after hot discussion that almost ended in a fight :-).

Background for the beginning. The problem occurs when SQL Server uses index seek. It starts from non-leaf levels of an index. Non-leaf level row contains PageID and indexed column values of the first row on the leaf-level page (I'm talking about the lowest non-leaf level row). Here is example of the data on such page (data refers to the demo below; c1 and c2 are indexed columns):

PageID	Col1	Col2
171	476	476
173	952	952
175	1428	1428

It means that Col1 value in the first row of page 171 is 476, in the first row of page 173 is 952 etc. But nobody can garantee that 952 can't be the value of Col1 also in the last row of page 171. Or in some number of last rows - nobody garanteed that index on Col1 is unique. Although all this explanation looked pretty logical, I still found it hard to believe that SQL Server behaviour can be such undeterministic - same query on the same data sometimes locks 1 row and sometimes more. So I decided to check. I created table with 2 integer columns c1 and c2, both are primary key though in fact c1 is also unique.

IF EXISTS( SELECT 1 FROM sys.databases WHERE [name] = 'LocksDemo' )
DROP DATABASE LocksDemo
GO
CREATE DATABASE LocksDemo
GO
USE [LocksDemo]
GO

CREATE TABLE Demo( c1 INT NOT NULL, c2 INT NOT NULL )
GO
ALTER TABLE Demo ADD CONSTRAINT PK_Demo PRIMARY KEY CLUSTERED (c1, c2)
GO

set nocount on
begin
declare @i int
set @i = 0
while @i < 2000
      begin
      insert into Demo values (@i, @i)
      set @i = @i + 1
      end
end
GO

My DBID is 7, objectID of Demo table is 2137058649. First of all let's see what pages does Demo table occupy.
DBCC TRACEON(3604); GO;
DBCC IND(7, 2137058649, -1);
The table contains one IAM page, 1 non-leaf level index page and 5 leaf-level clustered index pages (data pages actually).

BTW, question for storage engine geeks: data page in my example contains 476 records. Record lenght is 15 byte (for the explanation of what else besides data each row contains see this great book by Kalen Delaney). FLOOR(8060 / 15) = 537. So I counted 537 should-be-rows but you can check DBCC PAGE yourself - page contains only 476 rows. So where is the space for another 61 rows? Hint: fillfactor is not the answer.

In my case first row of page 171 (second leaf-level page) starts from row (476; 476). Le't start from second row in order to see IO and locks:

set statistics io on
begin tran
      select c1, c2 from Demo with(rowlock, xlock) where c1 = 477
      exec sp_lock
commit tran

Since shared lock on row is released immidiately after row had been read, I lock it exclusively using xlock hint in order to be able to see the lock using sp_lock. The result is as expected: 2 reads (one non-leaf level page and one leaf-level), 1 exclusive key lock (plus intent exclusive on page and on table). Now let's query first row of the page:

set statistics io on
begin tran
      select c1, c2 from Demo with(rowlock, xlock) where c1 = 476
      exec sp_lock
commit tran

Now we have 3 reads, still 1 key lock but 2 intent exclusive locks on pages. So it really goes to previous page. OK, let's try to lock previous page and see what happens.

Session 1 (SPID 53)	Session 2 (SPID 55)
begin tran select c1, c2 from Demo with(paglock, xlock) where c1 = 470
	select c1, c2 from Demo where c1 = 476

It is really waiting for the first transaction to release the lock on page that doesn't contain any rows that answer to the query criteria! Here is what sp_lock shows:

SPID	Type	Resource	Mode	Status
53	PAG	1:169	X	GRANT
55	PAG	1:169	IS	WAIT

Just wanted to remind that the relevant row is on page 171, not on 169. Now let's try to reverse scan order of session 2 and see what does it lock:

Session 1 (SPID 53)	Session 2 (SPID 55)
begin tran select c1, c2 from Demo with(paglock, xlock) where c1 = 470
	select c1, c2 from Demo where c1 = 476 order by c1 desc, c2 desc

Results of sp_lock:

SPID	Type	Resource	Mode	Status
53	PAG	1:169	X	GRANT
55	PAG	1:169	IX	WAIT
55	PAG	1:171	IX	GRANT

Session 2 has read the relevant row on page 171 and now waits for session 1 to release lock on page 169 in order to check whether it also contains relevant rows.

BTW, all this doesn't happen when you specify both c1 and c2 in search criteria:

select c1, c2 from Demo where c1 = 476 and c2 = 476

Why? Because SQL Server knows that c1 and c2 together are unique (they're primary key, remember?). So if it is first row on page 171, there can't be another row with the same value on any other page. On the other hand, adding unique index on c1 doesn't help initial query - it still goes to previous page - but somehow it reverses scan order of session 2 from backward to forward. Don't have a clue, why it is implemented this way.

To end with - two funny consequences of all this mess. Do you think that select of 1 row that uses index seek in read committed isolation level can't cause deadlock? You're wrong!

Session 1 (SPID 53)	Session 2 (SPID 55)
begin tran select c1, c2 from Demo with(paglock, xlock) where c1 = 470
	select c1, c2 from Demo where c1 = 476 order by c1 desc, c2 desc
select c1, c2 from Demo with(rowlock, xlock) where c1 = 476 and c2 = 476

Session 2 selected only 1 row and it was enough for deadlock. Session 2 was also the one to survive while session 1 had been chosen as deadlock victim.

Last trick: I add column c3 to the table and create unique index on it:

CREATE TABLE Demo( c1 INT NOT NULL, c2 INT NOT NULL, c3 INT NOT NULL )
GO
ALTER TABLE Demo ADD CONSTRAINT PK_Demo PRIMARY KEY CLUSTERED (c1, c2)
GO
CREATE UNIQUE INDEX IX_Demo_c3 ON Demo(c3)
GO

set nocount on
begin
declare @i int
set @i = 0
while @i < 2000
      begin
      insert into Demo values (@i, @i, @i)
      set @i = @i + 1
      end
end
GO

Now last record in the first page contains values (384, 384, 384), first row of the second page contains (385, 385, 385). And don't forget, c3 is unique.

Session 1 (SPID 53)	Session 2 (SPID 55)
begin tran select c1, c2 from Demo with(paglock, xlock) where c1 = 470
	select c1, c2 from Demo where c1 = 476 order by c1 desc, c2 desc
delete from Demo where c1 = 385 and c2 = 385 update Demo set c1 = 385, c3 = 385 where c1 = 384 and c2 = 384 commit tran

Resultset of session 2:

c1	c2	c3
385	385	385
385	384	385

Are you really, really sure c3 is unique?

I hope to write at least one more post on this theme - next time I will play around with different isolation levels.

*** Answer for the question about missing rows on page: remember, in the end of the page there is an offset array - for each row it contains an offset from the page start. Since we have pretty many rows on page, offset array also requires significant space.

Linked server and timestamp column

mz1313 — Tue, 20 Mar 2007 09:52:00 GMT

Something that looks like bug. Luckily, with workaround.
Let's say, you have MS SQL 2K on Server1 (I have SP4 - didn't check it on other configurations). Steps to reproduce the bug:
1) Create table with timestamp column (or add timestamp column to existing table) and populate with some data.

CREATE TABLE TSTest (c1 INT, TS TIMESTAMP NOT NULL)
GO
INSERT INTO TSTest (c1)
SELECT TOP 100 [id]
FROM sysobjects
GO

2) From another server "Server2" with SQL2K or SQL2K5 create linked server to Server1 using the following provider:

EXEC master.dbo.sp_addlinkedserver @server = N'Server1', @srvproduct=N'OLE DB Provider for ODBC', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=Server1;'
GO

Configure it to use the appropriate user (the problem is not security, so you can give it even sa).

3) Try to query the table via linked server.

SELECT TOP 10 * FROM Server1.DBA.dbo.TSTest
GO

Error message returns:
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "Server1" supplied inconsistent metadata for a column. The column "TS" (compile-time ordinal 2) of object ""DBA"."dbo"."TSTest"" was reported to have a "DBCOLUMNFLAGS_ISROWVER" of 0 at compile time and 512 at run time.

The message above is from SQL2K5. SQL 2K returns less specific message - in order to see the detailed one trace flag 7300 should be turned on. Needless to say, no DML operation had been performed on the table during the query via linked server. So timestamp column values hadn't changed.

Workaround:

1) Use another provider (not MSDASQL). In SQL2K5, Native Client (SQLNCLI) works fine though it has other problems: from time to time Distributed Transactions Coordinator unexpectedly aborts my transaction - out of the blue without any particular reason I can put my finger on. Not sure, I'll investigate it further because of (2):

2) Use OPENQUERY. It just works.

SELECT * FROM OPENQUERY(Server1, 'SELECT TOP 10 * FROM DBA.dbo.TSTest')

Saving Auto Recovery Information

mz1313 — Fri, 16 Feb 2007 16:53:00 GMT

If you have bad luck of working with Management Studio via VPN connection (maybe other slow connections as well - I experience it only via VPN), I'm sure that you're familiar with this situation: your screen is frozen, you stare on it with angry look on your face and see just a small line in a bottom left corner of your Management Studio saying: "Saving Auto Recovery Information". Last time when I worked from home with my version script (about 50K rows), the "Saving..." process had been taking about 5 minutes every time. So, here is the way to get rid of it. Go to Registry and change to 0 (zero) value of the following key:
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover\AutoRecover Enabled

TABLOCKX

mz1313 — Wed, 10 Jan 2007 01:00:00 GMT

BOL states:
TABLOCKX
Specifies that an exclusive lock is taken on the table. If HOLDOCK is also specified, the lock is held until the transaction completes

So it's not stated explicitly that without HOLDLOCK the lock is held until end-of-statement. But as far as my english - or logic - goes, I understood it that way. Otherwise - why to mention HOLDLOCK if TABLOCKX itself does the same? Let's check. Before opening transaction, I'll make some preparations.

CREATE DATABASE Demo
GO

USE [Demo]
GO

CREATE TABLE TabLockDemo( col1 INT, col2 VARCHAR(8000) )
GO

INSERT INTO TabLockDemo
VALUES(1, 'a')
GO

SELECT OBJECT_ID('TabLockDemo')
-- 2073058421

SELECT @@SPID
-- 52

Now let's set isolation level to read committed, open transaction and select data from a table using TABLOCKX hint:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT TOP 1 * FROM TabLockDemo WITH(TABLOCKX)

Now, in order to be sure that the statement ended, I would even execute another statement:

SELECT TOP 1 * FROM sys.indexes

Now let's see, what sp_lock tells us... The table is locked! You don't believe? Open another session and try to execute SELECT * FROM TabLockDemo. The reason for this behaviour is pretty obvious. Exclusive lock is usually being used for DML operations - not for SELECT. But if we open transaction, execute some INSERT statement and hold the lock only for the duration of a statement, then we can now read this data from another session - the table isn't locked. Afterwards we rollback the first session and so we've got classical dirty read! Thus exclusive lock is always being held until the end of transaction - without any need of HOLDLOCK. BOL is at least misleading.

Insert data via linked server - SQL2005 behaves differently

mz1313 — Tue, 26 Sep 2006 15:51:00 GMT

Something I ran into just recently. I have pretty big - 7Gb - table on Server1 that needs to be transfered to Server2 via linked server from Server2 to Server1.

INSERT INTO MyTable
SELECT * FROM Server1.MyDB.dbo.MyTable

I have a simple select that gives me datafile data for an active db:

For SQL Server 2000 (BTW, works in SQL2005 too):

SELECT
        filegroup_name(groupid) as Filegroup,
        [name],
        [filename],
        [size]/128.0 as TotalSizeInMB,
        FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,
        ([size] - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB
FROM sysfiles

For SQL Server 2005:

SELECT
        [name],
        [physical_name],
        [size]/128.0 as TotalSizeInMB,
        FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,
        ([size] - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB,
        CASE
            WHEN is_percent_growth = 1 THEN CAST(growth as VARCHAR(8)) + '%'
            ELSE CAST(FLOOR(growth/128.0) as VARCHAR(8)) + 'MB'
        END as growth,
        [is_read_only]
FROM sys.database_files
ORDER BY [Name] ASC

When I execute my query in SQL Server 2000 database, datafile and log in the MyTable immidiately start to grow. When I execute it on SQL Server 2005 db (while Server1 is SQL2000 - I didn't test it on two SQL2005 servers but I would be surprised if that matters), datafiles in my db don't grow. What grows instead is tempdb! I guess it has something to do with version stores though snapshot isolation is turned off.

Update: After some thought seems that I begin to understand the issue. In SQL 2000 deleted and inserted tables (which are used in triggers) aren't actually tables but are derived from transaction log. In SQL 2005 on the other hand deleted and inserted are managed by tempDB via version store mechanism. So TempDB grows as a substitute to transaction log.

Kalen Delaney started blogging

mz1313 — Tue, 12 Sep 2006 05:16:00 GMT

http://sqlblog.com/blogs/kalen_delaney/default.aspx

DBCC CHECKDB

mz1313 — Wed, 14 Jun 2006 07:07:00 GMT

It appears that DBCC CHECKDB can cause performance degradation immidiately after it finishes. KB article is here. In short, DBCC CHECKDB clears the whole procedure cache. Thus you can't reuse already compiled execution plans - they are just not there.

Another issue with DBCC CHECKDB that concerns only SQL Server 2005. In order to make DBCC CHECKDB run on transactionally consistent database, Yukon uses snapshot. It's hidden, so you can't tune it or even place at specific disk. The problem can occur when you have:
1. Large DB (so that DBCC CHECKDB takes significant time to complete)
2. Many DML operations at different tables (thus snapshot size will grow fast)
3. Low free space on disk.
So be careful - if your database usage patterns are similar to (1) and (2), verify that you have enough free space on disk prior to executing DBCC CHECKDB.
BTW, In SQL Server 2000 that's not the case. In order to provide transactional consistency it uses some clever mechanism that knows to integrate with transaction log.
Here is the detailed explanation (1st part of the future serie) about how DBCC CHECKDB works written by the guy that programmed it.
BTW, here you can ask him to add more issues to the scope of the serie.

Auto-indexing solution for SQL Server 2005 and other stuff

mz1313 — Mon, 05 Jun 2006 07:46:00 GMT

Looks like really helpful tool from SQL Query Optimization team at Microsoft. Auto-indexing solution based on Missing Index DMVs - now works in recomendation mode but can be easily transformed to a fully automated tool. It also knows to clean up the mess - not only to create missing indexes but also to drop unnecessary ones. Though I wouldn't recommend to execute it in fully automated mode - at least not for dropping indexes. Take the tool's recommendations and check them first.

Another promising tool: Visual Studio Team Edition for Database Professionals is due to be released at June 9th. Visual Studio + Team System + DB source control and, listen carefully, schema control - all in one tool. Hopefully also integrated together.

Server trace enabled by default in SQL Server 2005

mz1313 — Tue, 09 May 2006 07:04:00 GMT

Is your SQL Server 2005 surprisingly slow? It maybe because server trace runs on your server though you never started it! It comes out that 'default trace enabled' setting is turned on (1) by default during SQL Server 2005 installation. In order to verify it you can execute:

select * from sys.configurations where configuration_id = 1568

In order to check whether you have active traces runnig, execute:

select * from ::fn_trace_getinfo(0)

If you have active traces (you'll see trace IDs and paths to .trc files), you can stop them in the following way: for each active trace ID execute (stop trace at first step and clear trace definition from the server at a second):

exec sp_trace_setstatus ActiveTraceID, 0
exec sp_trace_setstatus ActiveTraceID, 2

On the other hand, this trace is very light and according to BOL also useful: BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur." Also if you turn off the trace, you won't have the option to debug stored procedures from Management Studio 2005.

Via: Alex Lerner (SRL Tech Ltd.)

DBCC DBREINDEX

mz1313 — Sun, 09 Apr 2006 08:27:00 GMT

In BOL for SQL 2K we read, that SORTED_DATA and SORTED_DATA_REORG clauses that existed in SQL Server 6.x are no longer supported in SQL 2K. Don't believe that, they are not just supported but even used by Maintenance Plan. Add Optimization step to your plan and query sysprocesses table when it runs - you'll see something like:

dbcc dbreindex(N'[dbo].[MyTable]', N'', 90, sorted_data_reorg)

But don't get used to it - in SQL 2K5 SORTED_DATA and SORTED_DATA_REORG clauses don't exist.

HowTo: delete old backups as part of maintenance plan in SQL Server 2005

mz1313 — Mon, 03 Apr 2006 08:13:00 GMT

In SQL 2000 there is nothing easier then that - just choose required options in the Complete Backup and/or Transaction Log Backup tabs. In SQL 2005 those who try to create Maintenance Plan using a wizard, usually ask aftewards, where did they miss the "delete old backups" option. The answer is: nowhere. There is no such option in a wizard. In order to delete old backups one should open existing plan (created with a wizard, for example) in SSIS window (open Management Studio, Management --> Maintenance Plans, right click on your plan --> Modify) and add "Maintenance Plan Cleanup" task.

SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP)

mz1313 — Sun, 19 Mar 2006 06:19:00 GMT

SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP)

The great news is that Database Mirroring will now be officially supported in production.