How can I improve performance?
I've tested in production environment under load and in dev environment with no load, all with the same slow results.
Is there anything glaring that I can look at? I see some missing index recommendations and I'll explore that route, but I'm pretty green at examining execution plans, so if anyone could offer any other advice that would be great.
Sure, no problem. I realize my question is vague, so if there's anything else I can supply just ask.
I think I can improve performance by replacing some CTE's with temp tables. I'm testing that now.
Attached the plan analysis after running the query in plan explorer.
The MERGE plan performs 1,613 full scans of the Queries table. That table contained 1,692,170 rows when the plan was last compiled. In total, over two billion rows (2,729,470,210 to be precise) are processed by the scans, though 'only' 10,149,811 rows pass the residual predicate on that index scan. The residual predicate applied to two billion rows is:
CONVERT_IMPLICIT(nvarchar(16),[AFS-Prod].[TrueChecks].[Queries].[RoutingNumber] as [q].[RoutingNumber],0)=[AFS-Prod].[Batch].[Queries].[RoutingNumber] as [q].[RoutingNumber] AND ([AFS-Prod].[TrueChecks].[Queries].[ClientId] as [q].[ClientId]<[@clientId] OR [AFS-Prod].[TrueChecks].[Queries].[ClientId] as [q].[ClientId]>[@clientId])
The Key Lookup immediately following the Index Scan is also very expensive. It is executed 10,149,811 times in total, applying the following residual predicate to the rows in locates:
CONVERT_IMPLICIT(nvarchar(50),[AFS-Prod].[TrueChecks].[Queries].[SearchableAccountNumber] as [q].[SearchableAccountNumber],0)=[q].[SearchableAccountNumber] AND CONVERT_IMPLICIT(nvarchar(50),[AFS-Prod].[TrueChecks].[Queries].[SearchableCheckNumber] as [q].[SearchableCheckNumber],0)=[q].[SearchableCheckNumber]
All this was very hard to spot in the original plan, because the query optimizer had engineered a dynamic seek. The huge number of rows were hidden by the innocent-looking Index Seek below the Merge Interval. In fact that 'seek' was selecting all Queries rows where the client id was not equal to the @ClientID parameter. The result was filtered by an equally-horrible residual predicate.
So, yes, the problem is caused by the implicit conversions (because they prevent an effective index seek) and inadequate indexing (because far too many rows require residual predicates and lookups).
Ideally, the Index Scan and Key Lookup with complex residual predicates needs to be replaced by a very selective seek (and not a dynamic one constructed by the optimizer). The seek could possibly include a lightweight residual and key lookups, but only if it is impractical or undesirable to make the index fully SARGable and covering.
The view definition behind the query appears to be moderately complex. A full analysis of the ideal index arrangement would require the view definition and table definitions (for data types).