SQL Intellisense

Question

SQL Intellisense

HBK_4700

SSCrazy Eights

Points: 8932
More actions
December 11, 2013 at 3:51 pm

#285936

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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

SQLBlimp SSCertifiable Points: 6330 More actions · Answer 2

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.