question about DBCC checkident

Hi all,
In our application we use a special table(only 2 columns, one of which
is identity) to generate unique keys to use in our client application.One of
my recent requests was to create a procedure that would reserve a set of
keys in the table and return it to client.
The procedure I wrote:
1. Inserts a new row into the table to get the current identity
2. execute dbcc checkident with reseed parameter and the
blocksize+current identity.
3. Another insert into the table to ensure the identity is reset
properly. (I added this step only because in testing I found that this makes
identity setup work correctly).
I have included the code for the procedure at the end of the message.
Now this procedure works fine for a single user. However in multiuser
scenario with more than 100 users running this procedure concurrently,
application server has started crashing.
While trying to simulate this problem, I created a batch process that runs
125 concurrent processes running this procedure. I found something strange
in this. One of things I observed is that the sessions that successfully
run, show the following dbcc output:
"C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
Checking identity information: current identity value '153757', current
column value '153906'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
But some sessions do not report this messages and I think this sessions are
failing the DBCC CheckIdent call siliently. There are no error messages in
sql server error log.
Has anyone seen or experienced this before. Is running DBCC checkident for
such a high number of concurrent users very bad?
Thanx, Amol.
ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
integer,@.al_startkey integer output)
as
begin
declare @.ls_revision varchar(40);
declare @.ls_msgprefix varchar(100);
declare @.ls_sql varchar(1024);
declare @.li_range_end integer;
set @.ls_revision='$Revision: 1.7 $';
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
(tablockx) (dummyvalue) values (1)';
execute(@.ls_sql);
set @.al_startkey=@.@.identity;
set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account for
the previous insert;
set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
'_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
execute (@.ls_sql)
set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
(dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
execute(@.ls_sql);
endDon't do it like that. You can create a simple table and sp that will allow
you to get the next ID for a specific table very easily without using
Identities. Have a look at this example:
CREATE TABLE [dbo].[NEXT_ID] (
[ID_NAME] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEXT_VALUE] [int] NOT NULL ,
CONSTRAINT [PK_NEXT_ID_NAME] PRIMARY KEY CLUSTERED
(
[ID_NAME]
) WITH FILLFACTOR = 100 ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE get_next_id
@.ID_Name VARCHAR(20) ,
@.ID int OUTPUT
AS
UPDATE NEXT_ID SET @.ID = NEXT_VALUE = (NEXT_VALUE + 1)
WHERE ID_NAME = @.ID_Name
RETURN (@.@.ERROR)
Andrew J. Kelly SQL MVP
"Amol" <apk@.nospam.cbord.com> wrote in message
news:OxXVhERHFHA.2420@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> In our application we use a special table(only 2 columns, one of which
> is identity) to generate unique keys to use in our client application.One
> of my recent requests was to create a procedure that would reserve a set
> of keys in the table and return it to client.
> The procedure I wrote:
> 1. Inserts a new row into the table to get the current identity
> 2. execute dbcc checkident with reseed parameter and the
> blocksize+current identity.
> 3. Another insert into the table to ensure the identity is reset
> properly. (I added this step only because in testing I found that this
> makes identity setup work correctly).
> I have included the code for the procedure at the end of the message.
> Now this procedure works fine for a single user. However in multiuser
> scenario with more than 100 users running this procedure concurrently,
> application server has started crashing.
> While trying to simulate this problem, I created a batch process that runs
> 125 concurrent processes running this procedure. I found something strange
> in this. One of things I observed is that the sessions that successfully
> run, show the following dbcc output:
> "C:\CBORD\split tables>osql -E -S APK -d cbord -n -i"test_blockinsert.sql"
> Checking identity information: current identity value '153757', current
> column value '153906'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
>
> But some sessions do not report this messages and I think this sessions
> are failing the DBCC CheckIdent call siliently. There are no error
> messages in sql server error log.
> Has anyone seen or experienced this before. Is running DBCC checkident for
> such a high number of concurrent users very bad?
> Thanx, Amol.
>
>
> ALTER procedure getnextkey_range(@.as_tablename varchar(128),@.ai_blockSize
> integer,@.al_startkey integer output)
> as
> begin
> declare @.ls_revision varchar(40);
> declare @.ls_msgprefix varchar(100);
> declare @.ls_sql varchar(1024);
> declare @.li_range_end integer;
> set @.ls_revision='$Revision: 1.7 $';
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey with
> (tablockx) (dummyvalue) values (1)';
> execute(@.ls_sql);
> set @.al_startkey=@.@.identity;
> set @.li_range_end = @.al_startkey + @.ai_blockSize - 1; -- -1 to account
> for the previous insert;
> set @.ls_sql = 'dbcc checkident (''' + rtrim(ltrim(@.as_tablename)) +
> '_nextkey'',reseed,' + cast(@.li_range_end as varchar(8)) + ')';
> execute (@.ls_sql)
> set @.ls_sql='insert into ' + rtrim(ltrim(@.as_tablename)) + '_nextkey
> (dummyvalue) values (' + cast(@.li_range_end as varchar(8)) + ')';
> execute(@.ls_sql);
> end
>
>