Workload by Application
Overview
When a server is slow, the first question is usually which application is using it? The Connections and Sessions reports count sessions by application, and the query reports rank statements, but neither says how much of the CPU, reads and writes each application, client machine or login is responsible for.
The Workload by Application report answers that directly. It reads every session’s CPU, reads and writes every ten seconds, takes the work each session did between two readings, and adds it up by the dimension you pick on the toolbar:
- Application – the program name the client connected with. SSMS query windows are shown as SSMS, every step of a SQL Server Agent job is shown under the job name, and the default SqlClient names are grouped as .NET SqlClient application.
- Host – the client machine.
- Login – the login the session runs as.
- Database – the session’s current database.
- App by Database – each application in each database it works in.
The first reading is the baseline, so the first interval arrives three seconds later; after that a reading is taken every ten seconds while the page is open, and the last sixty intervals (ten minutes) are kept.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Workload by Application |
| Instance reports navigator | Performance group, after Connections |
| Related Links bar | From Connections and Sessions |
| Report arrows | Previous is What is Active, next is Availability Groups |
Reading the page
Cards across the top give the CPU measured in the window (and the average number of CPU cores it kept busy), the top application (or host, login or database) with its share, the logical reads, physical reads and writes, and the sessions connected now with the requests running.
The chart stacks the CPU of the top ten over the window, one band each, with everything else in an Other band. The height is CPU cores kept busy in each interval: 2,000 ms of CPU in one second is two cores. Hover the chart to see every band at that moment; click a legend row to find that row in the grid.
The grid ranks every application, host, login or database by CPU:
| Column | Meaning |
|---|---|
| CPU ms, CPU %, Avg Cores | CPU in the window, its share of the total, and the cores it kept busy on average |
| Logical Reads, Physical Reads, Writes | Pages read from memory, read from disk, and written, in the window |
| Tempdb MB | tempdb its sessions hold now (temporary tables, sorts, hashes) |
| Memory MB | Memory its sessions hold now (sys.dm_exec_sessions.memory_usage) |
| Sessions, Active Requests | Connected now, and running a request now |
| Statements Seen | Distinct statements the readings caught running |
Double click a row (or right-click, Show the statements seen running) to see the statements the readings caught running for it, most CPU first. Right-click a host, login or database to go to the other pages about it. The grid supports the usual CSV and Excel export.
Toolbar: System sessions includes the instance’s own background sessions. Pause stops the readings (the choice is remembered). Sample now takes a reading at once, and Start over drops every reading and takes a new baseline, for measuring from a moment you choose.
Last 7 days
Pick Last 7 days on the toolbar to see the same breakdown over the past week. When historic monitoring is configured, the Database Health Monitor service runs trackWorkloadByApp every two minutes (from DatabaseHealthRunEvery2Minutes). It applies the same rules as the live readings and stores the CPU, reads and writes of each application, host, login and database in DBHealthHistory.dbo.WorkloadByAppHistory. Agent job sessions are stored under the job name.
- The chart stacks the top ten by hour, as the average CPU cores kept busy in each hour.
- The cards give the CPU over the range, the top application (or host, login or database), the reads and writes, and the busiest hour.
- The grid ranks every group over the range, with the hour it used the most CPU in. Sessions, tempdb, memory and statements are only on the Live view.
- The dimension picker and System sessions work as they do live. Refresh reads the history again. Live readings stop while the history is shown and carry on when you pick Live.
- The first collection after the service starts is a baseline, and a collection more than fifteen minutes after the one before is a new baseline, so a stopped service leaves a gap rather than piling its work onto one hour.
- Rows are kept for
HistoricRetentionDays(45 days when it is not set, 0 keeps everything), and never less than 8 days.dbHealthHistoryCleanupremoves older rows.
If the page says historic monitoring is not set up, or that DBHealthHistory does not collect the workload yet, configure historic monitoring or upgrade DBHealthHistory to the current version.
How the numbers are worked out
- A session’s own CPU, reads and writes move only when a request finishes, so each reading adds the counters of the requests still running. A long running query shows up while it runs, not only when it ends.
- A session is matched on its session id and login time, so a reused session id is a new session.
- A session that connected after the last reading did all of its work in the interval. A session that has gone counted what it did up to the last reading it was seen in, once.
- A session that connects and disconnects entirely between two readings is not seen. Applications that open a connection for every short query can be undercounted.
- An Agent job step session names its job by id; the report reads the job names from
msdb.dbo.sysjobs. A login that cannot read that table sees the job id instead.
Permissions and versions
- The report reads
sys.dm_exec_sessionsandsys.dm_exec_requests, which need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later) to see other sessions. Without it only your own sessions are visible, and the page says so. - tempdb use comes from
sys.dm_db_session_space_usageandsys.dm_db_task_space_usage. If they cannot be read, the tempdb column is left empty and the page says why. - The session’s database (
database_id) arrived in SQL Server 2012. On older builds the database of a running request is used.