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(sou rce code).Whatever
we have to do, has to done from database side.

can anyone guide me to solve this problem.

Thanks

[Yak]

Re: Blocking



Richard wrote:[color=blue]
> 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(sou rce code).Whatever
> we have to do, has to done from database side.
>
> can anyone guide me to solve this problem.
>
> Thanks[/color]


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]

Re: Blocking

hi yak,

Thank u for the reply[color=blue]
> 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.
>[/color]

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:[color=blue]
> Richard wrote:[color=green]
> > 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(sou rce code).Whatever
> > we have to do, has to done from database side.
> >
> > can anyone guide me to solve this problem.
> >
> > Thanks[/color]
>
>
> 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[/color]

[Yak]

Re: Blocking



Richard wrote:[color=blue]
> hi yak,
>
> Thank u for the reply[color=green]
> > 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.
> >[/color]
>
> 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
>[/color]
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[color=blue]
>
> Yak wrote:[color=green]
> > Richard wrote:[color=darkred]
> > > 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(sou rce code).Whatever
> > > we have to do, has to done from database side.
> > >
> > > can anyone guide me to solve this problem.
> > >
> > > Thanks[/color]
> >
> >
> > 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[/color][/color]

[Richard]

Re: Blocking

hi yak,

thank you.

:)Richard

Yak wrote:[color=blue]
> Richard wrote:[color=green]
> > hi yak,
> >
> > Thank u for the reply[color=darkred]
> > > 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.
> > >[/color]
> >
> > 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
> >[/color]
> 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[color=green]
> >
> > Yak wrote:[color=darkred]
> > > 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(sou rce 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[/color][/color][/color]