2

I am a .Net developer. I have a database upgrade coming up (from SQL Server 2005 to SQL Server 2012). The database team will be handling the migration. But I have seen forums posts saying that after migration, the queries are taking too long to execute even though amount of data and number of users are almost same. And one main reason is change in execution plan between these servers. Reference: Performance problem after migration from SQL Server 2005 to 2012

So, I am considering possibility of storing the actual execution plans from my current production server itself. In Oracle, I could find a feature for baselining:-

  1. SQL Plan Management (Part 1 of 4) Creating SQL plan baselines
  2. Using SQL Plan Management

In SQL Server, what is the usual practice for achieving this?

Note: I referred Creating a baseline for SQL Server, yet it doesn't say anything about baselining execution plan

Community
  • 1
LCJ
  • 900
  • 3
  • 7
  • 30

2 Answers2

4

You would just pull the execution plans for the most relevant queries from the plan cache. I would query sys.dm_exec_query_stats, and join to sys.dm_exec_query_plan and sys.dm_exec_sql_text to get the plan and query text, respectively. Within sys.dm_exec_query_stats you can pull queries with the highest IO, CPU, execution count, etc. - whatever is most important to you.

Looking to the future, SQL Server 2016 introduces the Query Store:

Monitoring Performance By Using the Query Store

Paul White
  • 94,921
  • 30
  • 437
  • 687
Erin Stellato
  • 146
  • 2
1

See this example query for getting these execution plans. Now it's ordered by CPU consumption:

SELECT qs.execution_count,
       qs.total_worker_time/1000 total_worker_time_msec,
       ((qs.total_worker_time/1000)/qs.execution_count) AVGtotal_worker_time_msec,
       qs.max_worker_time/1000 max_worker_time_msec,
       qs.total_elapsed_time/1000 total_elapsed_time_msec,
       ((qs.total_elapsed_time/1000)/execution_count) avg_elapsed_time_msec,
       qs.min_elapsed_time/1000 min_elapsed_time_msec,
       qs.max_elapsed_time/1000 max_elapsed_time_msec,
       qt.text,
       qp.query_plan
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
  ORDER BY qs.total_worker_time DESC

Some other tips for migrating SQL 2005 to 2012:

  1. Be sure you change compatibilty level to 110
  2. Be sure to update statistics after restoring databases.
Jeffrey
  • 111
  • 2