SQL Intellisense
-
Hi,
We have given read-only access to user1 on one of the production database1
But today we found out from our third party monitoring tool that user’s adhoc query which is executing from SQL Management studio is blocked by some other process. And surprisingly user1 query is running on our main production database (this database is different than database1)
Just wondering what will be the root cause? user1 has read-only access to database1 only.
Is this SQL Intellisense issue? Any idea?
SQL Version is 2008 + SP3
Thanks in advance!
-
What makes you think it's Intellisense? That's the type-ahead mechanisms within Management Studio. I haven't heard of, or seen, major blocking issues from that. Instead, blocking is when a query is running long and holding resources that other queries need. So, for example, if this person is running a query against your database, either connected directly or through four part naming, they can be preventing others from accessing the resources that they are accessing. That's blocking.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I have seen Intellisense blocked by other queries, but it is the Intellisense schema query (to enumerate objects) that is blocked by another process, in tempdb. this is because Intellisense takes a LCK_M_S lock on sysobjects (as close as I can tell) and the other process usually has a lock on for creating a table variable or a temp table.
Never seen Intellisense hold an extended blocking lock.
What locks do you see and what blocking, and what makes you believe that Intellisense is holding a blocking lock?
Thanks
John.
-
FWIW - Super old thread - but I see the exact blocking SQLBlimp is referring to all the time. Wondering it this has been addressed in newer SQL versions. Screen is from SQL2014.
-
I've noticed this on SQL Server Managed Instance too and happening for some days. Posted the Blocked query details below and the screenshot of it.
Even if this session is terminated , another SPId appears and whole SSMS have to be closed.
session_id10655
blocking_session_id10747
login_namexxxxx
open_tran_count0
database_nametempdb
program_nameMicrosoft SQL Server Management Studio - Transact-SQL IntelliSense
wait_info(276385ms)LCK_M_S
tempdb_allocations0
tempdb_current0
reads4730
writes0
physical_reads0
CPU70
used_memory573
statussuspended
request_id0
sql_text
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 ISNULL(user_name(amsp.execute_as_principal_id), N'') 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)--In 'thoughts'...
Lonely Rogue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply