New Processes after upgrade

Hi There

After upgrading to Sql Server 2005 Enterprise Edition , SP1.

if i run the following query:

select * from sysprocesses where waittime <> 0 order by waittime desc

I get the following result:

spid waittime lastwaittype status

10 4480 0 0x007E 78424187 ONDEMAND_TASK_QUEUE background
4 5620 0 0x009D 78418906 KSOURCE_WAKEUP background
16 4512 0 0x00A9 78418890 BROKER_TRANSMITTER background
17 4516 0 0x00A9 78418875 BROKER_TRANSMITTER background
12 5056 0 0x00AD 78418796 BROKER_EVENTHANDLER background
11 5728 0 0x0081 179140 CHECKPOINT_QUEUE background

Basscially the waittime just keeps going up infinitely for these processes, is this normal, should i be worried ?

Thanx

Have same issue with BROKER_EVENTHANDLER and BROKER_TRANSMITTER.

Normal behavior?

|||

Hi

This link from microsoft describes the various Wait Types (of which there a lot more in SQL Server 2005):

http://msdn2.microsoft.com/en-us/library/ms179984.aspx

Here is some information from it about your specific items.

ONDEMAND_TASK_QUEUE waits for high priority requests (and high wait times simply indicates there have been none of these.)

KSOURCE_WAKEUP waits for requests from the Service Control Manager and long waits are to be expected (pause then unpause the SQL Server service to cause this to reset).

CHECKPOINT_QUEUE - This is the Checkpoint task waiting for the next checkpoint request (few checkpoints = large wait - and that's only 3 minutes).

BROKER_* - These are associated with the service broker and whilst it does not specifically say, I suspect if you do not have service broker servicing requests then these just wait and wait.

If you are interested in monitoring blocking then you might like to read this knowledge base article:

http://support.microsoft.com/kb/271509 - (LiveID required)

This describes sp_blocker_pss80 - a stored procedure to monitor blocked processes in SQL server 2000 up (it does however still produce these system processes in it's list).