Hourly Drift
Overview
The question this page answers is which hour of the day is getting worse than that same hour used to be?
Two kinds of chart usually read hours, and each throws away half of the answer:
- A heatmap by hour shades each hour by what it cost. To do that it averages every occurrence of the hour into one cell, so a nine o’clock hour that has doubled over two weeks draws the same cell as one that has not moved.
- A trend for the whole database fits one line through everything. Mornings growing five percent a day while the overnight batch shrinks by the same amount comes out flat: two real movements reported as a healthy plateau.
This page keeps both halves. The window’s hours are sorted into the 24 hours of the clock, and inside each hour the days stay in order. What comes out is a level and a slope for every hour of the day, and the pair is the finding: an hour can be the busiest of the day and perfectly steady, or modest and growing four percent a morning.
Two readings, two kinds of work
The measure on the toolbar decides what a growing hour means:
| Measure | A growing hour means |
|---|---|
| CPU, Executions, Reads, Log bytes | More work at that time of day. That hour reaches the limit of the server before the rest of the day does, and a server is judged by its worst hour. Capacity work. |
| Avg duration | The same statements taking longer at that time of day than they used to. Queries do not do that to themselves: something else running beside them is. Contention work. |
The verdict points at different pages for each.
Where to find it
In the tree, under a database, Real Time → Query Store → Hourly Drift.
The Query Store folder is hidden below SQL Server 2016, and on master and tempdb, where Query Store cannot be turned on. Reaching the page another way runs the same checks and produces a message instead.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store arrived in SQL Server 2016. |
| Query Store on for the database | The history lives inside Query Store. |
| At least four whole days of history | A slope needs at least four readings per hour. |
| A Query Store interval of 60 minutes or less | A longer interval cannot be placed in an hour of the day. |
| SQL Server 2017 or newer for Log bytes | avg_log_bytes_used arrived in 2017. On 2016 the page shows CPU time instead and says so. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| CPU / Avg duration / Executions / Reads / Log bytes | What an hour is measured in. Switching does not re-read Query Store. |
| 7 d / 14 d / 28 d | How many days of history are laid out. |
| All days / Weekdays / Weekends | Which days are counted. Switching does not re-read Query Store. |
| 1% / 2% / 5% / 10% a day | How fast an hour has to move before it is called growing or falling away. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Hours are on this computer’s clock, and only whole hours the window completely covers are counted. The footer names the offset used.
TempDB is not offered: Query Store records tempdb use as a peak per execution, and a sum of peaks over an hour is not an amount of space.
Reading the chart
At the top, a verdict names the hour moving the most, or says that no hour is drifting and where the quiet hour is. Six tiles summarize the day:
| Tile | What it is |
|---|---|
| Busiest hour (Slowest hour for average duration) | The hour with the highest typical reading, and its share of the day. |
| Quietest hour (Fastest hour) | The hour with the lowest typical reading. An hour nothing ever ran in wins, because it is where maintenance belongs. |
| Growing hours | Hours getting steadily worse, and the one moving fastest. Click to filter. |
| Falling away hours | Hours getting steadily lighter or faster. Click to filter. |
| Steady hours | Hours whose movement is below the threshold or inside their own day to day wobble. Click to filter. |
| Days measured | How many days the fit ran over. Amber below eight. |
Click a filtering tile again to clear the filter.
Under the tiles, the cycle plot:
- The horizontal axis is the 24 hours of the day, one slot each.
- Inside each slot, time runs again: one mark per day of the window, oldest on the left.
- The gray rule across a slot is that hour’s usual level. Read across the slots, the rules are the shape of the day.
- The dashed line is the hour’s fitted trend. A slope inside a slot is drift.
- Color is the finding: red growing, green falling away, blue steady, teal nothing ever runs, gray too few days to say.
- Every slot shares one vertical scale starting at zero, because the hours are compared with each other as amounts. A fitted line that would fall below zero is cut off at the floor.
- Slots are never joined, and a day with no reading breaks the line.
Hover over a slot for its typical reading, the reading on the day under the pointer, and what the hour did. Click a slot to select its row; double click it for every day’s reading.
When is an hour called growing?
All three of these have to be true:
- It moves fast enough: the fitted slope is at least the toolbar’s percentage of the hour’s level per day.
- It moves far enough: over the whole window it changed by at least one second of CPU, one millisecond of average duration, 1,000 pages of reads, ten executions or one megabyte of log. Doubling from four hundred microseconds is still four hundred microseconds.
- It moves more than it wobbles: the fitted change is at least twice the hour’s own day to day wobble, measured as the average difference between consecutive days divided by 1.128. That estimator is used rather than a standard deviation because a standard deviation over a drifting series counts the drift as noise and then reports it as normal.
An hour needs at least four days of readings before a line is fitted through it.
Reading the grid
| Column | What it is |
|---|---|
| Hour | The hour of the day on this computer’s clock. |
| Days | Days something ran in the hour, of the days the window covered it. |
| Typical | The mean of the hour’s daily readings. |
| Quietest day / Busiest day | The lowest and highest daily reading. Blank for an hour nothing ran in. |
| Change a day | The fitted slope as a percentage of the typical reading. |
| Over the window | The fitted change from the first day to the last. |
| Own wobble | The hour’s day to day noise. |
| Share of day | The hour’s typical reading as a share of the whole day. Blank for average duration. |
| Reading | Growing, Falling away, Steady, Nothing ever runs, or Too few days to say. |
| Busiest was | The day of the busiest reading. |
Double-click a row to see every day’s reading for the hour. Right-click for:
| Action | What it does |
|---|---|
| Explain this hour | Every day’s reading, the typical level, the wobble and the fitted change. |
| Go to Slow Periods | The page that starts from a slow hour and names the queries in it. |
| Go to Load Sensitivity | For average duration: which queries slow down when the database is busy. |
| Go to Workload Change | For the totals: whether queries ran more often or each run cost more. |
| Copy this hour’s readings | The same text as Explain this hour. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_runtime_stats |
Executions and the average CPU, duration, reads and log bytes per plan per interval. |
sys.query_store_runtime_stats_interval |
The interval each row belongs to, and so its hour. |
sys.database_query_store_options |
The readiness banner and the interval length. |
Things the query and the page get right
Hours are anchored on a fixed midnight. Each interval’s hour is counted from a fixed date, so it is always the floor of the hour it started in. Counting hours from the window start instead puts every interval whose minute is below the start’s an hour late, and the overnight batch is filed under the hour after it ran.
Hours are keyed on the interval’s start. An hour long interval covering 04:00 to 05:00 ends at 05:00, and keying on the end files its work an hour late.
The window ends at the last completed interval, and only whole hours inside the window are counted. A half hour at either end would otherwise read as an hour that cost nothing, and a fit through it reports growth that never happened.
History before Query Store started is not counted as idle. On a store turned on inside the window, the days before it are left out rather than read as days of zeros.
Only regular executions are measured. A burst of client timeouts at one hour would otherwise read as that hour slowing down. Aborted and failed executions are counted in the footer instead.
Totals are weighted by executions, never averages of averages, and average duration is the hour’s total duration divided by its executions.
Messages you may see
One caution about the window rather than the database: its weekdays and weekends are 3.2 times apart. An office workload’s weekends are a different database, and a fit across both is decided by where the weekends fall in the window. Set the day filter to Weekdays and read it again.
Not enough days to say whether any hour is drifting. The window holds fewer than four whole days of history, perhaps because of the day filter.
Query Store recorded nothing over the last 14 days. There is no history in the window to lay out.
Query Store on this database aggregates every 1440 minutes. An interval longer than an hour cannot be placed in an hour of the day. Set
INTERVAL_LENGTH_MINUTESto 60 or less.
Query Store’s history starts inside the window, so only the days since then are counted.
Related reports
| Report | Why you would go there |
|---|---|
| Slow Periods | Starts from a slow hour and finds which queries it belongs to. |
| Load Sensitivity | Which queries slow down when the database is busy. |
| Workload Change | Whether a heavier hour is queries running more often or each run costing more. |
| Throughput and Latency Headroom | How much room the server has left at its busiest. |
| Waits by Query | What the workload queues for at a slow hour. |
| Rank Movement | Which individual queries are climbing, rather than which hours. |
Frequently asked questions
The window is 14 days, so why does the page say 15 days measured? The window starts part way through a day on this computer’s clock, so its first and last calendar days each hold only some of their hours. Both are counted, for the hours the window covers.
One hour has a single enormous spike. Is that why it is growing? Look at where the spike is. A least squares line is pulled by a spike near either end of the window. The picture shows it, which is why the page draws the marks rather than only reporting the slope.
Why is the quietest hour not the one with the lowest number? An hour in which nothing ran on any day outranks one that is merely quiet, because nothing competes with maintenance in it.
Why are average durations sometimes near zero? On a database of very fast statements, an average of a few microseconds sits on the floor of an axis drawn in milliseconds. The tooltip and the grid give the exact figure.