Inside Microsoft's Telemetry Benchmark

Where the benchmark comes from

Microsoft built the benchmark for its analytics service Azure Data Explorer (ADX), also known as Kusto, and published it on GitHub. The repository grew in steps: the data generator arrived in February 2021, the KQL queries in December 2021, and GigaOm's test code in July 2022, shortly before GigaOm's report of 15 August 2022 and Microsoft's announcement a day later. Microsoft archived the repository on 15 June 2026: it is read-only now, but still available.

The benchmark simulates the logs of a cloud service that ingests files from many sources: ingestion commands, file downloads, completion events with row counts and durations, warnings, errors and the occasional crash. It consists of two parts:

• A data generator, a C# command-line tool. It writes the logs as gzip-compressed CSV files without a header row, to a local disk or to Azure Blob Storage, in four sizes: 1 GB, 10 GB, 1 TB and 100 TB. In all sizes, the first events are dated 8 March 2014.
• 19 queries that answer typical diagnostic and monitoring questions, written in KQL. The repository's GigaOm directory holds these queries together with GigaOm's translations to BigQuery SQL and Snowflake SQL, table definitions and loading instructions for the three systems, and the Python driver GigaOm used for its measurements.

Two sets of KQL queries. The repository's top-level queries directory holds a second, older set of KQL queries, plus translations to Spark SQL. The two sets differ in four queries: Q03 searches for a different string, Q10 lists its grouping columns in a different order, Q16 is a different query altogether, and the older Q17 is a copy of Q15 with a shorter time window. GigaOm's measurements use the set in the GigaOm directory (although the report's summary table still describes the older Q16). All query descriptions in this article refer to that set.

GigaOm's 2022 study in brief

GigaOm's Log and Telemetry Analytics Performance Benchmark (v1.01, 15 August 2022, by William McKnight and Jake Dolezal) is the study the benchmark was published with. Microsoft sponsored it and, as the report states, chose the competitors, the test and the ADX configuration. GigaOm chose the Snowflake configuration closest to the ADX cluster in CPU and memory, and ran BigQuery with reserved slots. All three systems held the 100 TB data set.

ADX was the fastest system in all 19 queries with one user and in 18 of 19 with 50 concurrent users. With 50 users, BigQuery and Snowflake each ran into the two-minute timeout in eight queries. From costs and run times, GigaOm derives a 97% price-performance advantage for ADX: a run rate of $0.48 per hour, against $17.71 for BigQuery and $16.51 for Snowflake. Elasticsearch and AWS OpenSearch were attempted but not tested. With 80 Logstash instances, Elastic Cloud indexed 500 GB per hour, so loading the 100 TB would have taken about eight days; the data would have grown instead of being compressed; and hot storage couldn't be scaled independently of compute. GigaOm puts the test costs at well over $30,000 for Elastic Cloud and nearly $10,000 for OpenSearch.

GigaOm itself writes: "We leave the test's fairness for the reader to determine." Read next to the published code, the report leaves several questions open:

• Repetitions. The text says each query ran 20 times in a row, with medians and 95th percentiles reported. The result tables show averages instead: over three consecutive runs with one user, over a single run with 50 users.
• Timeouts. Queries that ran longer than two minutes were cancelled and counted as 120 seconds. With 50 users, this affects eight of 19 queries on BigQuery and on Snowflake, so their sums are lower bounds.
• What was timed. The report doesn't say whether it shows client-side or server-side times. The published driver records both. For ADX and BigQuery, the server-side value is the execution time the service reports; for Snowflake, both values are the time measured on the client, including fetching the result.
• Caches. The driver turns BigQuery's query cache off; it sets nothing for Snowflake or ADX, and the report doesn't mention caches.
• Query semantics. The translations deviate from the KQL originals in several queries; the section on correct ports below gives examples.

The study's core metric, the sum of the average execution times of all 19 queries, is simple and useful, as long as a measurement defines each of these points.

The data and how it is built

All data lives in a single table, Logs, with one row per log event:

The generator's source code explains most properties of the data:

• Sources. Each source is one generator thread; how many there are depends on the size of the data set. A source's name is a word from a fixed list plus a number, such as IMAGINEFIRST0 or CLIMBSTEADY83. Every source starts at midnight on 8 March 2014 and opens a new ingestion session every 0 to 63 seconds until it has used up its quota of sessions.
• Sessions. Each session has its own ClientRequestId, a GUID. It ingests 5 to 104 files of one format (csv, json, parquet or avro) into one of 607 tables. For each file, it writes a download event, a series of background traces, and an IngestionCompletionEvent, or an exception if the session fails. A final report closes the session. These ingestion events come from four components of their own: INGESTOR_GATEWAY, DOWNLOADER, INGESTOR_EXECUTER and FINALIZER.
• Background traces make up the vast majority of rows: about 50 to 5,000 per session, spread over 1,000 nodes (Engine000000000000 to Engine000000000999), 128 components and seven message templates. About one in ten has level Warning, the rest Information.
• Failures. About 2.5% of the sessions are marked as failed and about 0.26% as critical. Such a session writes an exception with one of 104 exception types in place of each completion event (this is where all Error rows come from), and a critical session adds one Critical alert at the end.
• Properties. Only three kinds of events carry JSON. An ingestion command lists the session's file URLs as a JSON array. A download event holds compressedSize, OriginalSize and downloadDuration. A completion event holds size, format, rowCount, cpuTime and duration. Sizes and row counts are integers; durations are text in .NET's format [d.]hh:mm:ss.fffffff, for example 1.02:03:04.5678901 for one day, two hours and a bit more. All other events leave Properties empty.
• Timestamps advance in steps of 100 nanoseconds and are written with seven fractional digits, the resolution of ADX's datetime.
• Error messages span several lines: an exception header with Exception=…;, HResult=…; and Message=exception happened; on separate lines, followed by a stack trace.

Data properties in different data sets

The generator doesn't create a larger data set by logging the same system for a longer time. It adds sources, and it gives each source more sessions. The two largest sizes, 1 TB and 100 TB, therefore differ in more than volume:

The 1 TB data set is therefore not a 1% sample of the 100 TB data set. It has a tenth of the sources, and each runs a tenth of the sessions: the result is a tenth of the hourly volume over a tenth of the time span. Nodes, components, levels, tables and message templates are the same in both sizes.

Within a data set, the rows are not spread evenly over time. All sources start at the same moment but run different numbers of sessions, so the volume falls from day to day as more and more sources finish. Loaded into a database, the 1 TB data set holds 4,091,441,279 rows from 2014-03-08 00:00 to 2014-03-16 12:40 UTC, in 54,288 gzip-compressed CSV files.

In the 100 TB data set, each source runs ten times as many sessions at the same pace, so the same decline stretches over about three months.

The 19 queries

The queries fall into a few families: text search in messages, point lookups, aggregations over time windows, extraction of values from message text and JSON properties, and joins. 17 of the 19 queries look at a time window between one hour and ten days; only Q02 and Q05 search the whole data set.

All queries are static. Unlike the TPC benchmarks, they have no parameters, so every run sends exactly the same query text. A system with a result cache can therefore answer every repetition from the cache without touching the data. Every measurement with this benchmark has to state how caches were set.

What the queries see

All query time windows start on 8 March 2014, where the data is densest. In the 1 TB data set, a three-day window covers 53% to 54% of all rows, and Q06's ten-day window covers all of them. In the 100 TB data set, the same windows cover a much smaller share of the data. The mix of levels doesn't depend on the size: in a typical six-hour window, 90.3% of the rows have level Information, 9.7% Warning, 0.05% Error and only a handful Critical. Filters on errors are therefore very selective, while filters on informational events hardly filter at all.

The source filters behave differently in the two sizes. In the 1 TB data set, Q03's prefix im matches a single source, IMAGINEFIRST0, and of the four sources that Q06 names, only IMAGINEFIRST0 exists. The other three would be generated only for 100 TB, and not even there with the generator's current list of names: their numbers point two places past the positions of their names in the list. The 100 TB data GigaOm used was therefore presumably generated with a slightly different list. All twelve sources that Q13 names exist in both sizes.

From KQL to SQL

The benchmark was originally defined for ADX with queries specified in KQL. This makes the KQL queries the golden copy of the benchmark and therefore the specification for other systems the benchmark should be ported to. Hence, porting the benchmark means mapping each query including its KQL operators to an expression in another language such as SQL with the same meaning. The following table presents frequently used KQL operators and suggestions for an SQL port.

Some examples, in standard SQL and, where SQL lacks a function, in ClickHouse SQL:

• Time windows. Timestamp between (datetime(2014-03-08) .. 12h) becomes Timestamp BETWEEN TIMESTAMP '2014-03-08 00:00:00' AND TIMESTAMP '2014-03-08 12:00:00'. Both ends are inclusive, so the precision of the stored timestamps matters: a table that keeps only milliseconds truncates events from just after the end of the window onto it. On the 1 TB data set stored that way, Q01 counts one row too many, Q08 eight, and Q09 one in each of four groups. Keep all seven fractional digits (in ClickHouse: DateTime64(7)).
• Term search. Message has 'enabled' ignores case and matches whole terms only; KQL defines a term as a maximal sequence of alphanumeric characters. A regular expression with word boundaries comes close, for example match(Message, '(?i)\\benabled\\b') in ClickHouse. One subtlety remains: \b treats _ as part of a word, and the KQL documentation doesn't say whether _ separates terms. ClickHouse's token functions, such as hasToken(lowerUTF8(Message), 'enabled'), are an alternative that can use a token index.
• Prefixes and substrings. Source startswith 'im' becomes LOWER(Source) LIKE 'im%', and Message contains 'PONSE' becomes LOWER(Message) LIKE '%ponse%'. ClickHouse has dedicated functions for both: startsWithCaseInsensitiveUTF8 and positionCaseInsensitiveUTF8.
• Top N. top 1000 by Timestamp returns the 1,000 most recent rows: KQL's top sorts in descending order unless told otherwise, with empty values last. The port is ORDER BY Timestamp DESC NULLS LAST LIMIT 1000, not ORDER BY Timestamp LIMIT 1000.
• Aggregations and subqueries. summarize Count=count() by Level becomes SELECT Level, count(*) AS Count … GROUP BY Level, and let top_nodes = …; becomes a common table expression, WITH top_nodes AS (…).
• Durations. totimespan(Properties.duration) parses text such as 1.02:03:04.5678901. The seven fractional digits count 100-nanosecond ticks; reading them as microseconds makes a duration up to ten seconds too long.
• Estimates. For Q12, an exact count is fine: at its default accuracy, dcount counts exactly up to 1,000 distinct values, and Q12 finds about 370 to 410 requests per hour in the 1 TB data set. For Q13, a port should compute exact nearest-rank percentiles (for example with percentile_disc), not interpolated ones (percentile_cont) or estimates from a random sample (ClickHouse's quantiles).

Pattern extraction needs the most care. Q07 extracts the exception type and message with parse:

| parse Message with
  'Exception=' ExceptionType ';' *
  'Message=' ExceptionMessage ';' *

A regular expression has to reproduce three properties of this pattern: it is anchored at the start of the message, each captured value ends at the next literal, and * crosses line breaks. The last point is not academic: in Q07's time window in the 1 TB data set, all 16,991 matching messages have Message= on a later line than Exception=. In ClickHouse, extractGroups(Message, …) with the following expression returns both values; the flag (?s) lets . match line breaks:

(?s)^Exception=(.*?);.*?Message=(.*?);

The KQL documentation doesn't spell out these details of parse; they follow from its examples.

What makes a port correct

The KQL queries of the GigaOm directory define what each query is supposed to return. A port to another system is correct if it meets two conditions:

1. It returns the same rows as its KQL original on the data set.
2. It means the same. Equal results are necessary, but not sufficient. Q04 searches all columns for the term “Internal” (* has 'Internal'). In the generated data, only Message contains it, so a query that searches Message alone returns the same rows. But it is a different query, one that would miss matches in other data, and it violates the spirit of the benchmark.

Two kinds of differences are allowed, because KQL leaves them open itself. Where a top-N cut-off falls into a tie, any of the tied rows is a valid answer: in the 1 TB data set, Q10's top 10 ends in a tie (four groups with a count of 3, many with a count of 2), so only the rows above the tie can be compared. And where KQL returns estimates, an exact result may differ slightly.

Measured against these rules, GigaOm's translations deviate from the KQL originals in several places. Some examples from the published code:

• The Snowflake port searches Q01's and Q02's terms as substrings (regexp_like(Message, '.*enabled.*', 'is')), not as whole terms.
• Snowflake's Q10 window ends twelve hours late: it covers three and a half days instead of three.
• Both ports sort Q04 in ascending order and thus ask for the oldest rather than the most recent events.
• Both ports extract Q07's values with greedy, unanchored expressions (Exception=(.*);).
• The Snowflake port interpolates Q13's percentiles (percentile_cont).
• Both ports pick Q15's top nodes as the ten combinations of node and CPU time with the highest CPU time. One node can occur several times, so fewer than ten distinct nodes may be chosen.
• Both ports of Q16 join back on the timestamps of the first and last error. This can duplicate rows on ties and take the component from a non-error event with the same timestamp.

Altogether, the Snowflake translation deviates from the KQL originals in 14 of the 19 queries. On the 1 TB data set, none of these deviations changes the returned rows, but on other data, some could.

Now in benchANT's tooling

We have integrated Microsoft's telemetry benchmark into benchANT's benchmarking tooling. The 19 queries run as a custom workload in our YCSB harness: YCSB executes the queries, takes the measurements and controls the client threads, and bindings connect it to the database under test. Adding a system requires a binding, a port of the queries and a load of the data. For loading data, we use the benchmark's data generator.

We plan to have bindings ready for Snowflake, ClickHouse and Google BigQuery with improved query mappings. These will be presented in separate blog articles.

Do you run log or telemetry analytics on one of these systems, or on another one? Talk to us: we can measure how your system and configuration handle this workload, and tune them.