August 2006 - Posts

I've spent this week building two new machines, a new server for me here at my office and also a Windows Media Centre machine that I can hook up to my Samsung plasma panel so my wife can check her emails and my 3 year son can start learning SQL.

I've had a problem that was driving me mad - I couldn't get the sound from the computer to play back through the TV, I tried everything - uninstalling / reinstalling the sound card drivers, hours of searching google etc... I even bought an adapter so the audio optic cable from the sound card could go into the back of the TV - I thought at the time it was an unusual connector.

Last night I realised what was wrong, the connector into the back of the TV wasn't optic afterall, it was a simple stereo phono socket - what a div! I'd been trying to put the optical cable into a analogue phono socket.

The machine and audio now work a treat; moral of the story - RTFM before you start!

 

Grab your attention? This entry came out of trying to reproduce PFS / GAM / SGAM contention and trying to put some science around seeing it and what the benefit of using multiple files or trace flag 1118 really is. I got half way through writing that and then realised I had behaviour in tempdb that went against how I thought writing to tempdb worked – probably a throwback to 6.5 days.

 

Look at the script below, it basically creates a temporary table, populates it with about 100Mbytes of data and then drops it and repeats that logic for 5 minutes.

 

set nocount on

 

declare @i int

set @i = 1

declare @st datetime

declare @st2 datetime

set @st = current_timestamp

 

while current_timestamp <= dateadd( minute, 5, @st )

begin

      create table #t ( mycol char(8000) not null )

 

      set @st2 = current_timestamp

      while current_timestamp <= dateadd( second, 5, @st2 )

      begin

            insert #t values( 'abc' )

            set @i = @i + 1

      end

 

      drop table #t

end

 

print 'iterations ' + cast( @i as varchar(20) )

 

Running this on a machine that is doing nothing other than the script above, that has 512Mbytes allocated to SQL Server, what do you think happens in tempdb? The LDF (transaction log) is written with ‘undo only’ records, there is no redo on tempdb because it’s cleared down on SQL Server restart, what about the data files? Do you think the data files get written too even for this short lived temporary table?

 

The answer is it depends, in my tests if the cumulative size of the data files for tempdb is larger than the available data cache then it will write to the data files even though logically we don’t want it to.

 

I was expecting it to find and reuse the extents that had just been freed up by the drop table but that doesn’t happen, if you use DBCC EXTENTINFO( 2 ) to look at what extents are being allocated you will see that it progresses through the database until it runs out of free extents and then starts to round robin back through the used and free pages and starts using them which is why in that particular scenario we won’t get the writes out to the data files – the LAZYWRITER never gets chance to flush the page out to disk.

 

How can you tell if SQL Server is writing out to the data files, run this:-

 

select * from ::fn_virtualfilestats( 2, 2 )

union all

select * from ::fn_virtualfilestats( 2, 1 )

 

FileId 2 is the log, FileID <> 2 are the data files, check the NumberWrites column and if its increasing by a high rate then you’ve got a lot of writes going out to the data files in tempdb and tempdb is probably a bottleneck that needs fixing – remember, the only difference between tempdb and any other application database is that tempdb does not log redo records so if you have a heavily used database – it might even be more write intensive than your application one because of ORDER BY, Hashing, CREATE TABLE #, table variables etc… you probably need to get it off onto its own disk and maybe even separate the tempdb log and data.

 

There is another way; remember that tempdb is recycled every time SQL Server is restarted so with that in mind the old tempdb in RAM springs to mind; that was a feature removed many releases ago but my feeling is that for some systems that make heavy use of tempdb a RAM drive will benefit.

 

