How can we help you today? How can we help you today?

Updating the database schema has become slower with new releases.

Hi team,

We're seeing significant performance degradation in Redgate SQL Prompt's schema refresh over recent versions—particularly when retrieving object definitions. While performance was never exactly fast given the size of our schema, it has clearly worsened over time.

Our sys.objects table currently contains around 60,200 entries. In earlier versions of SQL Prompt, schema refreshes were slow but tolerable. Now, however, they are becoming a real bottleneck.

As shown in the following captured statement, SQL Prompt appears to run a query like this:

SELECT obj.id AS object_id,
      OBJECT_DEFINITION(obj.id) AS object_definition
FROM (VALUES (...list of object IDs...))
ORDER BY object_id

This query is inefficient, and the inclusion of the ORDER BY clause places unnecessary load on SQL Server. Handling the sort on the client side might help slightly, but it wouldn't address the root issue, which is the sheer number of OBJECT_DEFINITION(obj.id) calls being made in a single operation.

At the moment, this inefficiency is blocking a key feature: parameter completion for EXEC statements no longer works, because the system can't load the necessary metadata fast enough.

We currently have about 20 active licenses across the team, and all users are experiencing this slowdown consistently during schema updates on many of our environmens, including high-performance production servers with lot of RAM and CPU.

It would be very helpful if future updates to SQL Prompt could improve the efficiency of this metadata loading process, especially in environments with a large number of database objects.

Our used current SQL Prompt version is 10.16.5.15563

Thanks,
Luca

luca.cestola
2

Comments

3 comments

Sort by
  • Courtney Layne

    I recently went from version 10.13.13.2147 to 11.0.3.17058 and it has become unusably slow.  Especially if you have a slow vpn connection to the server.  I will be downgrading if possible.

    Courtney Layne
    2
  • George Walkey

    My Current SW combo which now takes 60-90 seconds to startup every time

    and 1-2 seconds to open Database nodes after connecting to a server

    SSMS 21.3.7, Prompt 11.0.3.17058, win 11 (11 is way slower than 10 by itself)

    im back to using SSMS 20.2.1 with Prompt because its just too slow for daily work

    George Walkey
    1
  • ti 84 calculator

    Thanks for raising this, Luca—completely agree. We’ve noticed similar slowdowns in schema refreshes with large databases. Optimizing how OBJECT_DEFINITION is queried (or batching these calls more efficiently) would definitely improve productivity. Hope the team can prioritize this in upcoming updates.

    ti 84 calculator
    0

Add comment

Please sign in to leave a comment.