Microsoft – SQLServer – Background Processes

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:


        , [IO] 

        , [CPUusage]
			= tblSP.[cpu]

        , [waitType]
			= tblSP.waittype

        , waitTime
			= tblSP.waittime

        , lastWaitType
			= tblSP.lastwaittype

        , waitResource
			= tblSP.waitResource

        , databaseID
			= tblSP.[dbid]

        , dbName
			= case
                when (dbid > 0) then db_name(dbid) 
                else ''

        , [memUsage]
			= memusage

        , [status]
			= tblSP.[status]

from  master.dbo.sysprocesses tblSP



            --background process    
            (tblSP.spid <= 50)


In our case, here is what we found:

  1. Judging from what we saw in the physical_io and cpu columns, our biggest drag is our “DB Mirror” process
  2. 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)
  3. The “Checkpoint” process – Forces all dirty pages to be written to disk.
  4. “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
  5. The Memusage column always returns 0.  That is, for system processes, the memusage column is not populated \ tracked by the SQL Engine



To get in-depth reading of what is going with background processes, please use dbcc trace flags

  1. AutoStats
    DBCC TRACEON (8721, –1);
  2. Checkpoint
    DBCC TRACEON (3502, 3605, 3504, –1);

As this DBCC Trace Flags will log info into the error Log, please keep a watchful eye on your errorlog.

Use “dbcc tracestatus” to determine which Trace Flags are in effect.  And, use DBCC Traceoff (<N>) to turn off individual TraceFlags.


Ghost Process

  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 Cleanup 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)

Service Broker

  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?


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s