You’re a database developer or DBA with at least 3 years experience performance tuning queries and indexes.
You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level settings. You’ll also see firsthand how SQL Server 2017, 2019, and 2022 try to reduce it with adaptive joins, adaptive memory grants, automatic tuning, and caching multiple plans.
Join me at SQLBits on Tuesday, 8 March, at ExCel in London for a one-day workshop. I’ll be there in person – my flights are already booked, and I’m excited to see everybody in person again. The conference is taking great precautions: masks are required at the event, tables will be spaced apart, and proof of double vaccination, Covid-19 antibodies, or a negative lateral flow test (within the last 48-hours) will be required.
This course is 100% demos: the only slides are the introduction and recap. The rest of the time, I’ll be working live in SQL Server Management Studio and SQL Server 2022 (assuming the preview bits are ready.) You can even follow along in your laptop if you bring SQL Server 2017 or newer and the 50GB Stack Overflow 2013 database.
I’m taking the best modules of my 3-day Mastering Parameter Sniffing class and doing it as a training day session. We’re going to be moving quickly and covering a lot of ground, and I know it can be tough to keep up. That’s why attendees will also get one year’s access to the full 3-day Mastering Parameter Sniffing recordings, too! That’s a £695 value, and it’s free with your workshop admission. (You’ll get the access in class.)
My SQLBits workshops always sell out, and you’ll wanna move fast to save your seat. Talk to the finance folks now to get the credit card primed up and ready so that when registration opens – it’ll open by the end of this month – you’ll be able to grab your seat quickly.
See you in London!
2 Comments. Leave new
Hi Brent!
Thanks to your free classes and videos I’ve found interesting case in our DB.
TLDR: even unused CTE’s can really hurt compile time and memory usage.
Here is little repro with maximized effect and minimized dependencies.
Query:
——————————-
;with
tt0 as (select 1 i)
,tt1 as (select 1 i from tt0 a join tt0 b on a.i=b.i join tt0 c on b.i=c.i)
,tt2 as (select 1 i from tt1 a join tt1 b on a.i=b.i join tt1 c on b.i=c.i)
,tt3 as (select 1 i from tt2 a join tt2 b on a.i=b.i join tt2 c on b.i=c.i)
,tt4 as (select 1 i from tt3 a join tt3 b on a.i=b.i join tt3 c on b.i=c.i)
,tt5 as (select 1 i from tt4 a join tt4 b on a.i=b.i join tt4 c on b.i=c.i)
,tt6 as (select 1 i from tt5 a join tt5 b on a.i=b.i join tt5 c on b.i=c.i)
,tt7 as (select 1 i from tt6 a join tt6 b on a.i=b.i join tt6 c on b.i=c.i)
,tt8 as (select 1 i from tt7 a join tt7 b on a.i=b.i join tt7 c on b.i=c.i)
,tt9 as (select 1 i from tt8 a join tt8 b on a.i=b.i join tt8 c on b.i=c.i)
,tt10 as (select 1 i from tt9 a join tt9 b on a.i=b.i join tt9 c on b.i=c.i)
,tt11 as (select 1 i from tt10 a join tt10 b on a.i=b.i join tt10 c on b.i=c.i)
,tt12 as (select 1 i from tt11 a join tt11 b on a.i=b.i join tt11 c on b.i=c.i)
,tt13 as (select 1 i from tt12 a join tt12 b on a.i=b.i join tt12 c on b.i=c.i)
,tt14 as (select 1 i from tt13 a join tt13 b on a.i=b.i join tt13 c on b.i=c.i)
,tt15 as (select 1 i from tt14 a join tt14 b on a.i=b.i join tt14 c on b.i=c.i)
,tt16 as (select 1 i from tt15 a join tt15 b on a.i=b.i join tt15 c on b.i=c.i)
,tt17 as (select 1 i from tt16 a join tt16 b on a.i=b.i join tt16 c on b.i=c.i)
,tt18 as (select 1 i from tt17 a join tt17 b on a.i=b.i join tt17 c on b.i=c.i)
,tt19 as (select 1 i from tt18 a join tt18 b on a.i=b.i join tt18 c on b.i=c.i)
select * from tt0; — I select only from tt0, which is “select 1 i”! Other tt’s are totally unused
——————————-
My dev’s instance can’t compile this query, sure yours won’t too.
If lines with “tt10″…”tt19” are commented, query compiles (111 MB of memory, 76 ms CPU for compilation) and executes instantly.
Each next line **triples** compilation memory and time. But ALL of tt’s (except tt0) are totally unused!
Server version – 15.0.4053.23, compatibility level 150
Cool! That’s related to a demo I do in the Fundamentals of Query Tuning class, too.