[sims] Speeding up SIMS

[localzuk]

We have our SIMS server installed in a VM which is hosted on SSDs, connected to the storage server via 10GbE and has 16 cores of E5-2690 and 16GB RAM available to it. Doing a full database backup using BackupAssist takes a matter of seconds. Utilisation doesn't go much past 5% at its peak - so I'd guess that this isn't the issue that Capita have been working to resolve.

 

However, it still runs slow on clients.

 

How can we get this thing sped up? I shouldn't be receiving comments such as "reports are running slowly!" at all.

 

It even runs slower than I'd like for me - I'm running an i7 930 and 6GB RAM. Our finance officer, who made the comment above, is running a brand new i5 with 8GB RAM, and both of us are connected via 1Gbps.

 

So, what can we do?

Edited May 8, 2013 by localzuk

[Theblacksheep]

16 cores! I gave ours 2 (spring release made me double it)!

 

With VMware the amount of virtual cores requires the same number of physical cores to be available to use or 'ready' at any one time (vSMP). If they are not available the VM will wait in queue for all the cores to be ready. Not a problem if nothing else is running on it, but a nightmare if the VM is sharing with others, especially if they have multiple cores.

Edited May 8, 2013 by Theblacksheep

[Ashm]

How many other VMs do you have running on the host? Could it be that you've allocated too many resources to the VM and there is contention, especially with 16 vCPU's being allocated to one VM?

 

I know VMware best practice has always been to allocate as few resources as possible initially and then monitor performance and then increase resources if necessary.

[fiza]

Does the client run fast on the actual server?

Have you tried running pulsar.exe directly rather than simsload.exe

Some people have found it runs faster that way as it doesnt check for updates. You could then have a different shortcut called "update sims" which points to simsload.exe for when the client needs updating.

[localzuk]

16 cores! I gave ours 2 (spring release made me double it)!

 

With VMware the amount of virtual cores requires the same number of physical cores to be available to use or 'ready' at any one time (vSMP). If they are not available the VM will wait in queue for all the cores to be ready. Not a problem if nothing else is running on it, but a nightmare if the VM is sharing with others, especially if they have multiple cores.

 

How many other VMs do you have running on the host? Could it be that you've allocated too many resources to the VM and there is contention, especially with 16 vCPU's being allocated to one VM?

 

I know VMware best practice has always been to allocate as few resources as possible initially and then monitor performance and then increase resources if necessary.

 

The servers are barely in use at the moment - we have a total of 10 VMs on 2 Nodes - each node has 2 E5-2690s, so a total of 12 physical cores per server, or 24 hyper-threaded cores, as Windows sees them, with 96GB RAM each.

 

Does the client run fast on the actual server?

Have you tried running pulsar.exe directly rather than simsload.exe

Some people have found it runs faster that way as it doesnt check for updates. You could then have a different shortcut called "update sims" which points to simsload.exe for when the client needs updating.

 

It isn't a simsload/pulsar issue - the slowdown is inside SIMS - ie. reports running slower than they reasonably should. SIMSload is just a launcher. SIMS runs about the same speed on the server as it does on a client.

[fiza]

It isn't a simsload/pulsar issue - the slowdown is inside SIMS - ie. reports running slower than they reasonably should. SIMSload is just a launcher. SIMS runs about the same speed on the server as it does on a client.

 

Apologies - misread your post.

We moved our SIMS to a VM with 4 cores and 16GB RAM. Standard DAS 15k drives. Our heavy users have said the reports run a little faster. Yours should fly along!

[glennda]

How big is your database?

 

i don't know if they have fixed it but do you use the graphs etc on the home screen? This can cause dreadful performance.

[localzuk]

About 1.5GB.

 

We have graphs on our homescreen, yes. But as I said before, CPU utilisation is barely anything on the server. SQL Management Studio has the CPU usage down at tiny levels too.

[LosOjos]

I don't think there's much more you can do to be honest - SIMS is a memory hog, plain and simple. I suspect that the reason the reports take so long is not so much the time it takes to retrieve data from the server but the routines running in the background to format that data client end.

 

If you run a report that takes ages in SIMS.net via CommandReporter, it's a hell of a lot faster (which backs up my theory that it's SIMS.net that is the problem)

[Theblacksheep]

The servers are barely in use at the moment - we have a total of 10 VMs on 2 Nodes - each node has 2 E5-2690s, so a total of 12 physical cores per server, or 24 hyper-threaded cores, as Windows sees them, with 96GB RAM each.

 

Although they may have low use your SIMS VM has to wait until 16/24 cores have a free timeslice on the server cpu scheduler. Personally I'd drop the server down to a safe 6 cores and monitor its progress, that amount of cores is wasted on SIMS and you'll have more resources available for other stuff.

 

