Blocking

[Richard]

hi,

I am using SQL SERVER 2000.

Problem that i am facing is when ever I check locks in Enterprise
Manager I find
following blocking - :
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1

We dont have any control over application(source code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks

[Yak]

Richard wrote:

hi,

I am using SQL SERVER 2000.

Problem that i am facing is when ever I check locks in Enterprise
Manager I find
following blocking - :
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1

We dont have any control over application(source code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks

Richard,

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

If you just find you have got some generic slowness regarding the
applications, go set up some profiles focusing on long running
transactions.

Generally, locks are always needed in a database system when concurrent
users are assessing (in different ways) the same resource. MSSQL
handles locks internally, and my view is not to mess with them. Of
course you can use there are certain guidelines for the DBAs and
developers to utilise locks more properly as scarce resource.

Oracle guys might laugh over us on this one since in Oracle locks are
not scarce resource.

Hope this can help.

Cheers,

Yak

[Richard]

hi yak,

Thank u for the reply

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

I will be more specific ,when i checked my EM for blocking .I found out
their were 3 SPID with blocking remark,when i checked its property i
found the following
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1
and this blocking is effecting my users who are connected to my server.
I googled a lot for the solution but their was no result.I want to
solve this problem for ever(if possible).

:)
Richard
Yak wrote: Richard wrote:

hi,

I am using SQL SERVER 2000.

Problem that i am facing is when ever I check locks in Enterprise
Manager I find
following blocking - :
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1

We dont have any control over application(source code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks

Richard,

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

If you just find you have got some generic slowness regarding the
applications, go set up some profiles focusing on long running
transactions.

Generally, locks are always needed in a database system when concurrent
users are assessing (in different ways) the same resource. MSSQL
handles locks internally, and my view is not to mess with them. Of
course you can use there are certain guidelines for the DBAs and
developers to utilise locks more properly as scarce resource.

Oracle guys might laugh over us on this one since in Oracle locks are
not scarce resource.

Hope this can help.

Cheers,

Yak

[Yak]

Richard wrote:

hi yak,

Thank u for the reply

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

I will be more specific ,when i checked my EM for blocking .I found out
their were 3 SPID with blocking remark,when i checked its property i
found the following
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1
and this blocking is effecting my users who are connected to my server.
I googled a lot for the solution but their was no result.I want to
solve this problem for ever(if possible).

:)
Richard

Richard,

If you just want to kill the original culprit process, then go "process
info" to find where the locks came from. You should be able to figure
it out using "blocking" and "blockedby". Once you find it you can kill
it from EM.

To solve the problem "forever", I reckon you need to find out the
reason for these locks. Usually locks will be released after the
resource contention is resolved over times unlesss you have deadlocks
there. (if you do, you can also find out using the solution as above)

The easiest way is to monitor the application using profiles focusing
on long running transactions (there must be some there if you always
have locks not released for long time). You can find out the script the
application uses which cause serious resource contention.

Then talk to the programmer.

At the moment, all you can do is to find out the problematic process
and kill it.

Cheers,

Yak
Yak wrote:

Richard wrote:

hi,

I am using SQL SERVER 2000.

Problem that i am facing is when ever I check locks in Enterprise
Manager I find
following blocking - :
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1

We dont have any control over application(source code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks

Richard,

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

If you just find you have got some generic slowness regarding the
applications, go set up some profiles focusing on long running
transactions.

Generally, locks are always needed in a database system when concurrent
users are assessing (in different ways) the same resource. MSSQL
handles locks internally, and my view is not to mess with them. Of
course you can use there are certain guidelines for the DBAs and
developers to utilise locks more properly as scarce resource.

Oracle guys might laugh over us on this one since in Oracle locks are
not scarce resource.

Hope this can help.

Cheers,

Yak

[Richard]

hi yak,

thank you.

:)Richard

Yak wrote:

Richard wrote:

hi yak,

Thank u for the reply

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

I will be more specific ,when i checked my EM for blocking .I found out
their were 3 SPID with blocking remark,when i checked its property i
found the following
1) sp_cursoropen;1
2) sp_cursorclose;1
3) sp_cursorfetch;1
and this blocking is effecting my users who are connected to my server.
I googled a lot for the solution but their was no result.I want to
solve this problem for ever(if possible).

:)
Richard

Richard,

If you just want to kill the original culprit process, then go "process
info" to find where the locks came from. You should be able to figure
it out using "blocking" and "blockedby". Once you find it you can kill
it from EM.

To solve the problem "forever", I reckon you need to find out the
reason for these locks. Usually locks will be released after the
resource contention is resolved over times unlesss you have deadlocks
there. (if you do, you can also find out using the solution as above)

The easiest way is to monitor the application using profiles focusing
on long running transactions (there must be some there if you always
have locks not released for long time). You can find out the script the
application uses which cause serious resource contention.

Then talk to the programmer.

At the moment, all you can do is to find out the problematic process
and kill it.

Cheers,

Yak

Yak wrote:

Richard wrote:
> hi,
>
> I am using SQL SERVER 2000.
>
> Problem that i am facing is when ever I check locks in Enterprise
> Manager I find
> following blocking - :
> 1) sp_cursoropen;1
> 2) sp_cursorclose;1
> 3) sp_cursorfetch;1
>
> We dont have any control over application(source code).Whatever
> we have to do, has to done from database side.
>
> can anyone guide me to solve this problem.
>
> Thanks
Richard,

I am not sure about what you want to achieve by checking lock
information in EM. Locks dynamically occur all the time and not
necessarily bad things. Any database system won't work without some
kind of lock mechanism, the key is how it is utilised.

If you suspect some dead locks are happening, go check out the process
info. You might be able to find out which process is blocking others.

If you just find you have got some generic slowness regarding the
applications, go set up some profiles focusing on long running
transactions.

Generally, locks are always needed in a database system when concurrent
users are assessing (in different ways) the same resource. MSSQL
handles locks internally, and my view is not to mess with them. Of
course you can use there are certain guidelines for the DBAs and
developers to utilise locks more properly as scarce resource.

Oracle guys might laugh over us on this one since in Oracle locks are
not scarce resource.

Hope this can help.

Cheers,

Yak