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.

Introducing dbachecks v3

dbachecks v1 was released back in 2018, bringing together the DBA PowerShell magic of dbatools with pester, a PowerShell testing framework to create a powerful infrastructure validation tool.

dbachecks is able to answer questions such as:
- can I connect to all of my instances?
- was my last full backup with 7 days
- are my databases owned by the expected login
- am I using 'page verify' to detect corruption
- are any of my certificates nearing expiration
- and many more...

v2 introduced the ability to store these results in a database and create a time series chart showing changes over time.

But now, we introduce v3!

dbachecks v3 takes advantage of Pester v5 - a total rewrite of the tool enabled us to not only increase the performance of the module but position it to be able to grow and expand in a controlled and predictable way in the future.

In this session, we'll talk about the changes we've made to the module, demo the new features and prove the massive performance increase using Profiler.

Come and join us as we share the biggest release yet of the dbachecks module and learn how you can utilise it in your environments.

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.