Can SQL Prompt be causing blocking on db?

Lenka

I was just told by one of our DBAs that “RedGate Intelligence”, I’m sure he means SQL Prompts is casing locking in production. I have not looked into exactly how it works, but just the fact that we you have “Refresh suggestions” in order to pull in the latest database changes would tell me that it’s not live querying the database schema to pull this information and should not be causing any locking. I’m worried that they will try to keep us from using SQL Prompt (he was hinting to that). Could someone please let me know if it is accurate and if SQL Prompt could actually be causing locking and point me to a place in the documentation that would clarify this.

Eddie D

Hi, thank you for your post into the forum and sorry that you have encountered a problem.

What version of SQL Prompt are you using?

Version of SSMS, you are using?

Do by any chance also have SQL Search installed? This is another Redgate product that plugs-in to SSMS. If you do have SQL Search installed, what version is installed?

Many Thanks
Eddie

Eddie D

Hi, in case it is actually SQL Search causing the blocking you have reported in this forum topic.

SQL Search V2.3.8.1089 has just been made available. This new version contains a bug fix for the indexing problem on startup of SSMS, which maybe related to the blocking problem you reported.
To download SQL Serach V2.3.8.1089, please use this link to download:
ftp://support.red-gate.com/patches/SQLS ... Search.zip

Please do upgrade SQL Search and confirm that the problem is resolved in your environment. Or to inform us that you are still experiencing the problem.

Many Thanks
Eddie

JacobH

@Eddie D  and others, I don't think that blocking issue is fixed. Or it's regressed and appeared again.

I just experienced it on an Azure Managed Instance today. SQL Search window wasn't even active. I hadn't been using it for quite some time. I was working in another window and hit a random schema block that was causing me to not be able to create a view. I ran sp_whoisactive and found a SPID from myself... but no active query windows or connections that matched the SPID.

I let it go for about 6 minutes while my query was blocked. Eventually I started closing tabs in SSMS and viola--SQL Search was the blocker.

Not only was it blocking ME and one of our developers, it was also blocking some background "dmvcollector" query, which I assume is being run by someone/something at Microsoft.

Anyway, long story short, version 3.5.5.2703 of Redgate SQL Search can cause schema blocks (LCK_M_S) in Azure. Looks like the SQL Search window can become frozen in some state where the query doesn't complete.

This is the blocking query:

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