Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

Gulli Meel — Thu, 10 May 2012 11:45:54 GMT

One more thing you could do is that. Find the threshold value beyond which scan will be better and before this threshold seek + lookup is better. So if value is above threshold execute a query with forced scan plan using hints and below that use forced seek + lookup. I know it can not be done at optimizer level because optimizer wont be having these details stored somewhere.But an application designer or developer who knows what kind of data is there in table and how the data is accessed and how many times sql is called and what is the ratio of the values below threshold and above threshold. Thus based on this you could tweak the code to take adavntage of this technique to make all your queries as efficient as possible.

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

Conor — Fri, 20 Aug 2010 17:16:22 GMT

caching is done with an exact string match today. (memcmp), broadly.

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

David W Wright — Tue, 17 Aug 2010 20:25:18 GMT

Thanks Leon, that's a useful script.

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

los — Mon, 16 Aug 2010 17:47:10 GMT

Let me start by stating I am quite new to the world of SQL Server and DB administration in general. I'm trying to learn as much as I can.

My understanding of parameterized queries and cached plans is that each subsequent query must be identical to the cached version. My question is how identical must future queries be? Does it have to be identical character for character? Is the optimizer smart enough to recognize simple difference in column or predicate order? What if the only difference is the name of the database being queried?

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

Leon — Mon, 16 Aug 2010 13:45:46 GMT

@David,

I use this query to find the cached query plans. It takes a while to run depending on how you write the where clause but it works most of the time. When run against a production environment usually you can finger the highest volume plan as the one most used. If you click on the xml output and it's a suboptimal plan then you most likely have your winner. Hope this helps.

select * from sys.dm_exec_cached_plans c

cross apply sys.dm_exec_query_plan(plan_handle) p

cross apply sys.dm_exec_sql_text(plan_handle) s

where s.[text] like '%query string here%'

and usecounts > 50 -- I set this around 50 to filter out tests run by developers. For the most part I don't care about the low volume plans, it's usually the high volume ones that kill you, in prod at least.

Leon

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

David W Wright — Mon, 16 Aug 2010 13:44:19 GMT

Interesting. My problem wasn't due to a parameter, so I didn't use OPTIMISE FOR @p=whatever. I added option(RECOMPILE) to the query (instead of for the whole proc), but it did not help, perhaps because it was recompiling against misleading stats from when [Selection_Date] was always NULL, (is that what you mean by a gatekeeper case?). So I put UPDATE STATISTICS [tablename]; immediately before the query, and this + the recompile in the query DOES seem to have fixed the problem. I haven't been able to make it go wrong yet anyway!

Thanks for your help and guidance. I think I've fixed my problem, and I've learned a lot about SQL Server DMVs, statistics and execution plans in the process.

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

Conor Cunningham [MSFT] — Mon, 16 Aug 2010 00:42:58 GMT

Please read my earlier blog post on batch compilation in SQL Server:

blogs.msdn.com/.../conor-vs-dynamic-sql-vs-procedures-vs-plan-quality-for-parameterized-queries.aspx

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

David W Wright — Sun, 15 Aug 2010 20:17:03 GMT

Thanks. The profiler didn't help much - simply too much data - but

SELECT * FROM sys.dm_exec_requests ORDER BY logical_reads DESC;

gave me a clue - though sys.dm_exec_query_plan() showed a plan of NULL while running, and there was nothing in the cache afterwards due to WITH RECOMPILE. However, I did manage to work out a situation in which the problem ALWAYS happens, so I could then run the query under test in SSMS on my development system and see the Actual Execution Plan when it finished - 7 hours later! 99% of time doing CLUSTERED INDEX SEEK on estimated 1 row, actual 9722 rows. BUT the rest of the plan involved two Nested Loops (1%), one of which had met an Actual 2498 million rows instead of its Estimated 1 row!!!

So I did EXEC sp_updatestats and ran the query again - this time it took just 4 seconds, with a completely different plan that used the secondary indexes on [Order_Number] and [Order_Date], and did Hash Matches. This DESPITE the fact that there was an sp_updatestats just before the proc had run. Which also gave me a clue; the query in question is near the end of the proc, and depends on fields [Order_Date] and [Selection_Date]. The latter is set by the previous UPDATE step. If we're loading new data, [Selection_Date] would have been all NULL at the start of the proc - so the plan created at that point would have assumed it still was; hence the pathologically bad plan. Maybe I should put a WITH RECOMPILE in the query itself? Or an UPDATE STATS for that index? Or add a hint to use the [Index_Date] index? (This is all new to me; I always thought "SQL Server knows best" before!).

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

Conor — Sat, 14 Aug 2010 00:16:35 GMT

The main thing I can recommend is capturing the query plans (sql profiler, DMVs) when you see this case happening. This would give you insight into what is happening - table scan, lots of random IOs, etc.

re: Conor vs. Misbehaving Parameterized Queries + OPTIMIZE FOR hints

David W Wright — Fri, 13 Aug 2010 23:01:54 GMT

And of course, soon after I posted that, the job once again took 100 minutes for a step that normally takes 2 seconds, DESPITE the WITH RECOMPILE