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:
- Statistics were updated using Ola's script a couple of hours back
- The actual plan can be found here