The Timing Capture file format is a Sqlite database. Queries written in standard SQL syntax can be used to extract data from Timing Capture files to perform a variety of analysis tasks without requiring the PIX user interface.
PIX on Windows includes a Sqlite extension that simplifies the queries for common tasks such as determining the duration and execution times for a PIX event, extracting counter values and so on. The Sqlite extension is named PixStorage.dll. The extension can be found in the PIX on Windows installation directory (“C:\Program Files\Microsoft PIX\2408.05”, for example).
PixStorage.dll must be loaded into Sqlite before extracting Timing Capture data. The following SQL script illustrates how to load the extension. Several example queries are also provided. The script is in the format supported by the sqlite3.exe command line tool available from the Sqlite site. Note that the 64 bit version of Sqlite must be used.
For additional help with queries, contact the PIX team by sending an email using the PIX feedback button in the upper right corner of the PIX user interface.
/*
These commands are meant to provide examples and information to developers using PIX to extract data from their
captures for use in other tools or to analyze programmatically
*/
/*
The Timing Capture file format is a sqlite database. It can be opened using the 64-bit version of the sqlite3.exe commandline tool
available for download from sqlite.org (or any number of other available sqlite tools). The documentation for the CLI is here
https://sqlite.org/cli.html
The capture file's data can also be queried programmatically using the native or .net libraries available from sqlite.org
or any number of other language supported libraries.
These examples were written using the sqlite3.exe
*/
/*
Some of the data is compressed to improve capture performance. In order to facilitate query and processing of this data
the PIX team has supplied a sqlite extension that ships with PIX and can be loaded to expose virtual tables to expose the data.
*/
.load 'D:\pix1\Binaries\Release\x64\bin\PixStorage.dll' 'sqlite3_batchexpand_init'
.mode column
.headers on
/*
There are several constants that are stored in the capture's CaptureFacts table related to the capture's start and stop timestamps. The
following are primary keys for the rows in the CaptureFacts table and can be used to query valid capture data (the white area shown by PIX)
CaptureStartTime = 1
CaptureFirstReliableTime = 2
CaptureLastEventTime = 3
CaptureStopTime = 24
In general queries that are evaluating a region of time should use CaptureFirstReliableTime and CaptureStopTime to bookend the
timerange for data being queried
*/
/*
Pix Begin and End events are processed into "executions" during capture where each execution has a begin and end timestamp
(in nanoseconds). To query the executions that occur on the CPU the following:
*/
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;
/*
Result:
Pix event ProcThreadId Thread name BeginTimestamp EndTimestamp Level ThreadRowId EventId Color
---------- ------------- ----------- -------------- ------------ ----- ----------- ------- -----
Frame 2525 3899830305680 241524138 258196292 0 207 19 0
*/
/*
If a PIX event is provided a GPU context then a corresponding execution will be created on an ApiQueue. To query these executions
the following query could be used:
*/
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;
/*
Result:
Pix Event Api queue BeginTimestamp EndTimestamp Level ApiCommandQueueId EventId Color
--------- --------- -------------- ------------ ----- ----------------- ------- -----
Clear Graphics 241511462 241518677 0 1 2 0
*/
/*
Subtracting the EndTimestamp from the BeginTimestamp allows us to calculate the duration of a specific execution. On the CPU, sometimes
we want to know the actual time the event ran versus when it was stalled or context switched out of execution. In this case, a different
virtual function can be queried to calculate the time code within a specific PIX cpu execution was actually executing.
An example query:
*/
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;
/*
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 the above results, the query limited to the top 5 CPU events. In this case, the execution time was short for these events, most of
the time was spent stalled.
*/
/*
Counters are stored in a similar fashion to PIX events. In this case, two tables are used to store metadata about the counters and then
a virtual table is used to store the raw counter data. This is the data that is used to create the counters tree and graph data in
Metrics view.
*/
.width 6, 100
with recursive
counter_group(Id, ParentId, name) as
(
select c.Id, c.GroupId, s.Value
from PixCounterInfo c, Strings s
where c.NameId = s.Id
union all
select cg.Id, pcg.ParentGroupId, s.Value
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, ' -> ') as 'Counter tree'
from counter_group cg group by cg.Id;
/*
Id Counter tree
------ ----------------------------------------------------------------------------------------------------
1 Bytes remaining -> Title Writes Window
2 Bytes written -> Title Writes Window
3 CPU 0 -> % Busy -> Title CPU
4 CPU 1 -> % Busy -> Title CPU
5 CPU 2 -> % Busy -> Title CPU
6 CPU 3 -> % Busy -> Title CPU
7 CPU 4 -> % Busy -> Title CPU
8 CPU 5 -> % Busy -> Title CPU
9 CPU 6 -> % Busy -> Title CPU
10 CPU 0-7 -> % Busy -> Title CPU
11 CPU 8-13 -> % Busy -> Title CPU
12 All -> Bytes Received -> Title Network
13 Excluding Tools -> Bytes Received -> Title Network
14 All -> Bytes Sent -> Title Network
15 Excluding Tools -> Bytes Sent -> Title Network
16 Packets Received -> Title Network
17 Packets Sent -> Title Network
18 All -> Bytes Received Per Second -> Title Network
19 Excluding Tools -> Bytes Received Per Second -> Title Network
20 All -> Bytes Sent Per Second -> Title Network
21 Excluding Tools -> Bytes Sent Per Second -> Title Network
22 Packets Received Per Second -> Title Network
23 Packets Sent Per Second -> Title Network
24 Load % -> Power Monitor
25 Frame Duration -> D3D
26 Swap Throttle -> D3D
27 Stalled -> D3D
28 % Frame Time Stalled -> D3D
29 % Frame Time Swap Throttled -> D3D
Note the .width 6, 100 which controls the output of the table so that the counter hierarchy doesn't wrap.
The Id is the id of the counter and can be used to query for values directly. The Id of the counter could vary from one capture to the
next and shouldn't be hardcoded.
*/
/*
Once the Id of the counter in question is known values for that counter can be directly queried from the counters virtual table. For
example, the following query:
*/
select c.Value, c.timestamp
from PixCounters c, Strings sc, Strings sl1, Strings sl2, PixCounterGroup pcg2, PixCounterGroup pcg1, PixCounterInfo ci
where
sc.Value = 'CPU 1'
and sl1.Value = '% Busy'
and sl2.Value = 'Title CPU'
and ci.NameId = sc.Id
and pcg1.NameId = sl1.Id
and pcg2.NameId = sl2.Id
and c.CounterId = ci.Id
and ci.GroupId = pcg1.Id
and pcg1.ParentGroupId = pcg2.Id
and c.Timestamp >= (select Value from CaptureFacts where Id = 2)
and c.Timestamp <= (select Value from CaptureFacts where Id = 24)
limit 10;
/*
Value Timestamp
---------------- ----------
4.44190120697021 718600109
4.42664241790771 819775837
4.42969417572021 920941689
4.41443490982056 1022195683
4.42206478118896 1123288959
4.45563459396362 1224455335
4.40070199966431 1325650309
4.42816829681396 1426796804
4.42206478118896 1527965935
4.41443490982056 1629137932
*/
/*
If you would like all the CPU counters the filter on the counter's name could be removed from the query above
*/
select c.Value, c.timestamp, sc.Value as 'Counter name'
from PixCounters c, Strings sc, Strings sl1, Strings sl2, PixCounterGroup pcg2, PixCounterGroup pcg1, PixCounterInfo ci
where
sl1.Value = '% Busy'
and sl2.Value = 'Title CPU'
and ci.NameId = sc.Id
and pcg1.NameId = sl1.Id
and pcg2.NameId = sl2.Id
and c.CounterId = ci.Id
and ci.GroupId = pcg1.Id
and pcg1.ParentGroupId = pcg2.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;
/*
Value Timestamp Counter name
---------------- --------- ------------
0.0 718600109 CPU 0
4.44190120697021 718600109 CPU 1
0.0 718600109 CPU 2
4.70740842819214 718600109 CPU 3
0.0 718600109 CPU 4
4.59906911849976 718600109 CPU 5
0.0 718600109 CPU 6
1.71854734420776 718600109 CPU 0-7
0.0 718600109 CPU 8-13
0.0 819775837 CPU 0
*/
0 comments