Practical SQL Server Performance Troubleshooting

2013

TL; DR

In this precon we will take a default installation of SQL Server, and run a OLTP workload with a several hundred users to generate our initial baseline for performance tuning/troubleshooting.

Session Details

In this precon we will take a default installation of SQL Server, and run a OLTP workload with a several hundred users to generate our initial baseline for performance tuning/troubleshooting. Throughout the day we will work across various areas of SQL Server to implement different performance optimizations, and check how those changes will impact the throughput of our test workload. At the end of the day we will have a well-performing SQL Server which can handle a much larger workload than the initial (default) installation.

Following areas are covered in this precon:
*) Windows OS Settings
*) Storage Configuration
*) SQL Server Instance Settings
*) Database Settings
*) Index/Statistics Maintenance
*) Locking/Blocking/Deadlocking
*) Memory Management

3 things you'll get out of this session

Klaus Aschenbrenner's previous sessions

SQL Server 2014 In-Memory OLTP Deep Dive

Hekaton is the Greek word for 100 - the goal of In-Memory OLTP in SQL Server 2014 is to improve query performance up to 100 times. In this session we will look under the cover of Hekaton and the Multi Version Concurrency Control (MVCC) principles.

Headache guaranteed: Deadlocking in SQL Server!

SQL Server needs its locking mechanism to provide the isolation aspect of transactions. As a side-affect your workload can run into deadlock situations - headache for you as a DBA is guaranteed!

Latches, Spinlocks, and Lock Free Data Structures

Join this session to make a deep dive into how SQL Server implements physical locking with lightweight synchronization objects like Latches and Spinlocks.

The dangerous beauty of Bookmark Lookups

Join me in this session to get a basic understanding of Bookmark Lookups, and why they can be dangerous in SQL Server.

Troubleshooting the most difficult SQL Server problems

Do you have enough from "normal" performance tuning sessions? You can't hear anything about Indexing and Execution Plans anymore? Then you are right in this session!

Troubleshooting TempDb

For the most DBAs and DEVs the TempDb is a crystal ball. But the TempDb is the most critical component in a SQL Server installation and is used by your applications and also internally by SQL Server.

SQL Server 2008 Database Internals

Do you already wanted to know how SQL Server 2008 stores a database file physically on the hard drive? In this session you will learn the internal structure of a SQL Server 2008 database file.

Understanding SQL Server Execution Plans

Do you know SQL Server Execution Plans? Yes! - and can you read/analyze them? No... For the beginner it is not very easy to understand and explain execution plans generated by SQL Server for your queries.

Advanced SQL Server 2008 Troubleshooting

It's Friday, 05:00pm. You are just receiving an email that informs you that your SQL Server has enormous performance problems! What can you do? How can you identify the problem and resolve it fast?

Service Broker: Message in a bottle

Service Broker: Message in a bottle