Query Trace Patterns: missing columns, no drill-down #273
Description
Current State
The Query Trace Patterns tab shows a flat grid with 9 columns:
- Database, Executions, Avg Duration (sec), Max Duration (sec), Avg CPU (sec), Avg Reads (pages), Concern, Last Execution, Query Pattern
No drill-down exists — clicking a row does nothing.
Missing Columns
Already in model but not displayed
- AvgWrites — queried from SQL, loaded into
LongRunningQueryPatternItem.AvgWrites, but no XAML column - Recommendation — same (loaded but hidden)
- SampleQueryText — same (loaded but hidden); currently Query Pattern is
LEFT(sql_text, 200)truncated
Available in collect.trace_analysis but not queried
| Column | Value |
|---|---|
event_name |
SQL:BatchCompleted vs RPC:Completed — distinguishes ad-hoc from stored proc calls |
login_name |
Who ran it |
application_name |
What app ran it |
host_name |
Where it came from |
row_counts |
Avg rows returned |
start_time |
First execution time |
object_id |
Could link to stored procedures |
Drill-Down
Add a drill-down window (like Query Stats and Procedure Stats have) that shows individual executions of a pattern over time. The data exists in collect.trace_analysis — the main view groups by LEFT(sql_text, 200) pattern, so drilling down would show the individual rows matching that pattern with:
- Collection time, start/end time, duration, CPU, reads, writes, rows, login, application, host, full SQL text
- Chart showing execution timing over the selected time window
Data Available
- ~19K rows in
collect.trace_analysisgoing back 20 days - 2 event types (RPC:Completed, SQL:BatchCompleted)
- 7 databases, 11 distinct applications
- 22 columns in the table