November 20, 2014 at 12:33 am
Hi all
I was interested to know whether many people have experienced performance issues when upgrading to 2014 (and setting compatibility level to 120), and the reasons behind those issues, as they relate to the new CE.
We are in the process of upgrading and testing and have some stored procs which execute in approximately one second on 2012, yet which are taking over a minute on 2014.
After faffing around with execution plans, indexes, statistics, parameter sniffing and getting nowhere, we determined that the new CE was the culprit.
Interestingly, during these experiments, SSMS identified a missing index when I obtained the actual execution plan. I created that index and the execution time increased to 1 minute 50 seconds!
I was able to determine that calling a scalar UDF (which validates postcodes) in a query's WHERE clause was the problem. OK, we don't like these, so I refactored that to use an iTVF instead. That took the execution time down to 20 seconds. Still not good enough.
We currently have three 'solutions':
1) Revert compatibility level to 110
2) Use the query hint (QUERYTRACEON 9481) within those specific queries which have been hit.
3) Refactor the queries – the exact method used would depend on the query being used.
Have you had similar issues? How did you solve them?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 20, 2014 at 1:45 am
I think you already found out what the options are.
If you don't have time and/or means to test the whole workload in the new CE, I think your best bet is keeping the compatibility to 110.
Kimberly Tripp wrote a post about the topic that you can find here: http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-analyze-data-skew-create-filtered-statistics/
Basically, she outlined the same options that you already enumerated.
I think that what drives the choice is the results of your tests. You could set up a test environment in 2014 and run a significant workload capture taken in production against it first with compatibility 110 and then with compatibility 120. Then you just need to compare the results. RML utilities are great for that.
Good luck!
-- Gianluca Sartori
November 20, 2014 at 10:50 am
Thank you for the informative response.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 21, 2014 at 3:22 am
I know Milos has written a number of interesting articles about the new CE and some pitfalls he encountered.
http://milossql.wordpress.com/category/sql-server/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2016 at 2:35 pm
I found this blog post very useful when I was looking into a similar issue to the one you encountered:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply