• Status: Open
  • Priority: Low
  • Security: Public
  • Views: 36
  • Last Modified:

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
0
VENKAT KOKULLA
Asked:
VENKAT KOKULLA
  • 3
  • 2
6 Comments
 
pcelbaCommented:
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.
0
 
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.
0
 
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.

Thanks,
Venkat Kokulla
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pcelbaCommented:
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?
0
 
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
0
 
pcelbaCommented:
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.
0

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now