1

I'm trying to diagnose an abnormal CPU usage of the sqlservr.exe process from a SQL Server 2012 Express installation.

The installation is running in a VM with Windows Server 2012 R2, 8-core Xeon E5 and 8GB of RAM.

The CPU load (from both ProcessExplorer and Task Manager) stays constantly at around 12%-15%. Restarting the service has no effect, the CPU usage will go right back at the same level a few minutes after restarting.

My biggest problem is that the SQL server was bundled with a third-party software and I don't have sa access to the DB. I was given read only access to the software's database (I can only access views, not tables) but I can't run any reports from SSMS or execute this.

The high CPU consumption definitely comes from the third-party application (if I stop it the load goes back to idle levels) but their support has been so far useless in resolving this issue and I need to deal with it on my own.

Is there anything else I can try to pinpoint the source of the high cpu usage?

Community
  • 1
tsmr
  • 11
  • 2
  • 1
    What is the error message returned when you run the statement given as an answer to the question [How do I find out what is hammering my SQL Server?](http://stackoverflow.com/a/945290/1820861)? – John K. N. Nov 21 '16 at 08:44
  • `Msg 297, Level 16, State 1, Line 1` `The user does not have permission to perform this action.` – tsmr Nov 21 '16 at 09:45
  • Sadly Express is delivered without SQL Server Profiler and other useful tools: [Features Supported by the Editions of SQL Server 2012](https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx#Scalability), but even if you had the tools you wouldn't see much with your current permissions. SQL Performance Counters might be an option, but you won't glean any details from them. Only estimates and hints. – John K. N. Nov 21 '16 at 11:38
  • 1
    IMO, 12%-15% CPU utilization is hardly "high". How do you know that's "abnormal" for this application? Why are you worried about it? The CPU is a resource to be used, SQL Server is using it, why is that a problem? – joeqwerty Nov 21 '16 at 12:13
  • Unfortunately I've had issues where the third-party application cannot connect to the SQL Server anymore and it's impacting some other equipment. Now what I've noticed is that after a specific operation performed in the application, the sqlservr.exe process goes from 1%-2% CPU load to 12%-15% and stays there, even after service or system restarts. So far the developer refuses to inspect our installation and blames the problem on system/hardware configuration issues. – tsmr Nov 21 '16 at 12:46
  • 1
    I would be very, very surprised if 12-15% CPU utilization was the cause of the problem. – joeqwerty Nov 22 '16 at 01:55
  • Can you find out the exact version of the SQL 2012 Express Edition? Service Pack Level, Cumulative Update Level, .... There have been issues with CPU spikes and some high CPU levels in some versions of SQL Server. [FIX: Low CPU spikes at a set interval even without user activity in SQL Server 2008 R2 or in SQL Server 2012](https://support.microsoft.com/en-us/kb/2590839) – John K. N. Nov 22 '16 at 06:46

1 Answers1

1

You would require at least either the view server state or view database state before you could think of analysing any performance issues inside SQL Server:

What can user do with VIEW SERVER STATE permissions?

Seeing as you are limited to the built-in Windows Performance Monitor you could monitor the following counters to establish any basic issues:

  • Memory – Available MBytes
  • Physical Disk – Avg. Disk sec/Read
  • Physical Disk – Avg. Disk sec/Write
  • Physical Disk – Disk Reads/sec
  • Physical Disk – Disk Writes/sec
  • Processor – % Processor Time
  • SQLServer: General Statistics – User Connections
  • SQLServer: Memory Manager – Memory Grants Pending
  • SQLServer: SQL Statistics – Batch Requests/sec
  • SQLServer: SQL Statistics – Compilations/sec
  • SQLServer: SQL Statistics – Recompilations/sec
  • System – Processor Queue Length

These counters are listed according to the following article SQL Server Perfmon (Performance Monitor) Best Practices.

Seeing as you are having a CPU load the following quote from Brent's article seems adequate:

Look for Obvious CPU Bottlenecks

First, look at the Processor Queue Length for CPU pressure. If this number is averaging 1 or higher (except during the SQL Server’s full backup window if you’re using backup compression), this means things are waiting on CPUs to become available.

I’m suspicious when this number is over 1, because it often means that people have installed other software on the SQL Server such as applications or web sites. That’s a problem. If you get pushback from management saying that they don’t want to buy new servers, point out that two CPUs of SQL Server Enterprise licensing cost around $50-60k – which would pay for a separate server for the web app. If you can eliminate applications from the SQL Server, then you don’t have to use as much CPU power, and less CPUs mean less licensing costs.

There are more in-depth Perfmon metrics that you can add to your capture if you see the Processor Queue Length showing up, but for junior DBAs, the first thing I would recommend is simply remote desktop into the SQL Server. Right-click on the taskbar, click Task Manager, and click on the Processes tab. Check the box that shows processes for all users, and then click on the CPU column to sort the CPU percentage from high to low. Sit and watch it for a minute or two. Which processes are using CPU power? If it’s SQLServer, then we need to do more research, but there’s a good chance it’s another application, and we need to get that app off this server. Generally speaking, enabling hyperthreading on a SQL Server is not going to fix that kind of a problem.

If the sqlserver.exe process is the only one with 12-15% load then you will require additional permissions to be able to analyse the root cause.

Community
  • 1
John K. N.
  • 1,955
  • 1
  • 16
  • 26