[sims] Slow response - how to benchmark?

[limawhiskey]

We are having issues where SIMS is particularly slow lately. We're running a VM with Windows Server 2008 R2 and MS SQL 2012. I think it's a combination of factors:

 

 

1. We have a now ageing VM infrastructure, bought in 2010 (I have been trying to get a budget for 18 months to get this updated - no dice). CPU is usually maxed (as reported by the guest OS) during the school day.
   
2. This year the school began using SIMS for behaviour monitoring which it wasn't before.
   
3. MIS (dedicated VM) was upped to 8 GB RAM in the summer in anticipation, but SQL almost all of that, now upped to 16 GB and hovering around 10 GB in use (reported by the guest OS). Can't go any higher as it's taxing the memory pool we have available (see point 1). I was told 16 GB is what would be expected for a school our size (~1500 students).
   

 

I have given the VM high priority for CPU and memory. Though the guest OS reports high CPU, vSphere reports that host CPU usage is relatively low.

 

LA SIMS Support have recommended restarting SQL service each day.

 

This seemed interesting and I wondered if there is any mileage in it (from [sIMS] Sims slow after upgrade to SQL 2014):

One of the things we have noticed is that the newer versions of SQL2012 and SQL2014 are really RAM hungry even when they don't need it.

We have found that sometimes restricting the amount of RAM the SQL instance can access (needs SQL Management Studio) has speed up SIMS.

 

Unfortunately I'm not experienced in troubleshooting SQL, but I was wondering if, other than anecdotal evidence, there is any performance measure for SQL? Query to result time average or something similar? That way I can tell if proposed 'solutions' are effective without relying on (usually conflicting) opinions; one person saying it seems better while others say registers or reporting takes too long to respond.

 

Thanks in advance

Edited February 25, 2016 by limawhiskey
Improve title clarity

[bobsmith]

one issue you have when testing the SQL side of things is that the slowness most probably isn't really caused by the SQL - a query that takes SIMS 5 minutes to run through SIMS.Net itself may only take SQL 5 seconds to run natively. (at least a query that you write yourself).

 

The problem I've found here is the slowness can be caused by a number of different elements in the chain:

 

Client hardware

Server hardware

SQL Server

SIMS.Net on the client

Network infrastructure

 

and the interaction of any of the above!

[limawhiskey]

OK, thanks. So that would explain why people's experiences are so different. I hadn't appreciated the complexity but now you point it out, it seems obvious that the request you make in the client has to undergo several stages of translation to SQL and back. I was ruling out networking issues at least as there aren't other symptoms that I'm aware of, but my approach may need a re-think. Edited February 25, 2016 by limawhiskey