We have recently migrated from SQL Server 2012 Express to SQL Server 2017 Express and observed some of queries having performance issues in SQL Server 2017.
We have compared execution plan and found that both versions generate different execution plan. As Microsoft claims SQL Server 2017 is faster than earlier version of SQL server but we get reverse result.
We have used “LEGACY_CARDINALITY_ESTIMATION” hints and applied temporary solution.
We have changed DB Compatibility Level from 110 for 2012 to 140 for 2017.
UPDATE STATISTICS & Rebuild index
Does anyone have any ideas ?
How can we apply permanent solution?
How can we proof SQL Server 2017 is faster compare to SQL Server 2012?
How can find impact of queries which are not working SQL Server 2017?
August 22, 2019 at 11:37 am
In the vast majority of cases, 2017 is radically faster than 2012. However, in some edge cases, as you may be experiencing, the opposite is true. Usually, this involves situations where the queries in question are badly written, the structures are not properly configured, the statistics are skewed, or all of the above. There was a lot more give to the old query engine for badly written queries. The newer versions are a little less forgiving in some areas.
The way I would approach this is to enable Query Store and set the database compatibility level to the old version. Then run the load for a little while that way (a few days or weeks, you'll have to decide). Then, switch the compatibility level. Some queries will regress, meaning, they'll get a different execution plan that causes them to run slower. You can run the regression report to see those queries. You can then force the old plans on to those queries while you go through the task of tuning them (because they almost always need to be tuned, they probably needed tuning in 2012 too) as a more permanent solution.
In terms of proving something is faster, the mechanisms are simple. Measure query performance on both sides. Compare.
"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
August 22, 2019 at 11:52 am
Thank you very much for your time.
We have taken performance reading of both SQL version and identified whenever we used ROW_NUMBER,Rank() functions;SQL Server 2017 express edition generated different plan and sort operator is taking more time.
I just one doubt, we are using SQL Server 2017 express edition because of that we are not getting performance.Is there any difference on database engine between SQL Server 2017 express edition and Standard or Enterprise edition?
Yeah, there are differences and limits on Express. The core behavior is the same, but the amount of memory it can use and other stuff along those lines are different. You can get better performance out of Standard and you can get better performance from Enterprise over Standard. However, each of these usually requires the addition of CPU and memory to take advantage of added functionality. The core really is the same across all of them. Performance is much more often about the correct indexes, data structures, code and statistics instead of versions or settings or even hardware.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply