Import Execution Plans of SQL 2008 to SQL 2014

Question

Import Execution Plans of SQL 2008 to SQL 2014

pawana.paul

SSCommitted

Points: 1686
More actions
May 10, 2016 at 11:43 am

#310480

I am working on SQL Upgrade from SQL 2008 to SQL 2014.
Is there a way to import execution plans of SQL 2008 to SQL 2014?
There is one DB of size 2 TB and will take long time to perform update statistics so looking for the options.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

If you are on 2014 you don't want 2008 execution plans. 2014 has a new cardinality estimator. If you have time to update your version of SQL then you should have time to update your stats.

One thing to consider is wiping out unused indexes that exist on that 2TB DB.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pawana.paul SSCommitted Points: 1686 More actions · Answer 2

Thanks Alan,

Considering the cutoff time, it is very difficult to get big window for the maintenance. As update stats degrades the performance I am looking for other options.

Can this option mentioned in the link https://technet.microsoft.com/en-us/library/bb895281(v=sql.110).aspx helps?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I wouldn't recommend it. Fixing the query plans means that when the data changes, the optimiser can't choose a better option. While it might save time now, it'll mean more work later on.

It's not update stats that you need to do before upgrading to SQL 2014, it's a performance test, along with fixing the queries which degrade in performance under the new cardinality estimator (in my experience around 10-25% of queries). The rest improve in performance or remain the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pawana.paul SSCommitted Points: 1686 More actions · Answer 4

Thanks Gail,

update statistics I want to perform after upgrade to 2014. I was looking for other options to not to spend more time on this.

Yes we are working on capturing the complex queries which needs to run under new cardinality estimator. Refresh of the views are also part of our plan.

Please let me know if any other areas to be covered.

Thanks very much for your help.

pawana.paul SSCommitted Points: 1686 More actions · Answer 5

All,

As I was concerned about maintenance of 2 TB of database after upgrade to 2014. Came up with below plan to avoid.

1. Install SQL Server 2014 SP1

2. Restore 2008 Database on 2014

3. Setup replication from 2008 to 2014 (Synchronize From Backup). We have taken care of planning all the articles getting replicated to 2014.

4. Perform all the needed maintenance

There is no downtime from database side during cut-over (pointing application from SQL 2008 to 2014).

Later on one more node will be added to this cluster and configured as AlwaysOn.

Please let me know if you have any concerns on this plan.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

Hard to say... I am assuming this is a Production environment, correct? If so, why not try this in a Dev environment first? Why not run through this a couple times before doing it in prod? Not only to make sure it works as expected and you have a plan for validating your data, but to get an idea of how long each step takes.

For example, what kind of replication? You or a co-worker have solid replication experience? Setting a replication is a non trivial task. We talking snapshot, transactional? Either way you want to see how long the initial snapshot takes for a 2TB DB.

How is the data partitioned? And 2TB of what? Data, transaction log? This is not the kind of thing a forum can help you with a few paragraphs of discussion.

I would say, try it first in a device environment and report back with any concerns.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001