October 4, 2012 at 8:28 am
I have a reindex job that seems to get into a deadly lock with an application.
Essentially, on the first step, I'm getting into a deadly lock here (the app requires page locks for indexes so I have to turn them off temporarily to reindex):
USE mydb ALTER INDEX idx_mytable ON mytable (ALLOW_PAGE_LOCKS = OFF )
Then the next step in the job reindexes all the tables appropriately.
The last step is
USE mydb ALTER INDEX idx_mytable ON mytable (ALLOW_PAGE_LOCKS = ON )
How can I detect a deadly lock? Right now, I can think of creating a job to detect that during the running of it and kill it, but then I'd need to figure out what to do from there. What code might I use?
October 4, 2012 at 8:42 am
If you are rebuilding your Indexes, MS-SQL requires a lock on that page, no matter what. Even using Enteprise edition, it will temporary acquire a shared lock, between processing.
I recommend you using Ola's solution: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
He has tested this extensively, and lot of people successfully deployed this on live production environments. I've personally use it on 1TB+ databases without issues. It is good, because it is invoked only if needed or depending of fragmentation, minimizing locks and IO.
But if still... you want to see real time the locks, you can use this DMV
SELECT request_session_id,
DB_NAME(resource_database_id) AS DatabaseName,
COUNT(*) AS TotalLocksPerType,
request_mode,
request_status,
resource_associated_entity_id,
CASE
WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_type IN ('KEY', 'PAGE', 'RID' )THEN (SELECT OBJECT_NAME(OBJECT_ID) FROM sys.partitions p WHERE p.hobt_id = l.resource_associated_entity_id)
END AS resource_type_name,
resource_type AS LockType
FROM sys.dm_tran_locks l
WHERE DB_NAME(resource_database_id) = 'DatabaseName' AND resource_type IN ( 'KEY', 'PAGE', 'RID', 'OBJECT')
GROUP BY request_session_id,
DB_NAME(resource_database_id),
resource_associated_entity_id,
request_mode,
request_status,
resource_type
Run it on the desired database and of course, replace "DatabaseName" by yours. It will let you know what type of lock are being acquired by your statement and how many.
October 4, 2012 at 10:26 am
Why are you turning page locks off? Index rebuilds lock at the table level, so that setting won't affect them, but with page locks off you won't be able to reorganise the indexes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 4, 2012 at 10:53 am
I turn it off because I receive an error that it cannot reindex them with page lock on.
October 4, 2012 at 11:08 am
What will error is trying to reorganise with page locks off. Rebuild doesn't care and certainly doesn't need page locks off.
Can you post that error?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2012 at 8:05 am
Here's the command:
alter index [UQ__ICMUT012__130D42861D913A15] on [wpsdbJcr].[jcr_Sch].[ICMUT01279001] reorganize
Here's the error:
Msg 2552, Level 16, State 1, Line 1
The index "UQ__ICMUT012__130D42861D913A15" (partition 1) on table "ICMUT01279001" cannot be reorganized because page level locking is disabled.
I'm using 1.41 of this reindex script. http://qa.sqlservercentral.com/scripts/Admin/92984/
October 5, 2012 at 8:15 am
The error is because page locking is disabled, not because it's allowed. If you're disabling it (USE mydb ALTER INDEX idx_mytable ON mytable (ALLOW_PAGE_LOCKS = OFF )), you're causing that error
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2012 at 11:43 am
Sorry, somehow I got the steps mixed up, first is actually
Page locks = ON
then the reindex
then Page locks = OFF
The application requires this setting, otherwise it generates errors during app server startup.
October 5, 2012 at 1:13 pm
One way of handling this is to check sys.indexes.allow_page_locks in the index maintenance script. If page locking is disabled for an index (allow_page_locks = 0), the script can then do a rebuild instead of a reorganization. My script works like this and I think that Michelle Ufford's script works the same way.
Ola Hallengren
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply