[sims] Sims SQL DB using high amount of ram (15.7GB)

[IT_Man_Dan]

Dear all,

 

Lately our sims has been running very slow and has crashed out on several workstations (I've noticed from a similar thread that others have also had issues). This appears to be happening since the last update although it could be coincidental.

Currently I've noticed that the sqlservice (sims2012) is using 15gb of the servers 16gb of ram constantly..

 

I've contacted capita and have ran a patch they gave me (14265_reindex) but I haven't noticed any difference in performance.

We are running the latest sims version on Windows Server 2008r2 (all updates installed) with SQL Server 2012 SP2 installed.

 

I've updated Capita about the issue and am awaiting a call back but wondered if any of you had any ideas whilst I wait?

 

Thanks

[SHimmer45]

SQL unless you put limitations on how much ram it uses will use as much memory as it can find.

[winng]

As @SHimmer45 said. Go into SQL Server Management Studio, right-click on servername\ (top level) and select properties. Select Memory and enter a maximum server memory value, i.e. 10240, etc.

[IT_Man_Dan]

Thanks guys.

 

I was just reading something similar on the microsoft site about it. I guess that's not the cause of my problems then.

 

Back to the drawing board regarding what's causing our sims issues then.

[SHimmer45]

i would start by capping the SQL mem usage to see if it improves performance, if it does or doesnt it will give you a "baseline" to work from.

is the server "overcommitted" on the memory usage and then having to "thrash" the page file?

[IT_Man_Dan]

Thanks, I've just restricted it to 10240 as recommended and will monitor it over the next few hours. I suspect that the server could have been overcommitted as you suggest.

 

Many thanks

[Ghostgame]

Check the SQL Edition you have installed.

If you have the Express version of SQL, its limited to 1 GB of RAM and 4 cores.

(Doesn't matter how much RAM the service sucks up, Express is only allowed to use 1 GB of it)

[SHimmer45]

a long time ago on some smaller SQL 2005 databases i found this page Suggested Max Memory Settings for SQL Server 2005/2008 - SQLServerCentral useful for giving a guide on memory settings for SQL depending on how much system ram is installed.

not a hard or fast rule but appeared to work well enough. Some of the options arent going to be relevant to SQL2012.

im sure capita will be able to provide you with specific information.

i know from when we had them carry out a migration they left our SIMS SQL on the default setting for RAM (our server does have 32GB, it was on 16GB at the time and was using 90% of the available RAM)

[IT_Man_Dan]

Well it's been almost 24 hours since restricting the ram. Although the server now has sufficient available ram there has been no noticeable performance improvements with sims, in fact I've just had the data manager on the phone saying that various sims tasks that normally wouldn't take too long to run are taking ages. as of yet I haven't had any response at all from Capita enhanced service desk regarding my support call 24 hours after reopening it.

[Ghostgame]

What edition of SQL 2012 are you on?

[IT_Man_Dan]

Hi @Ghostgame it is the standard edition.

 

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)

May 14 2014 18:34:29

Copyright © Microsoft Corporation

Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

[matt40k]

How big is the sims .MDF and the .LDF? I suspect your LDF is swollen which is causing the performance problems. If it is, you'll need to check your backup process as it should be clearing down the LDF once its backed up. It's also worth checking the disc queue length - as this could be where the bottleneck is (which could be caused by the swollen LDF size). Personally, I wouldn't limit the RAM SQL needs, it will eat what it needs. The only time you put a hard limit on is when you have multiple services on the same server - which you shouldn't be doing nowadays what with virtualizations.

[lholland421]

I thought I would share my recent experience of sims.net performance issues. I have just migrated our sims.net to SQL2014 which seems fine. We started having users unable to login or very slow at peak times. I contacted capita and was given a couple of housekeeping tasks to do but the issue was still there.

 

I decided to disable all of our extra exporting to other platforms to see if that would help. So I disabled group calls xporter and sims.net started to run as it should with no performance issues,

 

I have contacted group call and it turns out that one of the reports on one of the jobs was taking too long to run and bringing sims,net to a halt.

 

Might be worth disabling any other systems to link in with sims,net to see if it improves performance, I have since ran a reindex patch that seems to have sorted the issue for now.

[RowlandoGloom]

I had a similar issue running SQL 2014 with SIMS consuming all available memory, 16,24,even32Gb.

 

Running the DB in 2008 compatibility mode reduced this problem and is now only taking about 6-10Gb with 16 allocated to the VM.

 

I'm not sure about 2012 if it's a similar problem, but you can try and give it a go.

Edited October 11, 2015 by RowlandoGloom

[jinnantonnixx]

You should always set memory limits on SQL server. If not, it will consume whatever it is offered to the detriment of the operating system, leaving it less responsive as it struggles to reclaim memory.

Note the difference introduced from SQL 2012.

Pre SQL Server 2012, this setting really is setting max memory for the buffer pool but folks have come across instance where they set the max memory setting and yet SQL Server shows it’s actually using more memory than that. Starting with SQL Server 2012, this setting actually dictates how much SQL Server (buffer pool + everything else) can use so it’s less confusing.

http://www.sqlservercentral.com/blogs/sqlchicken/2012/02/28/monday-morning-mistakes-not-setting-memory-limits/

 

 

I wouldn't bother shrinking your log files (LDF files) unless they have ballooned to absurd levels. In most cases, a large log file will not harm query performance, though it can cause issues with backup performance and other operations that read the log. This article is the best I've seen on the subject and everyone who has to work with SQL server should read it.

http://www.sqlservercentral.com/articles/Administration/64582/

Log size

 

A large log file will not cause performance problems for queries in the database. It is possible that a very large number of Virtual Log Files will result in slower than expected log backups and other operations that read the log, but that's not a problem of large log, that's a result of growing the log file in small intervals

 

and

 

Shrinking the log

 

In general, the transaction log should not be shrunk. It certainly should never be shrunk on a regular basis in a job or maintenance plan.

 

The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.

 

Shrinking the log on a regular basis will have just one effect - the log growing again once regular activity on the database requires the old size. Transaction log grow operations are not fast, they cannot take advantage of instant initialisation and hence the new portion of the log will always have to be zeroed out. The other effect of the log repeatedly growing is that unless the auto-grow setting has been carefully chosen, the growth of the log will result in log fragmentation - excessive VLFs that can degrade the performance of backups, restores, crash recovery, replication and anything else that reads the log.

 

Don't shrink the log regularly. Shrink only if something has blown the size of the log far beyond what it needs to be.

Edit: You have to register with SQLServerCentral to read this, but it's well worth it, as well as signing up to their newsletter. They also have an excellent Powershell+SQL section.

 

 

 

 

 

Are you OK running SQL queries? If not don't, but this query shows all the CPUs on your system, their on-line status and the runnable_tasks_count. It will show a status line for each of the CPUs on your server.

All the CPUs should have a '1' in the is_online field, and the status should be 'VISIBLE ONLINE'.

The runnable_tasks_count is an important measure as it shows tasks that are twiddling their thumbs waiting for CPU time to become available to them. In an ideal world, the runnable_tasks_count should show 0, but real-world values under about 5 are reasonable. Higher values are indicative of CPU pressure.

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

 

If you have configured a system with more sockets than is allowed by SQL Server (4 sockets, in the case of SQL 2014 standard) then you'll see some CPUs flagged as 'VISIBLE OFFLINE' and you're wasting these CPUs. Reconfigure to bring the socket count to within licensing parameters and up the cores-per-socket.

Edited October 12, 2015 by jinnantonnixx

[SimonCygnetIT]

From my experience try restarting the SQL Service. That should then reset the memory that is uses. If that does not improve performance check what your HDD Disks on the server and see if they are very busy, especially the disk your MDF and LDf are on . Also check the CPU amount that SQL is using. If it is sitting quite high very often then that may be an issue. Also check to see if anything else on your Server is hogging CPU time.

[jinnantonnixx]

The antivirus is often overlooked. Make sure you have an exception for the data and log files.