Performance of a T-SQL query - The mystery, the investigation and the root cause!

"The query is faster on the old environment! 3 times faster!" No one ever said that, right?

But what is causing that?
It is the same engine version, with no bottlenecks on CPU, memory or disk!
"Surely, it is statistics(!) and a different execution plan shape!" No! Even the execution plan looks the same!
No yellow triangles with warnings, nothing screams "I'm the problem!".

Let me share this true story that was driving me crazy with you. The analysis I did, and all the tools and methods I used to compare things, until, after some days, I got to the root cause of it.

Now that I know, it's obvious, but isn't it always that way?

I can guarantee you, as long as you remember this, you won't forget to check it again when this kind of performance issue strikes! Anyway, I will also share an easy way to remember to check it next time!

A journey to understand where the performance penalty came from!
If you are a DBA/Developer writing/analysing T-SQL, you will want to know this!

More concurrency with less locking and blocking? Let's check 'Optimized Locking'!

Locks on objects are needed to guarantee the consistency of the data.
However, locks need memory and with too many locks, we can get a lock escalation which will prevent other sessions from doing their work because they will go blocked.

What if we could have fewer locks being held even for larger transactions? This would allow more sessions to access, concurrently, the table.

Now, imagine this is turned on by default on Azure SQL DB.

Let's take a look at the optimized locking feature and how that differs from what we are used to.

Is it any good? Or bad? Maybe it depends!
You need to join me in this session to find out.

X-Raying Schema Operations: Adding and Removing Columns

As database developers and/or administrators, we often encounter the need to make schema changes to tables. These changes involve various operations, such as adding or removing columns.

Understanding the inner workings of these operations may be crucial. Why are some changes quick and seamless, while others can significantly impact system performance?

In this session, we'll delve into SQL Server internals to explore the processes involved in adding and removing columns. We'll examine the intricacies of these operations, including their utilization of the transaction log, the locking system, and space management. This exploration will unravel their impact (or lack thereof) on system resources and performance.

Is adding a new column always a slow and a blocking operation? And what about dropping a column? Will this single action immediately reclaim the used space? Let's explore these and other questions together.

By gaining insight into these specific examples, you will be better equipped to manage similar schema changes effectively in your database environments.

dbatools - Wheel of Fortune

Ad voice: Want to play Wheel of Fortune dbatools style?

Come and join us on a demo-driven session where you will be the player and we gift you with some next-level demos using dbatools!

Spin the wheel and let luck be with you!

There are so many things we want to show you with dbatools that we couldn't decide - so now you decide!

Demos could include:
- Managing Availability Groups
- Migrating databases
- Refreshing test environments
- Masking sensitive data
- Checking we're meeting best practices
- Managing Logins, Users & Permissions
- Using Database Snapshots for Application Upgrades
- Document your entire SQL environment
- Backups and Restores
- Copy table data around
- Deploy tools including WhoIsActive & Ola maintenance scripts
- Patching SQL Servers
- Finding objects
- and others - who knows where the wheel may take us

Let me show you why data types still matter

When building a SQL Server database, the selection of the data types is, sometimes, neglected. Usually, until you get lots of data and/or more concurrency you may not notice any problems. This means that NVARCHAR(MAX) is okay, right?

We also have examples where while doing some math operations or even concatenating columns/variables you won't get the result you expect! You may think that SQL Server is buggy but actually, it's just doing its thing, in the way it works.

In this session, we will go through some examples that hopefully will make you think twice next time you are deciding your data types.

Writing T-SQL code for the engine, not for you

You've been requested to analyze/change a query to include some new requirements. You are not sure what to do and your colleague/boss says "it's a quick change, you JUST need to copy-paste that block and change the filter". You are happy because after all, it's a quick change. You deploy the query change to production but now the business complains the Power BI report that was running in one hour now takes an extra five minutes.

You might think that was expected because you are getting more data.
However, this is where you should step back and ask, was the one hour ok? Couldn't it be faster already?
You have to stick around to find out. Regardless, let's stop pretending that running a query in one hour is OK when it should be possible to make it run in a couple of minutes by re-writing it.

This is just one of the development patterns I see almost every single day when analyzing and tuning T-SQL queries.

In this session, we will see some code patterns that won't scale and other pitfalls while writing T-SQL and which options we have to fix them to improve performance.

NOTE: This session is not about indexing.

Writing or changing a query may seem to be an easy task but, making sure they will still perform as expected can be a different story.
How can we avoid these pitfalls? What do we need to know?
NOTE: This session is not about indexing.

Don't go offline because of your index deployment strategy

Creating and/or altering indexes need some upfront preparation. These actions have an even bigger importance when dealing with busy systems where we can't afford or want to avoid big blocking operations or, even worse, downtimes.

Most recent versions of SQL Server introduced new options (mainly in the Enterprise edition) that make it possible to minimize or even avoid problems like these to happen.

This is a demo-based session where I will be showing the pitfalls and sharing the options you can use to help avoid them.

Also, along the way, I will tell you a couple of real-life stories on how and why things went wrong and made systems slower or even stopped.