Update statistics on sys.objects

Question

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.

Answer 1

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.

Answer 2

Warnings in query plans are only a problem if the query does not perform at an acceptable level. If I have a query that finishes in 10 ms then why spend time as a programmer trying to make it go faster just to make a warning go away? In fact, any query that spills to tempdb will throw a warning. I would be interested to see a system in which queries never spill to tempdb.

Also, there are a variety of possible causes for a spill to tempdb. In fact, sometimes SQL Server will deliberately spill to tempdb. Depending on the system, updating statistics on underlying tables can be a reasonable step towards addressing the issue. However, that will certainly not always fix the issue.

It looks like you're getting too caught up on the fact that you need to use the system views. Think of it like writing code against views provided by a vendor that you can't modify. You can use almost all of the same optimization techniques that you can use against other queries. I think that you just can't create indexes or statistics on the hidden system tables.

Erik already covered your question about the data type conversion warning so I will focus on the other questions.

The simplest way I know of to find which system objects are used is to issue SET STATISTICS IO, TIME ON; before running the query. For example:

SET STATISTICS IO, TIME ON;

SELECT COUNT(*) FROM sys.objects;

-- Table 'sysschobjs'. Scan count 1

UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN;

As you experienced that might not be enough to make the spills go away. I suspect that the problem here is that you wrote a recursive query. The SQL Server query optimizer often has trouble estimating exactly how many rows will be returned by a recursive query. To me, this seems like a reasonable limitation. It feels like something that would be very difficult to model.

Perhaps the problem here isn't that it's a recursive query. What other things can you try to make the spill go away?

1. Put important intermediate result sets into temp tables

Cardinality estimates often get worse as rows flow through the query plan. You may be able to put some of the results of the query into a temp table and reference that temp table in the query instead. For some queries you can get dramatically better performance and estimates just by putting a small result set into a temp table.

2. Try the legacy CE.

I've seen trace flag 9481 improve the performance of queries against the system views. If you think that you have a cardinality estimate issue it's one thing that you can try.

3. Trick SQL Server into issuing a larger query memory grant.

In SQL Server 2016 this is fairly easy with the new MIN_GRANT_PERCENT query hint. On older versions it's a bit harder. You would need to write logically equivalent code that increases the estimated number of rows returned.

4. Rewrite the query so that a sort is not needed at the end

I haven't looked at your code to see how difficult this would be but I think this suggestion is self evident.

In terms of other trace flags to help with spills, I only know of trace flag 7470, which is unlikely to apply to your situation.