Magnus Ahlkvist

Information & Communications Technology

SQL Sever performance tuning Database Administration T-SQL Data Platform Microsoft Data Platform PowerShell

Enköping, Uppsala, Sweden

Magnus Ahlkvist

SQL Server specialist, Data Platform MVP, SQL Community organizer. Paid work out of Transmokopter SQL AB.

20+ year database professional with a passion for sharing.

Current sessions

RBAR Bad. Sets good.

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.

Are you drunk SQL Server? Where did that estimate come from?

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.

Statistics, an unreliable friend

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.

My romantic relation with Powershell generally and DBATools especially

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

Eight hours of work in 20 minutes.

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.