SQL Server High CPU caused by trace filter

  • [アーティクル]

Consider the scenario where you take several memory dumps from SQL Server because you are getting very high CPU without apparent reason. After looking at this memory dumps you notice that almost every thread has a call stack similar to the one below then you might be facing the issue described at https://support.microsoft.com/kb/953496 (FIX: CPU utilization is high when you run a trace that contains a text filter in SQL Server 2005).

374 Id: 11e0.1a10 Suspend: 0 Teb: 7fe37000 Unfrozen

ChildEBP RetAddr Args to Child

79bcef70 7c827bab 77ea4914 00007318 000093b4 ntdll!KiFastSystemCallRet

79bcef74 77ea4914 00007318 000093b4 00000000 ntdll!ZwSignalAndWaitForSingleObject+0xc

79bcefe8 010021a1 00007318 000093b4 ffffffff kernel32!SignalObjectAndWait+0xaf

79bcf00c 01002931 4805a040 4805a040 7293a0e8 sqlservr!SOS_Scheduler::Switch+0x81

79bcf180 010029ad 42bbc0e8 7293a0e8 0b9720a4 sqlservr!SOS_Scheduler::SwitchContext+0x2f3

79bcf198 01001c57 4805a040 00000102 7293a0e8 sqlservr!SOS_Scheduler::SuspendNonPreemptive+0xb9

79bcf1b4 01001edc 7293a0e8 ffffffff 0b972060 sqlservr!SOS_Scheduler::Suspend+0x2e

79bcf1d8 0134e44d 00001a10 ffffffff 0b9720a4 sqlservr!SOS_Event::Wait+0x12a

79bcf224 01276e37 7293a0e8 4805a040 0b9720a4 sqlservr!SOS_UnfairMutexPair::LongWait+0x110

79bcf248 010055d2 7293a0e8 0b9720a4 00000000 sqlservr!SOS_UnfairMutexPair::AcquirePair+0x41

79bcf268 0173a4c1 0b972040 0000263a 0128c3d4 sqlservr!CMemThread::Alloc+0x42

79bcf2ec 0173a889 0b989540 2c9d31f8 0000000a sqlservr!CTraceFilter::FComparePattern+0xea

79bcf318 0147baa2 0b989540 61b44040 2c9d3860 sqlservr!CTraceFilter::FCompare+0x3d

79bcf35c 0145d9d6 61b4b07c 79bcf7f8 79bc45b9 sqlservr!CFilterGroupCollection::FPassesFilters+0x1f7

79bcf780 0145e29d 0000000a 79bcf7f8 79bcf7a8 sqlservr!CTrace::WriteRecord+0x1cc

79bcf824 01742709 0000000a 2c9d2278 00000000 sqlservr!CTraceController::ProduceRecord+0x501

79bcf86c 01b869d6 79bcf8e8 79bc4af1 2c9d2278 sqlservr!PostResetConn+0xc0

79bcf8c8 01b86fc1 79bc4b61 2c9d2278 79bcf9a8 sqlservr!CAutoPostResetConn::~CAutoPostResetConn+0x7a

79bcf958 01b87479 2c9d2278 79bcfb40 79bc4821 sqlservr!FCleanSessionForReuseKeepCtxt+0xfc

79bcfa18 010da3e4 2c9d2ab8 2c9d2a18 0f287eb0 sqlservr!ResetConnAndRedoLogin+0x15e

I can´t post the details about the next step (finding the trace and filters) because I used private symbols but after you see this call stack I suggest that you try to find what traces are running in your environment at the time the issue occurred.

Have fun!!!

Bruno