SIMS VM Running at 100% CPU

[NickLeonUK]

Hi All,

 

First off I'm relatively inexperienced but attempting to resolve this issue myself for a little extra credit from my manager.

 

We're using SIMS, running on a Server 2012 R2 VM with 24GB RAM and an 8 Core Xeon E5 @ 2.1GHz and for the last three weeks plus its been running at 100% of its CPU capacity.

The one process hogging a good ~95% of this is the SQL Server.

In itself it wouldn't be a problem but users are reporting SIMS being painfully slow, and although I expect our users to exaggerate at least a little, they're talking about 20-30 minutes to generate a single report with only a little data in it.

 

I wanted to just get some additional opinions before we just increase the allocated CPU Power. Is this normal, something you've seen before or something you might have a solution to?

 

Images shown below for additional info:

 

Thanks in advance for any opinions or suggestions!

 

Cheers - Nick

[Esteban_Child_of_the_Sun]

In short, no that isn't normal.

 

If you open SQL Management Studio, and open the Activity Monitor.

Under Processes, sort the list by Total CPU (ms), what is the highest process(es) what login, database, command (and if applicable wait type) are listed?

Under Resource Waits, what is the highest current and recent wait time and type?

Under Active Expensive Queries, what is the query?

Edited March 19, 2019 by Esteban_Child_of_the_Sun

[Norphy]

Do you have SQL Server Management Studio installed somewhere? If so, open it, connect it to the SIMS database and run the activity monitor. You might be able to get an idea of what's eating your CPU time there. There are probably some big reports running.

 

/edit hah, ninja'd

[NickLeonUK]

@Esteban_Child_of_the_Sun & @Norphy

 

Thanks for the advice, unfortunately I couldn't follow very far! Upon opening the activity monitor, it all freezes and I am presented with a lovely error box! - See here: https://pastebin.com/EhYgA5j2

[jinnantonnixx]

Are you OK using SQL queries from the Management Studio?

If so, this will tell you the task count and the 'visibility' of each CPU/core from SQL Server.

 

 

You want to see VISIBLE ONLINE for each of your assigned CPUs, a '1' for the 'is_online' field and a low-ish task count less than 5 tasks per CPU, ideally.

 

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

Edited March 19, 2019 by jinnantonnixx

[Esteban_Child_of_the_Sun]

If it's gotten to that point it may be best to find a quiet time when you can re-start the SQL service, then open the management studio and activity monitor when it comes back up and watch as it fills up again. That will throw anyone out of SIMS who is actively using it.

[NickLeonUK]

@Esteban_Child_of_the_Sun

 

Thanks for the advice, I'll see if I can get a chance to take it down today, and I'll update here. Thank you for you help!

[Sonic007]

We regularly restart the SQL server as it can get huge and slow things down. I have a batch file that stops and starts it that runs once a week.

[jinnantonnixx]

Just to check the obvious.... your OS, database files, log files and tempdb files should be on different drives/LUNs. Don't invite bottlenecks; keep busy I/O on separate channels.

 

https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/place-data-and-log-files-on-separate-drives?view=sql-server-2017

 

 

 

Are your users running some insane widgets recompiling reports every minute on their desktops? Stuff like that can drag down your server.

 

 

The script below lists the top users by CPU in the SQL realm, over a set time period. The script uses 10 seconds, but you can change that value in the line 'waitfor delay .......'

 

Provided as is, up to you if you use it, but I've found it useful.

 

At least it might help identifying the user(s) chewing up your CPU....

 

use master
go

-- drop temp tables if they exist
IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1
IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2


-- build first table 
Select db1.name as 'DBName',
   spid,
   pr1.status,
   cmd,
   nt_username,
   pr1.loginame,
   hostname,
   program_name,
   cpu
into ##cpuhog1
from sysprocesses pr1 (nolock)
join sysdatabases db1 (nolock)
on pr1.dbid = db1.dbid
where name not in ('master', 'msdb', 'model')
order by pr1.spid

-- wait for a period of time
print 'Waiting for a while...'
waitfor delay '00:00:10'	-- 'hh:mm:ss', you choose the delay


-- build second table
Select db2.name as 'DBName',
   spid,
   pr2.status,
   cmd,
   nt_username,
   pr2.loginame,
   hostname,
   program_name,
   cpu
