Home Dashboard Directory Help
Search

Allow Intellisense to use Read Uncommitted Isolation levels by akelly


Status: 

Closed
 as Won't Fix Help for as Won't Fix


6
0
Sign in
to vote
Type: Suggestion
ID: 776354
Opened: 1/11/2013 10:43:06 AM
Access Restriction: Public
1
Workaround(s)
view

Description

Currenlty in SQL Server Management Studio 2012 the queries used by Intelisense get blocked under certain conditions such as when another user is running a SELECT INTO query like the one listed below. This is due to the locking on the meta data tables. This results in many hung sessions for no real benefit.

SELECT
tr.name AS [Name],
tr.object_id AS [ID],
CASE WHEN tr.type = N'TR' THEN 1 WHEN tr.type = N'TA' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(
                tr.is_ms_shipped
            AS bit) AS [IsSystemObject],
CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted]
FROM
sys.triggers AS tr
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
WHERE
(tr.parent_class = 0)
ORDER BY
[Name] ASC
Details
Sign in to post a comment.
Posted by Microsoft on 1/25/2013 at 12:25 PM
Thanks for the input however we are not making investments in this area.
Posted by AaronBertrand on 1/11/2013 at 11:21 AM
Agreed, or snapshot if it is determined the target database has it enabled. But with read uncommitted - since 99.99% of the time I am not looking up metadata for objects that are being changed at the exact same time - I'm perfectly ok with not taking locks to get the list of column names. And even if I get the old column name right now while it is being changed underneath me, that's something I'll find out at runtime, and not worth waiting for IMHO.
Sign in to post a workaround.
Posted by TEversTX on 4/11/2014 at 5:04 AM
A couple of ways that might work to get around this:
Turn off Intelisense.
and/or
have the users that want to use intelisense use the SSMS 2008 version
or
I don't thiink its likely to work and I can't try it in my current environment, but maybe changing to the Read Committed Snapshot Isolation level will help.