Database Overview Reports

Overview

This report shows which stored procedures do this database’s writing, and – the useful part – whether each one is heavy because it runs constantly or because every call writes a lot.

Those are different problems. A procedure called ten thousand times an hour writing a little each time needs batching or caching; one called twice a day writing a million pages needs its logic examined. Total Writes alone cannot tell them apart, which is why Avg Writes/Exec and Executions sit beside it.

What this fixes

This was the oldest report skeleton in the product:

• A PictureBox bitmap of top-20 bars with no names, no values and no axis, coloured from a 26-slot rainbow dealt by row order so the colours reshuffled on every refresh, and cleared to hard-coded white whatever the theme.
• A grid of raw ten-digit numbers under SQL alias captions.
• Nothing said what share of the write work the top procs carried.
• Nothing warned about cached_time – see below.

---

Where to find it

A database level report. Expand the server → expand the database → Real Time → SPs by Logical Writes.

The tree abbreviates it. The report is called Stored Procs by Logical Writes everywhere else, including its own page title, but the node reads SPs by Logical Writes because the tree is narrow.

---

The cached_time caveat

This is the most important thing on the page and it was never mentioned before.

The counters run since each procedure’s own cached_time, not since a common point. So:

A proc cached two hours ago looks innocent next to one cached three weeks ago.

They are not comparable without knowing that. The Cached column gives you each procedure’s own start point, and it should be read alongside every total on the row.

A procedure recompiled recently – because of a schema change, a plan eviction, or memory pressure – restarts its counters from zero.

---

Reading the grid

Column	What it is
Stored Procedure	The procedure.
Total Writes	Logical writes since this procedure was cached. Drawn with a bar.
Data Written	The same in bytes.
Avg Writes/Exec	Per-call cost.
Executions	How many calls.
Calls/sec	Rate, which normalises for different cache ages.
Total Sec	Total duration.
Avg ms	Duration per call.
Cached	When this procedure’s counters started.

Calls/sec is the column that survives the cached_time problem – it is a rate rather than a total, so two procedures cached at different times can be compared on it.

---

The toolbar

Button	What it does
Top 25 · Top 100 · Top 500	How many procedures.
Refresh	Reload now.

---

How to read the report

1. Look at the Cached column first. Totals are only comparable between procedures cached at similar times.
2. Use Calls/sec to compare across different cache ages.
3. Compare Executions against Avg Writes/Exec. That is the frequent-versus-heavy distinction.
4. Check the share the top few carry. Write work is usually concentrated.
5. Cross-check with CPU by Query – a procedure heavy in writes is often heavy in CPU too.

Common patterns

One procedure carrying most of the writes. Normal for an application with a main write path. Worth knowing which it is.

High Executions, low Avg Writes/Exec. Called constantly. Look at whether the application can batch.

Low Executions, enormous Avg Writes/Exec. A bulk operation. Look at the procedure’s logic – often a full rewrite of a table that could be an incremental update.

A procedure that looks trivial with a very recent Cached time. Its counters just started. It may be your biggest writer.

---

Related reports

Report	Why you would go there
CPU by Query	Whether the same procedures are also expensive in CPU.
I/O by Database	Where those writes land on storage.
Tables With Triggers	Writes that fire more writes.

---

Frequently asked questions

Why are two procedures’ totals not comparable? Because each counter runs from that procedure’s own cached_time. Use Calls/sec, or check the Cached column before comparing.

What is a logical write? A page modified in the buffer pool. It is not the same as physical I/O – a page written many times may reach disk once.

Why did my biggest procedure disappear? It was recompiled, so its counters reset. Check the Cached column.

---