{"id":6530,"date":"2024-08-06T11:44:02","date_gmt":"2024-08-06T18:44:02","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/pix\/?p=6530"},"modified":"2024-08-06T13:51:53","modified_gmt":"2024-08-06T20:51:53","slug":"querying-the-pix-timing-capture-file-format","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/pix\/querying-the-pix-timing-capture-file-format\/","title":{"rendered":"Querying the PIX Timing Capture file format"},"content":{"rendered":"
\n
The Timing Capture<\/a> file format is a Sqlite<\/a> database. \u00a0Queries 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.<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n
\n
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. \u00a0The Sqlite extension is named PixStorage.dll. \u00a0The extension can be found in the PIX on Windows installation directory (“C:\\Program Files\\Microsoft PIX\\2408.05”, for example).<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n
\n
PixStorage.dll must be loaded into Sqlite before extracting Timing Capture data. \u00a0The following SQL script illustrates how to load the extension. \u00a0Several example queries are also provided. \u00a0The script is in the format supported by the sqlite3.exe command line tool available from the Sqlite<\/a> site. \u00a0Note that the 64 bit version of Sqlite must be used.<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n
\n
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.<\/div>\n<\/div>\n
<\/div>\n
\n
\/*\r\nThese commands are meant to provide examples and information to developers using PIX to extract data from their\r\ncaptures for use in other tools or to analyze programmatically\r\n*\/\r\n\r\n\/*\r\nThe Timing Capture file format is a sqlite database.  It can be opened using the 64-bit version of the sqlite3.exe commandline tool\r\navailable for download from sqlite.org (or any number of other available sqlite tools).  The documentation for the CLI is here\r\nhttps:\/\/sqlite.org\/cli.html\r\n\r\nThe capture file's data can also be queried programmatically using the native or .net libraries available from sqlite.org\r\nor any number of other language supported libraries.\r\n\r\nThese examples were written using the sqlite3.exe\r\n*\/\r\n\r\n\/*\r\nSome of the data is compressed to improve capture performance.  In order to facilitate query and processing of this data\r\nthe PIX team has supplied a sqlite extension that ships with PIX and can be loaded to expose virtual tables to expose the data.\r\n*\/\r\n\r\n.load 'D:\\pix1\\Binaries\\Release\\x64\\bin\\PixStorage.dll' 'sqlite3_batchexpand_init'\r\n.mode column\r\n.headers on\r\n\r\n\/*\r\nThere are several constants that are stored in the capture's CaptureFacts table related to the capture's start and stop timestamps.  The\r\nfollowing 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)\r\n\r\n    CaptureStartTime = 1\r\n    CaptureFirstReliableTime = 2\r\n    CaptureLastEventTime = 3\r\n    CaptureStopTime = 24\r\n\r\nIn general queries that are evaluating a region of time should use CaptureFirstReliableTime and CaptureStopTime to bookend the\r\ntimerange for data being queried\r\n*\/\r\n\r\n\/*\r\nPix Begin and End events are processed into \"executions\" during capture where each execution has a begin and end timestamp\r\n(in nanoseconds).  To query the executions that occur on the CPU the following:\r\n*\/\r\n\r\nselect s1.Value as 'Pix event', t.ProcThreadId, s2.Value as 'Thread name', cpu.*\r\nfrom Strings s1, Strings s2, PixCpuExecution cpu, PixEventInfo pei, Threads t\r\nwhere\r\n    cpu.EventId = pei.Id\r\n    and cpu.BeginTimestamp >= (select Value from CaptureFacts where Id = 2)\r\n    and cpu.EndTimestamp <= (select Value from CaptureFacts where Id = 24)\r\n    and pei.NameId = s1.Id\r\n    and cpu.ThreadRowId = t.Id\r\n    and t.ThreadNameId = s2.Id\r\nlimit 1;\r\n\r\n\/*\r\nResult:\r\nPix event   ProcThreadId   Thread name  BeginTimestamp  EndTimestamp  Level  ThreadRowId  EventId  Color\r\n----------  -------------  -----------  --------------  ------------  -----  -----------  -------  -----\r\nFrame 2525  3899830305680               241524138       258196292     0      207          19       0\r\n*\/\r\n\r\n\/*\r\nIf a PIX event is provided a GPU context then a corresponding execution will be created on an ApiQueue.  To query these executions\r\nthe following query could be used:\r\n*\/\r\n\r\nselect s1.Value as 'Pix Event', s2.Value as 'Api queue', gpu.*\r\nfrom Strings s1, Strings s2, PixGpuExecution gpu, PixEventInfo pei, ApiCommandQueue q\r\nwhere\r\n    gpu.EventId = pei.Id\r\n    and gpu.BeginTimestamp > (select Value from CaptureFacts where Id = 2)\r\n    and gpu.EndTimestamp < (select Value from CaptureFacts where Id = 24)\r\n    and pei.NameId = s1.Id\r\n    and gpu.ApiCommandQueueId = q.Id\r\n    and q.NameId = s2.Id\r\nlimit 1;\r\n\r\n\/*\r\nResult:\r\nPix Event  Api queue  BeginTimestamp  EndTimestamp  Level  ApiCommandQueueId  EventId  Color\r\n---------  ---------  --------------  ------------  -----  -----------------  -------  -----\r\nClear      Graphics   241511462       241518677     0      1                  2        0\r\n*\/\r\n\r\n\r\n\/*\r\nSubtracting the EndTimestamp from the BeginTimestamp allows us to calculate the duration of a specific execution.  On the CPU, sometimes\r\nwe 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\r\nvirtual function can be queried to calculate the time code within a specific PIX cpu execution was actually executing.\r\n\r\nAn example query:\r\n*\/\r\nselect s.Value as 'Pix event', times.*\r\nfrom Strings s, PixEventInfo pei, PixCpuExecutionTimes times\r\nwhere\r\n    times.BeginTimestamp >= (select Value from CaptureFacts where Id = 2)\r\n    and times.EndTimestamp <= (select Value from CaptureFacts where Id = 24)\r\n    and times.EventId = pei.Id\r\n    and pei.NameId = s.Id\r\norder by times.Execution desc\r\nlimit 5;\r\n\r\n\/*\r\nPix event   Duration  Execution  Stall     EventId  BeginTimestamp  EndTimestamp\r\n----------  --------  ---------  --------  -------  --------------  ------------\r\nFrame 2722  16683868  77058      16606810  216      3527275848      3543959716\r\nFrame 3005  16713218  71336      16641882  499      8247344274      8264057492\r\nFrame 2630  16699710  70163      16629547  124      1992790943      2009490653\r\nFrame 2905  16686363  64312      16622051  399      6579498021      6596184384\r\nFrame 2529  16734681  63259      16671422  23       308229107       324963788\r\n\r\nIn 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 \r\nthe time was spent stalled.\r\n*\/\r\n\r\n\/*\r\nCounters are stored in a similar fashion to PIX events.  In this case, two tables are used to store metadata about the counters and then\r\na 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\r\nMetrics view.\r\n*\/\r\n.width 6, 100\r\n\r\nwith recursive\r\ncounter_group(Id, ParentId, name) as\r\n(\r\n        select c.Id, c.GroupId, s.Value\r\n        from PixCounterInfo c, Strings s\r\n        where c.NameId = s.Id\r\n    union all\r\n        select cg.Id, pcg.ParentGroupId, s.Value\r\n        from PixCounterGroup pcg, counter_group cg, Strings s\r\n        where pcg.Id = cg.ParentId and pcg.NameId = s.Id\r\n)\r\n\r\nselect cg.Id, group_concat(cg.Name, ' -> ') as 'Counter tree'\r\nfrom counter_group cg group by cg.Id;\r\n\r\n\/*\r\nId      Counter tree\r\n------  ----------------------------------------------------------------------------------------------------\r\n1       Bytes remaining -> Title Writes Window\r\n2       Bytes written -> Title Writes Window\r\n3       CPU 0 -> % Busy -> Title CPU\r\n4       CPU 1 -> % Busy -> Title CPU\r\n5       CPU 2 -> % Busy -> Title CPU\r\n6       CPU 3 -> % Busy -> Title CPU\r\n7       CPU 4 -> % Busy -> Title CPU\r\n8       CPU 5 -> % Busy -> Title CPU\r\n9       CPU 6 -> % Busy -> Title CPU\r\n10      CPU 0-7 -> % Busy -> Title CPU\r\n11      CPU 8-13 -> % Busy -> Title CPU\r\n12      All -> Bytes Received -> Title Network\r\n13      Excluding Tools -> Bytes Received -> Title Network\r\n14      All -> Bytes Sent -> Title Network\r\n15      Excluding Tools -> Bytes Sent -> Title Network\r\n16      Packets Received -> Title Network\r\n17      Packets Sent -> Title Network\r\n18      All -> Bytes Received Per Second -> Title Network\r\n19      Excluding Tools -> Bytes Received Per Second -> Title Network\r\n20      All -> Bytes Sent Per Second -> Title Network\r\n21      Excluding Tools -> Bytes Sent Per Second -> Title Network\r\n22      Packets Received Per Second -> Title Network\r\n23      Packets Sent Per Second -> Title Network\r\n24      Load % -> Power Monitor\r\n25      Frame Duration -> D3D\r\n26      Swap Throttle -> D3D\r\n27      Stalled -> D3D\r\n28      % Frame Time Stalled -> D3D\r\n29      % Frame Time Swap Throttled -> D3D\r\n\r\nNote the .width 6, 100 which controls the output of the table so that the counter hierarchy doesn't wrap.\r\n\r\nThe 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\r\nnext and shouldn't be hardcoded.\r\n*\/\r\n\r\n\/*\r\nOnce the Id of the counter in question is known values for that counter can be directly queried from the counters virtual table.  For\r\nexample, the following query:\r\n*\/\r\nselect c.Value, c.timestamp\r\nfrom PixCounters c, Strings sc, Strings sl1, Strings sl2, PixCounterGroup pcg2, PixCounterGroup pcg1, PixCounterInfo ci\r\nwhere\r\n    sc.Value = 'CPU 1'\r\n    and sl1.Value = '% Busy'\r\n    and sl2.Value = 'Title CPU'\r\n    and ci.NameId = sc.Id\r\n    and pcg1.NameId = sl1.Id\r\n    and pcg2.NameId = sl2.Id\r\n    and c.CounterId = ci.Id\r\n    and ci.GroupId = pcg1.Id\r\n    and pcg1.ParentGroupId = pcg2.Id\r\n    and c.Timestamp >= (select Value from CaptureFacts where Id = 2)\r\n    and c.Timestamp <= (select Value from CaptureFacts where Id = 24)\r\nlimit 10;\r\n\r\n\/*\r\nValue             Timestamp\r\n----------------  ----------\r\n4.44190120697021  718600109\r\n4.42664241790771  819775837\r\n4.42969417572021  920941689\r\n4.41443490982056  1022195683\r\n4.42206478118896  1123288959\r\n4.45563459396362  1224455335\r\n4.40070199966431  1325650309\r\n4.42816829681396  1426796804\r\n4.42206478118896  1527965935\r\n4.41443490982056  1629137932\r\n*\/\r\n\r\n\/*\r\nIf you would like all the CPU counters the filter on the counter's name could be removed from the query above\r\n*\/\r\nselect c.Value, c.timestamp, sc.Value as 'Counter name'\r\nfrom PixCounters c, Strings sc, Strings sl1, Strings sl2, PixCounterGroup pcg2, PixCounterGroup pcg1, PixCounterInfo ci\r\nwhere\r\n    sl1.Value = '% Busy'\r\n    and sl2.Value = 'Title CPU'\r\n    and ci.NameId = sc.Id\r\n    and pcg1.NameId = sl1.Id\r\n    and pcg2.NameId = sl2.Id\r\n    and c.CounterId = ci.Id\r\n    and ci.GroupId = pcg1.Id\r\n    and pcg1.ParentGroupId = pcg2.Id\r\n    and c.Timestamp >= (select Value from CaptureFacts where Id = 2)\r\n    and c.Timestamp <= (select Value from CaptureFacts where Id = 24)\r\norder by c.Timestamp asc\r\nlimit 10;\r\n\r\n\/*\r\nValue             Timestamp  Counter name\r\n----------------  ---------  ------------\r\n0.0               718600109  CPU 0\r\n4.44190120697021  718600109  CPU 1\r\n0.0               718600109  CPU 2\r\n4.70740842819214  718600109  CPU 3\r\n0.0               718600109  CPU 4\r\n4.59906911849976  718600109  CPU 5\r\n0.0               718600109  CPU 6\r\n1.71854734420776  718600109  CPU 0-7\r\n0.0               718600109  CPU 8-13\r\n0.0               819775837  CPU 0\r\n*\/<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
The Timing Capture file format is a Sqlite database. \u00a0Queries 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 […]<\/p>\n","protected":false},"author":1915,"featured_media":4769,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-6530","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-pix"],"acf":[],"blog_post_summary":"
The Timing Capture file format is a Sqlite database. \u00a0Queries 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/posts\/6530","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/users\/1915"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/comments?post=6530"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/posts\/6530\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/media\/4769"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/media?parent=6530"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/categories?post=6530"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/pix\/wp-json\/wp\/v2\/tags?post=6530"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}