Dealing 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
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
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.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!