时间戳

首页 > 解决方案 > TSQL Performance Issues with SYS tables

sql-server - TSQL Performance Issues with SYS tables

问题描述

I'm running this script to get list of all objects which can use tables from my find list, this is raw list which later will be feed into sys.dm_sql_referenced_entities(Object). And I see super low performance, it's never finished, all tables have 2K rows max, is it because using SYSNAME data types maybe or something?

I see problem on that FindList join which takes 90% performance. I'm on production grade SQL Server 2016, where I never had any problems. There was dynamic SQL which produce this query, and I'm testing it now as a standalone script, my original join to find table was in first step, it's commented now, I played with different ways, results are same super slow (never finished). Used LEFT vs INNER join too to try, same thing. Appreciate you leads.

FindList table doesn't have any IDX, I think it won't help as I'm using LIKE on JOIN + size is small (2K max). DB also small has total 100 objects in sys.modules

; WITH cte AS 
(
    SELECT --TOP 2                     -- 100 rows
        mod.Definition,            
        o.name ObjName, 
        o.schema_id,
        sch.Name Schema_name,
        o.object_id,
        o.type ObjType, 
        o.type_desc ObjTypeDesc 
--       ,x.table_name --, 
    FROM  
        myDB.sys.sql_modules mod
--      INNER JOIN   (SELECT   TABLE_NAME FROM myDB.dbo.FindList  WHERE rn =1) x     ON    mod.Definition LIKE '%'+x.TABLE_NAME+' %' 
    INNER JOIN 
        myDB.sys.all_objects o ON o.object_id = mod.object_id   
    INNER JOIN 
        myDB.sys.schemas sch ON sch.schema_id = o.schema_id  
    WHERE
        o.name LIKE '%'+'0'+'%'            -- this part from dynamic SQL
        OR '0'  = '0' 
),  cte_table AS 
(
    SELECT TABLE_NAME 
    FROM myDB.dbo.FindList  
    WHERE rn =1     -- 2000  rows
) 
SELECT  
    ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) rowNum,
    mod.*,
    x.table_name
FROM 
    cte mod 
LEFT JOIN    
    cte_table x ON mod.Definition LIKE '%' + x.TABLE_NAME + ' %'

Estimated execution plan: Est Plan picture

标签： sql-servertsql

解决方案

像这样的表达式是性能杀手：

mod.Definition LIKE '%' + x.TABLE_NAME + ' %'

您尝试做的事情可能包括在：

sp_depends (Transact-SQL)

sp_depends 手册

ps：要获得更详细的答案，您需要提供可以在任何空白系统上执行的查询。（包括创建表语句等。）

推荐阅读