into ##cpuhog2
from sysprocesses pr2 (nolock)
join sysdatabases db2 (nolock)
on pr2.dbid = db2.dbid
where name not in ('master', 'msdb', 'model')
order by pr2.spid

-- debug - check the two tables, later one sometimes shows lower CPU values - weird 
-- select * from ##cpuhog1
-- select * from ##cpuhog2


-- two tables made, calulate the differences between them and order by CPU
select t1.DBName as 'Database Name',
t1.spid,
t1.status,
t1.cmd as 'Command',
t1.nt_username as 'Windows Username',
t1.loginame as 'SQL Login',
t1.hostname as 'Machine Name',
t1.program_name,
t2.cpu - t1.cpu as CPUDiff
from ##cpuhog1 t1
	 join ##cpuhog2 t2
	on 
		t1.DBName = t2.DBName
		and t1.loginame = t2.loginame
		and t1.hostname = t2.hostname
	order by CPUDiff desc
	
-- drop temp tables if they exist
IF OBJECT_ID('tempdb..##cpuhog1','U') IS NOT NULL DROP TABLE ##cpuhog1
IF OBJECT_ID('tempdb..##cpuhog2','U') IS NOT NULL DROP TABLE ##cpuhog2

Edited March 19, 2019 by jinnantonnixx

[matt40k]

@NickLeonUK What version of SQL Server is it? Be worth logging a ticket with Capita \ your local SIMS support team. Get them to run the reindex patch.

[LTurner3692]

Sounds like your SQL isnt capped, so just keeps eating away at the CPU untill it fills it! mine use to do that so i started restarting it one a week/fornight

[jinnantonnixx]

I've been out of the SIMS scene for a while (different department now) but this might still be an issue - it's do to with the cardinality estimator in SQL, and how setting your SQL compatibility to 2012 improves performance significantly.

 

http://www.edugeek.net/forums/mis-systems/147490-sims-slow-after-upgrade-sql-2014-a.html

 

These posts seem to suggest that it's still an issue, but of course I may be wrong.

 

https://myaccount.capita-cs.co.uk/forums/thread/kk-lastSIMS-technical-su/cf533f8d-91bd-e811-80f2-000d3a2632b4

Edited March 19, 2019 by jinnantonnixx

[supportman]

Right click on the Sims database in SQLserver manager >> reports >> standard reports >> user statistics.

 

That should show you which user is taking up all the CPU time. Very useful report!

 

Also you can check live with Activity monitor. Just load up SQLserver manager, right click on the root of the SQL server in the tree >> Activity monitor

 

We actully found a lot of the CPU time was being used up by Solus_deployment_server Database for some reason.

Edited March 19, 2019 by supportman

[Snuffkins]

I would not give it more vCPUs. It would make it worse. In fact having 8 will most likely not be helping either. Setting an amount does not mean that it has exclusive access itself, so it could also be slowing down other guests on the host as well on top of performing badly. I would be tempted to reduce that and perhaps if you are using Hyper-V it might be worth setting the resource control relative weight too.

 

I would definitely follow advice with regards to running the re-index patch. Might be worth setting up a maintenance plan to reindex and update statistics as well. There is a pdf guide, just google Sims maintenance plan and you should find it. Also make sure you have a max memory limit set as that can cause the issue as well.

 

I would start with the reindex and memory limit and see if that makes a difference first, especially if it has been working ok up to now.

Edited March 19, 2019 by Snuffkins

[supportman]

Any ideas on how to set the Maximum memory limit for SQL server?

 

Our server needs to be rebooted after a few months when it uses up all the memory so it would be good to prevent this.

 

EDIT: Google to the rescue! https://support.laserfiche.com/kb/1011758/setting-a-maximum-memory-limit-for-a-sql-server-instance

Edited March 19, 2019 by supportman

[jinnantonnixx]

Any ideas on how to set the Maximum memory limit for SQL server?

 

Our server needs to be rebooted after a few months when it uses up all the memory so it would be good to prevent this.

 

EDIT: Google to the rescue! https://support.laserfiche.com/kb/1011758/setting-a-maximum-memory-limit-for-a-sql-server-instance

 

 

A good guide here, with recommended limits for different memory configurations.

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