Stored Proc taking longer time on upgrade version (SQL 2016)

Hello Guys,

We are in process of upgrading one of the SQL Database from 2012 to 2016 version. The Database is moved/upgraded successfully to SQL 2016 version, now the problem is, the database is holding a Stored Proc which is taking longer time in the new version i.e on SQL 2016.  

On SQL 2012 stored proc will execute in 13 minutes where as on SQL 2016 it's taking 13 hours to complete. We been stuck with this since stored proc is having lot more dependencies to business.

We increased the memory , ram on the new server to 32 GB where as on old server it's 28 GB only, but both the servers are holding 2 cores only.

Old server OS is win 2008 and new box build with Win 2012.

We tried to find out blockings by running profiler when ever the stored proc runs but we haven't found any blockings ,this seems mysterious for us since no blockings, dead locks found.

Settings on SQL server are similar on both of the servers we haven't any odd.

Let us know if have any suggestions on how to solve this issue.

Quick help is really appreciated.

Venkat Kokulla
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The 28/32 Gigs is not as big difference. More important is the data storage. Do you use same data storage? Did you reserve sufficient space for your databases? If the SP uses tampDB heavily and this tempDB must autogrow in many small steps then the first SP run must be much longer etc. etc.

New SQL Server must build the statistics and also new query plans. And remember the query optimizer also changed in 2016.

So you should analyze query plans and compare logical reads between old and new version.
You should also look at the Resource Monitor during the query. It could show where is the bottleneck.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you change the database compatibility to SQL Server 2016 after migrating it from SQL Server 2012?
Did you run a full database reindex in the newly migrated database? This must be done for the engine create new execution plans based in the new SQL Server version.
VENKAT KOKULLASQL Server DBAAuthor Commented:
Yes, all the necessary settings form SQL and server level are taken care. We use to follow basic things after migration like compatibility settings, fill factor etc;

Since it's a Dev Env Reindex is scheduled once in a week that too week ends.

Execution plan, missing indexes, statistics, logical reads, resource monitoring  we seems good in all these things. There is no such difference in between old and new machine.

We are really confused since there is nothing to do from SQL end since we haven't found any odd but only thing we have to shown to developers that we are good from SQL part.

Just to brief about the SP: It will grab the data from the mention tables and it will  keep in a node to filter the data according to the parameters later on the final data will be converted in to XML format.

In the above mention scenario data filtering is done in the reasonable time, converting the data in to XML is taking more than expected time.

Venkat Kokulla
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Let me ask again:  "Do you use same data storage? Did you reserve sufficient space for your databases? "

Where is stored the XML output? In a disk file or in the database?
Did you measure the disk performance and compare it against the previous state?
VENKAT KOKULLASQL Server DBAAuthor Commented:
Yes, both the servers are using storage arrays, there is no variation. Databases on the instance are having sufficient space since we use to keep some reserve space all the time.

XML output is stored on disk. I/O ratings, settings, performance counters all seems similar we haven't found any difference.

Venkat Kokulla
We should move further.
I suppose you are using XML columns.
What processing is around these columns?  Do you also use indexes on these columns?
E.g. indexes described here have similar impact to SQL 2016 performance as you are describing in your question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.