SQL Server: CMEMTHREAD, High Wait Values and Solution

Wait stats is the first place when where we start analyzing health of a production database server. Recently, we have found that a new database production server is not performing up to mark and queries response getting slower and slower in peak hours.

On executing, well known query by Paul Randal to get wait stats, we have found that a time for strange wait “CMEMTHREAD” is too high for said server.

According to BOL “CMEMTHREAD, occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.”

On trying a lot, but totally in vain, I thought asking Paul Randal would be better, as his blog on SQLSkills is one of the big resources from where I have learned about waits and wait types. Paul replied that “My guess is ad hoc plans being inserted into the plan cache. Try turning on 'optimize for ad hoc workloads'.” (That’s what we have already tried)

Skimming through articles and forums, I came a across to Microsoft support team article http://support.microsoft.com/kb/2492381/en-us. Which stats that, it could be occurring due to a bug in SQL Server 2008 R2. On said production server, we have found that NO service pack is installed and it still contains RTM.

Without any second thought we have created a ticket for upgradation to ServicePack2 and after that we have found that problem is resolved and server start working normally.

SQL Server: Why We Should Avoid NOLOCK Table Hint in DELETE/UPDATE Queries

Recently, I was asked to review, already written stored procedures for optimization purpose. During this review process I have found that a group of developers is regularly committing a big mistake. This group of developers believes that table hint NOLOCK is used to execute queries quickly, as this hint will avoid placing any lock on target table records and it can you used in any query. Even they have applied this NOLOCK in DML statements.

WRONG

First thing, NOLOCK hint means, it will not take care of any lock (instead of placing lock). It will return data, that could be dirty (NOT YET COMMITTEED by other transactions). We can use this table hint to get results quickly when we are dead sure that dirty data is TOTALLY bearable.

In DELETE/UPDATE queries it should be totally avoided as it can produce junk results. Let’s prove.

