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

  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 SQLEngine

To get in-depth reading of what is going with background processes:

– Please use dbcc trace flags
a) AutoStats
DBCC TRACEON (8721, –1);

b)  Checkpoint
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.


References:

Ghost Process

1) What is the Ghost Process and what is it doing
http://sqlserverpedia.com/blog/sql-server-service-internals-and-architecture/164/ 

2) BUG: A Failed Assertion Is Generated During a BULK INSERT Statement
http://support.microsoft.com/kb/815594 

3) Ghost Clean-up task uses 100% CPU
http://support.microsoft.com/kb/978430/en-us 

Checkpoint

1) Checkpoints and Active Portion of the Log

http://technet.microsoft.com/en-us/library/ms189573(v=sql.105).aspx

2) How to monitor Checkpoints

http://www.sqlmag.com/blog/sql-server-questions-answered-28/sql-server/how-to-monitor-checkpoints-137148 

3) MSDN Blogs > Microsoft SQL Server troubleshooting > SQL Server checkpoint problems – Joao Loureirot

http://blogs.msdn.com/b/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx

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)

http://support.microsoft.com/kb/929240


Service Broker

1) A very strange Service Broker and TempDB problem

http://itknowledgeexchange.techtarget.com/sql-server/writeup-of-a-strange-service-broker-and-tempdb-problem/

2) How to know what BRKR TASK (or any background task for that matter) is doing?

http://social.msdn.microsoft.com/Forums/sa/sqlservicebroker/thread/f6e93024-efac-4df4-a3df-8c03670a1266 

 

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