Skip to Content
0

Procedure Cache Management&memory per statement

Jul 26, 2017 at 11:31 AM

41

avatar image
Former Member

I have a problem with the procedure cache , I have some questions on this topic. Maybe someone solve a similar problem and there is a solution.

1.

Sysmon contains these lines

Procedure Cache Management per sec per xact count % of total

--------------------------- ------------ ------------ ---------- ----------

Procedure Requests 9322.0 3.7 279659 n/a

Procedure Reads from Disk 747.1 0.3 22413 8.0 %

Procedure Writes to Disk 24.6 0.0 738 0.3 %

Procedure Removals 1496.4 0.6 44891 n/a

Procedure Recompilations 746.9 0.3 22408 n/a

Recompilations Requests:

Execution Phase 746.9 0.3 22408 100.0 %

Compilation Phase 0.0 0.0 0 0.0 %

Execute Cursor Execution 0.0 0.0 0 0.0 %

Redefinition Phase 0.0 0.0 0 0.0 %

Recompilation Reasons:

Table Missing 2.9 0.0 88 n/a

Temporary Table Missing 2.9 0.0 88 n/a

Schema Change 0.0 0.0 1 n/a

Index Change 0.0 0.0 0 n/a

Isolation Level Change 1.0 0.0 29 n/a

Permissions Change 0.0 0.0 0 n/a

Cursor Permissions Change 0.0 0.0 0 n/a

SQL Statement Cache:

Statements Cached 0.0 0.0 0 n/a

Statements Found in Cache 0.0 0.0 0 n/a

Statements Not Found 0.0 0.0 0 n/a

Statements Dropped 0.0 0.0 0 n/a

Statements Restored 0.0 0.0 0 n/a

Statements Not Cached 274.5 0.1 8234 n/a

At the normal time, the Execution Phase is between 1 and 50.per / sec.

Peaks hold for a few minutes - Sample Interval: 00:00:30 Periodically, these are the peaks 3-5 times per hour.

Nowhere could I find information about exactly what the parameter means and what such sharp growth means. Has anyone faced this situation? Where can I find information about this parameter?

2.

When worked 1 procedure (contains about 10 thousand lines)

I monitor the monProcessStatement table that contains the MemUsageKB column.

On 1 line(procedure) I constantly see the following.

PlanID BatchID ContextID LineNumber CpuTime WaitTime MemUsageKB

----------- ----------- ----------- ----------- ----------- ----------- -----------

32803066 326 1143 75 276 0 13772

At the same time on the line the following code:

"

if (@@NestLevel<28) and (@__TraceProcName not like 'PP[_]Log[_]%')

begin

"

Does MemUsageKB really show the number of kilobytes of memory used for execution of the statement (KB)? Really used 13 mb for the statement on line 75 and for request used 236 milsec cpu?

Or is it just a parameter that indicates the amount of memory for the all procedure?

If yes, then it seems to me the same as it is not correct, how can this be changed?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers