Update statistics on sys.objects

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:

2nd picture of warning on spillage on tempdb:

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

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

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.

Asked by user22336 (1 )
Apr 14, 2017 at 16:11