June 30, 2022 at 7:15 am
Hi
I have one server running SQL server 2017 and one running SQL server 2019. The developer has reported that the SQL 2019 is much slower than the SQL 2017.
Both servers are virtual and has the same spec regarding memory, CPU, OS ( Windows 2019). The settings in SQL is the same on the ones that the app require
I haven´t received any examples of question but they say that the difference is significant( minutes instead of sec )
The database from the 2017 server is restored from backup on the 2019 server. Same compatibility level (130)
What is the big difference between the two versions that can cause this problem ?
/Jonas
June 30, 2022 at 8:12 am
Nothing springs to mind; it's not like, for example, when the cardinality estimator was changed between 2012 and 2014. Though you could, perhaps, have the old cardinality estimator enabled in one and not the other, so you could check that as performance can be significantly different between the two.
Otherwise you really you need details of the queries that they say are performing significantly different; then you can start looking at things like the query plans, checking the definitions are the same between environments, parameter sniffing, etc.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 30, 2022 at 11:17 am
What's the version of your SQL2019 ? ( latest CU ? )
Did you try setting the db level to 150 ?
Another option is to test the database configuration setting "LEGACY_CARDINALITY_ESTIMATION"?
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
June 30, 2022 at 1:56 pm
Are the statistics up to date?
June 30, 2022 at 2:59 pm
I've seen no types of standard degradation between 2017 and 2019. So, I'd suggest examining all the server settings, cost threshold, the things already suggested here.
"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
June 30, 2022 at 5:26 pm
(1) Verify that the 'cost threshold for parallelism' has been set properly (i.e. increased from the default) on the new instance
(2) Verify that the tempdb settings -- number of files, etc. -- are optimal for the new instance
(3) Verify that IFI is enabled for the new instance
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 4, 2022 at 11:16 am
Hi
Thanks for the replies !!
I saw that the latest CU ( CU16) where not installed( should have been installed by WSUS, have to look into why not) . After I applied it the application worked much better and the developers are happy again. 🙂 It turned out to be a bug in SQL that hit the application
" Applies to Documentum Server 20.4
summary
When SQL Server 2019 is used for the Database of Documentum Server, it takes longer than Documentum Server using other DBs to install DAR and create a large number of objects at once.
Cause
This is a Bug specific to SQL Server 2019. "
Regards
Jonas
July 4, 2022 at 11:19 am
Hi
Thanks for the replies !!
I saw that the latest CU ( CU16) where not installed( should have been installed by WSUS) . After I applied it the application worked much better and the developers are happy again. 🙂
Regards
Jonas
Out of interest, what was it running prior to you updating it to CU16?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 4, 2022 at 11:40 am
A little embarrassed to say.... - none , only security updates..
Should have been applied automatic by WSUS. Works on the other servers I got. Some investigation to do there....
/Jonas
July 4, 2022 at 3:33 pm
Thanks for the feedback on what it turned out to be, Jonas.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2022 at 1:25 am
This was removed by the editor as SPAM
July 7, 2022 at 2:44 am
How fix SQL performance issues? Start by checking your wait stats
1-Ensure your TempDB database is configured optimally.
2-Make sure you're running index maintenance frequently.
3-Implement indexes that provide a benefit to your queries.
4-Check your most expensive queries and stored procedures.
5-Monitor your performance counters.
Item 2 is mostly incorrect for OLTP because a single row lookup takes exactly the same path on a 99% logically fragmented index as in does on one with 0% logical fragmentation. As for the rest, "It Depends". I will tell you that if your index maintenance includes the use of REORGANIZE, there's a very good chance that you're causing more problem than you're curing especially on indexes that you rely on lowing the fill factor to supposedly delay or prevent fragmentation.
The biggest bang for your buck is to keep index statistics up to date, especially if you have "ever-increasing" keys in your indexes.
I suppose you're going to tell folks how to accomplish the other 4 points, right?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2022 at 10:08 am
This was removed by the editor as SPAM
July 7, 2022 at 12:46 pm
How fix SQL performance issues? Start by checking your wait stats
2-Make sure you're running index maintenance frequently.
Everything Jeff said.
But, one exception, columnstore indexes. Reorganization and rebuild do things for columnstore beyond fragmentation. So consider using them because of that.
For those interested, it's the internals of a columnstore index. The delta store, where changes are stored, is a non-compressed, non-pivoted, b-tree index. As you exceed 100,000 rows in the delta store, you'll get that moved to compressed & pivoted, but there's all sorts of caveats around that because it's not a part of a straight data load. Again, details, details, details, in this. However, reorganize actually moves stuff out of the delta store. It helps. Rebuild, does the whole 9 yards with the data (at a much higher cost, of course). So, for columnstore, I do recommend index maintenance.
"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
July 21, 2022 at 4:40 pm
jefferyjordan26 wrote:How fix SQL performance issues? Start by checking your wait stats
2-Make sure you're running index maintenance frequently.
Everything Jeff said.
But, one exception, columnstore indexes. Reorganization and rebuild do things for columnstore beyond fragmentation. So consider using them because of that.
For those interested, it's the internals of a columnstore index. The delta store, where changes are stored, is a non-compressed, non-pivoted, b-tree index. As you exceed 100,000 rows in the delta store, you'll get that moved to compressed & pivoted, but there's all sorts of caveats around that because it's not a part of a straight data load. Again, details, details, details, in this. However, reorganize actually moves stuff out of the delta store. It helps. Rebuild, does the whole 9 yards with the data (at a much higher cost, of course). So, for columnstore, I do recommend index maintenance.
Aye! Thanks for the correction on the ColumnStore indexes concerning index maintenance. I always forget about that and agree that Reorganize on those is not the same monster that it can be for RowStore indexes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply