SQL Prompt taking a long time to refresh suggestions

mcook

Recently, SQL Prompt has been taking a very long time (10+ seconds) to refresh the suggestions. It seems to primarily be happening with a new SSMS instance, but also happens when switching catalogs (although that isn't taking as long).

I just installed the latest version a few days ago.

SSMS Version: 20.2.30.0
Prompt Version: 10.14.23.11448

I've turned on verbose logging and I've attached the log details.

Any ideas?

jsreynolds1

Same, and it was this version, 10.14.23.11448 which started the lagging. It just suddenly feels sluggish.

Jon_Kirkwood

Hi @mcook


Thank you for reaching out on the Redgate forums and sorry to hear about your lag in SQL Prompt running in SSMS20.

There are general slownesses which can be found when running SQL Prompt and/or other Redgate tools within SSMS as they do often scan databases to assist with different functionalities that may be taking place.

As a first step we recommend this article to go through some of the more common concerns:
SSMS is very slow with SQL Prompt installed


Thank you for providing your logs, there are a few warnings and errors connected to network availability. Are you connecting to any databases via VPN or other external network pipeline?

Are you able to confirm the database types you have in SSMS? Local, Azure etc. If you are using Azure, can you confirm your connection/authentication type. 

BiestMode

@Jon_Kirkwood
I am also experiencing this same issue. We've got some workstations with Prompt 10.14.21.9884 and others on 10.14.23.11448. We're on the same network connected to the same database. When you refresh suggestions on the system running 10.14.23.11448 prompt is querying the object definition for all stored procedures which takes some time to run. Doing the same thing on the older copy of prompt does not do this.

This is the query in question we see running on the newer version. 

SELECT

obj.id AS object_id,

OBJECT_DEFINITION(obj.id) AS object_definition
FROM (VALUES ...

jsreynolds1

I've given up; so many issues with the preview releases since after June of this year... this slowness, setting results in an update no longer complete for me, breaking issue with SQL compare, etc.

I'm about to just revert to June 14th release when everything 'just worked' and see if I can wait it out. I don't have time to install, uninstall, provide detailed logs, etc. for every little thing that comes along.

My bad for trying the preview releases. It was a good run though.

mcook

@Jon_Kirkwood yes, this primarily affects databases that I'm connecting to over a P2P VPN network. Databases that are within the local network still take a few seconds to refresh the SQL Prompt suggestions, but not as long as databases that reside in another data center. All of our databases are local, we do not have any Azure databases that we are connecting to.

I have reviewed the document you provided and have already done most of the suggestions there. However, the slowness is directly related to SQL Prompt, it's not general slowness. This was not happening with previous versions, so it seems that rolling back to a previous version sounds like the best remedy unless there is a way to disable the additional database scans that are taking place.