Solved

DEFWAKEUP status in sysprocesses with waittype = 0x0000

Posted on 2004-04-21
8
3,388 Views
Last Modified: 2007-12-19
I am getting DEFWAKEUP status in sysprocesses which tells me that the SPID is waiting on a resource, yet the waittype = 0x0000 and the lastwaittype = MISCELLANEOUS.

Any clue about what the SPID is waiting on?

Thanks,
Rick
0
Comment
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10878709
Any backups or scheduled jobs running?  
0
 

Author Comment

by:essentialdatasolutions
ID: 10878850
No backups or scheduled jobs are running.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10879363
Is this process using a paralell plan?  Multiple ECIDs for the SPID you're looking at?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 34

Expert Comment

by:arbert
ID: 10879674
Usually on a parallel plan, you'll see a CXPacket wait.  HOWEVER, you can also see a defwakeup if one of the threads happen to be contending with another thread of the same process.
0
 

Author Comment

by:essentialdatasolutions
ID: 10880277
It is a parallel plan with Multiple ECIDs
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 10880428
Ok, that's what it is then....

Is this a query you can run in analyzer?  If so, run the query and at the end add

option(maxdop 1)


See if you notice any difference.  However, the waits are just a nature of the parallel plans.
0
 

Author Comment

by:essentialdatasolutions
ID: 10880804
The query is a stored proc that is rather complex with nested procs and some cursors.  Earlier I wanted to add the maxdop 1 to take out the parallelism but this would involve adding it to several dozen queries.  I decided to investigate the DEFWAKEUP without a waittype first.

Thanks,
Rick
0
 
LVL 34

Expert Comment

by:arbert
ID: 10881425
You can also go into the server properties and up the value used to figure out when to use paralellism....
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question