[SIMS] Peaking CPU on server

[Rozzer]

Hi All,

I have read a lot of threads with people having similar issues but no responses have helped. I have had this as a on going issue for quite some time now. I think staff have got so use to SIMS running slow they just are creative when they want to run a report. Staff will get a coffee and finish it and still find some reports have not completed.

Our SIMS server spec is:

4 Cores
8GB Ram
Windows Server 2008 R2
Sql 2008

Example Workstation

128GB SSD
Intel Core 2 / i3
2GB / 4GB Ram
Windows 7 Enterprise

I am running VMWare 5.1 and recently had Dell run a DPACK diagnostics on my setup and reported that everything was working well and no alarming concerns with Memory / IOPS on the Virtual hosts. I am running a Dell MD 3620i SAN with 12 600GB 10k Sas Drives.

I am honestly really stumped. I have had the issue when my VM network was running on Hyper V and also updated my SAN as it was running on a MD3200i with 15k Sas Drives.

I have ran a re-index patch and still no difference. the Database size is 5GB

Any suggestions because I am stumped!

Thanks in advance.

Ross

[ricki]

Hi

Is anything appearing in the event log. Windows 2008 r2 does some strange things compared to windows 2008.

Check the performance is set to full on the server and the clients. This is not the default.

You might have to disable ip6, chimney, rss. http://support.microsoft.com/kb/951037

I have been told that windows tries to offload cpu processing to the nic card and if the nic card does not like this you can get loads of event log errors.

Richard

[Rozzer]

I have made the nic changes. You say performance to full where is this option?

Thanks,

Ross

[glennda]

How big is your Sims Database?

If anything I would try and increase the amount of memory the Server is allocated. SQL loves its RAM and the more you can give it, the better it will perform (obviously there are other factors too).


EDIT: is it any reports in particular?

[kmount]

Is your hypervisor contended for CPU?
How many cores are available in your hypervisor, and how many vCPU are allocated out across the host?
What do the performance charts show for the SIMS server plus host?
CPU Ready/Wait times?

[Rozzer]

How big is your Sims Database?

If anything I would try and increase the amount of memory the Server is allocated. SQL loves its RAM and the more you can give it, the better it will perform (obviously there are other factors too).


EDIT: is it any reports in particular?

Database is 5GB and I found out its an issue when running marksheets, it can take upto 25 minutes to run a behavior and attendance report.

In regards to the CPU when I had the DPACK looked at by Dell they said the CPU is fine with the current load. I have dual quads in both virtual hosts.

I am not 100% with perf mon but the CPU peaks for a few seconds then returns to around 10 - 40% then peaks.

Ross

[glennda]

I would up the ram to 12GB if you can. For Sims I say 4GB for the Windows OS the Database size plus 2Gb for queries.

Do you run FMS on the server also? In which case I would add the two database sizes together and then add the 2GB on top.

You are using full SQL yes? Or Express?

EDIT: how much is it paging? is the Paging file on the Same drive/Lun as the SQL database?

[jinnantonnixx]

Throwing all the memory at SQL can cause an apparently good spec'd machine to behave poorly. In theory, SQL should release its memory to the OS on demand, but this article explains why this sometimes doesn't happen and why it's best to specify memory limits for your system.
Suggested Max Memory Settings for SQL Server 2005/2008 - SQLServerCentral

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure.

This setting controls how much memory can be used by the SQL Server Buffer Pool. If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM MaxServerMem Setting
2GB 1500
4GB 3200
6GB 4800
8GB 6400
12GB 10000
16GB 13500
24GB 21500

In any case, SQL Activity Monitor is your best friend.

[Rozzer]

I would up the ram to 12GB if you can. For Sims I say 4GB for the Windows OS the Database size plus 2Gb for queries.

Do you run FMS on the server also? In which case I would add the two database sizes together and then add the 2GB on top.

You are using full SQL yes? Or Express?

EDIT: how much is it paging? is the Paging file on the Same drive/Lun as the SQL database?

We use a full sql version. I personally cant see adding more memory will make that much of a difference but I will up the memory tonight. The paging is done on the root drive and the SQL data is stored on a seperate drive. It is also running on a seperate lun.

Ross

[jinnantonnixx]

Your next step is SQL Activity Monitor. I'd suggest looking for blocking processes, keep an eye on the 'recent expensive queries' when you have a slowdown.
Performance Analysis Using SQL Server 2008 Activity Monitor Tool

If I was a betting man, I'd put money on a disk IO bottleneck. Is your database and log file on the same volume? And on the same volume as the OS? Are you using RAID?
http://www.scribd.com/doc/28721265/P...rver-Error-Log


Here's a useful 'best practices' guide about SQL on VMWare.
http://www.vmware.com/files/pdf/sql_server_virt_bp.pdf

[glennda]

We use a full sql version. I personally cant see adding more memory will make that much of a difference but I will up the memory tonight. The paging is done on the root drive and the SQL data is stored on a seperate drive. It is also running on a seperate lun.

Ross

It just means the entire database can be cached into memory, not always relevant but with Sims there is quite a lot of queries which need to be run quite often, especially if you use lesson monitor as well.

With regards to the paging that is fine with its location - how much is used regularly?

[Soulfish]

I'd also look at if you really need to run 4 vCPU for the VM. As @kmount says you may find that your issue is CPU contention. If the VM is unable to schedule all 4 vCPU to physical CPU cores simultaneously it will have to wait until the hypervisor can get 4 physical cores. This can cause very bursty performance issues when it looks like your VMs should be running just fine.

To be perfectly honest most VMs run fine with 1 vCPU, or 2 at a push. Very very rarely do you ever need more. Much better to start small with VMs and get bigger if you are being resource constrained then to do the opposite

[jinnantonnixx]

I'd also look at if you really need to run 4 vCPU for the VM. As @kmount says you may find that your issue is CPU contention. If the VM is unable to schedule all 4 vCPU to physical CPU cores simultaneously it will have to wait until the hypervisor can get 4 physical cores. This can cause very bursty performance issues when it looks like your VMs should be running just fine.

To be perfectly honest most VMs run fine with 1 vCPU, or 2 at a push. Very very rarely do you ever need more. Much better to start small with VMs and get bigger if you are being resource constrained then to do the opposite

Good point - you need to check if your SQL schedulers have a backlog.
Here's a bit of SQL code (not mine, picked it up from my travels on the net, credits to OP) that show two crucial items of importance for each of your SQL schedulers:
Current_task_count and runnable_task_count.
An SQL task that is in a 'runnable' state means that it is ready to go but cannot yet be serviced by an SQL scheduler. If the runnable_task_count is more than 0, this is a backlog and indicates that, at the time the query was run, there were jobs waiting but couldn't be serviced.

--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!

[jinnantonnixx]

Trawling through my bookmarks, this is a good in-depth article

SQL Server Wait Statistics: Tell me where it hurts - Paul Randal

[Rozzer]

I did try your query but i had no luck running it.

Ross