Query Trace Patterns: missing columns, no drill-down

[erikdarlingdata]

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_analysis going back 20 days
• 2 event types (RPC:Completed, SQL:BatchCompleted)
• 7 databases, 11 distinct applications
• 22 columns in the table