Hugo Kornelis
I make SQL Server fast (.com)
Zeewolde, The Netherlands
Actions
Hugo Kornelis is an established SQL Server community expert who spends a lot of time at various conferences. He is also a blogger, technical editor of a variety of books, and Pluralsight author. He was awarded SQL Server MVP and Data Platform MVP 17 times (2006 - 2016 / 2019 - now).
When not working for the community, he is busy at his day job: freelance database developer/consultant.
Hugo has over 25 years of SQL Server experience in various roles. Starting from a strong database design background, he has spent the last ten years specializing in execution plans and query performance tuning.
Links
Awards
Area of Expertise
Topics
Execution plans explained
You probably have some tricks up your sleeve for dealing with slow queries. Index the columns in the join and where. Rewrite the WHERE clause to enable index usage. Tinker with the join order, or perhaps even break up the query in smaller parts. Those tricks work. Sometimes. Not always. And when they don't, your job suddenly gets frustrating!
Sometimes, you wish you knew WHY a query is slow. So that you can target your changes exactly right, at precisely the root cause of the slowness. And the good news is, there already exists a way to find the root cause of bad performance. You "only" need to learn to work with execution plans
In this full-day workshop, you will learn everything you need. You will learn what execution plans are, and where you can find them. You will learn the basics of how to read execution plans. And you will learn all you need about all of the commonly encountered operators in execution plans: what their function is, how they operate, and what effect that has on performance.
In short: After attending this workshop, you will know how to obtain an execution plan for a slow running query, and you will know how to look at that plan and find the spot where it hurts, so that you know what to do to fix the performance issue.
Regardless of whether you have never seen an execution plan before, or whether you already have experience working with execution plans, this workshop will teach you how to look at an execution plan and then KNOW why your query is slow ... and what you can do to fix that!
Query processing in SQL Server 2022 - Inteligent++ ?
Microsoft has done a lot of work since SQL Server 2016 to make performance tuning easier. Query Store, Adaptive Query Processing, Intelligent Query Processing, and Automatic Tuning have been released in the past years. And now "NextGen Intelligent Query Processing" has been announced for SQL Server 2022. But what does this mean? What can you expect? And most important question: will ALL queries now always finish fast, or are your tuning skills still relevant?
Here’s the execution plan … now what?
You have learned the relevance of execution plans. You know where to find them, and you’ve been taught the basics of how to read them. You’ve looked at some of the clean, simple execution plans that presenters used in classroom training, or at conferences, and you feel confident that you can work with them. And then you get your first problem query at your workplace, you look at its execution plan, and you just want to crawl under a rock and cry.
Real code is much more complicated than demo code. Real code translates to large, complex, and often messy execution plans. The principles of reading execution plans still apply, but the plan is large and messy and you struggle where to even begin.
If your query uses a lot of I/O, then which operators are to blame? If your query uses a lot of memory, then what area is responsible? What are some things you should always look at?
Knowing the root cause of a problem can help find a cure. Knowing where to look in a large execution plan can help you find that root cause faster!
Five stages of grief - internals of a hash spill
You know the Hash Match operator. You know it requires a lot of memory. And you also know that it sometimes needs more memory than it has, in which case it spills to tempdb and is slow.
But do you know EXACTLY what happens under the cover in the case of a hash spill? Or does you knowledge stop at "it gets slow"?
Unless you work for Microsoft on the SQL Server engineering team, there really is no need to know the internals of a hash spill. But that doesn't mean you can't be curious! Would you like to waste some time to learn about dynamic role reversal, grace hash join, bail-out, bit-vector filtering, and more, then join this sessions!
From adaptive to intelligent: query processing in SQL 2019
As announced in September 2018, SQL Server 2019 expands the "adaptive query processing" features of SQL 2017 and relabels them as "intelligent query processing". This name now covers many features, such as batch mode on rowstore, memory grant feedback, interleaved execution, adaptive joins, deferred compilation, and approximate query processing.
In this high-paced session, we will look at all these features and cover some use cases where they might help - or hurt! - you.
Debugging without debugger: investigating SQL Server's internal structures
Have you ever wanted to know exactly how SQL Server stores data for temporary structures, such as for instance the spooled data in a Table Spool or Index Spool operator? No? I don't blame you. It's a bit like wanting to know who wattered the rubber tree that produced the rubber your tires are made off before you get in the car to drive to work. You really don't need it.
But you might still WANT to know. I did. And I figured it out.
Without ever touching the debugger (I'm a simple soul, that stuff is much too complex for me!), I figured out how I could look at the exact storage structures SQL Server creates and uses to store data in Table Spool and Index Spool operators.
If you are willing to commit to a fast-paced high-level internals session that is guaranteed to teach you exactly zero actually useful information, then this is the session for you.
Approximate functions: How do they work?
Sometimes, a close approximation is good enough. And sometimes, a close approximation is a lot faster. Microsoft has introduced “Approximate Query Processing” (the APPROX_COUNT_DISTINCT and APPROX_PERCENTILE functions) to give you exactly that benefit when you don't need exact answers.
But do you have a good response when you propose to use this function and your manager asks you to explain how they work first? Or is your only option to claim "black magic by smart Microsoft engineers"?
The algorithms used are not a secret. HyperLogLog and KLL Sketch. And now you most likely know exactly as much as you already knew before. And when you google for those terms ... you end up with a headache.
Time to join me for a session where I explain the black magic in the simplest possible terms, so that you can then explain it to your manager!
Execution Plans in Depth
For troubleshooting slow queries, looking at the execution plan is a good starting point. But sometimes, just looking at the plan does not help. Sometimes you need to dig deeper.
In this full-day pre-con session, you will learn everything you need to be take your understanding of execution plans to the next level. For almost every operator you can encounter in an execution plan, we will look at the inner workings, and look at properties that can affect their performance. We will also look at how operators in a plan interact with and affect each other.
Aside from explaining all of the common operators, we will also touch on several operators that were introduced or modified in the newer versions of SQL Server.
In short: After this pre-con, you will be better prepared to look at execution plans, find the spot where it hurts, and then rewrite your query to get a faster execution plan.
If you have seen some execution plans but feel you need to bring your understanding to the next level, then this pre-con is for you.
Now Where Did THAT Estimate Come From?
The SQL Server Query Optimizer makes its plan choices based on estimated rowcounts. If those estimates are wrong, the optimizer will very likely produce a poor plan. And there's nothing you can do about it. Or is there?
In this session, you will learn exactly where these estimates come from. You will gain intimate knowledge of how statistics are built and maintained and how they are used to estimate row counts. But you will also learn how filters and joins influence those estimates.
Though the focus of this session is on understanding the cause of bad estimates, you will also learn ways to fix the problems and get better estimates - and hence, better performing queries.
Improve your Database Performance in Seven Simple Steps
You wrote the code, you tested it, it works, and it’s fast. So you deploy. And then those pesky users insist on entering not hundreds, not thousands, but millions of rows – and suddenly, you have performance problems.
What to do? Blaming SQL Server is a good start, but won’t solve the problems. You can of course hire a database consultant to make all your performance problems (and all your money) disappear - but why not first take a look yourself?
This session will show you seven simple things, that might alleviate most of your database related performance problems. Use these tricks at your workplace, and you can be the hero of the department!
Hash Match, the Operator
SQL Server has a lot of different execution plan operators. By far the most interesting, and the most versatile, has to be the Hash Match operator.
Hash Match is the only operator that can have either one or two inputs. It is the only operator that can either block, stream, or block partially. And it is one of just a few operators that contribute to the total memory grant of an execution plan.
If you ever looked at execution plans, you will have seen this operator. And you probably have a rough idea of what it does. But do you know EXACTLY what happens when this operator is used? In this black belt session we will dive deep into the bowels of the operator to learn how it performs.
This is going to be wild ride, so keep your hands, arms, and legs inside the conference room at all times; and please remain seated until the presenter has come to a full stop.
Advanced Indexing
So you know all about heaps, clustered indexes, and nonclustered indexes, but are still not satisfied? Then this is just the session for you! You will learn about advanced features such as included columns, indexed views, and filtered indexes. You will find out how they are stored on disk, and how SQL Server uses them to satisfy queries. Then, you will see the tools SQL Server itself hands you to help identify just the right subset of indexes. And finally, you will get an stunning demonstration of just how bad you can hurt performance by creating too many indexes.
Execution plans ... where do I start?
SQL (the language) is not a third generation language, where the developer tells the computer every step it needs to take. It is a declarative language that specifies the required results. SQL Server itself will figure out what steps it takes to get to those results. Most of the time, that works very well.
But sometimes it doesn't. Sometimes a query takes too much time. You need to find out why, so you can fix it. That's where the execution plan comes in. In the execution plan, SQL Server exposes exactly which steps it took for your query, so you can see why it's slow.
However, execution plans can be daunting to the uninitiated. Especially for complex queries. Where do you even start?
In this session you will learn how to obtain execution plans. and how to start reading and understanding them.
T-SQL User-Defined Functions, or: How to kill performance in one, easy step
User-defined functions in SQL Server are very much like custom methods and properties in .Net languages. At first sight, they seem to be the perfect tool to introduce code encapsulation and reuse in T-SQL. So why is this feature mostly avoided by all T-SQL gurus?
The reason is performance. In this session, you will learn how user-defined functions feed the optimizer with misleading and insufficient information, how the optimizer fails to use even what little information it has, and how this can lead to shocking query performance.
However, you will also see that there is a way to avoid the problems. With just a little extra effort, you can reap the benefits of code encapsulation and reuse, and still get good performance.
Powerful T-SQL improvements that reduce query complexity
We’ve all dealt with nightmare queries: huge, twisted monsters that somehow work, despite being ugly and unmanageable. The time has come to tame these beasts, and the solution is available now, in SQL Server 2012.
New T-SQL functions offer out-of-the-box solutions for many problems that previously required complex workarounds. Paging, Running totals, Moving aggregates, YTD, and much more comes at the power of your fingertips in SQL Server 2012. The only thing you need to do is learn the syntax. And that is exactly what this session is all about: a thorough description and explanation of the syntax, and loads of demos to demonstrate how you can use all these new features.
Attend this session to boldly take SQL Server where it has never gone before!
Normalization beyond Third Normal Form
Do you believe the myths that “Third Normal Form is good enough”, or that “Higher Normal Forms are hard to understand”?
Do you believe the people who claim that these statements are myths?
Or do you prefer to form your own opinion?
If you take database design seriously, you cannot afford to miss this session. You will get a clear and easy to understand overview of all the higher Normal Forms: what they are, how to check if they are met, and what consequences their violations can have. This will arm you with the knowledge to reject the myths about higher Normal Forms. But, more important: it will make you a better designer!
Managing Execution Plans
An execution plan is in many ways similar to an organization. And the operators are like the employees, trying hard (and sometimes even succeeding) to work together effectively. Managing people, or execution plans, is easier if you know their idiosyncrasies. In this Lightning Talk, I will highlight a few of my favorite empl...eehrmm, operators; and tell you what you need to know in order to manage them effectively.
Deep dive into Adaptive Query Processing
Until SQL Server 2016, the Query Optimizer and the Execution Engine were strictly separated. The Query Optimizer produces an execution plan that, based on statistics and estimates, should be fast. That execution plan is then faithfully executed by the Execution Engine, even if reality turns out to be different from expectations.
But this is changing! SQL Server 2017 introduced three new features that allow execution plans to adapt to reality: Batch Mode Memory Grant Feedback, Batch Mode Adaptive Join, and Interleaved Execution. And even more of these adaptive features are already available as preview in Azure SQL Database.
In this session we will look at these new features. We will skip the shiny marketing slides, and instead look at how all of this ACTUALLY works. If you are happy to spend your time knee-deep in execution plan internals, come to this session!
Hash Match, the Operator - part 2
SQL Server has a lot of different execution plan operators. By far the most interesting, and the most versatile, has to be the Hash Match operator.
Hash Match is the only operator that can have either one or two inputs. It is the only operator that can either block, stream, or block partially. And it is one of just a few operators that contribute to the total memory grant of an execution plan.
If you ever looked at execution plans, you will have seen this operator. And you probably have a rough idea of what it does. But do you know EXACTLY what happens when this operator is used? In this double length black belt session we will dive deep into the bowels of the operator to learn how it performs.
This is going to be wild ride, so keep your hands, arms, and legs inside the conference room at all times; and please remain seated until the presenter has come to a full stop.
SQLGLA 2018 Sessionize Event
SQLGrillen 2018 Sessionize Event
Hugo Kornelis
I make SQL Server fast (.com)
Zeewolde, The Netherlands
Links
Actions
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top