5

I'm capturing the lock_escalation event on our production servers (running the Enterprise Edition of SQL Server 2014) and trying to make sense of the output, particularly the resource_type field. Although there are 17 different possible values for the resource_type field, all I see is OBJECT and HOBT. On some servers I only see OBJECT, but on one I see HOBT quite a lot.

What is SQL Server trying to tell me with that field? All of our tables, on all servers, are currently configured with LOCK_ESCALATION = TABLE, which we are considering changing. The server where I see HOBT so often has many more partitioned tables than our other servers, but, from what I understand and given our configuration, SQL Server should only be escalating to the table level and not to the partition level.

I am quite certain that all of our tables are configured with LOCK_ESCALATION = TABLE. I ran this query in the database where I am seeing so many HOBT results:

SELECT lock_escalation_desc, total = COUNT(*)
    FROM sys.tables
    GROUP BY lock_escalation_desc

And I got one row:

lock_escalation_desc  total
TABLE                 143

(That particular database is so enormous that it basically gets its own server.)


EDIT: I'm pretty sure now that OBJECT indicates a table lock and HOBT indicates a partition-level lock. But I am very surprised to see the partition-level locking, because the tables involved are not configured to be locked that way, and everything I've read indicates that SQL Server should be locking at the table level.

Here is the full XML for one of the events:

<event name="lock_escalation" package="sqlserver" timestamp="2017-06-08T18:00:23.590Z">
  <data name="resource_type">
    <type name="lock_resource_type" package="sqlserver"></type>
    <value>12</value>
    <text><![CDATA[HOBT]]></text>
  </data>
  <data name="mode">
    <type name="lock_mode" package="sqlserver"></type>
    <value>5</value>
    <text><![CDATA[X]]></text>
  </data>
  <data name="owner_type">
    <type name="lock_owner_type" package="sqlserver"></type>
    <value>1</value>
    <text><![CDATA[Transaction]]></text>
  </data>
  <data name="transaction_id">
    <type name="int64" package="package0"></type>
    <value>10067714031</value>
  </data>
  <data name="database_id">
    <type name="uint32" package="package0"></type>
    <value>8</value>
  </data>
  <data name="lockspace_workspace_id">
    <type name="ptr" package="package0"></type>
    <value>0x0000004e491a1e50</value>
  </data>
  <data name="lockspace_sub_id">
    <type name="uint32" package="package0"></type>
    <value>4</value>
  </data>
  <data name="lockspace_nest_id">
    <type name="uint32" package="package0"></type>
    <value>1</value>
  </data>
  <data name="resource_0">
    <type name="uint32" package="package0"></type>
    <value>38609</value>
  </data>
  <data name="resource_1">
    <type name="uint32" package="package0"></type>
    <value>256</value>
  </data>
  <data name="resource_2">
    <type name="uint32" package="package0"></type>
    <value>0</value>
  </data>
  <data name="escalation_cause">
    <type name="lock_escalation_cause" package="sqlserver"></type>
    <value>0</value>
    <text><![CDATA[Lock threshold]]></text>
  </data>
  <data name="object_id">
    <type name="int32" package="package0"></type>
    <value>1052582838</value>
  </data>
  <data name="hobt_id">
    <type name="uint64" package="package0"></type>
    <value>72057596568207360</value>
  </data>
  <data name="escalated_lock_count">
    <type name="uint32" package="package0"></type>
    <value>6248</value>
  </data>
  <data name="hobt_lock_count">
    <type name="uint32" package="package0"></type>
    <value>6247</value>
  </data>
  <data name="statement">
    <type name="unicode_string" package="package0"></type>
    <value><![CDATA[(@accountId int, @date int, @basisId int)DELETE FROM StatFxLotCumulativeActivity WHERE accountId = @accountId AND reportDate > @date]]></value>
  </data>
  <data name="database_name">
    <type name="unicode_string" package="package0"></type>
    <value><![CDATA[aws_db]]></value>
  </data>
</event>

When I query the system views for the object_id/hobt_id pair referenced in that event:

select * from sys.tables where object_id = 1052582838
select * from sys.partitions where hobt_id = 72057596568207360

It seems clear to me that the table involved is partitioned and has a clustered index:

name                        object_id  principal_id schema_id parent_object_id type type_desc  create_date             modify_date             is_ms_shipped is_published is_schema_published lob_data_space_id filestream_data_space_id max_column_id_used lock_on_bulk_load uses_ansi_nulls is_replicated has_replication_filter is_merge_published is_sync_tran_subscribed has_unchecked_assembly_data text_in_row_limit large_value_types_out_of_row is_tracked_by_cdc lock_escalation lock_escalation_desc is_filetable is_memory_optimized durability durability_desc
--------------------------- ---------- ------------ --------- ---------------- ---- ---------- ----------------------- ----------------------- ------------- ------------ ------------------- ----------------- ------------------------ ------------------ ----------------- --------------- ------------- ---------------------- ------------------ ----------------------- --------------------------- ----------------- ---------------------------- ----------------- --------------- -------------------- ------------ ------------------- ---------- ---------------
StatFxLotCumulativeActivity 1052582838 NULL         1         0                U    USER_TABLE 2015-02-19 10:24:19.463 2017-02-21 14:06:03.857 0             0            0                   0                 NULL                     10                 0                 1               0             0                      0                  0                       0                           0                 0                            0                 0               TABLE                0            0                   0          SCHEMA_AND_DATA

