更新sys.objects上的统计数据

Question

我正在处理一个查询，以便给我所有直接或间接依赖于名为dbo.tblborder的表的数据库对象，这个表是非常重要的。 然而，特别是这个问题与此查询的查询计划有关， 因为我在查询计划中看到了警告（在不同的排序运算符中） 两种类型的警告，一种与溢出到tempDB相关，另一种与数据类型转换和基数估计相关。 查询和查询计划在图片之后进一步展示。 问题 由于我正在处理系统对象，我如何找出哪些对象需要更新统计信息？ 或者，如何解决查询计划中的这个警告？ 关于数据类型转换，有什么办法可以避免这个问题和基数估计问题吗？ 也许有一些跟踪标志吗？ 这是一个600GB的数据库，我想找出对特定表的所有依赖关系，仅一级就显示了325个对象，但这不是我每天都会运行的查询。我对清除这些警告感兴趣，但这不是生死攸关的问题。 信息

关于tempdb泄漏的警告的第一张图片：

第二张关于tempdb溢出的警告图片。

第三个警告 - 与数据类型转换有关，可能会影响基数估计。

;WITH Radhe AS (

            SELECT DISTINCT 
            s2.object_id, 
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name, 
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=SCHEMA_NAME(S1.schema_id) + '.' + S1.Name,  
            0 as Level

            FROM sys.sysdepends DP

            INNER JOIN sys.objects S1 
                    ON S1.object_id = DP.DepID

            INNER JOIN sys.objects S2 
                    ON S2.object_id = DP.ID

            WHERE S1.object_id = OBJECT_ID('DBO.tblborder')

            UNION ALL


            SELECT  
            s2.object_id, 
            Name=SCHEMA_NAME(S2.schema_id) + '.' + S2.Name, 
            ObjectType = S2.Type,
            DependsOn = s1.object_id,
            DependsOn_Name=s1.Name,  
            Level + 1 

            FROM sys.sysdepends DP

            INNER JOIN Radhe S1 
                       ON S1.object_id = DP.ID

            INNER JOIN sys.objects S2 
                       ON S2.object_id = DP.DepID

            WHERE Level < 100
              AND S1.object_id <> S2.object_id
              AND S2.object_id <> OBJECT_ID('DBO.tblborder')

)

SELECT DISTINCT * 
FROM Radhe
ORDER BY LEVEL DESC, DependsOn_Name

这是此查询的查询计划

在按照如何更新数据库系统表的统计信息的方式更新统计信息之后：

DECLARE @TSql NVARCHAR(MAX) = ''
SELECT @TSql = @TSql +  'UPDATE STATISTICS sys.' + o.name + ' WITH FULLSCAN;' + CHAR(13) + CHAR(10)
FROM sys.objects o
WHERE o.type in ('S')
ORDER BY o.name

--Verify/test commands.
PRINT @TSql

与tempdb溢出相关的警告仍然存在，但是根据下面的图片，它们已经发生了变化。

虽然以下警告没有提到或解决：

在表达式中进行类型转换(CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0))可能会影响查询计划选择中的"CardinalityEstimate"，在表达式中进行类型转换(CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0))可能会影响查询计划选择中的"CardinalityEstimate"

相关的问答如何更新数据库系统表的统计信息非常好，但似乎并没有完全解决我的问题，并且没有涉及到"cardinality estimate"警告。

- Marcello Miorelli

Answer 1

你看到的警告很可能来自 sys.sysdepends 视图。

如果你使用脚本来生成它，

EXEC sys.sp_helptext @objname = N'sys.sysdepends'

定义中有一堆转换和其他胡言乱语。

