[Bug]: The procedure report opens very slowly in SSMS

[anapplebeee]

What happened?

open some report is very slowly， it maybe will send these sql to database， i have 2000+ procedure， it will cost 5-6 minutes
SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
'Server[@name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, 'OwnerId')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = 'P' OR sp.type = 'RF' OR sp.type='PC')and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit)=0 and ISNULL(sm.uses_native_compilation,0)=0)

SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
'Server[@name=' + quotename(CAST(
serverproperty(N'Servername')
AS sysname),'''') + ']' + '/Database[@name=' + quotename(db_name(),'''') + ']' + '/StoredProcedure[@name=' + quotename(sp.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(sp.schema_id),'''') + ']' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N'P' THEN 1 WHEN sp.type = N'PC' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, 'OwnerId')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = 'P' OR sp.type = 'RF' OR sp.type='PC')and(CAST(
case
when sp.is_ms_shipped = 1 then 1
when (
select major_id
from
sys.extended_properties
where
major_id = sp.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit)=0 and ISNULL(sm.uses_native_compilation,0)=1)

Version

BABEL_5_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

No response

Relevant log output

Code of Conduct

• I agree to follow this project's Code of Conduct.

[tanscorpio7]

@anapplebeee Could you please share the plan for this query execution ?

[jsudrik]

@anapplebeee , please also let us know if this is coming from a tool such as SSMS or ORM tool and if there is a possibility of re-writing this query for better efficiency? Which client you are using for this query?

Thanks in advance.

[anapplebeee]

@jsudrik @tanscorpio7
it is coming from ssms18 and i tried in ssms20 is same condition， plan is very long， i used babelfish4.6

plan.sql
maybe some sys.view modify to cte MATERIALIZED is a good idea， i modify sys.all_sql_modules_internal to MATERIALIZED will have a good performance，like this

newview.sql

plan3.sql