Question :
We have recently migrated from SQL Server 2008 R2 to SQL Server 2012 Standard Edition. We and witnessing poor performance related to quite a number of stored procedures running against a database that is not that big in size (8 GB).
While analyzing the plans we have seen lot of warnings pop ups for eg:
CONVERT_IMPLICIT(int,[DBNAME].[dbo].[tblname].[col1] as
[s].[col1],0) CONVERT_IMPLICIT(nvarchar(100),[DBANME].[dbo].[tblname2].[col1]
as [s].col1],0)
I have a few questions related to above.
-
How can I find the mismatch for those datatypes in the warnings shown in the plan? Is there any script or do I have to manually check the data types for each column?
-
When we use index hints, the query performs better. Does that mean that when the above warnings are received, the optimizer won’t use available nonclustered indexes? If yes, why so? Also, will using index hints be a benefit in the future or harm?
We have already conveyed these to the developers to change or look at data types for any mismatch. Is there be any better approach to solve this problem?
- The server and database collation are same:
SQL_Latin1_General_CP1_CI_AS - Statistics were updated using Ola’s script a couple of hours back
- The actual plan can be found here
Answer :
sp_BlitzCache will break down implicit conversion information for you, for seek affecting converts.
For example, with this query (for brevity):
DECLARE @butthead SQL_VARIANT = N'your mom'
SELECT COUNT(*) FROM dbo.Comments AS c WHERE c.Text = @butthead
And this plan, it’ll return results like this:
If you click on the implicit conversion info column, it’ll give you a break down of what happened where:
So while it doesn’t search your plan cache specifically for that issue, it will give you your most resource intensive queries and warn you when implicit conversion is a problem, and then break down the details.
Hope this helps!