SSMS Expanding Tables Lock Timeout for DB_READER

2.2k views Asked by At

I have a very odd issue.

If a user with public and db_datareader access attempts to expand the tables in SSMS 17.2 they receive a Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222) error.

If a user with sysadmin does the same thing, the operation works as expected.

I ran the profiler while SSMS was expanding the tables which returned the code below.

If I run this code as an administrator, it completes in less than a second. However, when the user with db_datareader access runs it it takes over 20 seconds to execute.

What could be causing it?

SET LOCK_TIMEOUT 10000

exec sp_executesql N'SELECT
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/Table[@Name='' + quotename(tbl.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(tbl.schema_id),'''''''') + '']'' AS [Urn],
tbl.create_date AS [CreateDate],
CAST(tbl.is_memory_optimized AS bit) AS [IsMemoryOptimized],
CAST(CASE idx.type WHEN 5 THEN 1 ELSE 0 END AS bit) AS [HasClusteredColumnStoreIndex],
CAST(tbl.is_remote_data_archive_enabled AS bit) AS [RemoteDataArchiveEnabled],
tbl.temporal_type AS [TemporalType],
CAST(CASE WHEN ''PS''=dsidx.type THEN 1 ELSE 0 END AS bit) AS [IsPartitioned],
CAST(
        ISNULL((SELECT distinct 1 from sys.all_columns
                WHERE object_id = tbl.object_id 
                AND is_sparse = 1), 0)
       AS bit) AS [HasSparseColumn],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type > 1 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasNonClusteredIndex],
CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusteredIndex],
CAST(case idx.index_id when 1 then case when (idx.is_primary_key + 2*idx.is_unique_constraint = 1) then 1 else 0 end else 0 end AS bit) AS [HasPrimaryClusteredIndex],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type = 3 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasXmlIndex],
CAST(ISNULL((select top 1 1 from sys.all_columns as clmns join sys.types as usrt on usrt.user_type_id = clmns.user_type_id where clmns.object_id = tbl.object_id and usrt.name = N''xml''), 0) AS bit) AS [HasXmlData],
CAST(ISNULL((select top 1 1 from sys.all_columns as clmns join sys.types as usrt on usrt.user_type_id = clmns.user_type_id where clmns.object_id = tbl.object_id and usrt.name in (N''geometry'', N''geography'')), 0) AS bit) AS [HasSpatialData],
CAST(ISNULL((select top 1 1 from sys.indexes ind where ind.object_id = tbl.object_id and ind.type = 6 and ind.is_hypothetical = 0 ), 0) AS bit) AS [HasNonClusteredColumnStoreIndex]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON 
        idx.object_id = tbl.object_id and (idx.index_id < @_msparam_0  or (tbl.is_memory_optimized = 1 and idx.index_id = (select min(index_id) from sys.indexes where object_id = tbl.object_id)))

LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id = idx.data_space_id
WHERE
(CAST(
 case 
    when tbl.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = tbl.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit)=@_msparam_1 and tbl.is_filetable=@_msparam_2 and CAST(tbl.is_memory_optimized AS bit)=@_msparam_3 and tbl.temporal_type=@_msparam_4 and CAST(tbl.is_external AS bit)=@_msparam_5)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'2',@_msparam_1=N'0',@_msparam_2=N'0',@_msparam_3=N'0',@_msparam_4=N'0',@_msparam_5=N'0'

Edit 1: @Dan Guzman's suggestion to check the execution plans was a good one. The execution plans between both users was identical, except for the following Wait for the db_datareader user: <Wait WaitType="LCK_M_SCH_S" WaitTimeMs="7868" WaitCount="1" />.

There's a large INSERT BULK operation being run against that database, but I don't understand why it would the lock would appear for one user but not the other?

0

There are 0 answers