Rozzer Posted February 3, 2013 Posted February 3, 2013 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 Posted February 3, 2013 Posted February 3, 2013 (edited) 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 Edited February 3, 2013 by ricki
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 I have made the nic changes. You say performance to full where is this option? Thanks, Ross
glennda Posted February 4, 2013 Posted February 4, 2013 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 Posted February 4, 2013 Posted February 4, 2013 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 Posted February 4, 2013 Author Posted February 4, 2013 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 Posted February 4, 2013 Posted February 4, 2013 (edited) 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? Edited February 4, 2013 by glennda
jinnantonnixx Posted February 4, 2013 Posted February 4, 2013 (edited) 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. Edited February 4, 2013 by jinnantonnixx
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 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 Posted February 4, 2013 Posted February 4, 2013 (edited) 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/Poor-Disk-Performance-Time-Outs-Database-and-the-SQL-Server-Error-Log Here's a useful 'best practices' guide about SQL on VMWare. http://www.vmware.com/files/pdf/sql_server_virt_bp.pdf Edited February 4, 2013 by jinnantonnixx
glennda Posted February 4, 2013 Posted February 4, 2013 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 Posted February 4, 2013 Posted February 4, 2013 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 Posted February 4, 2013 Posted February 4, 2013 (edited) 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! Edited February 4, 2013 by jinnantonnixx
jinnantonnixx Posted February 4, 2013 Posted February 4, 2013 Trawling through my bookmarks, this is a good in-depth article SQL Server Wait Statistics: Tell me where it hurts - Paul Randal
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 I did try your query but i had no luck running it. Ross
jinnantonnixx Posted February 4, 2013 Posted February 4, 2013 (edited) I did try your query but i had no luck running it. Ross You can get these numbers from SQL Activity Monitor. Launch SQL Server Management Studio, right-click your SQL server and choose Activity Monitor. Leave it for a few moments to settle down. Keep your eye on the 'wating tasks' graph. Ideally this number should be 0, but if it's consistently more than a few (I'd say 3 to 4), then your certainly have a bottleneck. Check this figure when the system goes slow. Edited February 4, 2013 by jinnantonnixx
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 Thats a lot easier! Its been on 0 for 5 minutes and still going. Ross
jinnantonnixx Posted February 4, 2013 Posted February 4, 2013 (edited) Hmmm. Was it slow for users during this period of 0 waiting tasks? The waiting tasks graph is the litmus test for bottlenecks. It's quite possible that an SQL routine is blocking others. This can give the impression of an overloaded machine, even when it has adequate capacity.This is a job for SIMS to fix, not you. http://www.sswug.org/articles/viewarticle.aspx?id=56398 Edited February 4, 2013 by jinnantonnixx
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 I've been monitoring it for about 30 minutes now and it briefly hit 3 for a few seconds. Ross
jinnantonnixx Posted February 4, 2013 Posted February 4, 2013 I've been monitoring it for about 30 minutes now and it briefly hit 3 for a few seconds. Ross The odd peak is fine. Don't worry about that. Can you predict when the slowdown will happen?
Rozzer Posted February 4, 2013 Author Posted February 4, 2013 Not really I have asked the office staff to stress test it for me but that requires them to have 5 minute spare.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now