December 17, 2018 at 2:38 am
Hello everyone,
One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi Markovski
December 17, 2018 at 7:10 am
tttcoding - Monday, December 17, 2018 2:38 AMHello everyone,One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi Markovski
Are you querying sys.dm_tran_locks? The example doesn't make sense if that's what you are using. The translations of the resource descriptions is based on the resource type and are explained in the documentation.
Sue
December 17, 2018 at 9:45 am
tttcoding - Monday, December 17, 2018 2:38 AMOBJECT: 7:480772820:27.
Database ID 7
Object ID 480772820
Index ID 27
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
December 18, 2018 at 1:58 am
Sue_H - Monday, December 17, 2018 7:10 AMtttcoding - Monday, December 17, 2018 2:38 AMHello everyone,One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi MarkovskiAre you querying sys.dm_tran_locks? The example doesn't make sense if that's what you are using. The translations of the resource descriptions is based on the resource type and are explained in the documentation.
Sue
Hello Sue, thanks for replying.
I got this information about the blocking through the Red Gate's SQL monitor. There is a head blocked - an update statement, and all the blocked sessions wait for this same resource. I was just confused about the last part of its description because query-ing sys.indexes about that particular object I wasn't able to find an Index with ID of 27.
Thanks a lot,
Tomi
December 18, 2018 at 2:03 am
GilaMonster - Monday, December 17, 2018 9:45 AMtttcoding - Monday, December 17, 2018 2:38 AMOBJECT: 7:480772820:27.Database ID 7
Object ID 480772820
Index ID 27
Hello Gail,
thanks a lot for replying. I did search all the indexes for this table, there is only the clustered index and no other index. That's why I was confused about that last part of this wait resource.
Regards,
Tomi
December 18, 2018 at 2:07 am
Maybe check the SQLMonitor documentation?
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
December 18, 2018 at 6:17 am
tttcoding - Tuesday, December 18, 2018 2:03 AMGilaMonster - Monday, December 17, 2018 9:45 AMtttcoding - Monday, December 17, 2018 2:38 AMOBJECT: 7:480772820:27.Database ID 7
Object ID 480772820
Index ID 27Hello Gail,
thanks a lot for replying. I did search all the indexes for this table, there is only the clustered index and no other index. That's why I was confused about that last part of this wait resource.
Regards,
Tomi
Try this
😎
USE master;
GO
DECLARE @OBJ_ID NVARCHAR(10) = N'480772820';
DECLARE @IDX_ID NVARCHAR(10) = N'27';
DECLARE @DBID INT = 7;
DECLARE @CHECKSTUFF NVARCHAR(MAX) = N'USE ' + DB_NAME(@DBID) + N'
SELECT OBJECT_NAME(' + @OBJ_ID + N') AS OBBJNAME
,SIDX.[name]
FROM sys.indexes SIDX
WHERE sidx.object_id = ' + @OBJ_ID + N'
AND sidx.index_id = ' + @IDX_ID + '
';
EXEC (@CHECKSTUFF);
December 18, 2018 at 6:35 am
Eirikur Eiriksson - Tuesday, December 18, 2018 6:17 AMtttcoding - Tuesday, December 18, 2018 2:03 AMGilaMonster - Monday, December 17, 2018 9:45 AMtttcoding - Monday, December 17, 2018 2:38 AMOBJECT: 7:480772820:27.Database ID 7
Object ID 480772820
Index ID 27Hello Gail,
thanks a lot for replying. I did search all the indexes for this table, there is only the clustered index and no other index. That's why I was confused about that last part of this wait resource.
Regards,
TomiTry this
😎
USE master;
GO
DECLARE @OBJ_ID NVARCHAR(10) = N'480772820';
DECLARE @IDX_ID NVARCHAR(10) = N'27';
DECLARE @DBID INT = 7;DECLARE @CHECKSTUFF NVARCHAR(MAX) = N'USE ' + DB_NAME(@DBID) + N'
SELECT OBJECT_NAME(' + @OBJ_ID + N') AS OBBJNAME
,SIDX.[name]
FROM sys.indexes SIDX
WHERE sidx.object_id = ' + @OBJ_ID + N'
AND sidx.index_id = ' + @IDX_ID + '
';
EXEC (@CHECKSTUFF);
Hello Eirikur,
thanks for replying. No results from this code. Tried almost the same when checking for the indexes on that object.
Regards,
Tomi
December 18, 2018 at 7:44 am
tttcoding - Tuesday, December 18, 2018 6:35 AMEirikur Eiriksson - Tuesday, December 18, 2018 6:17 AMtttcoding - Tuesday, December 18, 2018 2:03 AMGilaMonster - Monday, December 17, 2018 9:45 AMtttcoding - Monday, December 17, 2018 2:38 AMOBJECT: 7:480772820:27.Database ID 7
Object ID 480772820
Index ID 27Hello Gail,
thanks a lot for replying. I did search all the indexes for this table, there is only the clustered index and no other index. That's why I was confused about that last part of this wait resource.
Regards,
TomiTry this
😎
USE master;
GO
DECLARE @OBJ_ID NVARCHAR(10) = N'480772820';
DECLARE @IDX_ID NVARCHAR(10) = N'27';
DECLARE @DBID INT = 7;DECLARE @CHECKSTUFF NVARCHAR(MAX) = N'USE ' + DB_NAME(@DBID) + N'
SELECT OBJECT_NAME(' + @OBJ_ID + N') AS OBBJNAME
,SIDX.[name]
FROM sys.indexes SIDX
WHERE sidx.object_id = ' + @OBJ_ID + N'
AND sidx.index_id = ' + @IDX_ID + '
';
EXEC (@CHECKSTUFF);Hello Eirikur,
thanks for replying. No results from this code. Tried almost the same when checking for the indexes on that object.
Regards,
Tomi
That's a bit funny, I've used this with SQL Monitor and it has worked fine.
😎
Have you checked the statistics on the object?
Page ID 27 does not make any sense for a user database, and as Gail said, it is the index id.
December 18, 2018 at 1:02 pm
tttcoding - Tuesday, December 18, 2018 1:58 AMSue_H - Monday, December 17, 2018 7:10 AMtttcoding - Monday, December 17, 2018 2:38 AMHello everyone,One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi MarkovskiAre you querying sys.dm_tran_locks? The example doesn't make sense if that's what you are using. The translations of the resource descriptions is based on the resource type and are explained in the documentation.
Sue
Hello Sue, thanks for replying.
I got this information about the blocking through the Red Gate's SQL monitor. There is a head blocked - an update statement, and all the blocked sessions wait for this same resource. I was just confused about the last part of its description because query-ing sys.indexes about that particular object I wasn't able to find an Index with ID of 27.
Thanks a lot,
Tomi
If you know what the update statement is, you should post it. There's an illegal form of joined update that people frequently use that seems to have all the characteristics of "halloweening" despite the optimizer's best efforts to prevent such things that can cripple a system. I've had such problems before where a 2 second update slammed 4 CPUs against the wall for 2 hours. And it's not predictable when it will go bad.
The good part is, if it's what I think it is, the fix is super easy. Just need to see the code to confirm or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2018 at 3:21 am
Jeff Moden - Tuesday, December 18, 2018 1:02 PMtttcoding - Tuesday, December 18, 2018 1:58 AMSue_H - Monday, December 17, 2018 7:10 AMtttcoding - Monday, December 17, 2018 2:38 AMHello everyone,One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi MarkovskiAre you querying sys.dm_tran_locks? The example doesn't make sense if that's what you are using. The translations of the resource descriptions is based on the resource type and are explained in the documentation.
Sue
Hello Sue, thanks for replying.
I got this information about the blocking through the Red Gate's SQL monitor. There is a head blocked - an update statement, and all the blocked sessions wait for this same resource. I was just confused about the last part of its description because query-ing sys.indexes about that particular object I wasn't able to find an Index with ID of 27.
Thanks a lot,
TomiIf you know what the update statement is, you should post it. There's an illegal form of joined update that people frequently use that seems to have all the characteristics of "halloweening" despite the optimizer's best efforts to prevent such things that can cripple a system. I've had such problems before where a 2 second update slammed 4 CPUs against the wall for 2 hours. And it's not predictable when it will go bad.
The good part is, if it's what I think it is, the fix is super easy. Just need to see the code to confirm or not.
Hello Jeff,
yes, I have the update that caused the blocking, I must have had to mask it a bit for to post it here, but it is in its right form:
UPDATE t1
SET t1.ExDate = t2.ExDate
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t2.ID = t1.ID AND (t1.Type = 1 OR t1.Type = 2)
WHERE t1.ExDate != t2.ExDate
Regards,
Tomi
December 19, 2018 at 9:23 am
tttcoding - Wednesday, December 19, 2018 3:21 AMJeff Moden - Tuesday, December 18, 2018 1:02 PMtttcoding - Tuesday, December 18, 2018 1:58 AMSue_H - Monday, December 17, 2018 7:10 AMtttcoding - Monday, December 17, 2018 2:38 AMHello everyone,One of our servers is experiencing locks on particular objects of a production database, like for an example OBJECT: 7:480772820:27. I am trying to dig in deep in what was locked, and I can't find any documentation on what the third part of such a wait resource means. The database on which this lock happened is in READ COMMITTED SNAPSHOT isolation level, meaning we are using row versioning. I did read through the web, where the explanations are that the third part if 0 means heap, 1 means cluster index, and everything larger than 1 should be non-clustered index.
It is not the case for this example because we have no non-clustered indexes for this table - we found that it is a table by the object_id. The lock requested is LCK_M_IX. The table is not partitioned. Is '27' a page number on which the lock was requested?
Thanks in advance,
Tomi MarkovskiAre you querying sys.dm_tran_locks? The example doesn't make sense if that's what you are using. The translations of the resource descriptions is based on the resource type and are explained in the documentation.
Sue
Hello Sue, thanks for replying.
I got this information about the blocking through the Red Gate's SQL monitor. There is a head blocked - an update statement, and all the blocked sessions wait for this same resource. I was just confused about the last part of its description because query-ing sys.indexes about that particular object I wasn't able to find an Index with ID of 27.
Thanks a lot,
TomiIf you know what the update statement is, you should post it. There's an illegal form of joined update that people frequently use that seems to have all the characteristics of "halloweening" despite the optimizer's best efforts to prevent such things that can cripple a system. I've had such problems before where a 2 second update slammed 4 CPUs against the wall for 2 hours. And it's not predictable when it will go bad.
The good part is, if it's what I think it is, the fix is super easy. Just need to see the code to confirm or not.
Hello Jeff,
yes, I have the update that caused the blocking, I must have had to mask it a bit for to post it here, but it is in its right form:
UPDATE t1
SET t1.ExDate = t2.ExDate
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t2.ID = t1.ID AND (t1.Type = 1 OR t1.Type = 2)
WHERE t1.ExDate != t2.ExDateRegards,
Tomi
Ok... that's in a "legal" form where the table being updated IS in the FROM clause with a proper join.
However, and it's not supposed to present a problem, the code is updating a column that is being joined on for the update. Despite the fact that SQL Server generally does a table spool to help alleviate problems with such an update, it is sometimes still a problem.
My recommendation would be to use a SELECT to create a temporary table of the IDs of the rows that need to be updated along with the dates they should be updated to. Then, do a dupe check for IDs. If there are any, you've identified the first part of the problem in the form of an accidental many-to-many join (also known as an accidental CROSS JOIN) and you need to decide on the best way to avoid having duplicate IDs to be updated. SQL Server won't update the same row more than once in the update but it can make a table sppol that's absolutely huge and that's not good for performance or resource usage. Once you've got that down, use that same table to do the actual update of the ExDatecolumn. The join should ONLY be by ID for that.
Someone else may suggest a method to do it all in one query and you can certainly try that. I'm just suggesting what I've had the most success with and it also creates that temporary table for some prechecks and allows me to troubleshoot what's going on if another performance issue rears its ugly head in the future.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2018 at 10:45 am
Dumb thought, could ID:27 be a statistic on the table?
December 23, 2018 at 12:07 pm
Lynn Pettis - Wednesday, December 19, 2018 10:45 AMDumb thought, could ID:27 be a statistic on the table?
Hello Lynn,
I have only three statistics for that table, one for the clustered index (which is the only index for this table), and two auto-created statistics. Querying the sys.stats I can find these three statistics, with values of 1 to 3 for the stats_id column. I don't see 27 anywhere 🙂
Regards,
Tomi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply