The last few days has being interesting for this particular MS SQL Server Instance.
One weekend afternoon I saw it so busy. As a DBA sometimes you learn a bit more from under-provisioned machines. I hope I can learn a bit from this one.
Here is what I found so far:
- Take it off the Internet. It was being attacked and their were numerous entries of failed logins (sa – username no less)
- Configured Symantec AntiVirus to exclude checking Network and MS SQL Server files; files with “known” SQL Server Extensions (mdf, ndf, ldf)
- Configured Symantec AV to exclude C:\WINDOWS\system32\NavLogon.dll (http://www.symantec.com/connect/forums/rtvscanexe-high-memory-usage-sav)
Though these changes are in place and server restarted, some simple queries were still timing out and SQL Instance was still registering high CPU even when no queries were being processed.
From experience I know that Full-Text Search sometimes works in the background, but that did not appear to be the case at this time.
So ran a simple query looking for which Session was using high CPU.
SELECT tblSysProcess.cpu as processor, * from master.dbo.sysprocesses tblSysProcess order by tblSysProcess.cpu desc
And, here is what we got back:
So after a couple of days running this same query, SPID 1 stayed on top of our list of top CPU users.
Took the easy way and Googled for “SQL Server High CPU SP 1” and ended up where it made sense.
High CPU Usage for SPID #1 (SQL Server 2008)
In this particular post, Leila (posting as Amin S) did all the background work and asked the same question I was going to ask:
I have SQL Server 2008 (SP1) on Windows Server 2003 (R2).
Sometimes this server becomes very slow and task manager indicates high CPU usage. I added all SQL Server related threads in Performance Monitor to monitor Processor Time% per Thread.
By correlating Thread ID and KPID, I realized that this abnormal increase for CPU usage is always related to SPID #1 which is system process thus cannot be killed. After restarting SQL Server, everything works fine but after few minutes, exactly the same situation happens and SPID #1 consumes almost whole capacity of processors. I tried different ways to determine the source of problem. First I took our main database (financial) to offline state, and turned it back online. All users for this database were killed and SPID #1 released the processor! Sometimes this solution does not work and I use a loop to kill all users in every database. This works in most of times, but today I encountered situation that even killing all users did not work and I forced to restart the SQL Server engine several times during the day.
I was wondering if somebody could suggest a test for further observation or any solution for this issue.
Many thanks in advance,
Like I said, Leila did all the work. A day later (s)he came back and answered his\her own question.
Leilia found a MS Link:
FIX: The CPU usage of the resource monitor is very high when the virtual memory is running low in SQL Server 2008
And so it seems the next thing to do is determine the OS Thread ID for the “Resource Monitor”. This query will do so:
SELECT STasks.session_id , SThreads.os_thread_id , b.command FROM sys.dm_os_tasks AS STasks INNER JOIN sys.dm_os_threads AS SThreads ON STasks.worker_address = SThreads.worker_address LEFT OUTER JOIN sys.dm_exec_requests b ON STasks.session_id = b.session_id WHERE STasks.session_id IS NOT NULL and command = 'RESOURCE MONITOR' ORDER BY sthreads.os_thread_id
I tried using “Performance Monitor” \ Perfmon. But, really not able to filter by Thread ID.
Probably should have tried SysInternals \ ProcessMonitor; as it has ability to observe at Thread ID, as well.
Need to address Memory Issue.
But, I think I have enough comfort level, that nothing will be hurt by upgrading from MS SQL Server v2008 SP1 to SP2.
Off we go.
- High CPU Usage for SPID #1 (SQL Server 2008)
- FIX: The CPU usage of the resource monitor is very high when the virtual memory is running low in SQL Server 2008