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.
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.
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.
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.
External links referenced by this document:
Local articles referenced by this article: