byobi
commented
8 years ago one option is to have a query table (containing the query text, query hash, integer surrogate key -- 1 row per unique query) and a QueryExecution table (containing the integer SK, start/end/duration/etc -- 1 row for each execution of a query)...
When processing a new xEvent query trace, the hash (on the TextData field) would then be used to check if this query had been run before... if not, then a new record would be inserted into the Query table before inserting into the QueryExecution table...
When analyzing query data, the integer SK (from the QueryExecution table) could be used to efficiently lookup the actual text (from the Query table) when investigating a specific query.
below is an example from a solution I've used in the past...
Query-table
QueryExecution-table
marcosqlbi
I created a column with a hash string from the Text column of the event. The rationale is that storing the query text in a Tabular model (or Power Pivot / Power BI) for the Analysis could be very expensive. However, in order to make this column useful, we should create a storage for the query in order to have one row for each query only. The hash should be used as an access key to retrieve the original query from the table. However, I don't have a clear idea about how to implement such a link in a productive way.