A customer found many examples of a SQL Server query that had to convert data in a column because the application passed in a data type different than the SQL Server table was defined. This resulted in a CONVERT_IMPLICIT against that column, which negated the use of the existing index. As a result, a full clustered index scan was required against the 10 million row table and DPA showed this nicely in the Table Tuning Advisor.
The customer then asked if there was a way to search through DPA to find other examples of the CONVERT_IMPLICIT happening. The attached query will search saved plans in the DPA repository for the specified instance and timeframe, and find each of the occurrences. The result set provides the DPA hash values for the SQL statement and plan ranked by the durations. With these values, use the DPA Find SQL feature to review these statements.
Note: since this query is searching through SQL Server plan XML, it may take a minute or more to complete.
