Is there a way to quickly collect the summary of how many logical IOs/writes have been done for a given procedure?

[Former Member]

Hi

I can run set statistics IO on for a given session, but when running a procedure, each step returns its statistics...   it would be helpful for a quick look

on the procedure to collect the summary for the whole proc execution. Is there a way to do this?

I could not find in the docs a way to activate this, and the the best i could think for now is to look for the MDA tables for a given session and collect the LIO/PIO data prior and after the execution and take the delta....   it may not be perfect, but its an alternative....   

Anyone have additional ideas to share?  Thanks!

Alexandre 

[Former Member · Accepted solution]

in a non-production environment,

data can be in monProcessStatement

use ISQL or some other tool that keeps the same spid.

set statement_cache off -- session level

go

exec yourproc with parameters

select * from  monProcessStatement where SPID=@@SPID

go

This will give information per statement.

Cory

If an answer is complete or helpful, please mark the reply.

[Former Member]

I haven't looked further, but would the IO effort done for Sorting and Logging be reported in the total for this procedure?  I could guess that if its a procedure which is designed for data retrieval, the number of pages written could be helpful for this?  ...  (PagesModified on monProcessStatement)

Anyone have tips for how to look for Sorting and Temp distinct effort?

I could guess that physical IOs could be incremented when a temp table is written, but so far i havent been able to see this

Thanks!

[Former Member]

Thanks Guys for the info provided    Both are really very useful 

[former_member182259]

If ASE 16 - select * from monCachedProcedures  (note that all below already exist in your server - the defs are from installmontables which is no longer necessary unless remotely monitoring a different server):

create existing table monCachedProcedures (

ObjectID                        int,

InstanceID                      tinyint,

OwnerUID                        int,

DBID                            int,

PlanID                          int,

MemUsageKB                      int,

CompileDate                     datetime,

ExecutionCount                  int,

CPUTime                         int,

ExecutionTime                   int,

PhysicalReads                   int,

LogicalReads                    int,

PhysicalWrites                  int,

PagesWritten                    int,

SnapCodegenTime                 int,

SnapJITTime                     int,

SnapExecutionTime               int,

SnapExecutionCount              int,

ObjectName                      varchar(30) NULL,

ObjectType                      varchar(32) NULL,

OwnerName                       varchar(30) NULL,

DBName                          varchar(30) NULL,

RequestCnt                      int NULL,

TempdbRemapCnt                  int NULL,

AvgTempdbRemapTime              int NULL,

Active                          varchar(3) NULL,

)

external procedure

at "@SERVER@...$monCachedProcedures"

go

The problem with monProcessStatement, is that it may only give you stats for the line of the proc that is currently executing:

create existing table monProcessStatement (

SPID                            int,

InstanceID                      tinyint,

KPID                            int,

DBID                            int,

ProcedureID                     int,

PlanID                          int,

BatchID                         int,

ContextID                       int,

LineNumber                      int,

CpuTime                         int,

WaitTime                        int,

MemUsageKB                      int,

PhysicalReads                   int,

LogicalReads                    int,

PagesModified                   int,

PacketsSent                     int,

PacketsReceived                 int,

NetworkPacketSize               int,

PlansAltered                    int,

RowsAffected                    int,

DBName                          varchar(30) NULL,

StartTime                       datetime NULL,

)

....you'd have to sum the monSysStatement for that SPID, KPID, DBID, ProcedureID, PlanID, BatchID to get the sum.

create existing table monSysStatement (

SPID                            int,

InstanceID                      tinyint,

KPID                            int,

DBID                            int,

ProcedureID                     int,

PlanID                          int,

BatchID                         int,

ContextID                       int,

LineNumber                      int,

CpuTime                         int,

WaitTime                        int,

MemUsageKB                      int,

PhysicalReads                   int,

LogicalReads                    int,

PagesModified                   int,

PacketsSent                     int,

PacketsReceived                 int,

NetworkPacketSize               int,

PlansAltered                    int,

RowsAffected                    int,

ErrorStatus                     int,

HashKey                         int,

SsqlId                          int,

ProcNestLevel                   int,

StatementNumber                 int,

DBName                          varchar(30) NULL,

StartTime                       datetime NULL,

EndTime                         datetime NULL,

)

external procedure

at "@SERVER@...$monSysStatement"

One advantage of monSysStatement is you could find the LINE that contains the most expensive statement in the proc.....which is even better for diagnostics.

[former_member182090]

There are various ways to obtain the numbers you're looking for from the MDA tables. Check out the MDA-related example procs on my personal web site www.sypron.nl/mda, this is one possible approach.

[former_member89972]

Hi Alexandre

MDA tables can help you.

You have two MDA tables to tap into, Sample them as frequently as  needed and record  the data with time stamps to get meaningful delta values.

monProcessActivity : will give you the figures for SPID

monProcessStatement : will give you the figures for batches/contexts for a SPID (watch the context changes and line number progress). You may have to add up deltas for various batches/contexts to get net total for the whole run.

Combining these most probably may give you what you are looking for.

HTH

Avinash