I’ve done some tests using a software RAM drive (http://www.amtsoftware.com/Ramdisk-Plus/) and the results are outstanding, installation is easy and it installed in minutes on my Windows 2003 Server R2 64bit OS; you create the drive and format it using their tool, the drive is then seen as a local drive (try exec master..fixeddrives).

 

Another method is to using Gigabytes i-RAM which looks pretty cool, it’s a PCI card that has 4 banks of DDR RAM that can hold up-to 4GB it uses the SATA interface so it just looks like another hard drive to your system, the benefit is that the seek times are memory speed (nanoseconds rather than milliseconds) and the Mbytes/second throughput is high. Due to dosh I haven’t evaluated it, the card costs £93 from scan and you could fill it out with 4GBytes for about another £200.

 

The tests where run on SQL 2000 Standard SP4 with hotfix 2187 and trace flag 1118 was applied so as not to use mixed extents; I basically run the script shown early with 10 concurrent connections. It’s probably worth noting that the disk is a 74GB SATA 1.5Gbit 10Krpm drive and the memory is DDR2 800, the motherboard supporting dual DDR, it’s also probably worth noting that no animals where hurt whilst conducting these tests – only my head against a brick wall….

 

Tempdb was configured as below, except when doing the RAM drive test, I kept one of the files on disk (the 1MByte one) so that if pressure where put on tempdb then it could autogrow successfully albeit to disk but your application wouldn’t fail, and why only 1Mbyte its because of the round robin write algorithm I describe in my other blog entry.

 

Tempdev                       200Mbytes         NO autogrowth

Tempdev2                     200Mbytes         NO autogrowth

Tempdev3                     200Mbytes         NO autogrowth

Tempdev4                     1Mbytes            Autogrowth 10MBytes

Templog                        50Mbytes          Autogrowth 10MBytes

 

Results

 

Tempdb: 4 data, 1 log files on disk                                 130,520 iterations

Tempdb: 3 data, 1 log files in RAM, 1 data on disk        1,587,359 iterations

 

Bit of a difference there!

 

To move tempdb to another drive run this and restart SQL Server…

 

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = templog , FILENAME = 'f:\sql2000\templog.ldf' )

 

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev , FILENAME = 'f:\sql2000\tempdev.mdf' )

 

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev2, FILENAME = 'f:\sql2000\tempdev2.mdf' )

 

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempdev3, FILENAME = 'f:\sql2000\tempdev3.mdf' )

 

ALTER DATABASE tempdb

      MODIFY FILE ( NAME = tempaug, FILENAME = 'f:\sql2000\tempaug.mdf' )

 

How does this translate into your own environment? Check your tempdb usage by monitoring ::fn_virtualfilestats(2, <fileid>) and also tempdb sizing. What are the IO characteristics of your applications? Does your existing machine facilitate enough memory for a RAM drive or PCI card for the Gigabyte i-RAM approach? Unfortunately without seeing and understanding your SQL Server system then I can’t comment but if you need some consultancy you know where I am ;).

 

I’ll get back to the PFS / GAM / SGAM contention thing sometime in the next couple of days – got to get back to client work…

 

 

In this entry I show how you can very easily shoot yourself in the foot if you don’t understand how SHRINKFILE / SHRINKDATABASE and DBCC DBREINDEX or CREATE INDEX with DROP_EXISTING work. Running DBREINDEX to remove fragmentation from 99% down to 98% (not good eh!).

 

Let’s set out some of the tools I’m using here…

 

DBCC SHOWCONTIG

Look at books online, basically this shows us the state of the table in terms of space usage and fragmentation (extent fragmentation within the database and logical fragmentation within the table or index itself).

 

DBCC EXTENTINFO

Check out http://support.microsoft.com/kb/324432/.

This useful statement shows us extent usage information for the database, we can capture this information to a table for analysis.

 

DBCC DBREINDEX

Look at books online, basically this rebuilds the index which will effect the ‘extent fragmentation’ and ‘logical fragmentation’ reported by DBCC SHOWCONTIG.

 

DBCC INDEXDEFRAG

Look at books online, whereas DBCC DBREINDEX and CREATE INDEX with DROP_EXISTING physically create a new copy of the index or table and then make that live, INDEXDEFRAG just poodles away fixing the logical fragmentation, it doesn’t assign any new extents so no data is copied, its only moved and that move uses very small transactions hence it can be run throughout the online day.

 

DBCC SHRINKFILE

Look at books online, essentially this moves data from the end of the database filling up all the free extents at the beginning of the database so it can then physically reduce the size of the file, there are options to stop it doing the data move – check bol.

 

Ok, now that’s out of the way what do I want to get across?

 

Essentially I want people to be aware of what happens when you do a DBCC SHRINKFILE and DBCC SHRINKDATABASE, you’d be surprised how often the mistake is made where somebody creates a near contiguous table/index using DBREINDEX / CREATE INDEX with DROP_EXISTING only to undo it all by running SHRINKFILE / SHRINKDATABASE straight after.

 

Create out test database, notice I’m using a separate file for this test, it makes life so much easier on the demo; there are benefits for using multiple files and multiple file groups and this contiguous data maintenance is one of them!

 

USE [master]

GO

 

CREATE DATABASE [testfile] ON  PRIMARY

