Query

The Query view runs SQL against the connected database while tracing what the storage engine does - every physical page read, lock, latch, wait, and plan operator - then replays the activity on a Timeline.

Open it with the Query button on the database toolbar, or the Query link beside a SQL Server entry in the Start page's recent connections. It needs a live SQL Server connection.

Running a query

Enter SQL in the editor and press Execute. With Track query on, as it is by default, the query runs with a trace session, and when it completes the captured activity loads into the timeline. The first run in a new Query tab also opens the Allocations, Execution Plan and Events panes.

The editor's command bar has the tracing options, and its tab strip the result panes - see SQL Editor for the full set, and Multi-statement queries for tracing a single statement out of a larger script.

WARNING

Data modification queries (INSERT / UPDATE / DELETE) are run inside a transaction that is rolled back after the trace is captured, so the data is left unchanged. See Log Records for how this works and what it captures. This only happens while Track query is on - with it off, a modification runs normally and changes the data.

Events menu

The Events menu selects what the trace captures:

Page I/O is always captured. Locks opens a submenu of lock categories to capture (Read, Update, Write, Schema, Range, Bulk, or None/Default) - by default this excludes Schema locks, since they are held for a large part of the query's lifetime and would otherwise dominate the Locks band. Waits and Latches can be toggled independently, as can Columnstore (segment scans, rowgroup reads and elimination, object pool lookups, batch and bitmap filters, and aggregate pushdown - on by default, see Columnstore), Memory (grants, spills, and sort warnings, listed in the Events pane) and Call Stack - see Call Stack.

By default Waits, Columnstore and Call Stack are on, and Latches and Memory off.

The lock categories, Waits and Latches also filter what is already loaded - turning one off hides its events and its band straight away, without running the query again. Columnstore, Memory and Call Stack change what the next run captures.

Query menu

• Track query - on by default. Runs the query with a trace session, so its events, plan and log records are captured. With it off the query simply runs - see Track query
• Crop to query - on by default. Limits the captured trace to the statement being run, rather than everything happening on the connection
• Include system objects - includes system tables and indexes in captured events, normally filtered out. It also filters what is already loaded
• Open all indexes - opens an Index View pane for every rowstore index the plan seeks or scans, each linked to the replay, so a join's indexes can be watched side by side as it runs. A columnstore index is opened on its own from the plan or the timeline - see Columnstore Viewer
• Export to file and Import from file - save a run to a file and open it again later - see Exporting a run

Exporting a run

Export to file saves the last run to an .ivtrace file - the SQL, the messages, the execution plan, the captured events and log records, and the call tree. For a Full Trace run it also saves what the replay produced - the call tree, the Trace Timeline and every call's arguments - but not the recording itself, so a Full Trace can be looked at again without recording it again. It is available once a query has run, and for a Full Trace run once the recording has loaded. The result rows are not saved.

Import from file opens a saved run in the current Query tab, in place of whatever was there, and puts its SQL in the editor. Messages starts with the server and database it was exported from, followed by the messages from the original run.

A run refers to the database's objects and pages, so import it into a Query tab on the same database it was exported from. Importing into a different one works, but a message warns that its objects and pages may not match.

WARNING

An exported run can hold data from your database - the rows in a modification's log records, and for a Full Trace the values each call was passed. Treat the file like the database it came from before sending it to anyone.

Views

The View menu opens additional panes:

• SQL Editor - the query editor
• Allocations - the allocation map, scoped to the query - see Allocations
• Execution Plan - the captured plan, connected to the timeline - see Execution Plan
• Events - the raw list of captured events behind the timeline - see Events
• Call Tree - the query's call stacks merged into one tree, with Members and Symbols beside it on its submenu - see Call Stack
• Timeline - the replay timeline - see Timeline
• Trace Timeline - every call recorded by Record Full Trace on the Trace menu, which takes the Timeline's place while it is on - see Trace Timeline
• Simulation - steps through the query's plan operator by operator - see Simulation
• Reset Layout - restores the default pane arrangement
• Instructions - a quick reference for the view, with links that open panes and switch capture options

Pages and indexes opened from the trace - by double-clicking a timeline event, clicking a page link in the Events pane, or right-clicking an operator - also open as panes, so everything about the query stays in one tab.

Trace menu

Record Full Trace records each run with Microsoft's Time Travel Debugging while it is on, so the Call Tree holds every call the query made rather than the stacks sampled at each event - see Full Trace. It is for development and test instances only: it needs administrator consent, can restart the SQL Server service, slows the recorded query down considerably and writes very large files. The dialog shown when it is turned on explains the requirements, and the Time Travel Warning setting brings the dialog back once it has been dismissed. While it is on, a red Full Trace badge sits at the end of the menu bar - click it to turn recording off again.

Capture chooses what a Full Trace records of the query's memory for the Memory History in the Trace Timeline. Heap Memory has Reads and Writes, for the memory the query allocates, and Stack Memory covers the objects its functions keep on their own stacks and hand to each other. All three are on by default. Turning some off makes a large query's recording quicker to load and smaller. They apply from the next run. The Memory History run option turns them all off together.

Search symbols is covered in Searching Symbols.

Operator Tracks opens the Operator Tracks tab, which lists the tracks you have added to the Trace Timeline's operators - see Operator tracks.

Layout

Panes are tabs that can be dragged into any layout - drop a tab beside or below another pane to split the space, or onto a pane to stack them. While dragging, the drop zones highlight to show where the tab will land. Right-click a tab for Close All, which closes every tab in its group, and Close Others, which closes every tab in the group except that one. Tabs that cannot be closed are left open. The default layout is the SQL Editor above the Timeline.

The layout is remembered - the pane arrangement, timeline visibility, and the Events menu options, Crop to query, Include system objects and the Trace menu's Capture options are all restored the next time a Query tab is opened. Track query and Record Full Trace are not, and neither are the Index, Page, Columnstore and Simulation panes opened from a query. Reset Layout on the View menu puts everything back to the default.

How this works

The query runs with an Extended Events session filtered to the connection, capturing page reads, locks, waits, per-operator profiles, and the execution plan. The events are matched to plan operators to build the timeline.

See How query tracing works for the details.

Next steps

For a walkthrough, the tutorial's Query section traces queries against a sample database - including scans vs seeks and the three physical join operators.