1

Setup:

Using SQL Server 2008 R2.

We've got a stored procedure that has been intermittently running very long. I'd like to test a theory that parameter sniffing is causing the query engine to choose a bad plan.

Question:

How can I copy the query's execution plans from one database to another (test) database?

Note:

I'm fully aware that this may not be parameter sniffing issues. However, I'd like to go through the motions of creating a test plan and using it, if at all possible. Therefore please do not ask me to post code and/or table schema, since this is irrelevant at this time.

Nick Vaccaro
  • 5,428
  • 6
  • 38
  • 60

2 Answers2

3

Plans are not portable, they bind to object IDs. You can use planguides, but they are strictly tied to the database. What you have to do is test on a restored backup of the same database. On a restored backup you can use a planguide. But for relevance the physical characteristics of the machines should be similar (CPUs, RAM, Disks).

Normally though one does not need to resort to such shenanigans as copy the plans. Looking at actual execution plans all the answers are right there.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Makes sense. The issue is that these errors occur very, very infrequently. Actual execution plans show me that everything is working fine, which is total bollocks. Any way I can query the plans? – Nick Vaccaro Apr 25 '13 at 14:02
  • [`sys.dm_exec_query_stats`](http://msdn.microsoft.com/en-us/library/ms189741.aspx) – Remus Rusanu Apr 25 '13 at 14:28
1

Have you tried using OPTIMIZE FOR clause? With it you can tune your procedure easier, and without the risk that plan that you copy from another database will be inappropriate due to differences in those databases (if copying the plan is even possible).

http://www.mssqltips.com/sqlservertip/1354/optimize-parameter-driven-queries-with-sql-server-optimize-for-hint/

AdamL
  • 12,421
  • 5
  • 50
  • 74
  • I have not, but thank you for passing along this info. I'll definitely try that. Will the actual execution plan show differences if I run the same query with 2 different OPTIMIZE FOR variable values? – Nick Vaccaro Apr 25 '13 at 14:03
  • Not in every case, but basically yes. For example, if you have 10M rows, and your parameter is used to filter an indexed column, where 1% of values is 'A', and 99% of values is 'B', then OPTIMIZE FOR 'A' will generate a plan with index seek, and OPTIMIZE FOR 'B' will give you a scan. But remember, that you'll probably need to recompile your procedure in order for changes to take place. – AdamL Apr 29 '13 at 07:43