Query Timing Capture files with SQL

A Timing Capture file is a SQLite database. You can use standard SQL queries to extract data from a capture, for example to analyze it programmatically or to feed it into other tools, without the PIX UI.

PIX includes a SQLite extension, PixStorage.dll, that decompresses the capture data that PIX stores in compressed form and exposes it as virtual tables. This article shows how to load the extension and gives example queries for common tasks.

Prerequisites

  • PIX on Windows. PixStorage.dll is in the PIX installation folder, C:\Program Files\Microsoft PIX\<version>, where <version> is the PIX version, such as 2408.05.
  • A 64-bit SQLite tool or library. The examples in this article use the sqlite3.exe command-line shell, which is in the SQLite tools package for Windows x64 on the SQLite download page. You can also use the SQLite library from sqlite.org, a .NET SQLite provider, or a SQLite library for another language.
  • A Timing Capture file. Timing Captures and GPU Captures both use the .wpix file name extension, but only Timing Captures are SQLite databases.

Open a capture and load the PIX SQLite extension

Load PixStorage.dll before you query the capture. Its entry point is sqlite3_batchexpand_init.

  1. Open the capture in sqlite3.exe. Use the -readonly option so that you don't modify the capture.

    sqlite3.exe -readonly "C:\captures\MyCapture.wpix"

  2. Load the extension, and format the output as columns with headers. Replace <version> with your PIX version.

    .load 'C:\Program Files\Microsoft PIX\<version>\PixStorage.dll' 'sqlite3_batchexpand_init'
.mode column
.headers on

If you use a SQLite library, enable extension loading, and then load PixStorage.dll with the sqlite3_batchexpand_init entry point.

Understand the main tables

The following tables are used in the examples in this article.

Table Contents
Strings Text values, such as event, thread, queue, and counter names. Other tables refer to them by Id.
CaptureFacts Values about the capture, such as its start and stop timestamps.
Threads CPU threads in the capture.
ApiCommandQueue GPU command queues in the capture.
PixEventInfo One row per unique PIX event name.
PixCounterGroup, PixCounterInfo The counter hierarchy that the Metrics layout shows.
PixCpuExecution Virtual table. PIX event executions on CPU threads.
PixGpuExecution Virtual table. PIX event executions on GPU queues.
PixCpuExecutionTimes Virtual table. Time that each CPU PIX event spent running and stalled.
PixCounters Virtual table. Counter values over time.

The virtual tables are available only after you load PixStorage.dll. Timestamps and durations are in nanoseconds.

Limit queries to the valid capture range

The CaptureFacts table stores the capture's start and stop timestamps. Use these values to limit queries to the data that PIX shows as valid, which is the white area of the timeline.

CaptureFacts Id Value
1 Capture start time
2 First reliable time
3 Last event time
24 Capture stop time

In general, queries that cover a range of time should use the first reliable time (Id 2) and the capture stop time (Id 24) as the bounds.

Query CPU PIX events

PIX processes each pair of PIX begin and end events into an execution, which has a begin and end timestamp. The following query returns a PIX event execution on a CPU thread, with the event name, the thread, and the thread name.

select s1.Value as 'Pix event', t.ProcThreadId, s2.Value as 'Thread name', cpu.*
from Strings s1, Strings s2, PixCpuExecution cpu, PixEventInfo pei, Threads t
where
    cpu.EventId = pei.Id
    and cpu.BeginTimestamp >= (select Value from CaptureFacts where Id = 2)
    and cpu.EndTimestamp <= (select Value from CaptureFacts where Id = 24)
    and pei.NameId = s1.Id
    and cpu.ThreadRowId = t.Id
    and t.ThreadNameId = s2.Id
limit 1;

To calculate the duration of an execution, subtract BeginTimestamp from EndTimestamp.

Query GPU PIX events

If a PIX event has a GPU context, PIX also creates an execution on the GPU queue. The following query returns a PIX event execution on a GPU queue, with the event name and the queue name.

select s1.Value as 'Pix event', s2.Value as 'Api queue', gpu.*
from Strings s1, Strings s2, PixGpuExecution gpu, PixEventInfo pei, ApiCommandQueue q
where
    gpu.EventId = pei.Id
    and gpu.BeginTimestamp > (select Value from CaptureFacts where Id = 2)
    and gpu.EndTimestamp < (select Value from CaptureFacts where Id = 24)
    and pei.NameId = s1.Id
    and gpu.ApiCommandQueueId = q.Id
    and q.NameId = s2.Id
limit 1;

Measure CPU execution and stall time

The duration of a CPU PIX event includes time when its thread was stalled or context-switched out. To see how long the code in the event actually ran, query the PixCpuExecutionTimes virtual table. For each execution, it returns:

  • Duration: the total time between the begin and end of the event.
  • Execution: the time that the thread was running.
  • Stall: the time that the thread wasn't running.

