Tool in Sql Anywhere 11 to view procedure/function I/O?

[Former Member]

We have had some of our branches complaining about slowness. I have tried tracing the database and viewed the slow running queries to see if that would help but would like to see the I/O impact of some of the procedures to see if it needs to be tuned more.

Branches are on 11.0.1.2452 (running on Windows)

[former_member244518 · Accepted solution]

Hi Kevin,

There are a few different ways you can do this:

First, try looking at the %Idle Time counter on the disk in perfmon.exe. If this is getting close to zero while the procedure is running, then your procedure is probably affected by I/O performance.

Are there specific queries that are taking a long time? If so, you should try gathering the plans using Interactive SQL. Select the top node and look at the DiskReadTime and DiskWriteTime counters. If these are a significant portion of the RunTime then you are likely bound by disk I/O.

If you already have the trace database of the procedure executing, you can try running some queries against the sa_diagnostic_statistics table to view the state of the disk counters over time. You will need to capture the trace at a fairly high tracing level for this to work. The below code compares the DiskReadTable to the CacheReadTable, you should also check the corresponding index counters (DiskReadIndLeaf, DiskReadIndInt, etc.). The percent read from disk should be less then 10% for table reads and very small for index reads (except for perhaps initial runs on a cold cache)

Here's a rough outline:

-- Create index for performance reasons
CREATE INDEX IF NOT EXISTS idx_stats ON sa_diagnostic_statistics ( counter_id ASC, connection_number ASC );
DROP TABLE IF EXISTS #samples;
DECLARE LOCAL TEMPORARY TABLE #samples(
"time" timestamp,
connection_number unsigned int,
counter_id unsigned int,
counter_value unsigned int)
NOT TRANSACTIONAL ;
-- Iterate over the connections table and grab the initial and final
-- DiskReadTable and CacheReadTable counter values (per connection)
FOR myloop as curs CURSOR FOR
    SELECT connection_number as c
    FROM sa_diagnostic_connection
FOR READ ONLY
DO
    INSERT INTO #samples
    SELECT DISTINCT TOP 1 "time",connection_number,counter_id,counter_value
    FROM sa_diagnostic_statistics
    WHERE connection_number = c
    AND counter_id = 61         --DiskReadTable
    ORDER BY "time" DESC;
    INSERT INTO #samples
    SELECT DISTINCT TOP 1 "time",connection_number,counter_id,counter_value
    FROM sa_diagnostic_statistics
    WHERE connection_number = c
    AND counter_id = 19         --CacheReadTable
    ORDER BY "time" DESC;
    INSERT INTO #samples
    SELECT DISTINCT TOP 1 "time",connection_number,counter_id,counter_value
    FROM sa_diagnostic_statistics
    WHERE connection_number = c
    AND counter_id = 61         --DiskReadTable
    ORDER BY "time" ASC;
    INSERT INTO #samples
    SELECT DISTINCT TOP 1 "time",connection_number,counter_id,counter_value
    FROM sa_diagnostic_statistics
    WHERE connection_number = 64246
    AND counter_id = 19         --CacheReadTable
    ORDER BY "time" ASC;
END FOR;
-- Display the CacheReadTable vs. DiskReadTable for each connection
SELECT c.connection_number as "Connection Number" ,c.value as "CacheReadTable", d.value as "DiskReadTable", IF "CacheReadTable" > 0 THEN ("DiskReadTable"*100.0)/"CacheReadTable" ENDIF as "% Disk" FROM
(SELECT connection_number, MAX(counter_value)-MIN(counter_value) as value
FROM #samples
WHERE counter_id = 19
GROUP BY connection_number ) c
JOIN
(SELECT connection_number, MAX(counter_value)-MIN(counter_value) as value
FROM #samples
WHERE counter_id = 61
GROUP BY connection_number) d
ON c.connection_number = d.connection_number
WHERE "% Disk" IS NOT NULL
ORDER BY "% Disk" DESC

edit- Fixed code