April 28, 2016 at 8:00 am
I posted this question on the SQL Server 2008 forum as well:
We upgraded SQL Server from 2008R2 to 2014 and we've seen severe performance degradation of stored procedures that user table variables (@). Performance greatly improves by reverting to temp (#) tables. The system has hundreds of stored procedures and it is very time consuming and expensive to revert all stored procedures from table variables to temp tables.
Is this a known issue? Is there a way to prevent that?
April 28, 2016 at 9:09 am
Sounds like you've fallen foul of the new Cardinality Estimator.
Please have a read here [/url]& you'll get a better understanding of what it is and how to handle it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 28, 2016 at 10:16 am
Without any other information, yeah, I'd agree. It's likely that it's the cardinality estimator.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2016 at 11:23 am
Yup, cardinality estimator changes.
Most queries show a performance improvement, or stay the same under the new CE. A smaller number show degradations. It's why I tell people they MUST test before upgrading to SQL 2014 (currently fixing queries for a client who chose to ignore that recommendation)
You can change the compatibility mode to 110 (SQL 2012) while you fix the problem, then once you've fixed all the queries put it back to 120.
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
April 28, 2016 at 3:28 pm
Thanks everyone for the replies. I get it that it is probably CE. By my reading, I see that most people indicate the new CE improves performance, but may degrade performance of some queries. All examples I've seen show how much better the new CE is.
Perhaps the experts could shed some light into what conditions lead to poor performance under the new CE. Or is it a hit and miss in terms of queries that perform poorly.
April 28, 2016 at 3:57 pm
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.
This.
If there was only a couple of known patterns of queries that always got performance degradations, MS would have fixed the cause during 2014's development. There aren't
It's got to do with the row estimate changes, and it's really, really hard to predict. Hence the recommendations of end-to-end performance tests before upgrading.
I've done a few upgrade (or post-upgrade) comparisons and it's usually a small percentage of the queries, roughly 5-10%. The rest stay the same or improve.
The one client to did have the tests (and fixes) done before the upgrade got an overall 35% reduction in database execution times, measured at the web tier after upgrading to 2014 and doing nothing else, so it is better in general, you just have to fix the exceptions.
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
April 28, 2016 at 4:06 pm
Or is it a hit and miss in terms of queries that perform poorly.
I had a similar experience to you with table variables & replacing these with temp tables solved all problems.
This was legacy code that I was happy to change. I very rarely use TVs in production code, so it was a golden refactoring opportunity.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2016 at 4:38 am
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.
I'll just pile on and agree.
The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2016 at 8:49 am
Grant Fritchey (4/29/2016)
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.I'll just pile on and agree.
The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.
That begs the question regarding table-valued functions. I have many of those. Would it be better to gather the data from the TVF into a temp table, and then join, or just keep it in the join.
One other point I noticed re:TVF is the location of the TVF (with cross apply) on the join. I had a query joining six tables and one TVF. The very first table on the join limited the number of calls to the TVF based on the cross apply. However, performance changed significantly as I moved the TVF across the join, from second to last, with last being the worst. Note I did not have option FROCE ORDER set, so SQL Server decided on the order of joins.
April 29, 2016 at 8:54 am
N_Muller (4/29/2016)
Grant Fritchey (4/29/2016)
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.I'll just pile on and agree.
The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.
That begs the question regarding table-valued functions. I have many of those. Would it be better to gather the data from the TVF into a temp table, and then join, or just keep it in the join.
Maybe.
Do note that Grant's talking specifically about multi-statement TVF, not the inline TVFs which tend to behave better.
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
April 29, 2016 at 10:44 am
N_Muller (4/29/2016)
Grant Fritchey (4/29/2016)
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.I'll just pile on and agree.
The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.
That begs the question regarding table-valued functions. I have many of those. Would it be better to gather the data from the TVF into a temp table, and then join, or just keep it in the join.
One other point I noticed re:TVF is the location of the TVF (with cross apply) on the join. I had a query joining six tables and one TVF. The very first table on the join limited the number of calls to the TVF based on the cross apply. However, performance changed significantly as I moved the TVF across the join, from second to last, with last being the worst. Note I did not have option FROCE ORDER set, so SQL Server decided on the order of joins.
It really does depend on the query, the plan, whether or not the optimizer is timing out, but, the position of a JOIN or APPLY within the query shouldn't matter because the optimizer, assuming no timeout, will rearrange the order to optimize the performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2016 at 1:50 pm
Grant Fritchey (4/29/2016)
N_Muller (4/29/2016)
Grant Fritchey (4/29/2016)
N_Muller (4/28/2016)
Or is it a hit and miss in terms of queries that perform poorly.I'll just pile on and agree.
The main issues I've seen are with multi-statement, table-valued, functions where the default row count was changed from 1 to 100. The other place is when the value passed falls outside of statistics. Instead of assuming one row, it assumes an average of the returned values for the existing rows. In some cases that causes issues.
That begs the question regarding table-valued functions. I have many of those. Would it be better to gather the data from the TVF into a temp table, and then join, or just keep it in the join.
One other point I noticed re:TVF is the location of the TVF (with cross apply) on the join. I had a query joining six tables and one TVF. The very first table on the join limited the number of calls to the TVF based on the cross apply. However, performance changed significantly as I moved the TVF across the join, from second to last, with last being the worst. Note I did not have option FROCE ORDER set, so SQL Server decided on the order of joins.
It really does depend on the query, the plan, whether or not the optimizer is timing out, but, the position of a JOIN or APPLY within the query shouldn't matter because the optimizer, assuming no timeout, will rearrange the order to optimize the performance.
This is what I thought, but not what happened in the real world. Rearranging the position of JOIN and APPLY in the query changed performance from 45 seconds to only a couple of seconds with no other modification.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply