SQL Server – Update Statistics on sys.objects

cardinality-estimatessql serversql server 2014statisticssystem-tables

I am working on a query to give me all the database objects that depend directly or indirectly in any level, to a table called dbo.tblborder, which is heavily depended on.

However, this question in particular is related to the query plan of this query,
because I see warnings in the query plan (in the distinct sort operators)
two types of warnings, one related to spillage to tempDB and the other warning related to conversion of data types and cardinality estimate.

the query and the query plan are further down, after the pictures.

Questions

As I am dealing with system objects, how can I find out which ones I need to update the statistics?

Or otherwise, how to get out of this warning on the query plan?

And regarding the data type conversion, is there anything I can do to avoid this, and the cardinality estimate issue?

Some trace flag maybe?

it is a database of 600GB, I would like to find all the dependencies on a specific table, first level alone shows me 325 objects, but it is not a query I would run everyday. I am interested in clearing those warnings, but it is not a question of life and death.

Information

1st picture of warning on spillage on tempdb:

picture of warning on spillage on tempdb

2nd picture of warning on spillage on tempdb:

enter image description here

3rd warning – related to data type conversion and may affect cardinality estimation:

enter image description here

;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

here is the query plan for this query

After updating the stats this way (from How to update statistics for a database's system tables) :

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

The warnings related to tempdb spillage are still there, however, they have changed as per the picture below:

enter image description here

Nothing has been said or addressed to the following warning though:

Type conversion in expression
(CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0)) may affect
"CardinalityEstimate" in query plan choice, Type conversion in
expression (CONVERT(bigint,[Bocss2].[sys].[sysobjvalues].[value],0))
may affect "CardinalityEstimate" in query plan choice

The related Q & A How to update statistics for a database's system tables is very good, but did not seem to have solved my problem here entirely, plus no address to the cardinality estimate warning.

Best Answer

The warnings you're seeing most likely come from the sys.sysdepends view.

If you script it out using 

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

The definition has a bunch of converts and other nonsense going on.

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, on the other hand, is fairly straightforward.

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$

The view definition for sys.sysdepends causes the same warnings when queried on its own.

SELECT *
FROM  sys.sysdepends

In general, if you want to control datatypes and indexes and have some performance tuning ability when referencing system views or tables, your best bet is to dump them into a temp table first.

Related Question