For Part 1 of this series, see LINQ: The Good, The Bad, and The Poorly Optimized.
T-SQL supports a variety of “hints”. They are hints in the same sense that your mother used to hint that it’s time to wash up and come to dinner. In other words, it’s what you have to do whether you like it or not.
Hints provide T-SQL developers with the ability to override almost all of the judgments made by the query optimizer including JOIN types, index usage, and locks requested. That’s great for the SQL developer but of no help to the LINQ developer without any direct control over the generated SQL. In some cases, however, it may be possible to apply hints after-the-fact to queries using the so-called plan guides. Unfortunately, plan guides support only a subset of the full spectrum of available hints. Fortunately, query plans to allow us to control how pre-existing SQL should be parameterized. Plan guides come in three flavors, object, SQL, and template. Either the SQL or the template guide can be used to force parameterization on a SELECT statement generated by LINQ.
We must start with the exact query to be parameterized. SQL Server is very literal when it matches a plan guide with a query. “Close enough” won’t do. Fortunately, we have access to the SELECT statement via the Profiler. We literally copy-and-paste the query argument to sp_executesql into the @stmt parameter of the sp_create_plan_guide procedure. Similarly, the parameters defined in the call to sp_executesql are copied to the @params argument for sp_create_plan_guide. In this example, the hint is a query hint. If we had direct control over the query text, we could add the query hint to the very end of the SELECT statement.
Since sp_create_plan_guide, like sp_executesql, is an extended stored procedure, it does not support implicit type conversion for ANSI query strings. Therefore, all literal strings must be prefixed with the national indicator N so the strings are represented in unicode.
@name = N’GetRegionCustomers_Guide’,
@stmt = N’SELECT [t0].[CustomerID] AS [Item1], [t0].[CompanyName] AS [Item2], [t0].[Region] AS [Item3]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[Region] = @p0′,
@type = N’SQL’,
@params = N’@p0 nvarchar(4000)’,
@hints = N’OPTION (OPTIMIZE FOR(@p0=”California”))’
After this code is executed and the plan guide successfully created, SQL Server will match the statement in the guide to incoming queries, and if it finds a match the hint will be applied.
Once again we will run DBCC FREEPROCCACHE on the server and then run the F# LINQ program. We should see the Delaware plan in the procedure cache as before. We then create the plan guide. Like the creation of stored procedures and database-specific objects, the plan guide create statement must be run in a window connected to the Bigwind database.
Curiously, the existing cached plan is flushed from the cache when the plan guide is created, leaving no plan. When the F# LINQ code is run yet again, another plan appears in the procedure cache, but this time it is the California plan and we see that the plan guide has done its job.
Many .Net developers are concerned with the performance of LINQ queries. Aside from using good standard practices most effective optimization techniques, such as querying views rather than tables and using plan guides as we illustrated here, will require some server-side T-SQL techniques.