Module Execution Statistics by Database
Overview
CPU by Database says which database is busy. The Module Execution Statistics by Database report says which code, in which database, from the statistics SQL Server keeps for every cached plan of a module:
- stored procedures from
sys.dm_exec_procedure_stats, - triggers from
sys.dm_exec_trigger_stats, with the table they are on and the events they fire for, - scalar functions from
sys.dm_exec_function_stats(SQL Server 2016 and later).
The chart ranks the top modules across the whole instance; the grid lists every module grouped by database, the database with the largest total for the measure first.
It is the instance view of the database level Module Execution Statistics report, and shares its toolbar, columns and rules.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Module Execution Statistics by Database |
| Instance reports navigator | Performance group |
| Report arrows | Previous is Missing Indexes, next is Open Transactions |
The page title reads Module Execution Statistics by Database for <server name>.
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the plan cache again |
| CPU / Duration / Reads / Writes / Phys Reads / Executions / Avg CPU / Avg Duration | The measure the chart draws, the grid is sorted by and the percent column follows. CPU by default |
| All / Procedures / Triggers / Functions | Which kind of module the chart and grid show. All by default |
| Cancel | Stops a read in progress (shown only while reading) |
The measure and the type filter are remembered for next time, and are shared with the database level page. Changing them does not read the server again.
Reading the page
The heading of the chart says what is drawn, for example Top modules by CPU across all databases, and how many procedures, triggers and functions are cached. The note under it says when the oldest plan on the page was cached and how long ago, because every figure here resets when a plan leaves the cache, followed by anything that could not be read.
The chart draws the top 20 modules on the instance for the measure, each labeled database.schema.name, as horizontal bars colored by type: blue for a procedure, orange for a trigger, green for a scalar function. Modules with nothing recorded for the measure are left out. Hover a bar for the executions, the CPU and duration in total and per call, the logical reads and writes, and when the plan was cached; click it to select the row in the grid.
The grid lists every module that passes the type filter. The databases are ordered by their total for the measure (for Avg CPU and Avg Duration, by total CPU and total duration), largest first, and the modules inside each database largest first by the measure.
| Column | What it is |
|---|---|
| Database | The module’s database |
| Module | schema.name, or the object id when the name cannot be read |
| Type | Procedure, Trigger or Function |
| Parent Table (Events) | For a trigger, the table it is on (or Database for a database DDL trigger) with its events, INSTEAD OF and disabled. A disabled trigger’s entry is gray |
| Executions | execution_count |
| CPU ms, Avg CPU ms | total_worker_time and the average per call |
| Duration ms, Avg Duration ms, Max Duration ms | total_elapsed_time, the average per call and the longest single call |
| Logical Reads, Avg Reads | Total pages read and the average per call |
| Logical Writes | Total pages written |
| Physical Reads | Pages read from disk |
| Spills | total_spills; empty on builds without that column (before SQL Server 2016 SP2 and 2017) |
| Execs per Minute, CPU ms per Minute, Reads per Minute | The total divided by the whole minutes since the plan was cached, never less than one |
| % of Database <measure> | The module’s share of its own database’s total for the measure, whatever the type filter. For Avg CPU and Avg Duration it is the share of total CPU and total duration |
| Cached Since | When the plan was cached |
| Last Execution | When it last ran |
| Plans | How many cached plans were added together |
A module compiled under two sets of SET options, or recompiled while an old plan was still running, has more than one cached plan. The report adds them into one row: the totals and counts are summed, the longest call is kept, Cached Since is the earliest of them and Last Execution the latest. The resource database is left out.
Hover a row for the same summary the chart shows. Selecting a row outlines its bar.
Right-click actions
| Item | What it does |
|---|---|
| Show Cached Plan (or double-click) | Opens the module’s most executed cached plan in the plan analyzer. If the plan has left the cache since, the page says so |
| Script Module Definition | Shows the module’s definition, read from its own database, in a query window with a USE for that database. Nothing is run. An encrypted or CLR module, one dropped since, or one your login cannot see has no definition to show |
| Open Module Execution Statistics for <database> | Opens the database level page for the row’s database |
| Open Tables With Triggers for <database> | On a trigger |
| Open Scalar UDF Inlining for <database> | On a function, on SQL Server 2019 and later |
| Copy the top modules as text | The heading, the note and the top ten modules on the instance for the measure |
Right-click the chart to copy it as an image. The grid exports to CSV and Excel like every other grid.
The Related Links bar on this page leads to CPU by Database, Waits, Optimizer Effort, Memory Grants and Spills, and What is Active.
Permissions and versions
- The report reads
sys.dm_exec_procedure_stats,sys.dm_exec_trigger_statsandsys.dm_exec_function_stats, which need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later). When the connection check finds the login does not have it, the menu item is shown muted with a tooltip naming the permission; clicking it explains and still lets you open the report. Each view is read on its own, so a refused view costs only its part and the note says which. If every view is refused, the page says the statistics could not be read and names the permission. - Scalar function statistics need SQL Server 2016 or later. On an older instance the note says only procedures and triggers are shown.
- A trigger’s parent table and events come from
sys.triggersandsys.trigger_eventsin each trigger’s own database. A database that is offline or cannot be opened costs its triggers only that column, which shows (unknown), and the note names the first such database. - The statistics are cumulative only since each plan was cached, and are lost when a plan leaves the cache, on a restart or after
DBCC FREEPROCCACHE. A module with no plan in the cache is not listed. - The read runs in the background with a 120 second timeout, and can be canceled.