Figuring out which cached statements are doing the most write activity using monCachedProcedures

[sladebe]

I wanted to analyze some replication slowness for data being replicated to an ASE server.

I was hoping to use monCachedStatement to look at write activity via the various "PIO" (aka Physical I/O) columns.   But it turns out that "PIO" means read physical I/O (although it doesn't say "read" in the manual")

Surprisingly, there's no write activity monitoring in monCachedStatement.

But you can play a trick.  It turns out that cached statements show up as "lightweight stored procedures" in the monCachedProcedures table with procedure names like *ss1719010174_1175458873ss*, where 1719010174 (in this case) is the ObjectId of the lightweight stored proc and corresponds to the SSQLID in the monCachedStatement.

Once you know this, it's possible to write a fancy-dancy query to show execute stats for both stored procs and cached statements, including write activity:

select top 50

       ExecTime=CP.ExecutionTime, ExecCnt=CP.ExecutionCount, TimePerExec=CP.ExecutionTime/CP.ExecutionCount,

       CP.CPUTime, PhysReads=CP.PhysicalReads, LogReads=CP.LogicalReads, PhysWrites=CP.PhysicalWrites,

       DBName=case when ObjectName like "*ss[0-9][0-9]%"

                   then substring(CS.DBName,1,16)

                   else substring(CP.DBName,1,16)

              end,

       CachedProcOrStatement=case when CP.ObjectName like "*ss[0-9][0-9]%"

                                  then substring(show_cached_text(CP.ObjectID),1,110)

                                  else substring(CP.ObjectName,1,110)

                             end

from monCachedProcedures CP

left outer join monCachedStatement CS on CP.ObjectID=CS.SSQLID

order by CP.ExecutionTime desc

Producing output like:

 ExecTime    ExecCnt     TimePerExec CPUTime     PhysReads   LogReads    PhysWrites  DBName           CachedProcOrStatement

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

     1040412      928495           1        9771           0           0        6159 mydb1           insert into ThisTable (f1, f2) values (@@@V0_INT, @@@V1_INT)(@@@V0_INT INT, @@@V1_INT INT)     

      850124      180026           4       10220           0           0           0 mydb2           insert into ThatTable ...snip...

      645990      121866           5       36531           0           0       16248 mydb3           insert into WhatTable ...snip...

If you have longer database names, increase the length of the substring commands, and so on.

It's too bad that monCachedStatement didn't include PhysicalWrites.  It would be useful in situations like this.

After using this query in production, it turns out that statements like update and delete don't show a lot of write activity.   Maybe that's because modified pages are actually written later by a checkpoint operation.

In any case, this is still a useful "go to" query for getting a summary of the most active stored procedures and/or SQL statements in your ASE server in a single query.