SQL Prompt taking a long time to refresh suggestions

mcookmcook Posts: 3 New member
in SQL Prompt
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?
Tagged:
· Share on Twitter

Answers

  • jsreynolds1jsreynolds1 Posts: 96 Silver 2
    Same, and it was this version, 10.14.23.11448 which started the lagging. It just suddenly feels sluggish.
    · Share on Twitter
  • Jon_KirkwoodJon_Kirkwood Posts: 441 Gold 1
    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. 
    Jon Kirkwood | Technical Support Engineer | Redgate Software
    · Share on Twitter
  • BiestModeBiestMode Posts: 3 Bronze 2
    @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 ...
    · Share on Twitter
  • jsreynolds1jsreynolds1 Posts: 96 Silver 2
    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.
    · Share on Twitter
  • mcookmcook Posts: 3 New member
    @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.
    · Share on Twitter
  • JonasEckermanJonasEckerman Posts: 2 New member
    This is happening for me as well.

    SSMS and SqlPrompt are not slow in general, but SqlPrompt is very slow when refreshing suggestions.

    Before the last two updates the refresh used to be much faster.

    (I hope I will have time to gest some verbose logs later today.)
    · Share on Twitter
  • JonasEckermanJonasEckerman Posts: 2 New member
    Jon_Kirkwood said:

    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. 
    Hello!

    I'm having the same problem. SQL Prompt is very slow when refreshing suggestions, but both SSMS and SQL Prompt are fine other wise.
    The problem started one or two SQL Prompt updates ago (with no SSMS upgrades in between IIRC).

    It seems that most of the time is from "Processing parameters", and made worse because information is fetched twice for the same database.

    I see the problem for the same two sets of dabases in dev, test and prod environments.
    Both databases have a lot of stored procedures, and quite a few functions. and has been deveiped continuously since 2009 or so (so some legacy code may refer to objects or columns that no longer exists).

    The servers are:
    - on-premise SQL Server 2016 Standard, over VPN, using SQL Server autentication.
    - on-premise SQL Server 2019 Developer, without VPN, using Windows authentication.

    The attached log file is for a database in a remote location, so the connection is through a VPN.
    The effect is noticable on for databases on local network as well, but is much worse for the remote databases.

    I hope the attached log file helps.

    Regards
    /Jonas
    · Share on Twitter
Sign In or Register to comment.