Mês: Novembro 2020

Not All Seeks Are Created Equal #1

Seeks seeks seeks… The number of the beast. Seeks are cool though. I like seeks. Sometimes, at least. Perhaps some seeks are cooler than others. I just said seeks six times, now we’re ready to go.

Let’s begin by creating a temporary table that holds values from 1 to 10 000.

CREATE TABLE #T(id integer PRIMARY KEY CLUSTERED);

WITH n(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < 10000)

INSERT INTO #T(id) 
SELECT n FROM n ORDER BY n
OPTION (MAXRECURSION 10000);

Now, let’s assume that we need to retrieve a specific range of values – e.g. between 500 and 563. There are a couple of ways to do that, that’s not exactly rocket science, but let’s consider the following two approaches:

Using the BETWEEN operator:

SELECT 
	Id 
FROM 
	#X
WHERE 
	Id BETWEEN 500 AND 563

Using the IN operator:

SELECT 
	Id 
FROM 
	#X
WHERE 
	Id in
	(
		500,501,502,503,504,505,506,507,508,509,510,511,512,
		513,514,515,516,517,518,519,520,521,522,523,524,525,
		526,527,528,529,530,531,532,533,534,535,536,537,538,
		539,540,541,542,543,544,545,546,547,548,549,550,551,
		552,553,554,555,556,557,558,559,560,561,562,563
	)

Uh, truly fascinating.

But, behind these boring and functional equivalent queries, there is a dirty seekret – Wow, I’m on fire today. Both result in a Clustered Index Seek, but one is sus. Discuss!

That’s quite a difference. The second query results in 64 times more logical reads. But why?

One Seek To Rule Them All

In the execution plan of the query that uses the BETWEEN operator, it’s possible to notice that there’s only one seek operation, which range starts at 500 and then traverses the leaf level of the index, until it finds a row beyond the range limit – 563 in this case.

The Meeseeks Box

The seek #2, in the execution plan of the query that uses the IN operator is rather different:

The list continues down to a total of 64 individual seeks.

The underlying reason for the 128 logical reads is related to the size of our table and consequently, the size of the index. Since it is large enough to need a separate root page, each one of the 64 seeks will imply 2 logical reads.

But I digress, the bottom line here is that Seek #2 is the impostor. It is not actually a seek, but a whole bunch of them.

Query Performance Baselining With Query Store

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.