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.
Bluesky
