When we’re fine-tuning queries individually, it’s easy to observe and even measure accurately the improvements that specific changes have in specific executions. Nevertheless, when changes are deployed to production, they are put to test against a much more complex environment, where all sorts of crazy things happen concurrently and users on a personal quest to destroy your work will try to make unexpected things happen. Still, even if things go according to plan, it’s important to translate those improvements into numbers, for a couple of reasons.

Query Store is not the only tool in the toolbox, but if you choose to establish a baseline using, let’s say Extended Events, you need to set up the respective session and, obviously, the baseline will be restricted to the extent of that respective session. If data is collected during a small period of time, there’s a risk to catch an abnormal pattern, hence distorting the results. On the other hand, long running sessions must be even more carefully configured not to introduce undesired overhead. Extended Event sessions are just as light-weight as the data we’re collecting. I won’t even mention Trace/Profiler, but I heard that there is a special place in hell for anyone still using it.  

I Know What You Queried Last Summer

Don’t get me wrong, Extended Events are awesome, but specifically for baselining, I personally find Query Store to be more practical. If the queries executed in the database are compatible with the healthy lifestyle of Query Store just go for it (e.g. ad-hoc queries are like trans fats that can quickly force your Query Store to appear on a “My 600 lb. Life” episode). After the initial setup where a couple of configurations regarding what’s captured and for how long are defined, you will never have to worry about setting up sessions, or managing the underlying data again. You’ll have relevant historical data always at your disposal that goes as far as the chosen settings allow.

Sauce

The query bellow will show relevant execution stats for a particular procedure, during the chosen time interval:

DECLARE @StartDate	DATETIME = ''
DECLARE @EndDate	DATETIME = ''
DECLARE @ObjName	VARCHAR(100) = ''

SELECT 
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2) avg_duration,
    ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2) avg_cpu_time,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*8,2) avg_logical_io_reads,
    ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*8,2) avg_logical_io_writes,
    ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*8,2) avg_physical_io_reads,
	SUM(rs.count_executions) count_executions,
    ROUND(CONVERT(float, SUM(rs.avg_clr_time*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2) avg_clr_time,
    ROUND(CONVERT(float, SUM(rs.avg_dop*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*1,0) avg_dop,
    ROUND(CONVERT(float, SUM(rs.avg_query_max_used_memory*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*8,2) avg_query_max_used_memory,
    ROUND(CONVERT(float, SUM(rs.avg_rowcount*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*1,0) avg_rowcount,
    COUNT(distinct p.plan_id) num_plans
FROM 
	sys.query_store_runtime_stats rs (NOLOCK)
JOIN 
	sys.query_store_plan p ON p.plan_id = rs.plan_id 
JOIN 
	sys.query_store_query q ON q.query_id = p.query_id
JOIN 
	sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id 
JOIN 
	sys.query_store_runtime_stats_interval i ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE 
	i.start_time BETWEEN @StartDate AND @EndDate AND
	OBJECT_NAME(q.object_id) = @ObjName
GROUP BY 
	p.query_id, qt.query_sql_text, q.object_id
HAVING 
	COUNT(distinct p.plan_id) >= 1
order by 
	AVG_CPU_TIME DESC

Simply run it for both desired time intervals and feel the joy of baselining query performance without breaking a sweat.