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:


select 
            tblSP.cmd

        , [IO] 
			=tblSP.physical_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 ''
             end

        , [memUsage]
			= memusage

        , [status]
			= tblSP.[status]

from  master.dbo.sysprocesses tblSP

where 

        (

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

 

Indepth

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.


References:

Ghost Process

  1. What is the Ghost Process and what is it doing
    Link
  2. BUG: A Failed Assertion Is Generated During a BULK INSERT Statement
    Link
  3. Ghost Cleanup task uses 100% CPU
    Link

Checkpoint

  1. Checkpoints and Active Portion of the Log
    Link
  2. How to monitor Checkpoints
    http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-Link
  3. MSDN Blogs > Microsoft SQL Server troubleshooting > SQL Server checkpoint problems – Joao Loureirot
    Link
  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)
    Link


Service Broker

  1. A very strange Service Broker and TempDB problem
    Link
  2. How to know what BRKR TASK (or any background task for that matter) is doing?
    Link

 

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s