Lock waits, SQL server or application?

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!

more ▼

asked Jan 04, 2011 at 11:56 AM in Default

avatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Jan 04, 2011 at 03:44 PM

avatar image

40.9k 39 94 168

(comments are locked)
10|1200 characters needed characters left

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.

  1. Are you defragmenting indexes at all?

  2. Are you proactively managing database file growth?

  3. Are you managing logfile growth?

  4. Are you running external processes on the machine (external to SQL Server)?

  5. As it is a VM - what is the storage layer up to? Is it shared? Is it correctly configured?

  6. Are you fully patched (windows and sql server)

  7. Are you maintaining your database statistics properly/at all?

  8. How is your instance setup? (disks, memory, cpu, extra startup parameters)

  9. What recovery model are your using on the databases (simple, full, bulk logged)?

  10. What version and edition of windows and sql server are your running?

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.

more ▼

answered Jan 05, 2011 at 12:17 AM

avatar image

26.2k 18 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 04, 2011 at 11:56 AM

Seen: 4735 times

Last Updated: Jan 04, 2011 at 11:56 AM

Copyright 2018 Redgate Software. Privacy Policy