I get a much better picture of the number of times that the recompiled queries have run, AND the number of rows they’ve returned each time. I’m going to turn on Query Store in the Stack Overflow database, and then run the two queries again: How a recompiled query shows up in Query Store
The plan cache just isn’t a good fit for tracking queries with RECOMPILE hints – but Query Store is. The problem is that they’re just not true whenever there’s a recompile hint: the totals are underreported, and the avg/min/max just reflect the last execution of any query with a recompile hint. In sp_BlitzCache, we add up the totals for each statement in a proc, and we present those as the total numbers for the proc. (That’s fine, and that part I was also kinda aware of.)īut the part that I keep forgetting is that when I’m looking at the stored procedure’s totals in sp_BlitzCache, the total, min, and max values are useless: You’re not just recompiling the execution plan, but you’re also not getting query plan metrics here. However, the second query – the one with the recompile hint – has a brand new plan in the cache, but also new metrics. Its row metrics are correct through the life of the stored procedure’s time in cache. The first query’s plan stuck around in memory, so it now shows 2 executions, and 2 total rows returned. If I execute the stored procedure twice – for two different parameters – and then check metrics, things look different: It might seem odd to use rows as a measurement right now, but sys.dm_exec_query_stats’s columns – CPU, reads, writes, duration, etc – all behave the same way as I’m about to show you here, and the rows numbers are more repeatable than some of the others, so let’s use rows. So right now, if I’m inclined to do math, I can add up the total number of rows for both statements and I can see the total number of rows returned by the query. Line 2 is the second query, and it returns 9,149 rows for reputation = 2. It’s a COUNT(*), and it only returns 1 row. Line 1 is the first query in the stored procedure. If I check sys.dm_exec_query_stats right now, there are a few columns with interesting results – I’m going to move them around a little so you can see ’em: How a recompiled query shows up in the plan cache When I run it for = 2, the second query returns 9,149 rows.
0 kommentar(er)
