Module Execution Statistics

Overview

SPs by Logical Writes covers one measure for stored procedures only, and nothing else in the product read sys.dm_exec_trigger_stats, so a trigger that costs a second on every insert was hard to find. The Module Execution Statistics report answers which code in this database costs the most? for every kind of module the plan cache keeps statistics for:

  • 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).

Each module is ranked by the measure you pick, with totals, averages per call, per minute rates since the plan was cached, and its share of the database’s total.

A second page, Module Execution Statistics by Database, reads every database on the instance.

Where to find it

Route How
Database tree Expand a database → Real Time → Module Execution Statistics
Related Links bar From Tables With Triggers, Scalar UDF Inlining and Plan Guides and Hints
Module Execution Statistics by Database Right-click a row → Open Module Execution Statistics for <database>

The page is offered on SQL Server 2008 and later. The page title reads Module Execution Statistics for <database 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. 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 in Sales, 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 for the measure 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, largest first by the measure:

Column What it is
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 the database 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.

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 in a query window, with a USE for its database. Nothing is run. An encrypted or CLR module, one dropped since, or one your login cannot see has no definition to show
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 for the measure
Go to Schema Search for the module and, for a trigger on a table, the table pages

Right-click the chart to copy it as an image. The grid exports to CSV and Excel like every other grid.

Permissions and versions

  • The report reads sys.dm_exec_procedure_stats, sys.dm_exec_trigger_stats and sys.dm_exec_function_stats, which need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later). 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.triggers and sys.trigger_events in the database. If they cannot be read, the parent column shows (unknown) and the note says why.
  • 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.