Share via

Sql Server Sql Statement Always Reompiled

mohamadreza YY 1 Reputation point
2022-08-24T12:15:35.827+00:00

I am using the 2017 version of SQL Server. The auto update statistics setting is enabled
As follows :
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

I have a procedure whose statements are always recompiling. With Extended event, when I check, the reason for recompilation is Statistics changed.

This causes the stored plans to change constantly and increases the CPU consumption during recompilation.
234488-01.png

SQL Server | Other

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,416 Reputation points
    2022-08-24T12:22:29.827+00:00

    Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

    I suggest applying the latest CU (CU 30) as a first step. You are running the RTM version and several years behind in patching.

    0 comments
  2. Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator
    2022-08-24T21:54:05.4+00:00

    I agree with Dan. Apply CU30 before any discussion. There are means to turn off autostats, but we need to know more so we can give accurate advice. But first CU30, to make sure that you are not victim to something that has been fixed.

    0 comments
  3. CathyJi-MSFT 22,406 Reputation points Microsoft External Staff
    2022-08-25T03:04:10.707+00:00

    Hi @mohamadreza YY ,

    Agree with Dan and Erland, please apply the latest CU30 for SQL 2017 at first, you can get it from below link. If it is not work, then we can continue to discuss this issue.

    SQL Server® 2017 for Microsoft® Windows Latest Cumulative Update

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.