PIX calculates Execution and Stall from the context switches in the capture. If the capture doesn't include context switches, Stall is 0 and Execution equals Duration.

The following query returns the five CPU PIX event executions with the longest execution time.

select s.Value as 'Pix event', times.*
from Strings s, PixEventInfo pei, PixCpuExecutionTimes times
where
    times.BeginTimestamp >= (select Value from CaptureFacts where Id = 2)
    and times.EndTimestamp <= (select Value from CaptureFacts where Id = 24)
    and times.EventId = pei.Id
    and pei.NameId = s.Id
order by times.Execution desc
limit 5;

The results look like the following example:

Pix event   Duration  Execution  Stall     EventId  BeginTimestamp  EndTimestamp
----------  --------  ---------  --------  -------  --------------  ------------
Frame 2722  16683868  77058      16606810  216      3527275848      3543959716
Frame 3005  16713218  71336      16641882  499      8247344274      8264057492
Frame 2630  16699710  70163      16629547  124      1992790943      2009490653
Frame 2905  16686363  64312      16622051  399      6579498021      6596184384
Frame 2529  16734681  63259      16671422  23       308229107       324963788

In this example, the thread ran for only a small part of each event's duration, and spent most of the time stalled.

Query counters

Counters use two metadata tables and a virtual table:

  • PixCounterInfo lists each counter and the group that it belongs to.
  • PixCounterGroup lists the counter groups and their parent groups.
  • PixCounters contains the counter values over time. The Metrics layout uses this data for its counter tree and graphs.

List the counters in a capture

The following query lists each counter's Id with its full path in the counter tree. The .width command keeps long counter paths from wrapping in sqlite3.exe. The query uses group_concat with order by, which requires SQLite 3.44 or later.

.width 6 100

with recursive
counter_group(Id, ParentId, Name, Depth) as
(
        select c.Id, c.GroupId, s.Value, 0
        from PixCounterInfo c, Strings s
        where c.NameId = s.Id
    union all
        select cg.Id, pcg.ParentGroupId, s.Value, cg.Depth + 1
        from PixCounterGroup pcg, counter_group cg, Strings s
        where pcg.Id = cg.ParentId and pcg.NameId = s.Id
)
select cg.Id, group_concat(cg.Name, ' -> ' order by cg.Depth) as 'Counter tree'
from counter_group cg group by cg.Id;

Each row shows the counter name, followed by its parent groups. For example, if you enabled the GPU resources option when you took the capture, the row Local Usage -> GPU Memory (Adapter #1) is the counter Local Usage in the group GPU Memory (Adapter #1). This counter reports, in megabytes, how much memory your app asked to place in the GPU's local memory.

A counter's Id can differ from one capture to the next, so don't hard-code it. Look up counters by name instead, as the next queries show.

Get the values of a counter

The following query returns values for one counter. Replace the counter name and the group name with names from the counter tree. The adapter number in a GPU Memory group name varies between captures, so this example matches the group name with like.

select c.Value, c.Timestamp
from PixCounters c, PixCounterInfo ci, PixCounterGroup pcg, Strings sc, Strings sg
where
    sc.Value = 'Local Usage'
    and sg.Value like 'GPU Memory (Adapter #%'
    and ci.NameId = sc.Id
    and pcg.NameId = sg.Id
    and ci.GroupId = pcg.Id
    and c.CounterId = ci.Id
    and c.Timestamp >= (select Value from CaptureFacts where Id = 2)
    and c.Timestamp <= (select Value from CaptureFacts where Id = 24)
limit 10;

This query matches a counter whose group is at the top of the counter tree. If your system has more than one GPU, it returns values for each adapter's group. For a counter in a nested group, join PixCounterGroup again on ParentGroupId for each additional level.

To get the values of every counter in a group, remove the filter on the counter name, and include the counter name in the results:

select c.Value, c.Timestamp, sc.Value as 'Counter name'
from PixCounters c, PixCounterInfo ci, PixCounterGroup pcg, Strings sc, Strings sg
where
    sg.Value like 'GPU Memory (Adapter #%'
    and ci.NameId = sc.Id
    and pcg.NameId = sg.Id
    and ci.GroupId = pcg.Id
    and c.CounterId = ci.Id
    and c.Timestamp >= (select Value from CaptureFacts where Id = 2)
    and c.Timestamp <= (select Value from CaptureFacts where Id = 24)
order by c.Timestamp asc
limit 10;

Get help with queries

For help with queries, send feedback to the PIX team. In PIX, select the Email button in the top-right toolbar. You can also ask in the pix channel of the DirectX Discord server.

Related content

  • Last updated on