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

question about charts

I have a pie chart which is been populated from a sproc.

and for a particular fund the values that generate are 78%,20% and 2% respectively.

And when it generates a chart the pie chart is uniformly displayed... Like for eg.. its been broken into 3 parts and there isnt a difference between the 78% and the 2% so ... How can i get the pie chart to be a displayed according to its values..

Any help will be appreciated..

Regards

Karen

So you are saying that each piece of the pie is showing as one third of the pie instead of being divided according to the percentage if you have three values in the pie chart?

Have you tried an exploded pie with 3D effects? Have you displayed the value for each piece of the pie on the chart?

Since it is a pie chart, it should naturally divide the pie according to the values in the chart.

If not, it would be like a bar graph with all of the bars at the same height when the values were in fact different.

|||

i have a exploded pie chart ... in design mode i can see the differences but not when i preview it..

Regards

KAren

|||

So when you preview it you can see the percentages over the pieces of the pie, but it looks as if it's not dividing it accordingly?

I'm making sure I understand the problem...

|||Maybe it's having a problem with the percentage itself. Is your field in itself a percentage? If so, I don't think it should be. The field itself should be a number (not a percentage) and the pie chart will divide the number by the total to get the percentage behind the scenes. You don't need to make it a percentage yourself.|||

greg,

The fields is varchar field in Sql server and i am not doing any thing special in the report..

78% part should be bigger than 2 % but 78% and 2% are the same size.

So how can i solve it

Regards

Karen

|||

i changed the datatype to decimal and it works fine.

Thanks

Regards,

Karen

Question - SQL - Help - Pleased

Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
ESTUVO_univoldev,ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc),SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
ESTUBA_unidbase,ESTUBA_unibasedev,
ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.
Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquettXe.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.Xhtm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:

> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,ESTUBA_unibaserec,ESTUVO_univolum,
> ESTUVO_univoldev,ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal),SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc),SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev),SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev),SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev),SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev),SUM(ESTCFU_cantfunrec),SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec),SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec),SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec),SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor,CXCCLI_codigo,ESTNIV_nivel4,INVITM_codigo,
> ESTUBA_unidbase,ESTUBA_unibasedev,
> ESTUBA_unibaserec,ESTUVO_univolum,ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.

Question - SQL - Help - Pleased

Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTU
BA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc
),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal)
,
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valba
ldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantf
unrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costl
ocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numer
o), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_
nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTU
VO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquett_e.asp?id=5006 and
example data as insert statements [url]http://vyaskn.tripod.com/code._htm#inserts[/url
]
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmente
d
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:

> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, EST
UBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impulo
c),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal
),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valb
aldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_descl
ocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocd
ev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cant
funrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_descl
ocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_cost
locrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_nume
ro), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV
_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, EST
UVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultatio
n
> is for the Harward of it schemes it but well I think that it is for some b
ad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.

Question - SQL - Help - Pleased

Hello,
He/she wanted that they helped me in solving a mnovedad that I have since
with the Sql 7.0 it becomes very slow when I generate a consultation to a
chart of about 850000 registrations
Next I put them the sentence that is executed.
SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
ESTUVO_univoldev, ESTUVO_univolrec,
SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
SUM(ESTPES_peso), SUM(ESTIMP_impubal),
SUM(ESTDES_descloc), SUM(ESTDES_descbal),
SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
SUM(ESTCOS_costbaldev),
SUM(ESTVOL_volumen),
SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
SUM(ESTPES_pesorec),
SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
SUM(ESTREC_numero)
FROM ESTE_ESTDISTICAS (NOLOCK)
GROUP BY
ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
ESTUBA_unidbase, ESTUBA_unibasedev,
ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
ESTUVO_univolrec
In the personal thing I don't believe that the slowness of the consultation
is for the Harward of it schemes it but well I think that it is for some bad
configuration of the team or for the some service pack.
Good he/she wanted them to help me with that inconvenience that I have.
Thank you.Hi
Without DDL for the tables and example data it is hard to comment see
http://www.aspfaq.com/etiquett­e.asp?id=5006 and
example data as insert statements http://vyaskn.tripod.com/code.­htm#inserts
You will need to look at the query plan and check out the indexes that are
being used. Make sure that the indexes are appropriate and are not fragmented
(DBCC SHOWCONTIG) and that the statistics are up-to-date. You may want to
look at the Index Tuning wizard to see if that will provide any suggestions.
John
"Jems" wrote:
> Hello,
> He/she wanted that they helped me in solving a mnovedad that I have since
> with the Sql 7.0 it becomes very slow when I generate a consultation to a
> chart of about 850000 registrations
> Next I put them the sentence that is executed.
>
> SELECT ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev, ESTUBA_unibaserec, ESTUVO_univolum,
> ESTUVO_univoldev, ESTUVO_univolrec,
> SUM(ESTCFU_cantifunci), SUM(ESTLOC_valorloc),
> SUM(ESTBAL_valorbal), SUM(ESTIMP_impuloc),
> SUM(ESTPES_peso), SUM(ESTIMP_impubal),
> SUM(ESTDES_descloc), SUM(ESTDES_descbal),
> SUM(ESTCOS_costoloc), SUM(ESTCOS_costobal),
> SUM(ESTCFU_cantfundev), SUM(ESTBAL_valbaldev),
> SUM(ESTLOC_vallocdev), SUM(ESTIMP_impulocdev),
> SUM(ESTIMP_impubaldev), SUM(ESTDES_desclocdev),
> SUM(ESTDES_descbaldev), SUM(ESTPES_pesodev), SUM(ESTCOS_costlocdev),
> SUM(ESTCOS_costbaldev),
> SUM(ESTVOL_volumen),
> SUM(ESTVOL_volumendev), SUM(ESTCFU_cantfunrec), SUM(ESTBAL_valbalrec),
> SUM(ESTLOC_vallocrec), SUM(ESTIMP_impulocrec),
> SUM(ESTIMP_impubalrec), SUM(ESTDES_desclocrec),
> SUM(ESTDES_descbalrec), SUM(ESTCOS_costlocrec), SUM(ESTCOS_costbalrec),
> SUM(ESTPES_pesorec),
> SUM(ESTVOL_volumenrec), SUM(ESTFAC_numero), SUM(ESTDEV_numero),
> SUM(ESTREC_numero)
> FROM ESTE_ESTDISTICAS (NOLOCK)
> GROUP BY
> ESTVEN_vendedor, CXCCLI_codigo, ESTNIV_nivel4, INVITM_codigo,
> ESTUBA_unidbase, ESTUBA_unibasedev,
> ESTUBA_unibaserec, ESTUVO_univolum, ESTUVO_univoldev,
> ESTUVO_univolrec
> In the personal thing I don't believe that the slowness of the consultation
> is for the Harward of it schemes it but well I think that it is for some bad
> configuration of the team or for the some service pack.
> Good he/she wanted them to help me with that inconvenience that I have.
> Thank you.