cancel
Showing results for 
Search instead for 
Did you mean: 

How to monitor stored proc recompile time at a server level (mon table?)

sladebe
Active Participant
0 Kudos

I occasionally see a process with sysprocesses.cmd == "COMMAND". We're not using the statement cache (because we had replication problems with it where a simple update statement scans a big table, hanging replication). We *do* have "procedure deferred compilation" turned on (for when we create schemas from ddlgen output, it let's all objects get created successfully).

I wanted to monitor what percentage of the time ASE spends compiling statements and/or stored procs. I can't seem to find a mon table to do it. Any suggestions?

Thanks in advance
Ben Slade

Accepted Solutions (1)

Accepted Solutions (1)

former_member188958
Active Contributor

The MDA tables monProcessStatement, monProcessActivity, and monSysStatement have a column "QueryOptimizationTime" that may be useful.

sladebe
Active Participant
0 Kudos

Interesting. Looking at the online docs, I see QueryOptimizationTime is supposed to be in the monProcessActivity table in ASE 16.0 SP03 (CR 800312 in the ASE release notes), but that doesn't seem to be the case (at least for my SP03, EBF 27167 on 64 bit Linux). I have a SP03 PL02 ASE server, but it's not there either.

So I bet it shows up in a slightly later release. I look forward to using it someday. Thanks for the info.

Answers (3)

Answers (3)

former_member188958
Active Contributor
0 Kudos

Hi Ben,

The column should be there in 16.0 SP03 and higher.
Changes to the MDA tables are made by the installmaster script; perhaps that wasn't run or didn't run to completion when you upgraded.
Run "sp_version" to see if the installed installmaster version matches your @@version version (just to verify the theory)
Try running the installmaster script in any case, save the output.
If the column still hasn't appeared, check the installmaster output carefully for errors.

Cheers,
-bret

sladebe
Active Participant
0 Kudos

Whoops, sorry, QueryOptimizationTime *is* in the monProcessActivity table, it's just that the column order differed from the docs and I didn't pay attention.

And a quick check shows that the QueryOptimizationTime is very small compared to CPU usage across all servers (even with statement caching turned off). Good answer!

THANKS!!!!

former_member89972
Active Contributor
0 Kudos

Bret thanks for the feedback.

Lucky Ben.

We are still on ASE 16 SP02 PL05 ! So I did/do not see that magic column !

Avinash

sladebe
Active Participant
0 Kudos

I guess I have a fast system. The "exec sp_who with recompile" example showed zero for the parse and compile times (there were several parse and compile operations in a run).

But I need this for a system where hundreds of different SQL statements and stored procs maybe be running. And each invocation may be short, but there could be hundreds of thousands of calls, including replication activity. So totals counters are really what's needed. Thanks anyway.

former_member89972
Active Contributor
0 Kudos

CPU usage granularity in MDA tables is : system, user, I/O and Idle.

So unless SAP/Sybase provides some finer levels of granularity for CPU usage,

you will get only a static times for compile, parse and execution with the "set statistics time on" option.

Just curious to know :

What made you search for the resources/time spent in compilation and parsing ? Do you think it is a big chunk of CPU usage ?

You may also want to experiment with :

- the longest stored procedure you have

- longest SQL query you have

to get some idea for a ballpark estimate.

HTH

Avinash

former_member89972
Active Contributor
0 Kudos

The nearest reference to "parse and compile" times is under command "set statistics time" option.

No luck MDA Tables side, to capture timed snape shots.

CPU usage will be included in overall server level CPU usage counters.

BTW with a wrapper script to set the option on/off capture output for Procedures, SQL you are interested in.

My simple test of "set statistics time on" is below :

===

1> set statistics time on

2> go

Execution Time 0. Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

===

1> select @@servername, db_name(), getdate()

2> go

Parse and Compile Time 0. Adaptive Server cpu time: 0 ms.

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

PHI4_DEV PRISMQA_PRD Jan 24 2019 3:03PM

Execution Time 0.

Adaptive Server cpu time: 0 ms. Adaptive Server elapsed time: 0 ms.

(1 row affected)

1>

HTH

Out of curiosity you could try "exec sp_who with recompile" to see system procedure parse/compile time.

Avinash