SQL Server Performance

LOCK_M_S waits caused by SQL server or IIS application?

Discussion in 'SQL Server 2008 General DBA Questions' started by jaga23, Jan 4, 2011.

  1. jaga23 New Member

    I am not a dba, but am hoping I can get some general advice.
    Here's my situation: Running SQL server 2008 in Win Serv 2008 on a hyper-v VM. We have plenty of ram and cpu for the VM. We are running Sage CRM and Accpac databases on the same instance of SQL server. In Activty Monitor, i am noticing anywhere from 0-8 Waits due to Locks. The locks are for the CRM database and either say pagelock or keylock. These Waits are anywhere from 1000-4000 (is that miliseconds?) which cause the CRM web application (running on IIS in a windows 03 server, also a VM) to tank, or be completely unresponsive.
    Is there anything that can be tweaked in SQL server, or does this sound like an issue with the CRM application? If I completely reboot the CRM server, it will again function correctly someone does something to create the locks again.
    Any ideas would be much appreciated!
  2. satya Moderator

    Welcome to the forums.
    It is a clear indication that blocking is causing the generation of these waits, as you are using SQL Server 2008 have a look at the Performance Dashboard reports by choosing Server ---> Right-click --> Reports and various reports that will get you required information.
    Take help from this KBA http://support.microsoft.com/kb/224453 to identify the blocking and reduce. Also see what is the SNAPSHOT_ISOLATION level on the SQl Server & database, also the TEMPDB contention can cause the blocking see what is the size set on TEMPDB including the auto-growth.

Share This Page