processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a écrit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time... :)
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql

Process blocking itself since SP4

Hi,

Since installing SP4 we seem to encounter issues where a process seems to block itself. Has anyone seen this issue. Is this a feature?

Thanks,
Dave

Please refer to the following for more details. This may be the reason

The original posting can be found in many places, for example: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/23496/SP4-reports-queries-blocking-on-themselves

There is also a KB article on this: http://support.microsoft.com/default.aspx?scid=KB;EN-US;906344
Thanks

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advanced
Hi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:

> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>
|||Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
file[vbcol=seagreen]
|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas

"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
file[vbcol=seagreen]

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advancedHi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:

> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>|||Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
>
file[vbcol=seagreen]|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas
"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I havent problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribi en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like[vbcol=seagreen]
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
>
file[vbcol=seagreen]

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advancedHi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:
> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>|||Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas
"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.

|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue

process block itself

Hi to everybody,

i have this problem.

sometimes a process in my SQL Server 2005 (upd 2) is blocked from itself. If I find in monitor it wait for a lock_M_Sch_M on a temporary table as you can see from

select * from sys.dm_tran_locks where request_session_id=51 and request_status <> 'GRANT'

resorce type : OBJECT

resource id : 218899226

request mode : Sch-M

request status WAIT

request_session id : 51

request life time : 33554432

or from

SELECT * FROM sys.dm_os_waiting_tasks WHERE SESSION_ID=51

waiting task address : 0x0000000000C2F198

session id : 51

wait type : LCK_M_SCH_M

resource address : 0x0000000201C71300

blocking task address : 0x0000000000C2F198

blocking session id = 51

resource description objectlock lockPartition=0 objid=218899226 subresource=FULL dbid=2 id=lock80d04900 mode=Sch-S associatedObjectId=218899226

I resolve this problem only with restart of sql server . that clear tempdb and eliminate this process

I think is not a problem about latch present from sql server 2000 sp4 (I read some document abount this problem)

So I haven't idea how to resolve this problem and how to kill this kind of process without to restart sql server

thanks in advance

Luca

Is this a job that runs a DTS package? If so, make sure you didn't port the dts jobs to a different server. It is possible that you have two of the same jobs running the same package at the same time but from different servers.|||

No, it isn't a DTS package but I have found where is problem...

I use a procedure like this to retry a block of istructions when i have an error

Procedure 2

.....

create table #tmp.....

declare @.n int

declare @.maxcicle int

declare @.cicle int

set @.n = 100

set @.maxcicle = 10

set @.cicle = 1

while @.n = 100

begin

begin try

begin transaction

--

-- instructions that use #tmp

--

commit tran

set @.n = 0

end try

begin catch

set @.cicle = @.cicle + 1

set @.n = 100

rollback transaction

if @.clicle > @.maxcicle

begin

raise error

end

waitfor delay '00:00:10'

end catch

end

end

I call this procedure from Procedure 1

procedure 1

begin try

begin transaction

exec procedure 2

commit transaction

end try

begin catch

rollback transaction

end catch

end

when I have an error in Procedure 2 procedure make a rollback of all instruction from begin transaction in procedure 1 so also temporary table was destroy and when try the cicle again procedure 2 can't find temporary table and block process from itself becouse it wait from a resource and resource now don't exists (and SQL Server think that it's in use from that process). SQL Server can't resolve this problem and process remain block from itself, wait from a resource that is in use for another process ( but for SQL Server becouse process is the same) a resource that now doesn't exists.

Only to restart SQL Server service process was destroy becouse tempdb restart.

Thanks

Luca

|||i had this problem and traced it to the processing of the table variables created with my procs. when i converted them to temp tables the problem resolved. under certain circumstances tempdb will be used for a table variable. the resource contention actually occurs with tempdb. I also turned on trace flag 1118 and increased the number of tempdb files to avoid my tempdb allocations from being pulled from the same 4GB address space in a tempdb file. i believe this is a bug in sql 2005 but haven't confirmed this yet.|||

Table variables are materialized in tempdb the same way that temp tables are. That's one of the common myths, misconceptions about table variables - that they are a memory only structure - which led way too many people to assume they are faster. It's just not the case. You'd decide one or the other based on scope needs, number of rows, weighing out the need for statistics vs potential recompiles, etc. Too often, table variables become the first choice based on thinking they are "better" or faster which is not true.

-Sue

Procedure to search a table

Hi All,

I have the following table:

Code Block

Request
requestid
customername
age
sex
address
status

[status =
0 deleteme
1 addme
2 updatme

3 processed]

I need to write a stored proc that will do the following :

Input : requestid

Logic : check the customers who already have a status of 'deleteme' and have now filed a status of 'addme'. For this, inside the stored proc, i will retreive the customername and address using the requestid and then check in the table for all the customers who have the samename, address and status=0. How can i detect this.

Output : 0 for no entry found and requestid if a request of 'deleteme' for the same person was found.

For eg:

Code Block

requestid,customername,age,sex,address,status
22352,Jack,23,M,'Texas',1

I need to check if a person with the name of Jack and address Texas is there in the db with the status of 0.

Plz note: My Request table can contain more than 100000 records at a time. Please sugest accordingly

Plz help. Thanks

Here it is,

Code Block

Create Table #sample (

[requestid] int ,

[customername] Varchar(100) ,

[age] Varchar(100) ,

[sex] Varchar(100) ,

[address] Varchar(100) ,

[status] int

);

Insert Into #sample Values('20352','Jack','23','M','Texas','0');

Insert Into #sample Values('22352','Jack','23','M','Texas','1');

Insert Into #sample Values('22353','Jack','23','M','Austin','0');

Insert Into #sample Values('22362','Buck','23','M','Austin','0');

Insert Into #sample Values('22392','Buck','23','M','Austin','1');

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 0

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] < [add].[requestid]

and [add].[status] = 1

)

|||

Hi,

Thanks for query. I ran the query and it returns all people who have status as 1.

I want all people who have status as 0 and not 1. How can i make this in a stored proc so that i accept only request id and then search.

thanks.

|||

Expected one.. Use the following query..

Code Block

select

*

from

#sample [add]

Where

Exists

(

select

[requestid]

,[customername]

,[address]

from

#sample [del]

where

[del].[status] = 1

and [del].[customername] = [add].[customername]

and [del].[address] = [add].[address]

and [del].[requestid] > [add].[requestid]

and [add].[status] = 0

)

|||

thanks so strange..i tried that previously on the query you gave first..it gave no records..no when i try yours, it gave the needed results..

nyways thanks.