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?