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.
Thanks,
Venkat Kokulla
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.
Thanks,
Venkat Kokulla
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.
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.
ASKER
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.
Thanks,
Venkat Kokulla
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.
Thanks,
Venkat Kokulla
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?
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?
ASKER
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
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.
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
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.