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:
- 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 SQL Engine
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 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.
- What is the Ghost Process and what is it doing
- BUG: A Failed Assertion Is Generated During a BULK INSERT Statement
- Ghost Cleanup task uses 100% CPU
- Checkpoints and Active Portion of the Log
- How to monitor Checkpoints
- MSDN Blogs > Microsoft SQL Server troubleshooting > SQL Server checkpoint problems – Joao Loureirot
- 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)
- A very strange Service Broker and TempDB problem
- How to know what BRKR TASK (or any background task for that matter) is doing?