NOP command in Sysprocesses

Looking at blocking on a SQL Server 2000 system sitting on Windows 2003
Server I am somtimes catching a blocker sitting with multiple locks and
a command of NOP blocking an update user looking for a exclusive key
lock.
The main question is what is NOP command in SQL Server? Of the ones
I've been able to witness, the commands that come up as NOP, during the
blocking period will change to a INSERT cmd.
This system is SQL Server 2k Enterprise on a 4 processor box with AWE
mode and 16gb of memory.
I can't find any reference to this in any documentation and I seem to
recall hearing what such a command represented at one time but can't
find it now. My feeling is that this might help me identify some
performance issues (beyond the bad code I've already identified) so it
would help to understand what causes SQL Server to post that as a
command in Sysprocesses.
thanks
NOP stands for no operation, which is a low level instruction that waits for
a specific number of cpu cycles. I am not much familiar with how it impacts
SQL server, but a quick googling suggests it may impact deadlocking, when
one process waiting for a resource held by the another process.
Anith
|||Anith Sen wrote:
> NOP stands for no operation, which is a low level instruction that waits for
> a specific number of cpu cycles. I am not much familiar with how it impacts
> SQL server, but a quick googling suggests it may impact deadlocking, when
> one process waiting for a resource held by the another process.
>
Unfortunately I can't find any information regarding SQL Server
specifically, not even in the Inside SQL Server books I've read over
time.
I'm still seeing it show up, it's not part of a deadlock as the
deadlock isn't being found by basic tracking, though what found is it's
a user creating a block. When I first catch the fact a block is
occuring I see the command NOP. 3 seconds later I check again and the
block is still occuring but the command says UPDATE.
I've searched the web extensively as well, so hopefully someone with
more visibility to the SQL Engine will see this and pipe up.
One interesting component, the server this is running on is a Quad
Processor Dell box with 4 Dual Core Xeon processors (1st generation
dual core chips) and they have it running hyperthreading as well. So
SQL sees 16 processors but there are really only 4 dual cores, which in
this family of Intels acts like about 1.5 each.
|||Mike H wrote:
...
> I'm still seeing it show up, it's not part of a deadlock as the
> deadlock isn't being found by basic tracking, though what found is it's
> a user creating a block. When I first catch the fact a block is
> occuring I see the command NOP. 3 seconds later I check again and the
> block is still occuring but the command says UPDATE.
> I've searched the web extensively as well, so hopefully someone with
> more visibility to the SQL Engine will see this and pipe up.
...
It's suprising that this is a valid wait state in SQL Server but no one
knows where it's coming from. I still see these come up and it would
be very helpful to know what's causing them.
|||Mike H wrote:
> Mike H wrote:
> ...
> ...
> It's suprising that this is a valid wait state in SQL Server but no one
> knows where it's coming from. I still see these come up and it would
> be very helpful to know what's causing them.
bueller ? anyone?

NOP command in Sysprocesses

Looking at blocking on a SQL Server 2000 system sitting on Windows 2003
Server I am somtimes catching a blocker sitting with multiple locks and
a command of NOP blocking an update user looking for a exclusive key
lock.
The main question is what is NOP command in SQL Server? Of the ones
I've been able to witness, the commands that come up as NOP, during the
blocking period will change to a INSERT cmd.
This system is SQL Server 2k Enterprise on a 4 processor box with AWE
mode and 16gb of memory.
I can't find any reference to this in any documentation and I seem to
recall hearing what such a command represented at one time but can't
find it now. My feeling is that this might help me identify some
performance issues (beyond the bad code I've already identified) so it
would help to understand what causes SQL Server to post that as a
command in Sysprocesses.
thanksNOP stands for no operation, which is a low level instruction that waits for
a specific number of cpu cycles. I am not much familiar with how it impacts
SQL server, but a quick googling suggests it may impact deadlocking, when
one process waiting for a resource held by the another process.
--
Anith|||Anith Sen wrote:
> NOP stands for no operation, which is a low level instruction that waits for
> a specific number of cpu cycles. I am not much familiar with how it impacts
> SQL server, but a quick googling suggests it may impact deadlocking, when
> one process waiting for a resource held by the another process.
>
Unfortunately I can't find any information regarding SQL Server
specifically, not even in the Inside SQL Server books I've read over
time.
I'm still seeing it show up, it's not part of a deadlock as the
deadlock isn't being found by basic tracking, though what found is it's
a user creating a block. When I first catch the fact a block is
occuring I see the command NOP. 3 seconds later I check again and the
block is still occuring but the command says UPDATE.
I've searched the web extensively as well, so hopefully someone with
more visibility to the SQL Engine will see this and pipe up.
One interesting component, the server this is running on is a Quad
Processor Dell box with 4 Dual Core Xeon processors (1st generation
dual core chips) and they have it running hyperthreading as well. So
SQL sees 16 processors but there are really only 4 dual cores, which in
this family of Intels acts like about 1.5 each.|||Mike H wrote:
...
> I'm still seeing it show up, it's not part of a deadlock as the
> deadlock isn't being found by basic tracking, though what found is it's
> a user creating a block. When I first catch the fact a block is
> occuring I see the command NOP. 3 seconds later I check again and the
> block is still occuring but the command says UPDATE.
> I've searched the web extensively as well, so hopefully someone with
> more visibility to the SQL Engine will see this and pipe up.
...
It's suprising that this is a valid wait state in SQL Server but no one
knows where it's coming from. I still see these come up and it would
be very helpful to know what's causing them.|||Mike H wrote:
> Mike H wrote:
> ...
> > I'm still seeing it show up, it's not part of a deadlock as the
> > deadlock isn't being found by basic tracking, though what found is it's
> > a user creating a block. When I first catch the fact a block is
> > occuring I see the command NOP. 3 seconds later I check again and the
> > block is still occuring but the command says UPDATE.
> >
> > I've searched the web extensively as well, so hopefully someone with
> > more visibility to the SQL Engine will see this and pipe up.
> ...
> It's suprising that this is a valid wait state in SQL Server but no one
> knows where it's coming from. I still see these come up and it would
> be very helpful to know what's causing them.
bueller ? anyone?

NOP command in Sysprocesses

Looking at blocking on a SQL Server 2000 system sitting on Windows 2003
Server I am somtimes catching a blocker sitting with multiple locks and
a command of NOP blocking an update user looking for a exclusive key
lock.
The main question is what is NOP command in SQL Server? Of the ones
I've been able to witness, the commands that come up as NOP, during the
blocking period will change to a INSERT cmd.
This system is SQL Server 2k Enterprise on a 4 processor box with AWE
mode and 16gb of memory.
I can't find any reference to this in any documentation and I seem to
recall hearing what such a command represented at one time but can't
find it now. My feeling is that this might help me identify some
performance issues (beyond the bad code I've already identified) so it
would help to understand what causes SQL Server to post that as a
command in Sysprocesses.
thanksNOP stands for no operation, which is a low level instruction that waits for
a specific number of cpu cycles. I am not much familiar with how it impacts
SQL server, but a quick googling suggests it may impact deadlocking, when
one process waiting for a resource held by the another process.
Anith|||Anith Sen wrote:
> NOP stands for no operation, which is a low level instruction that waits f
or
> a specific number of cpu cycles. I am not much familiar with how it impact
s
> SQL server, but a quick googling suggests it may impact deadlocking, when
> one process waiting for a resource held by the another process.
>
Unfortunately I can't find any information regarding SQL Server
specifically, not even in the Inside SQL Server books I've read over
time.
I'm still seeing it show up, it's not part of a deadlock as the
deadlock isn't being found by basic tracking, though what found is it's
a user creating a block. When I first catch the fact a block is
occuring I see the command NOP. 3 seconds later I check again and the
block is still occuring but the command says UPDATE.
I've searched the web extensively as well, so hopefully someone with
more visibility to the SQL Engine will see this and pipe up.
One interesting component, the server this is running on is a Quad
Processor Dell box with 4 Dual Core Xeon processors (1st generation
dual core chips) and they have it running hyperthreading as well. So
SQL sees 16 processors but there are really only 4 dual cores, which in
this family of Intels acts like about 1.5 each.|||Mike H wrote:
...
> I'm still seeing it show up, it's not part of a deadlock as the
> deadlock isn't being found by basic tracking, though what found is it's
> a user creating a block. When I first catch the fact a block is
> occuring I see the command NOP. 3 seconds later I check again and the
> block is still occuring but the command says UPDATE.
> I've searched the web extensively as well, so hopefully someone with
> more visibility to the SQL Engine will see this and pipe up.
...
It's suprising that this is a valid wait state in SQL Server but no one
knows where it's coming from. I still see these come up and it would
be very helpful to know what's causing them.|||Mike H wrote:
> Mike H wrote:
> ...
> ...
> It's suprising that this is a valid wait state in SQL Server but no one
> knows where it's coming from. I still see these come up and it would
> be very helpful to know what's causing them.
bueller ? anyone?

Non blocking query on a table in SQL Server 2000

Hi Folks,
How can I invoke a non blocking ODBC query on a table in a SQL Server
database. Our current design requires the method invoking the query to
'return' immediately after invoking the query. The results of the query
should be put in a globally accessible location.
I think SQLFetch is a blocking call.
Thanks,
Vishal
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Vishal,
You have to use muliple threads. It is SQL Execute that usually takes
long time, not SQLFetch(). A call to single SQLFetch usually does not
take long unless you have some network connection problem.
Here is what you can do:
From your main application thread spawn a new one that does
SQLExecute(). The same spawned thread will call SQLFetch and put the
result in a shared buffer, possibly a global to you app. Then send a
Window message to you application notifying it that some data has be put
in the buffer. Read this data from your primary thread.
Regards,
Elvis
Dead not so long ago.
Vishal Prabhu wrote:

quote:

---

> Hi Folks,
> How can I invoke a non blocking ODBC query on a table in a SQL Server
> database. Our current design requires the method invoking the query to
> 'return' immediately after invoking the query. The results of the query
> should be put in a globally accessible location.
> I think SQLFetch is a blocking call.
> Thanks,
> Vishal
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

Non blocking query call

Hi Folks,
How can I invoke a non blocking ODBC query on a table in a SQL Server
database. Our current design requires the method invoking the query to
'return' immediately after invoking the query. The results of the
query should be put in a globally accessible location.
I think SQLFetch is a blocking call.
Thanks,
VishalWhat you are looking for is called Asynchronous Execution. You must enable
async using SQLSetConnectAttr() and SQL_ATTR_ASYNC_ENABLE.
ODBC APIs will return SQL_STILL_EXECUTING while the operation is still
pending. Once the operation completes, the API will return something other
than SQL_STILL_EXECUTING (SQL_SUCCESS if the operation succeeded, etc).
The async model exposed by ODBC is a Poll model, where you have to
periodically poll for completion (instead of a Notification model, where you
are notified that the operation completed).
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Vishal Prabhu" <vprabhu@.uci.edu> wrote in message
news:31cf0289.0312191118.4354c4fd@.posting.google.com...

quote:

---

> Hi Folks,
> How can I invoke a non blocking ODBC query on a table in a SQL Server
> database. Our current design requires the method invoking the query to
> 'return' immediately after invoking the query. The results of the
> query should be put in a globally accessible location.
> I think SQLFetch is a blocking call.
> Thanks,
> Vishal

---

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-Dan
What's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>
|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.co m...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

NOLOCK hint causes process blocking?

We recently added NOLOCK hints to our less-important queries to cut down on
deadlocks. It does decrease the deadlocks but it seemed to increase other
process blocking... the type that nearly hangs the sql server. Is that
possible? Has anyone else noticed? I am starting to think NOLOCK is a
NO-NO, and that deadlocks are better.
-DanWhat's probably happeneing is that without NOLOCK the queries were being
slowed down. Now with NOLOCK they run full out and are hitting resource
shortages elsewhere. Deadlocks are NEVER better.
How is your server nearly hanging? If you're trying to resolve deadlocks by
adding NOLOCK then it may be that your queries/updates have problems.
Are you accessing resourcing in the same sequence?
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Dan English" <dan_english2@.cox.net> wrote in message
news:OC8PKDE0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> We recently added NOLOCK hints to our less-important queries to cut down
> on deadlocks. It does decrease the deadlocks but it seemed to increase
> other process blocking... the type that nearly hangs the sql server. Is
> that possible? Has anyone else noticed? I am starting to think NOLOCK is
> a NO-NO, and that deadlocks are better.
> -Dan
>|||Thanks for the response. I've posted the offending stored proc in a new
post.
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:PlJ3f.136806$vt2.119755@.fe08.news.easynews.com...
> What's probably happeneing is that without NOLOCK the queries were being
> slowed down. Now with NOLOCK they run full out and are hitting resource
> shortages elsewhere. Deadlocks are NEVER better.
> How is your server nearly hanging? If you're trying to resolve deadlocks
> by adding NOLOCK then it may be that your queries/updates have problems.
> Are you accessing resourcing in the same sequence?
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria

Nolock

Will nolock on queries on the concerning tables will reduce blocking in DB ?
Assumption- I am ready to do dirty reads as I am just checking counts.It can, yes -- it will keep writes from blocking reads, but not writes from
blocking other writes, which can be an issue as well in some cases.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:5B762CA5-55B8-4F66-988E-1DCA6D71EA0F@.microsoft.com...
> Will nolock on queries on the concerning tables will reduce blocking in DB
> ?
> Assumption- I am ready to do dirty reads as I am just checking counts.

Nolock

Will nolock on queries on the concerning tables will reduce blocking in DB ?
Assumption- I am ready to do dirty reads as I am just checking counts.It can, yes -- it will keep writes from blocking reads, but not writes from
blocking other writes, which can be an issue as well in some cases.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rect" <Rect@.discussions.microsoft.com> wrote in message
news:5B762CA5-55B8-4F66-988E-1DCA6D71EA0F@.microsoft.com...
> Will nolock on queries on the concerning tables will reduce blocking in DB
> ?
> Assumption- I am ready to do dirty reads as I am just checking counts.