Our DB is 4.5GB and has 250 staff users. FMS (2DBs) and 6 users. Up until last week only 2 cores!

Edited May 8, 2013 by Theblacksheep

[chazzy2501]

are you using full sql? if your using the express it's limited to 2 cpus and 2gb of ram usage? (I think) you need full sql if you want to beef it up.

 

EDIT: it's worse 1 cpu and 1gb of ram

Edited May 8, 2013 by chazzy2501

[localzuk]

Yup, full SQL.

[jinnantonnixx]

First off, have you separated your OS, Database file and Log file onto different physical volumes?

 

Have you assigned an upper limit for the SQL Server memory usage ('max server memory' setting)? In an ideal world, you can give it all the memory available, and it will release on demand to the OS and and when needed. In the real world this sometimes doesn't happen and a server with loads of memory can be sluggish.

 

Recommened settings (rule of thumb)

Physical RAM | MaxServerMem Setting (MB)

2GB 1500

4GB 3200

6GB 4800

8GB 6400

12GB 10000

16GB 13500

24GB 21500

32GB 29000

48GB 44000

64GB 60000

72GB 68000

96GB 92000

128GB 124000

 

 

Lots here:

How to Identify Microsoft SQL Server Memory Bottlenecks

 

 

SIMS's queries are often horrendous - look at the activity monitor and query plan to see, including the index hints.

 

I've tried optimizing for ad-hoc queries, but that showed no improvements at all.

 

Well anyway, usual caveats apply, but here are a few scripts from my toolbag. Some are ones I collected so credits to original authors.

 

 

 

Check the number of 'runnable tasks'. In other words tasks that have exhausted their quantum, been swapped out, but cannot be serviced because of lack of resources of server. You'll see one row per CPU. This is a simple snapshot, so you'll need to run it several times to get a picture.

--If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

-- You can run the DMV query to confirm that:
-- Check SQL Server Schedulers to see if they are waiting on CPU
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

--If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern! 

 

 

 

 

-- SQL Server 2008 and R2 Memory Related Queries
-- Glenn Berry 
-- October 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

-- Instance Level queries

-- Good basic information about memory amounts and state (SQL 2008 and 2008 R2)
SELECT total_physical_memory_kb, available_physical_memory_kb, 
      total_page_file_kb, available_page_file_kb, 
      system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- You want to see "Available physical memory is high"

 

 

 

-- SQL Server Process Address space info (SQL 2008 and 2008 R2)
--(shows whether locked pages is enabled, among other things)
SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
      page_fault_count, memory_utilization_percentage, 
      available_commit_limit_kb, process_physical_memory_low, 
      process_virtual_memory_low
FROM sys.dm_os_process_memory;

-- You want to see 0 for process_physical_memory_low
-- You want to see 0 for process_virtual_memory_low

Edited May 8, 2013 by jinnantonnixx

[zag]

Yeh I'd reduce the amount of RAM, 4GB should be enough for any sims server.

 

Anything more might actually slow down Sims due to the way SQL server allocates it.

[localzuk]

First off, have you separated your OS, Database file and Log file onto different physical volumes?

 

Not really relevant when they're running on SSDs. IOPS are far higher, and seek times basically non-existent.

 

Have you assigned an upper limit for the SQL Server memory usage ('max server memory' setting)? In an ideal world, you can give it all the memory available, and it will release on demand to the OS and and when needed. In the real world this sometimes doesn't happen and a server with loads of memory can be sluggish.

 

I haven't, no, but it isn't using all the RAM available to it either - SQL is using 6GB RAM, with about another 5GB available if it wants it.

 

Check the number of 'runnable tasks'. In other words tasks that have exhausted their quantum, been swapped out, but cannot be serviced because of lack of resources of server. You'll see one row per CPU. This is a simple snapshot, so you'll need to run it several times to get a picture.

 

--If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern! 

 

Results are zero across the board for runnable, and tasks are minimal - 1/2/3 across the board.

 

-- You want to see "Available physical memory is high"

 

We see that.

 

-- You want to see 0 for process_physical_memory_low

-- You want to see 0 for process_virtual_memory_low[/code]

 

Yup, zero for both.

[jinnantonnixx]

Missed the bit about SSD - agreed, irrelevant here.

 

I'd put a hard upper limit on the SQL memory usage.

[localzuk]

Well, I'll do some tweaks tonight - reduce cores down, put a hard upper limit for RAM etc... And see what happens.

[chazzy2501]

