SQL Sever performance tuning Database Administration T-SQL Data Platform Microsoft Data Platform PowerShell
Enköping, Uppsala, Sweden
I will show some way too common uses of RBAR (Row by Agonizing Row) processing and show different examples of how they can be replaced with a set based approach.
We will look at performance comparisons and talk about code maintainability. Most of the examples are war stories from my 20 years working with databases. I may or may not be responsible for many of the bad examples myself.
If you write T-SQL queries on a regular basis, you will have seen unexpected query execution plans, making you wonder "Hmm.. Why did SQL Server decide to make one million lookups against this 100 row table instead of just scanning the whole thing once?".
In this session, we will look at some really strange rowcount estimates and figure out how SQL Server came up with them.
You learned that your statistics should be regularly updated. You even implemented Ola Hallengren's maintenance scripts. That should be enough, right? What if it's not?
Join me on some head-ache, a dive into statistics histograms, fun with flags and a praise to the evolution of SQL Server optimizer.
Much of this session is about the "ascending key problem", and how Microsoft have made shanges to the SQL Server optimizer to improve, but not completely eliminate the problem.
I was very late to adopt Powershell in my day-to-day work as a DBA. I did some copy/change "programming" with Powershell. But it wasn't until I got in touch with DBATools that I really understood the power of Powershell.
In this session, I will demo some of the reasons I really love Powershell and DBATools.
I will cover a brief overview of DBATools and then jump into some scenarios where DBATools really helped me, including:
- Automatic restore testing
- Homecooked log shipping
- Migration work
A case study of how a monthly data load has evolved, from just loading data into a table, through some indexes being disabled before loading and enabled after data is loaded, to partitioning the table and loading into a staging table. The last changes to the solution has taken loading time from eight hours down to 20 minutes, saving a whole day of work for the staff checking and refining the data.
The presentation also includes some reflections on how partitioning the table has changed how both T-SQL and Entity Framework code is being written, to take advantage of partition elimination.