SIMS Server CPU nearly at Capacity

[phildyer]

Hi all

 

I'm having an issue with my SIMS server. It is constantly running at between 80 and 99%. Its definitely something to do with SQL 2014 but I'm not sure why its started all of a sudden. The server is a virtual machine running in hyper v 2012 and has 6 AMD Opteron 2.59GHz cpu's and 16GB of ram:

 

I'm not great at SQL so I'm a bit lost where to look. Any help would be appreciated.

[robsonma]

Hi all

 

I'm having an issue with my SIMS server. It is constantly running at between 80 and 99%. Its definitely something to do with SQL 2014 but I'm not sure why its started all of a sudden. The server is a virtual machine running in hyper v 2012 and has 6 AMD Opteron 2.59GHz cpu's and 16GB of ram:

 

[ATTACH=CONFIG]37792[/ATTACH]

I'm not great at SQL so I'm a bit lost where to look. Any help would be appreciated.

 

Couple of Options, give the Server a Re-boot and see if the problem is still there. could be something stuck in a loop

[Mark182]

Have a look here

[ITJS2015]

I would recommend you upgrade your RAM to 32GB , I know at my last secondary school that SQL database was getting used a lot and it was maxing out

 

I know it may cost in the long run but at least you wont have any problems after this

[zag]

I would recommend you upgrade your RAM to 32GB , I know at my last secondary school that SQL database was getting used a lot and it was maxing out

 

I know it may cost in the long run but at least you wont have any problems after this

 

I've found reducing the ram actually improves cpu usage. This is because SQL server will use up any amount of ram and this causes issues with processor speed.

[bobsmith]

I would recommend you upgrade your RAM to 32GB , I know at my last secondary school that SQL database was getting used a lot and it was maxing out

 

I know it may cost in the long run but at least you wont have any problems after this

 

But if you look at his memory usage stats it's nowhere near capacity.

[ITJS2015]

Seems very odd reducing is makes it better

[jinnantonnixx]

Seems very odd reducing is makes it better

 

It leaves the the OS with enough memory to perform effectively. If all available memory is consumed by SQL server, it will choke the OS causing paging to disk.

https://www.brentozar.com/blitz/max-memory/

 

P.S. Only make one change at a time. Otherwise, you won't know what worked.

Edited July 6, 2016 by jinnantonnixx

[phildyer]

Thanks guys its a VM so I've made a checkpoint. I'm going to make the changes at 3pm as staff get visibly upset if you make changes to SIMS while its live.

[bobsmith]

It leaves the the OS with enough memory to perform effectively. If all available memory is consumed by SQL server, it will choke the OS causing paging to disk.

https://www.brentozar.com/blitz/max-memory/

 

P.S. Only make one change at a time. Otherwise, you won't know what worked.

 

Just to clarify, it's reducing the RAM available to SQL, not the VM itself right?

[zag]

I reduced the ram on the entire VM. Think ours is 10gb at the moment.

[pantscat]

Make sure that you set a RAM usage limit in SQL - this will help it stop eating up all available RAM - generally speaking, with SIMS, I find that you'll want to allocate 1.5x the size of the MDB file.

[jinnantonnixx]

Just to clarify, it's reducing the RAM available to SQL, not the VM itself right?

 

That's right. If the Windows OS has little memory, it can reclaim what it needs by asking SQL server to relinquish memory, but this is a comparatively very slow operation. it's always best to ensure that the Windows OS has a comfortable amount of memory in the first place by setting the MaxMemory of SQL Server. Don't reduce the VM memory!

[dapaulio]

Its definitely something to do with SQL 2014 :

 

Check you are running SQL in compatibility mode and hasn't been inadvertently switched back out of compatibility mode

Edited July 7, 2016 by dapaulio

[phildyer]

Morning guys, it looks like its already running in compatibility mode.

 

 

Our LEA suggested adding a line into the connect.ini:

 

CommandTimeout=600

 

This only seems to affect the workstations though and the server is still going hell for leather.

[jinnantonnixx]

A couple of gotchas which may have been overlooked. SQLExpress will only use one physical CPU socket. If you have configured your VM to use two physical sockets, only one will be used. However, it can use four cores, so you can configure your VM to use four cores of one physical socket for maximum performance from your system.

 

If you have SQL Standard edition, it will use a maximum of four physical sockets or 24 cores, whichever is the lesser.

Here's a chart:

https://msdn.microsoft.com/en-us/library/ms143760.aspx?f=255&MSPPError=-2147217396

 

If you're OK with SQL queries, this will show the CPUs available to your SQL server. Everything should have a '1' in the 'is_online' column. If not, you've misconfigured your VM CPUs and you're not firing on all cylinders.

 

*** If you're NOT OK with SQL queries, don't run it. ***

 

select cpu_id,scheduler_id,is_online, current_tasks_count, status  from sys.dm_os_schedulers
where status IN ('VISIBLE OFFLINE', 'VISIBLE ONLINE')

 

 

 

Assuming none of this applies, your next step should involve SQL Activity Monitor. This is useful to get an overview of your SQL system.

 

Have a look at the 'Waiting Tasks' graph. In an ideal world, this should be 0. Real world values of 2-3 are OK, anything higher shows that your system has a bottleneck.

 

A good place to start is here:

Activity Monitor in SQL Server

 

More in-depth:

Monitor SQL Server queries ? find poor performers ? Activity Monitor and Data Collection - SQL solution center

Edited July 8, 2016 by jinnantonnixx