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
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:
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