( NAME = N'testfile', FILENAME = N'E:\SQL2005\testfile.mdf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [FileTest]

( NAME = N'testfile1', FILENAME = N'E:\SQL2005\testfile1.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'testfile_log', FILENAME = N'E:\SQL2005\testfile_log.LDF' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

 COLLATE Latin1_General_CI_AS

GO

 

EXEC dbo.sp_dbcmptlevel @dbname=N'testfile', @new_cmptlevel=90

go

 

ALTER DATABASE testfile

   MODIFY FILEGROUP FileTest DEFAULT;

go

 

Let’s create and populate our initial sample data set; notice how I’m deliberately inserting into the tables together so as to demonstrate how things become fragmented.

 

use testfile

go

 

create table test_table_1 (

    id  int not null identity unique clustered,

    somedata char(4000) not null

)

create table test_table_2 (

    id  int not null identity unique clustered,

    somedata char(4000) not null

)

go

 

set nocount on

 

declare @i int

set @i = 1

 

while @i <= 10000

begin

    insert test_table_1 ( somedata ) values( replicate( 'a', 4000 ) )

    insert test_table_2 ( somedata ) values( replicate( 'a', 4000 ) )

    set @i = @i + 1

 

end

go

 

Let’s create a temporary table to hold the results from running EXTENTINFO, the structure of the table depends on which version of SQL Server you are running.

 

if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 9

begin

   create table #extentinfo

   (

   [file_id] smallint,

   page_id int,

   pg_alloc int,

   ext_size tinyint,

   obj_id int,

   index_id tinyint,

   partition_number int,

   partition_id bigint,

   iam_chain_type varchar(100),

   pfs_bytes varbinary(10)

   )

end

go

if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 8

begin

   create table #extentinfo

   (

   [file_id] smallint,

   page_id int,

   pg_alloc int,

   ext_size tinyint,

   obj_id int,

   index_id tinyint,

   pfs_bytes varbinary(10)

   )

end

go

if convert(int,substring(convert(binary(4), @@microsoftversion),1,1)) = 7

begin

   create table #extentinfo

   (

   [file_id] smallint,

   page_id int,

   pg_alloc int,

   ext_size tinyint,

   obj_id int,

   index_id tinyint,

   pfs_bytes varbinary(10),

   avg_used tinyint

   )

end

go

 

Now our environment is complete lets get on and see what’s happening.

 

First, lets look at the outcome of the INSERT’s, notice how the object’s are interleaved through the database thereby causing ‘Extent Scan Fragmentation’.

 

insert #extentinfo

exec( '

dbcc extentinfo ( 0 ) with tableresults

' )

go

 

--  Take a look at the object interleaving

select *

from #extentinfo

where file_id = 3

order by page_id

 

Start Page

ObjectId

8

2073058421

10

2105058535

12

2073058421

13

2073058421

14

2105058535

15

2105058535

16

2073058421

17

2105058535

18

2073058421

 

Let’s take a look at DBCC SHOWCONTIG to see what it reports.

 

--  Note extent scan fragmentation

dbcc showcontig( test_table_1 )

dbcc showcontig( test_table_2 )

go

 

DBCC SHOWCONTIG scanning 'test_table_1' table...

Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 5000

- Extents Scanned..............................: 630

- Extent Switches..............................: 629

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.21% [625:630]

- Logical Scan Fragmentation ..................: 0.48%

- Extent Scan Fragmentation ...................: 99.37%

- Avg. Bytes Free per Page.....................: 70.0

- Avg. Page Density (full).....................: 99.14%

 

 

DBCC SHOWCONTIG scanning 'test_table_2' table...

Table: 'test_table_2' (2105058535); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 5000

- Extents Scanned..............................: 630

- Extent Switches..............................: 629

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.21% [625:630]

- Logical Scan Fragmentation ..................: 0.48%

- Extent Scan Fragmentation ...................: 99.37%

- Avg. Bytes Free per Page.....................: 70.0

- Avg. Page Density (full).....................: 99.14%

 

Look at that ‘Extent Scan Fragmentation’ – yikes! That’s because the objects are really badly interleaved – remind you of a production system, yep – that’s how it happens!

 

Try and get rid of the fragmentation:

 

dbcc indexdefrag( 0, test_table_1 )

go

 

This does nothing, INDEXDEFRAG only tackles and fixes the ‘Logical Scan Fragmentation’ which for both objects is fine.

 

Let’s reindex the table to get it back contiguous..

 

dbcc dbreindex( test_table_1 )

go

 

DBCC SHOWCONTIG scanning 'test_table_1' table...

Table: 'test_table_1' (2073058421); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 5001

- Extents Scanned..............................: 626

- Extent Switches..............................: 625

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [626:626]

- Logical Scan Fragmentation ..................: 0.02%

- Extent Scan Fragmentation ...................: 0.64%

- Avg. Bytes Free per Page.....................: 71.6

- Avg. Page Density (full).....................: 99.12%

 

Notice how ‘Extent Scan Fragmentation’ is now fine, but what has happened in our database file?

 

truncate table #extentinfo

go

 

insert #extentinfo

exec( '

dbcc extentinfo ( 0 ) with tableresults

' )

 

select *

from #extentinfo

where file_id = 3

order by page_id

go

 

Lovely! The database size has been increased and the object created in the new space, well, it’s lovely for my sample but what if you are dealing with GBytes? Also, we have a ton of free extents at the beginning of our database where the object resided before being defragged (re-indexed).

 

What does SQL Server do with this freespace? Well, as you guessed it uses it! Let’s create another object and populate that.

 

create table test_table_3 (

    id  int not null identity unique clustered,

    somedata char(4000) not null

)

go

 

set nocount on

 

declare @i int

set @i = 1

 

while @i <= 10000

begin

    insert test_table_3 ( somedata ) values( replicate( 'a', 4000 ) )

    set @i = @i + 1

 

end

go

 

Check where the data went…

 

truncate table #extentinfo

go

 

insert #extentinfo

exec( '

dbcc extentinfo ( 0 ) with tableresults

' )

 

select *

from #extentinfo

where file_id = 3

order by page_id

go

 

As you can see we have interleaved extents again; SQL Server has used those extents that we made available as part of the DBREINDEX, let’s drop the table and check what happens.

 

drop table test_table_3

go

 

truncate table #extentinfo

go

 

insert #extentinfo

exec( '

dbcc extentinfo ( 0 ) with tableresults

' )

 

select *

from #extentinfo

where file_id = 3

order by page_id

go

 

Back to normal – the freespace comes back, shall we try and re-index test_table_2 and see what happens, remember the current Extent Scan Fragmentation is 99%.

 

dbcc dbreindex( test_table_2 )

go

dbcc showcontig( test_table_2 )

 

Any joy? Oh dear, we haven’t don’t very well there – Extent Scan Fragmentation is 1% better, now at 98%!

 

Lets give our database some room, extend the database from 130Mbytes up to 500Mbytes…

 

ALTER DATABASE [testfile] MODIFY FILE ( NAME = N'testfile1', SIZE = 512000KB )

 

Now try the DBREINDEX again.

 

dbcc dbreindex( test_table_2 )

go

dbcc showcontig( test_table_2 )

 

That’s better! Extent Scan Fragmentation is now down to 0.48%. The thing is pages 32 through to 10063 are free, that’s around 79Mbytes of space at the start of the database that is unused.

 

Let’s run DBREINDEX on test_table_1 and see where it goes, well, because there is plenty of room at the beginning of the database its gone there and with no Extent Scan Fragmentation. If we hadn’t have grown the database and tried this then the DBREINDEX will have caused test_table_1 to be rebuilt back on the extents if first occupied because a) there is not enough free room in the database and b) the only free extents are interleaved with test_table_2.

 

Where do SHRINKFILE and SHRINKDATABASE come into this problem? Essentially you can think of them as doing a DBREINDEX of sorts, they take data from the end of the database and fill in all the free extents at the start of the database so they can shrink and release the unused space in the files. This is very bad because your objects get fragmented; a lot of installations do a DBREINDEX and then reclaim unused space as a matter of course!

 

Beware of places that might run a SHRINKFILE/SHRINKDATABASE like a well intentioned DBA/developer in a maintenance script or through the database maintenance plans.

 

More importantly always know your data, always do a proper capacity planning excercise, do not be afraid to use multiple file groups (don't go mad though), if fragmentation and index rebuild is a problem then just put the one object and/or index(s) on that file group.

 

Anyway, dats all folks; I sincerely hope you managed to follow this and grasp the concept and behaviour.

 

Enjoy….

 

I thought I'd seen everything until this, a Coffee/Tea Mug Warmer with a built in 4 port USB hub!

http://www.savastore.com/productinfo/product.aspx?catalog_name=Savastore&product_id=10282319&pid=0&rstrat=6952

Feel free to send me one...

Tony Rogerson
26 Moorland Road
Harpenden
Hertfordshire
AL5 4LA

LOL !

You will have no doubt seen or heard branded about that its good to use multiple files for a file group but you’ve always wondered why?

 

The current recommendation from Microsoft is that you have a file per logical CPU for tempdb so as to balance load not only for object creation but for extent allocation and performance.

 

But what does SQL Server do when you use multiple files? SQL Server round robins the rows being inserted so the rows are distributed across the multiple files.

 

Consider this first database, notice the files have the same initial size and autogrowth-

 

USE [master]

GO

CREATE DATABASE [testfile2] ON  PRIMARY

( NAME = N'testfile2', FILENAME = N'E:\testfile2.mdf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [FileTest]

( NAME = N'testfile21', FILENAME = N'E:\testfile21.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

( NAME = N'testfile22', FILENAME = N'E:\testfile22.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'testfile2_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testfile2_log.LDF' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

 COLLATE Latin1_General_CI_AS

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'testfile2', @new_cmptlevel=90

go

 

ALTER DATABASE testfile2

   MODIFY FILEGROUP FileTest DEFAULT;

 

We can now do a test to see how the file grows when we do an INSERT….

 

select * from ::fn_virtualfilestats(db_id(), NULL) where FileId > 2

go

select *

into SalesOrderDetail

from AdventureWorks.Sales.SalesOrderDetail

go

select * from ::fn_virtualfilestats(db_id(), NULL) where FileId > 2

go

 

I am using ::fn_virtualfilestats( <dbid>, <fileid> ) to capture the statistics against the file, thanks to Linchi Shea for pointing the best way to do that out.

 

On my test box measurements where

 

Before population

fileId            BytesOnDisk

3                 1048576      

4                 1048576      

After population

fileId            BytesOnDisk

3                 6291456

4                 6291456

 

We can see by the autogrowth that SQL Server has round robin’d between the two files equally because they had an equal amount of freespace in them.

 

Lets now look at a situation where the two files have different amounts of freespace, a situation that can result from a) autogrowth different, b) file added to the file group at different times.

 

Drop the original database and run this…

 

USE [master]

GO

CREATE DATABASE [testfile2] ON  PRIMARY

( NAME = N'testfile2', FILENAME = N'E:\testfile2.mdf' , SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [FileTest]

( NAME = N'testfile21', FILENAME = N'E:\testfile21.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

( NAME = N'testfile22', FILENAME = N'E:\testfile22.ndf' , SIZE = 1MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'testfile2_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\testfile2_log.LDF' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

 COLLATE Latin1_General_CI_AS

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'testfile2', @new_cmptlevel=90

go

 

ALTER DATABASE testfile2

   MODIFY FILEGROUP FileTest DEFAULT;

 

Notice we are still creating 2 files, but this time one starts with 10MB free and one with 1MB free – autogrowths are the same.

 

Re-running the select and insert gives this result…

 

Before population

fileId            BytesOnDisk

3                 1048576      

4                 10485760    

 

After population

fileId            BytesOnDisk

3                 3145728

4                 13631488

 

More data has been written to FileId 4 which is the one with more freespace.

 

So, the moral of the story – if you are going to use multiple files then be aware of how the round robin insert stuff works!

 

One last thing, another benefit of using multiple files is that each file has its own NT thread so its good when you have multiple logical CPU’s which is pretty much the norm now-a-days.

 

There are lots of other things I want to show – effects of shrinking, defraging etc… that will need to be another entry!

 

One last thing, the unit is rows, a single row can't span files - I think its done at the page level but I'm not sure - if anybody knows for sure perhaps they'd comment, otherwise I'll get back in the not too distant future with the answer....

 

 

The same passive failover rules that apply to SQL Server 2005 database mirroring and log shipping also apply to a log shipped standby on SQL Server 2000 - very good news for people still on SQL Server 2000 who can't move in the near term but want a resilient DR and standby configuration.

What does that mean in plain english?

You have a production server and want a seperate DR and/or Standby server, the license rules allows you to have passive servers with the same number of CPU's (or less) and do log shipping to it with the databases in standby mode, the other server's do not need to be licensed - you must absolutely not use it for anything other than a passive failover - so no using it for MIS, reporting, etc... as soon as you do that then it needs licensing; one further caveat is that if you do failover to the box then you can only run on it for 30 days after that you need be back on you main production box.

Just for reference in case of query, this was confirmed by Microsoft, case number: SRX1018514930ID.

This is a good aricle as well that describes the scenerio.

Now, remember who to come to if you need a DR and/or standby server sorting out ;).

 

How many places have you seen or used this bit of SQL to get the next number and insert it?

insert checkmaxplusone ( numb )
   select max( numb ) +
1
   from checkmaxplusone

Did you know or realise that if more than one connections are running this bit of code at the same time they can end up trying to insert the same value and if you have a unique constraint or primary key on the column then you will be getting this error on insert:-

Msg 2627, Level 14, State 1, Line 9

Violation of PRIMARY KEY constraint 'PK__checkmaxplusone__7E6CC920'. Cannot insert duplicate key in object 'dbo.checkmaxplusone'.

To illustrate this point and to give you a better understanding try the following test script.

First create the table and first row...

CREATE TABLE [dbo].[checkmaxplusone](
   [numb] [int] NOT NULL PRIMARY KEY
 
   )

INSERT checkmaxplusone values( 1 )

Now open two connections to the same database in iSQLw or Management Studio and run this bit of SQL, notice how many Msg 2627's you are getting.

set nocount on

declare @i int
set
@i = 1

while @i <= 10000
begin
   insert checkmaxplusone ( numb
)
      select max( numb ) +
1
      from
checkmaxplusone

   set @i = @i + 1

end

We can fix the Msg 2627 problem by using a different transaction isolation level, instead of the default READ COMMITTED we can use SERIALIZABLE. Go back to the script and add a new statement at the top and then re-run them both...

set transaction isolation level serializable

Notice how the Msg 2627 problems have gone away now, instead, you may well have got (like I did) a deadlock situation :-

Msg 1205, Level 13, State 48, Line 9

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

So what's happening?

In a nutshell the SELECT MAX()+1 query is being executed to calculate the value; but the locks taken to run that query are IS locks (Intent Shared) which is fine for queries but not for isolating the result against multiple connections; now switching to serializable isolation the lock changes to RangeS-S locks for the MAX()+1 value through to infinitety (the idea with serialisable is to prevent anybody entering a row that will make MAX()+1 different).

Looking at the INSERT part of the statement, at this stage in the execution life cycle we will already have the value of MAX()+1; when using serializable you are fine because another connection cannot insert another MAX()+1 so you know that you will be the only connection trying to INSERT it, but this is where the deadlock situation comes in - two connections will infact still have the same result from MAX()+1 because of the RangeS-S lock but only one is allowed to insert.

The Read Committed isolation is different in the respect that both connections are allowed to try and insert the same MAX()+1 value; the locking mode is IX (Intent Exclusive) which is why you get the PK violation Msg 2627.

Ways round the problem

Knowing MAX()+1 has the inherent problem above then we need to look at other mechanisms.

If you don't care about gaps and your data is an incremental number then just be done with it and use the IDENTITY property otherwise read on...

If you have a single stored procedure in your application that is doing the MAX()+1, which quite frankly is usually the case, then you can use application locks again as discussed in my other blog entry (http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx). If that is not to your taste then you can use a seperate table to hold the last used number and have a proc that requests it and passes it back, you can allocate blocks of numbers if you know how many rows you are inserting (thinking an INSTEAD OF trigger here and using the INSERTED table in combination with a proc.

create table lastnumber (
   last_numb int not null

)

insert lastnumber values( 0 )
go

create proc get_number
   @new_numb
int
output
as
begin
   begin tran

   update lastnumber
      set last_numb = last_numb +
1

   set @new_numb =
(
         select
last_numb
         from lastnumber )

   commit tran

end

Notice the use of a transaction - this is extremely important because it maintains the lock used by the UPDATE statement, if you didn't do this in a transaction then the SELECT query will give different answers depending on what connection last committed and again you will end up with Msg 2627.

And the new insert logic is shown below - note the use of another transaction, this time to make sure you don't get any gaps; if you didn't use a transaction then the number can be incremented and gaps occur.

set nocount on

declare @new_key int
declare
@i
int
set
@i = 1

while @i <= 100000
begin
   
begin
tran

   exec get_number @new_numb = @new_key output

   insert checkmaxplusone values( @new_key )

   commit tran

   set @i = @i + 1

end

Thats about it for this entry, there is actually more I can talk about on this subject but I'm hungry and need my breakfast! Again, questions and answers on a postcard please...

 

So much to do, so little time to do it.