SQL Server optimization stuff you won’t find on Google (yet)

2009

TL; DR

Henk van der Valk from the Unisys performance lab will be my special guest for this session. He has a test system that we all would like to have at home. The ES7000 has 96 Cores and half a Terabyte of RAM yes RAM (!). We will look at various ways to push SQL Server workloads by methodically detecting and resolving bottlenecks.Together we will show you how you can apply this approach on mid-sized and even smaller systems. (We’ll even try some of them on a laptop, and of course we will remote connect into the big machine ) In this session you will: Learn how to measure what the next bottle-neck is,using perfmon and waitstats. Hear about optimization tips that will get the most out of your hardware to speed up your processsing.

Session Details

Henk van der Valk from the Unisys performance lab will be my special guest for this session. He has a test system that we all would like to have at home, probably until you get the first electricity bill :-) The ES7000 has 96 Cores and half a Terabyte of RAM (!).

We will look at various ways to push SQL Server workloads by methodically detecting and resolving bottlenecks.

A number of tips, tricks and testing methods that Henk tought the (Dutch) SQL Server community over the past years I applied successfully on various troubled systems. Together we will show you how you can take some lessons learned and apply them on mid-sized and even smaller systems. (We’ll even try some of them on a laptop, and of course we will remote connect into the big machine )

In this session you will:

Learn how to measure what the next bottle-neck is so you don’t have to guess. (Using perfmon and waitstats)
Hear about optimization tips that will get the most out of your hardware to speed up your ETL process.
Learn some magic to speed up tablescan speed from disk and memory (!) significantly.

3 things you'll get out of this session

Andre Kamman's previous sessions

A Python primer for DBAs, how to talk to a SQL Server

Tinkered with Powershell but not with Python yet? Let's get you started, and we'll do that with some practical examples focussed around talking to SQL Server and importing and exporting some data.

The Enterprise Cloud Journey Learning Curve

From the perspective of a DBA who had to work with the internal fairly new Cloud Competence Center of an Enterprise to get many TB's of data into Azure, a story about the learning curve corporations go through

Creating complex labs from start to finish with Lability

Introduction on how to use the Powershell library Lability to create complex SQL Server based test labs.

Powershell community scripts for DBAs

We will take a look into my favourite Powershell scripts

Visualizing performance tuning data

Over the years I've tried various ways to look at output from SQL Server for performance tuning reasons. We will look at virtual_file_stats, waitstats, the buffer cache etc. and we will visualize them in various ways

ORM Tuning From A Database Perspective

Most ORM's tend to be very chatty and generate complex queries. In this session we will look at optimizing database access in ORM's. Using Nhibernate, LINQ &Entity Framework in the demos, we will examine how your ORM talks to the database and why.

Build your own Monitoring Environment with Powershell

Learn how to write Powershell that will use Remoting to run scripts parallel on any number of servers. We'll talk about remoting, background jobs and how to collect and report on the results

How to write professional Powershell scripts

It's time to take your ad-hoc Powershell scripts turn them into your very own module. And while we're at it we will add proper error handling, parameterization & pipeline support. I will also demonstrate how to build help, force and whatif support.

Running SQL Server 2014 on a Scale Out File Server

In this session I will show you how to use Storage Spaces and Auto Tiering and some other features to build a highly available, fast, scale out file server. And then we'll use it to host SQL Server data & log files on it.

ETL shootout-SSIS vs Powershell

I know what you're thinking, Powershell is not an ETL tool. And you're probably right. But I keep running into weird requests that were just easier to fix with Powershell. I'll show you why some things are not easy in SSIS.

A better SSIS? Migrating a datawarehouse to SQL Server 2012

SSIS in SQL Server 2012 has a lot of new features to help control larger projects. I'm doing a trial migration of our datawarehouse (100+ packages, 1TB+ db size) This session will be a will be a walkthrough of this process and a lessons learned

How DBAs can use PowerPivot for Performance Tuning / Troubleshooting

PowerPivot can be a great troubleshooting / performance tuning tool for a dba besides just loading all the data in a database and start querying. I'll show the pro's and cons of PowerPivot while trying work with waitstats, profiler data etc.

I'll explain how to manage a large environment of 400+ instances in such a way that it's also useful if you have only 5 instances. Expect 50% processes and war stories and 50% demos and scripts (lots of PowerShell of course, but not everything)

Automating SSIS

I had to import and process 100+ files for my latest project and wondered if there was a way to generate SSIS packages based on a repository table containing the rules. As it turns out, this can be done but the programming model is a bit awkward. The samples that you can find online are not allways functional and debugging is difficult. I did get it to work though and will show you how I did it and what problems I ran into. After this session you will know how to generate packages that contain sources, destinations, cached lookups, derived columns, conditional splits, sorts, union all, etc. This session is not about the script component inside SSIS but about generating SSIS packages and programmatically reading and changing properties of existing packages.This session should also be usefull if you're not a full blown developer but more of a DBA type. I'm a DBA and I got through this :-)