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
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.