partition_id      object_id  index_id partition_number hobt_id           rows      filestream_filegroup_id data_compression data_compression_desc
----------------- ---------- -------- ---------------- ----------------- --------- ----------------------- ---------------- ---------------------
72057596568207360 1052582838 1        32               72057596568207360 982143818 0                       2                PAGE

The table has 126 partitions, but only 13 have any rows. Running this query:

select * from sys.partitions where object_id = 1052582838 and rows > 0

Gives me this:

partition_number hobt_id              rows
---------------- -------------------- --------------------
18               72057596141502464    34861
21               72057596141699072    89201
22               72057596141764608    573135
23               72057596141830144    471881
24               72057596141895680    1776464
26               72057596142026752    9463538
25               72057596299771904    1454117
27               72057596359606272    3748776
28               72057596360327168    2650215
29               72057596361048064    2700981
30               72057596361179136    448102594
31               72057596361310208    1375155391
32               72057596568207360    998318651

Here's the SSMS-generated DDL for the table:

CREATE TABLE [dbo].[StatLotCumulativeActivity](
    [accountId] [int] NOT NULL,
    [reportDate] [int] NOT NULL,
    [awsId] [int] NOT NULL,
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [value] [bigint] NOT NULL,
    [activityType] [int] NOT NULL,
    [balanceId] [int] NOT NULL,
    [sourceOriginalTransactionId] [int] NULL,
    [basisId] [int] NOT NULL,
    [sourceSecurityId] [int] NULL,
 CONSTRAINT [PK_dbo_StatLotCumulativeActivity] PRIMARY KEY CLUSTERED 
(
    [accountId] ASC,
    [reportDate] ASC,
    [awsId] ASC,
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
  • 1
    with lock escalation=table,sql server should lock at table level,not at partition level,are you sure of LOCK_ESCALATION = TABLE setting on the servers where you are seeing hobt locks more – TheGameiswar Jun 8 '17 at 18:46
  • @TheGameiswar, I've edited my question to include my method for checking our LOCK_ESCALATION settings. – sam.bishop Jun 8 '17 at 19:04
  • 1
    Do you have heaps in this server? – SqlWorldWide Jun 8 '17 at 19:18
  • @SqlWorldWide, I do have a few, but they are unused. I have spot-checked a few of the object_id and hobt_id values from the events being returned, and they map to partitioned tables with clustered indexes. However, the SQL text in the statement field creates temporary tables that don't have clustered indexes. But if they're the culprit then I would expect the object_id and hobt_id values to be for those temporary tables. – sam.bishop Jun 8 '17 at 19:54
  • @SqlWorldWide, most but not all of the statements include references to temporary tables. A few of them are simple DELETE FROM ... statements involving our normal (but partitioned) tables. – sam.bishop Jun 8 '17 at 21:38
3

You shouldn't need to guess at this. According to the documentation for sys.dm_tran_locks (under "Resource Details"), it shows that:

HOBT

means:

Represents a heap or a B-tree. These are the basic access path structures.

and the "resource_associated_entity_id" field (that should be there or should be added) value relates to:

HoBt ID. This value corresponds to sys.partitions.hobt_id.

Meaning, plug the "resource_associated_entity_id" value into:

-- run this in the DB where the object exists
SELECT OBJECT_SCHEMA_NAME(sp.[object_id]) AS [SchemaName],
       OBJECT_NAME(sp.[object_id]) AS [ObjectName],
       si.[name] AS [IndexName],
       *
FROM   sys.partitions sp
INNER JOIN sys.indexes si
        ON si.[object_id] = sp.[object_id]
       AND si.[index_id] = sp.[index_id]
WHERE  sp.[hobt_id] = <reported_hobt_id>;

Some additional resources:

  • My question isn't very clear, but if I edited it further then the comments probably wouldn't make sense. I have two questions. Does OBJECT indicate a table lock and HOBT a partition-level lock? And if so, why is it that partition-level locks are being taken when Microsoft's documentation would indicate that table-level locks would be taken instead? (And to be clear, partition-level locks are what I want, but I just don't expect to see them given our configuration.) – sam.bishop Jun 8 '17 at 20:24
  • @sam.bishop The documentation does say that OBJECT would be Table, View, etc, but less clear about HOBT outside of it being an index but not a "page" or "row" specifically. So maybe could be partition? I do see your reply to SqlWorldWide and that info should be added to the question. It would also help if you could include the "subtype" or state that it was empty, and please include the SQL text that you mentioned. Basically post the full XML in the question, please :) – Solomon Rutzky Jun 8 '17 at 20:31
  • there's no "subtype" information in the lock_escalation event, unfortunately. But I've added the full XML to my question. – sam.bishop Jun 9 '17 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.