I'm getting a warning on my main SELECT on one field in the explain/execution plan of my query on the following:
SELECT ISNULL(CAST(p.loadDiagnostic as nvarchar(4000)),'''') FROM myTable p
This select to convert the loadDiagnostic field which is in XML format coming from myTable, is part of a source T-SQL data flow in an SSDT 2017 DTSX package which flows to a destination table in which the target field is mapped to a DT_WSTR type (a null-terminated Unicode character string with a maximum length of 4000 characters).
The warning in the explain plan is telling me the following:
Type conversion in expression (CONVERT(nvarchar(4000),[p].[loadDiagnostic],0)) may affect "CardinalityEstimate" in query plan choice, The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 6368 KB, Final 6368 KB, Used 96 KB.
Other than removing the CAST type conversion altogether to resolve the warning, I'm looking for an alternate method of handling implicit conversions of XML source fields in SSDT 2017 in order to address this warning.
I've read that adding a TYPE directive on XML fields can sometimes address this issue, but I'm not sure how to handle this in my T-SQL source SELECT or what the impact would be.
Hopefully, I've provided enough details. Any help would be great!