SQL Server SSMS background Queries locking out by user Queries

• aallee1

  SSC Enthusiast

  Points: 186

  More actions

  December 1, 2017 at 9:00 am

  #341908

  ceDealing with a rather strange issue.

  A user is complaining about getting lock timeout errors when executing a stored procedure from SSMS.

  SQL Server 2014 64-bit Enterprise
  SSMS 2016

  

  I have captured a trace as the person was getting the lock out errors and this is what I have found.

  The Proc gets fired, The SQL Statement is something like

  
  User Query
  
    INSERT INTO UserDB.Schema.TableName WITH (TABLOCK)
  SELECT Col1 , Col2 , Col3
  FROM AnotherUserDB.Schema.TableName

  Nothing fancy pretty simple insert query.

  Then I can see another query coming in from the client machine, which is a SSMS background query.

  SSMS Background Query 1

      SELECT
  sp.name AS [Name],
  sp.object_id AS [ID],
  sp.create_date AS [CreateDate],
  sp.modify_date AS [DateLastModified],
  ISNULL(ssp.name, N'') AS [Owner],
  CAST(case when sp.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
  SCHEMA_NAME(sp.schema_id) AS [Schema],
  CAST(
  case
   when sp.is_ms_shipped = 1 then 1
   when (
    select
      major_id
    from
      sys.extended_properties
    where
      major_id = sp.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) AS [IsSystemObject],
  CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id,N'ExecIsAnsiNullsOn'),0) AS bit) AS [AnsiNullsStatus],
  CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id,N'ExecIsQuotedIdentOn'),0) AS bit) AS [QuotedIdentifierStatus],
  CAST(ISNULL(OBJECTPROPERTYEX(sp.object_id, N'IsSchemaBound'),0) AS bit) AS [IsSchemaBound],
  CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
  CAST(ISNULL(smsp.is_recompiled, ssmsp.is_recompiled) AS bit) AS [Recompile],
  case when amsp.object_id is null then N'' else asmblsp.name end AS [AssemblyName],
  case when amsp.object_id is null then N'' else amsp.assembly_class end AS [ClassName],
  case when amsp.object_id is null then N'' else amsp.assembly_method end AS [MethodName],
  case when amsp.object_id is null then case isnull(smsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case isnull(amsp.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end end AS [ExecutionContext],
  case when amsp.object_id is null then ISNULL(user_name(smsp.execute_as_principal_id),N'') else user_name(amsp.execute_as_principal_id) end AS [ExecutionContextPrincipal],
  CAST(ISNULL(spp.is_auto_executed,0) AS bit) AS [Startup],
  CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType],
  CAST(CASE sp.type WHEN N'RF' THEN 1 ELSE 0 END AS bit) AS [ForReplication],
  ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
  FROM
  sys.all_objects AS sp
  LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, 'OwnerId')))
  LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
  LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
  LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
  LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
  LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
  LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
  WHERE
  (sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)

  This background query gets locked by the User Query for a moment trying to acquire Shared Schema lock (LCK_M_SCH_S) before it is completed.

  SSMS Background Query 2

  After the above the another query call comes in from the client machine and this query also is trying to get schema share lock.

     SELECT
  SCHEMA_NAME(tbl.schema_id) AS [Schema],
  tbl.name AS [Name],
  tbl.object_id AS [ID]
  FROM
  sys.tables AS tbl
  ORDER BY
  [Schema] ASC,[Name] ASC

  This query runs for a couple of seconds before the end user's query connection gets timed-out and the user sees the error message.

  When The management Studio starts it sets the LOCK_TIMEOUT setting to 10000 by default, I can see that from the SQL Server profiler.

  I really can't make any sense of it and how to stop this blocking, Also another important thing is that when the user is logged in SQL Server with different  user it works fine,

  Any advice pointers in the right direction are much appreticated. Thank you.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  December 4, 2017 at 11:54 am

  #1970584

  Try monitoring the locks next time it happens. The queries from SSMS are for checking properties and such of the database. I would wonder if someone isn't checking the properties or doing a "design" from SSMS against the table being inserted into. If that stored procedure isn't doing much then you likely don't need a tablock hint. If it's actually inserting a large amount of data then the users really shouldn't be using SSMS to check on the data or view the table, if that's happening. SSMS wasn't really designed to be an query tool for the users. But you could check locks using something like:
  SELECT
    s.session_id,
    s.[host_name],
    s.[program_name],
    s.login_name,
    s.status,
    DB_NAME(l.resource_database_id) DBName,
    l.resource_type,
    l.request_mode,
    l.request_type,
    l.request_status,
    s.open_transaction_count
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s
ON l.request_session_id = s.session_id

  Sue