CREATE VIEW sys.sysdepends AS  
 SELECT  
  id = object_id,  
  depid = referenced_major_id,  
  number = convert(smallint,  
   case when objectproperty(object_id, 'isprocedure') = 1 then 1 else column_id end),  
  depnumber = convert(smallint, referenced_minor_id),  
  status = convert(smallint, is_select_all * 2 + is_updated * 4 + is_selected * 8),  
  deptype = class,  
  depdbid = convert(smallint, 0),  
  depsiteid = convert(smallint, 0),  
  selall = is_select_all,  
  resultobj = is_updated,  
  readobj = is_selected  
 FROM sys.sql_dependencies  
 WHERE class < 2  
 UNION ALL  
 SELECT  -- blobtype dependencies  
  id = object_id, depid = object_id,  
  number = convert(smallint, column_id), depnumber = convert(smallint, type_column_id),  
  status = convert(smallint, 0), deptype = sysconv(tinyint, 1),  
  depdbid = convert(smallint, 0), depsiteid = convert(smallint, 0),  
  selall = sysconv(bit, 0), resultobj = sysconv(bit, 0), readobj = sysconv(bit, 0)  
 FROM sys.fulltext_index_columns  
 WHERE type_column_id IS NOT NULL  

sys.objects，另一方面，相对简单。

CREATE VIEW sys.objects AS  
 SELECT name,   
  object_id,  
  principal_id,  
  schema_id,  
  parent_object_id,  
  type,   
  type_desc,   
  create_date,   
  modify_date,  
  is_ms_shipped,  
  is_published,  
  is_schema_published  
 FROM sys.objects$  

sys.sysdepends的视图定义会在单独查询时引发相同的警告。

SELECT *
FROM  sys.sysdepends  

总的来说，如果您想控制数据类型和索引，并在引用系统视图或表时具有一些性能调优能力，最好的方法是先将它们转储到临时表中。

- Erik Darling

Answer 2

查询计划中的警告只有在查询性能未达到可接受水平时才会成为问题。如果我的查询在10毫秒内完成，那么为了消除警告而花时间让其更快没什么意义。事实上，任何溢出到tempdb的查询都会引发警告。我很想看到一个永远不会溢出到tempdb的系统。 此外，溢出到tempdb有很多可能的原因。实际上，有时SQL Server会故意将结果溢出到tempdb。根据系统的不同，更新底层表的统计信息可能是解决该问题的合理步骤之一。然而，并不总是能解决问题。 你似乎过于注重需要使用系统视图这一事实。把它想象成针对无法修改的供应商提供的视图编写代码。你可以使用几乎所有相同的优化技巧来优化其他查询。我认为你只是不能在隐藏的系统表上创建索引或统计信息。 Erik已经回答了关于数据类型转换警告的问题，所以我将重点回答其他问题。 我知道的最简单的找到使用哪些系统对象的方法是在运行查询之前输入"SET STATISTICS IO, TIME ON;"。例如：

SET STATISTICS IO, TIME ON;

SELECT COUNT(*) FROM sys.objects;

-- Table 'sysschobjs'. Scan count 1

UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN;

你可能已经体验到，仅仅这样做可能还不足以消除溢出问题。我怀疑问题出在你写的递归查询上。SQL Server查询优化器经常难以准确估计递归查询将返回多少行数据。对我来说，这似乎是一个合理的限制。感觉像是一件非常难以建模的事情。 也许问题不在于它是一个递归查询。你还可以尝试哪些其他方法来解决溢出问题呢？ 1. 将重要的中间结果存入临时表中 随着行通过查询计划流动，基数估计往往变得更糟。你可以将一些查询结果放入临时表中，并在查询中引用该临时表。对于某些查询，只需将一个小型结果集放入临时表中，就可以获得大幅提升的性能和估计值。 2. 尝试使用传统的优化器。 我曾看到9481跟踪标志改善了针对系统视图的查询性能。如果你认为存在基数估计问题，这是一个可以尝试的方法。 3. 欺骗SQL Server以发出更大的查询内存授予。 在SQL Server 2016中，使用新的MIN_GRANT_PERCENT查询提示相对容易。在旧版本中则稍微困难一些。您需要编写逻辑上等效的代码，以增加预计返回的行数。 4. 重写查询，使得最后不需要排序。 我还没有查看过您的代码，所以无法确定这个建议是否简单明了。 至于其他与溢出处理相关的追踪标志，我只知道trace flag 7470，但它可能不适用于您的情况。

- Joe Obbish