In following example, we need to correct discount column of SalesOrderDetail, but according to discount provided in lookup table of SpecialOffer. Before we execute our update statement (Statement #2 in Transaction# 2), someone has accidently changed SpecialOffer, but good thing is that, he has not committed these changes yet. But as we have placed NOLOCK hint in our Statement #2 in Transaction# 2, it will change data according to dirty data, though, later on transaction#1 is rolledback.

SQL Server: Script to Fix ALLOW_PAGE_LOCKS Option for All indexes on All Databases

Recently we have found that our index defragmentation job is failing on a production server, due to REORGANIZE   failure of one of our index. SQL Server was unable to REORGANIZE this index because mistakenly we have an index with the ALLOW_PAGE_LOCKS options set to OFF

What is Page Lock Option?

According to BOL, If ALLOW_PAGE_LOCKS option is set to ON, it means Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.

If ALLOW_PAGE_LOCKS  option is set to off following query will return an error.
ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON Person.Address REORGANIZE

The index "IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode" (partition 1) on table "Address" cannot be reorganized because page level locking is disabled.

You can correct it by simply updating ALLOW_PAGE_LOCKS option to ON with the help of following query

ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON  Person.Address
SET (ALLOW_PAGE_LOCKS = ON);

To make sure to avoid this problem in futur for all datbases on a instance, Amna Asif has suggested following simple but efficient script to detect indexes with ALLOW_PAGE_LOCKS option set to OFF and automatically fix this problem for all of your databases on an instance.

/***********************************

Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on
                 all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

DECLARE @DBName NVARCHAR(255)

DECLARE @IndexCount int
DECLARE @UpdateIndexQuery Varchar(500)
--- Table variable to hold intermediate result set
            DECLARE @IndexsInfo TABLE
            ( 
            RowNo int identity(1,1),
            DatabaseName varchar(100),
            TableName varchar(100),
            IndexName varchar(100)
            )
--- Cursor to work on each changeable index of each db on an instance
      DECLARE DatabaseList CURSOR 
                  FOR
                    SELECT Name
                    FROM sys.databases
                    WHERE state_desc = 'ONLINE'
                    AND is_read_only = 0
                    ORDER BY name
      OPEN DatabaseList
             FETCH NEXT FROM DatabaseList INTO @DBName
             WHILE @@FETCH_STATUS = 0
             BEGIN   
               INSERT INTO @IndexsInfo (DatabaseName,TableName,IndexName)
               EXEC( '  SELECT '''+@DBName+''' AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS IndexName     
                              FROM '+@DBName+'.SYS.INDEXES indx
                              LEFT OUTER JOIN '+@DBName+'.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
                              WHERE ALLOW_PAGE_LOCKS = 0           -- where page lock option is not selected
                              AND indx.name NOT LIKE ''QUEUE%''  ' -- we need only user defined indices
                        )
            FETCH NEXT FROM DatabaseList INTO @DBName
             END
            CLOSE DatabaseList
            DEALLOCATE DatabaseList
-----Update allow_page_locks option for those indexes where we need 
 SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
      WHILE @IndexCount >0  
      BEGIN
          SET @UpdateIndexQuery=( SELECT ' ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
            IndexsInfo.DatabaseName+'].dbo.['+IndexsInfo.TableName+']
            SET (
                  ALLOW_PAGE_LOCKS = ON
                  ) ; '
            FROM @IndexsInfo AS IndexsInfo
            WHERE IndexsInfo.RowNo=@IndexCount)
     
            EXEC(@UpdateIndexQuery)
        
           SET @IndexCount=@IndexCount-1
      END

SQL Server: How to Analyze Blocking and Deadlocking

In response to an early post Difference Between Locking, Blocking and Dead Locking , I have received few mails where I was asked “How to analyze this blocking and dead locking phenomenon”.

I like to use performance monitor counters to check the frequency of blocking and dead locking. You can find these counters by selecting SQL Server: Locks. Three counters under this group are very useful.

1. Lock Timeouts/sec
2. Lock Wait Time (ms)
3. Deadlocks/sec

Out of these three counters first two are used to analyze blocking. Value for “Lock Timeouts/sec” should be zero (0) and “Lock Wait Time (ms)” must also be very low. If you are observing nonzero value for “Lock Timeouts/sec” and continuous high value for “Lock Wait Time (ms)”, then there is excessive blocking occurring. Your long running queries can cause this blocking. Use profiler or sys.dm_exec_query_stats to identify such culprit queries.

None zero values “Deadlocks/sec” counter is an indication of deadlocks. Value for this counter must always be zero.

We can also use following query

SELECT *

FROM    sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Locks' 

AND instance_name = '_Total'

AND counter_name IN ('Lock Waits/sec','Lock Wait Time (ms)','Number of Deadlocks/sec')

Do you have any better idea ? Please do share with us.

SQL Server: Difference Between Locking, Blocking and Dead Locking

Like ever, today’s article of Pinal Dave was interesting and informative. After, our mutual discussion between our DBAs and Developers on Pinal Dave topic of Concurrency and Isolation. I felt that most of us are intermingling three equally sounding words. Those are LOCKING, BLOCKING and DEAD LOCKING.

Lets try to revisit these concepts with some simple analogies.

LOCKING occurs when connection needs access to a piece of data in database and it’s necessary for SQL Server when managing multiple connections. Just assume an example of your garage, when you park your car in garage, basically you are locking the place of garage.

BLOCKING occurs when two connections need access to same piece of data concurrently and one connection is blocked because at a particular time, only one connection can have access. Just like, you stop (block) your car on a traffic signal because some other car or cars are using the crossing area.

DEAD LOCK occurs when one connection is blocked and waiting for a second to complete his work, but on other side, second connection is also waiting for first connection to release the lock. Just like, you need to cross the signal area but same time someone else from opposite side also want to cross the signal. Now, you need a way which other is holding and other need way where your car is.

That is why, one should be clear that locking is integral part of SQL Server to handle concurrency, blocking is bad when one connection/transaction is waiting unnecessary for a long time, and deadlocking is a phenomenon which should never occur.