Forum Discussion
SQL 2019 Std Edition high recompilation and CPU spike
Hello, I am having an issue in SQL 2019 Std edition where I am seeing high compilation across multiple stored procedure. The reason for the recompilation as per the extended event trace is 'temp tab...
Hi KanishkaB
Let's start with fact that this is not so rare case. In most cases new versions bring better performance but there are many changes in any new versions which might lead to worse performance in rare specific cases comparing to the previous version.
This related to new features, improving and new internal algorithm and more.
In most cases, we can see the source of the issue in the Execution Plan, but with the information you (did not) provided we have nothing to discuss about
If you want to discuss the issue and find best practice then you should start with providing minimum information to preproduce the scenario. Please provide:
1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).
6) the full Execution Plans XML (not screenshot but the full XML) for both databases
With this information we will be able to start the discussion
Let's start with fact that this is not so rare case. In most cases new versions bring better performance but there are many changes in any new versions which might lead to worse performance in rare specific cases comparing to the previous version.
This related to new features, improving and new internal algorithm and more.
In most cases, we can see the source of the issue in the Execution Plan, but with the information you (did not) provided we have nothing to discuss about
If you want to discuss the issue and find best practice then you should start with providing minimum information to preproduce the scenario. Please provide:
1) Queries to CREATE your table(s) including indexes
2) Queries to INSERT sample data.
3) The desired result given the sample, as text or image of excel for example.
4) A short description of the business rules, and how you got 1-2 of the results
5) Which version of SQL Server you are using (this will help to fit the query to your version).
6) the full Execution Plans XML (not screenshot but the full XML) for both databases
With this information we will be able to start the discussion
hello Ronen,
While I agree that you need the info for better insights, however what I am still not able to figure out is a same test with identical configuration, DB settings, the database itself is behaving differently between SQL 2016 and SQL 2019. Pls note that what ever is being done on SQL 2016, a similar approach is being followed in SQL 2019. So, the question remains why SQL 2019 is behaving this odd. Let me know if I am missing anything here.
with all factors/setup/configurations fully identical see below how each edition behaved.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)-->>100% CPU
Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07
Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows Server 2016 Datacenter 10.0 <X64> (Build 1U4393: )(Hypervisor)-->>100% CPU
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) -->~40% CPU
While I agree that you need the info for better insights, however what I am still not able to figure out is a same test with identical configuration, DB settings, the database itself is behaving differently between SQL 2016 and SQL 2019. Pls note that what ever is being done on SQL 2016, a similar approach is being followed in SQL 2019. So, the question remains why SQL 2019 is behaving this odd. Let me know if I am missing anything here.
with all factors/setup/configurations fully identical see below how each edition behaved.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)-->>100% CPU
Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07
Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit)
on Windows Server 2016 Datacenter 10.0 <X64> (Build 1U4393: )(Hypervisor)-->>100% CPU
Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64) Nov 6 2020 16:50:01 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor) -->~40% CPU
