Skip to Content

Why does a sql statement in procedure cache get corrupted?

We have for a periode of time had the problem that the procedure cache for one specific table (ATP_RESB) gets corrupted.

(Windows server, MSSQL 11.00.6567.00, SAP_BASIS 7.40, SAP_ABA 7.40)

I happens regularly - maybe once a week that the availability check in sales order becomes very slow.

The fix is to drop the sql statement from the procedure cach (in transaction ST04). This solves the problem instantly.

The problem has existed for the last ½ year or so, but we have had it before. When I googled the problem i found a similar thread started by my self in 2010!

I 2010 we found the quick fix solution, but now I want to know if it is possible to identify the root cause of the problem.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jul 24, 2017 at 11:49 PM

    Hi Thomas,

    I don't think it has to do with corruption in the SQL statement cache. What might happen is what mostly happens with SQL Server and SQL statement performance: Skewed data.

    Therefore, you have first a good execution plan compiled, and its performance is good for almost all executions. But due to some reason, the plan is recompiled for this object (statistics update, manual recompile) and the next execution will compile a new plan. Depending on the parameters of the next execution, it might compile a different execution plan (e.g. instead doing a clustered index scan, the optimizer decides that is better to do a clustered index scan) and it will be cached for next executions. The next executions have bad performance then, and suddenly goes back normal when you drop the proccache (DBCC FREEPROCCACHE) mark the object for recompile or update the statistics.

    You can read the following blog for more details and even how to identify and options to resolve it:

    https://blogs.msdn.microsoft.com/saponsqlserver/2015/10/09/data-distribution-and-sql-plans/

    Add comment
    10|10000 characters needed characters exceeded