x

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 '11 at 11:56 AM in Default

jaga23 gravatar image

jaga23
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 '11 at 03:44 PM

Tim gravatar image

Tim
35.5k 32 40 138

(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 '11 at 12:17 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x37

asked: Jan 04 '11 at 11:56 AM

Seen: 3285 times

Last Updated: Jan 04 '11 at 11:56 AM