Microsoft – SQLServer – Background Processes
Have a little bit of hide & seek with MS SQL Server … Needing to know what is going on with our background processes…
Quickly issued a Query:
select tblSysProcess.cmd , tblSysProcess.physical_io IOUsage , tblSysProcess.cpu as CPUusage , tblSysProcess.waittype as waitType , tblSysProcess.waittime as waitTime , tblSysProcess.lastwaittype as lastWaitType , tblSysProcess.waitResource as waitResource , tblySysProcess.dbid as databaseID , case when (dbid > 0) then db_name(dbid) else '' end as dbName , memusage as memUsage , status from master.dbo.sysprocesses tblSysProcess where ( --background process (tblSysProcess.spid < 50) )
In our case, here is what we found:
- Judging from what we saw in the physical_io and cpu columns, our biggest drag is our “DB Mirror” process
- The “Ghost Cleanup” process – The Ghost Clean-up process performs the actual work associated with performing deletes, drop columns, drop Indexes, auto-shrink. Basically tasks that can give the perception that the System understands the User’s request, acknowledges the requests, and performs \ complete the actual request (after hours)
- The “Checkpoint” process – Forces all dirty pages to be written to disk.
- “Service Broker” – “BRKR Task”
a) Database Mirroring uses “Service Broker” and so even when you are
not using “Service Broker” for User Sessions, “Service Broker” is still
used for Database Mirroring
- The Memusage column always returns 0. That is, for system processes, the memusage column is not populated \ tracked by the SQLEngine
To get in-depth reading of what is going with background processes:
– Please use dbcc trace flags
DBCC TRACEON (8721, –1);
DBCC TRACEON (3502, 3605, 3504, –1);
As this DBCC TraceFlags will log info in to the errorLog, please keep a watchful eye on your errorlog.
Use “dbcc tracestatus” to determine which TraceFlags are in effect. And, use DBCC Traceoff (<N>) to turn off individual TraceFlags.
1) What is the Ghost Process and what is it doing
2) BUG: A Failed Assertion Is Generated During a BULK INSERT Statement
3) Ghost Clean-up task uses 100% CPU
1) Checkpoints and Active Portion of the Log
2) How to monitor Checkpoints
3) MSDN Blogs > Microsoft SQL Server troubleshooting > SQL Server checkpoint problems – Joao Loureirot
4) FIX: I/O requests that are generated by the checkpoint process may cause I/O bottlenecks if the I/O subsystem is not fast enough to sustain the IO requests in SQL Server 2005 (Describes using -k <N> to throttle IO burst that occurs during Checkpoints)
1) A very strange Service Broker and TempDB problem
2) How to know what BRKR TASK (or any background task for that matter) is doing?