Hello. I am definitely not a dba, so could use some general direction debugging.
My issue: we are running Sage CRM and Accpac databases on the same SQL server 2008 instance. The CRM product runs on IIS on a separate server. It will run fine for hours, but then this app tanks due to SQL errors. Looking at the Activity monitor, I notice a bunch of CRM database processes that are SUSPENDED state, with wait type usually being LCK_M_S and wait resource beginning with pagelock or keylock. The waiting tasks pile up, meanwhile the CRM app does nothing but wait. The Resource Waits section shows high Lock times, average right now is around 4000 but typically 1000-4000.
Also notice tempdb is always running a SELECT task... is this normal?
Does this sound like a CRM application issue, or is there something that needs tweaking on SQL server? memory and cpu resources are abundant for the virtual machine running SQL server, on a win 2008 server. VM's are running on hyper-v.
Thank you for any guidance!
asked Jan 04 '11 at 11:56 AM in Default
There are so many possibilities with this one. It runs fine for hours then starts having issues. What process kicked off that started the issues. When your server it hitting this road block the queries may be the same workload as the previous hour but some other external factor. There could be issues with connectivity between the app and SQL box, there could be disk issues, virus scan running, etc.
Can you replicate the slowness? What does CPU, Memory, and the disk look like when this is going on? With you saying this SQL box is a VM, what else is happening on the VM HOST when these issues are going on. Just because there is "abundant CPU and memory" for the VM doesn't mean it isn't over subscribed and during that time of slowness that some other server on the host isn't crushing the system.
If you can provide more feedback we can probably get you going in the right direction.
answered Jan 04 '11 at 03:44 PM
Jaga23, you mention that tempdb is always running a select - This may be a non-issue if you are using the Activity Monitor in SSMS. If so, that select may well be you running the monitor - it runs queries against SQL Server and stores and retrieves this information from tempdb.
As for the lock waits, I would suggest you start with the basics on that system as these can be the cause for quite some headaches.
If you can provide us with the answers to those questions we will get a better picture and be able to point you in the right direction.
answered Jan 05 '11 at 12:17 AM