can anyone suggest a report to run so I can compare my sims server? (average report takes 8 seconds) client PC specs don't seem to matter.

[CAM]

What part of the reports take the longest? Split it into two, data recovery and data formatting (yes I just made those up, bear with me!).

 

Data recovery is the point where the client says to SIMS "I want this data" and SIMS goes and gets it. It will then get punted into Excel and a blank Excel window will appear and vanish.

 

From this point on the client is doing the legwork and now into the data formatting stage. I've written VBA Macros and whilst editing them, noticed that SIMS uses a macro to assemble the data from a list of values into the sheet, formats data and adds borders. This is what it is doing whilst Excel is hidden and a new instance of Excel appears with your data once done.

 

If the recovery stage is slow, you are probably seeing server issues. If the formatting stage is slow, it is probably the client.

 

This is my interpretation of how reports are created but I think someone will correct me if I am barking up the wrong tree.

[LosOjos]

This is my interpretation of how reports are created but I think someone will correct me if I am barking up the wrong tree.

 

You're dead on with the Excel formatting, but I suspect there's a little more to it. Try running a report in SIMS.net in to Excel then run the same report in CommandReporter (if you set the output file extension as CSV, CR can do Excel too). It's significantly quicker via CR, suggesting SIMS.net is creating an inefficiency at some point in the chain.

 

EDIT: CR actually creates a CSV which will obviously be slightly quicker than applying additional formatting, though it shouldn't take that much longer as it only does basic tasks like making the first row bold and some minor formatting - not a lot of additional data parsing done comapred to CSV

Edited May 8, 2013 by LosOjos

[Ephelyon]

@jinnantonnixx, I'm having similar issues to @localzuk and I just ran the report code you gave. scheduler_id always has 0 runnable tasks (out of about 8 current), but if I run it several times over 30 seconds I will occasionally see scheduler 1 (with around 12 current tasks) having 1 runnable task. Is there cause for much concern there? This host has 8 cores, running 5 VMs (DC, file server, Exchange, print and SQL) and this SIMS VM is the only one with more than 1 vCPU (it has 2). 8GB RAM assigned, 16GB for Exchange, 2GB for each of the others.

[jinnantonnixx]

@jinnantonnixx, I'm having similar issues to @localzuk and I just ran the report code you gave. scheduler_id always has 0 runnable tasks (out of about 8 current), but if I run it several times over 30 seconds I will occasionally see scheduler 1 (with around 12 current tasks) having 1 runnable task. Is there cause for much concern there? This host has 8 cores, running 5 VMs (DC, file server, Exchange, print and SQL) and this SIMS VM is the only one with more than 1 vCPU (it has 2). 8GB RAM assigned, 16GB for Exchange, 2GB for each of the others.

 

Those figures look healthy.

[jinnantonnixx]

Fire up SQL Activity Monitor and look at the overiew

SQL Server Management Studio>Right-click the server>Activity Monitor

 

Activity monitor is a great tool for all us 'accidental DBAs'.

 

Good starting point >>> SQL Server Reference Guide | Performance Monitoring Tools: SQL Server 2008 Activity Monitor | InformIT

 

Activity Monitor

Edited May 8, 2013 by jinnantonnixx

[Ecclesbury]

@jinnantonnixx Those links are really useful, thanks for the tip.

 

Time to go and ask management if I can add "accidental DBA" to my job description!

[vikpaw]

Dropping cores sounds like sound advice

I would also look towards the application itself rather than the server. Easy test is to run the client on the server if you don't mind installing it there, i think most people do for testing.

The re-index patch can help with data calls.

 

Some reports, especially those with sub tables and filters on those tables do take an age. General rule of thumb is if you have to think for more than a few secs to understand the report description (pseudo SQL query) it generates when you design one, then the system will probably need a few seconds to process it.

 

I wouldn't under estimate the data formatting part from @CAM 's analogy. It does write a temp .txt file to disk, then initiate Excel, run the macro, process it into a new file. It should be quick, but i bet it isn't.

 

Can you monitor the local machine RAM usage? specifically how much the pulsar.exe process uses. Regardless of how much is free, i have a feeling that it gets 'heavy' and bloated, so once it's gets to a certain stage it burns out. We all know SIMS doesn't return the memory very well, i can imagine it might just hinder itself after doing a few memory heavy tasks.

 

Is there any chance you're also competing with a network bottleneck somewhere?

 

I remember the days when just clicking on assessment manager templates would take a few minutes for it to switch to that module and load the list of marksheets (at a previous school). Here i can't say it's ever had major issues.

 

My client is very slow, Win 7 i5 x86 with 4GB, and often hangs, but once SIMS is active, it's actually pretty good despite that.