Question regarding state of processes in sysprocesses table

hi all,
I am having one question releated to processes in sysprocesses table.
What i want to ask is, is it possible that state of sleeping process
can go change to running state.
If yes, then say suppose i am running one time consuming stored
procedure. Then is it possible that once i start execution of this sp
it sill randomly goto running then sleeping or in idle state.
One more question, when the state of any process goes to sleeping, is
it like after execution of process completed running process goes to
sleeping.
And Is it possible that running process goes to idle state.
Because after executing stored procedure and checking sysprocess table
sometime i am getting entry for sp in sysprocess and some times not.
Please help me if i am wrong.
Thanks in advance.Archana (trialproduct2004@.yahoo.com) writes:
> I am having one question releated to processes in sysprocesses table.
> What i want to ask is, is it possible that state of sleeping process
> can go change to running state.
> If yes, then say suppose i am running one time consuming stored
> procedure. Then is it possible that once i start execution of this sp
> it sill randomly goto running then sleeping or in idle state.
> One more question, when the state of any process goes to sleeping, is
> it like after execution of process completed running process goes to
> sleeping.
> And Is it possible that running process goes to idle state.
>
I have a feeling that your problem is not the one you are asking about.
Normally, a "sleeping" process is a connection that is idle and running
any query. But I have seen "sleeping" also for processes that undeniably
are busy doing something. I have not investigated this in detail, but one
thing to watch out for is the ecid column. A spid can spawn several threads,
when parallelism is employed. And in such case, some threads can be sleeping
while waiting for others to complete, I guess.

> Because after executing stored procedure and checking sysprocess table
> sometime i am getting entry for sp in sysprocess and some times not.
Not sure what you mean here, since you are never able to see a stored
procedure as such in sysprocesses. You can of course see the process that
runs it, but if it disconnects after running the procedure, you may not
see it. (Here it's depends on whether connection pooling is in play.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
Thanks for ur reply.
Ya you are right that we never able to see stored procedure in
sysprocesees.
But See i am running only 4 sp. and then i am getting id of that
process and passing that id to dbcc inputbuffer to get information
about stored procedure along with its parameter.
So i can get total number of stored procedure currently running by
looking at process id.
My question is even after executing long running SP, sometime its entry
is displayed in sysprocess table and sometimes not.
Can u tell me why this is happening.
Any help will be truely appreciated.
Thaks in advance.|||Archana (trialproduct2004@.yahoo.com) writes:
> Ya you are right that we never able to see stored procedure in
> sysprocesees.
> But See i am running only 4 sp. and then i am getting id of that
> process and passing that id to dbcc inputbuffer to get information
> about stored procedure along with its parameter.
> So i can get total number of stored procedure currently running by
> looking at process id.
> My question is even after executing long running SP, sometime its entry
> is displayed in sysprocess table and sometimes not.
> Can u tell me why this is happening.
No, because I don't know how you run the procedures. Do you run them
from an application? In such case, what kind of application? How does
the code look like?
Or do you run them from Query Analyzer or Management Studio?
Best would be if you post a script that demonstrates the problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||hi,
thanks for your reply.
see suppose i have one stored procedure sp_SetId which is accepting one
int parameter say jobid,
So suppose through one application written in c#.net i am executing
this sp using sqlcommand.commandtext like exec sp_setid 1,
and otehr through sql query analysz.
When i see sysprocess table i get two ides with state as runable for
this stored procedure.
say suppose i am getting processid 65 and 66 for these sp. then to get
exactly which jobid is associated with processid i am calling dbcc
inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
get jobid.
This is all what i am doing.
Most of the time this result is proper.
But sometime even if my sp is running its entry is not getting
displayed in sysprocess table i don't know whether that procedure has
started its execution or not.
Can you tell me what am i doing wrong?
Thanks|||Archana (trialproduct2004@.yahoo.com) writes:
> see suppose i have one stored procedure sp_SetId which is accepting one
> int parameter say jobid,
> So suppose through one application written in c#.net i am executing
> this sp using sqlcommand.commandtext like exec sp_setid 1,
> and otehr through sql query analysz.
> When i see sysprocess table i get two ides with state as runable for
> this stored procedure.
> say suppose i am getting processid 65 and 66 for these sp. then to get
> exactly which jobid is associated with processid i am calling dbcc
> inputbuffer ( 65) where i am getting eventinfo from whihc i can easily
> get jobid.
> This is all what i am doing.
> Most of the time this result is proper.
> But sometime even if my sp is running its entry is not getting
> displayed in sysprocess table i don't know whether that procedure has
> started its execution or not.
If the spid is not present in sysprocesses, that spid is obviously
not running the stored procedure. Or any other stored procedure for
that matter. I would be inclined to assume that you are mistaken about
the spid for your procedure.
If all you want do to is to associate spid with jobid, I suggest that
you add a table:
CREATE TABLE spidjobids
(spid int NOT NULL,
CONSTRAINT default_spid DEFAULT @.@.spid,
started datetime NOT NULL.
CONSTRAINT default_spidjobs_started DEFAULT getdate(),
jobid int NOT NULL,
CONSTRAINT pk_spidjobs PRIMARY KEY (spid, started),
CONSTRAINT u_spidjobs UNIQUE (jobid, started)
)
And then modify the procedure to read from that table.
If this not feasible, maybe my procedure aba_lockinfo can be useful.
This procedure gives you the process information and DBCC INPUTBUFFER in
one go. You find it at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx