SQL PASS Detroit

January 2018

Black Arts Indexing: A Science and an Art
You already know that good table design and proper indexing go hand-in-hand with writing high performance code, but do you know how indexes work under the hood? This is an in-depth look at indexing, and starts by examining the mechanics of pages, index structure and seeks versus scans. Then we'll discuss the inner workings of clustered and nonclustered indexes. Lastly, we'll look at covering indexes, heaps and over-indexing.

This presentation will illustrate why understanding index structure is so very important. We'll learn why some indexes can hurt performance while others will make your code scream. We'll take on the classic myths of “seeks always being better than scans” and that “duplicate indexes are always bad”. We’ll also examine why too many indexes can hurt performance.

Speaker Bio: Ed Wagner
Ed's a Senior DBA and SQL mentor at Ipsos RDA with 25 years experience as a developer and DBA. He's the president of the the SQL PASS in Detroit (SPID) chapter and a frequent poster and guest author on sqlservercentral.com. Ed has spoken at several local PASS chapters and SQL Saturdays. He's passionate about high-performance and "black arts" SQL and actually enjoys it.

Powerpoint Presentation and Code (.zip 922 KB)

February 2018

Why Defragmenting Your Indexes Isn't Helping
For years, you’ve heard that you’re supposed to reorganize your indexes to make SQL Server go faster. It sounds like it makes sense – keep things in order, right? But you keep doing it, and SQL Server isn’t getting any faster. You’ve even heard that setting fill factor will help prevent fragmentation, and you’re doing that too – but your indexes still keep getting fragmented every day, and users aren’t happy with performance.

This advice made a lot of sense at the turn of the century, but today, things are different – and we’re not just talking solid state drives. In just the first 15 minutes, you’ll have a series of ah-ha moments when you realize that your daily index maintenance jobs might just be making the problem worse instead of better. Then, you’ll learn what you need to do instead.

Speaker Bio: Brent Ozar
Brent Ozar loves to make SQL Server faster and more reliable. He created sp_Blitz and the SQL Server First Responder Kit, and he loves sharing knowledge at BrentOzar.com.

Presentation on Brent's Site

March 2018

Your AG might not be as “Available” as you think
You’ve got an availability group! Good for you! It’s set up all nice and shiny with all the settings configured just right for maximum availability, and the dashboard says “Synchronized.” But wait, did you know someone added a new job to your primary server last week but didn’t add it to your secondary? Or what about the new linked server that was added on your readable secondary to aid with the reports accounting was asking for…did anyone add it to the primary?

An availability group will keep all of your database objects synchronized, but server-level objects are often overlooked. In this session I’ll show you how to set up a reporting tool that will show you which objects are out of sync so that you can correct them, including a query that will let you compare both objects so you can see what’s different.

Speaker Bio: Joe Fleming
Joseph Fleming is a Senior Consultant at Fortified Data with over 20 years of database mishaps under his belt. By sharing his wacky misadventures with you, he hopes to make you smile and avoid some future tears.

Powerpoint Presentation and Code (.zip 2.5 MB)

April 2018

Index Fragmentation: Internals, Analysis, and Solutions
As soon as you have more than one index on a table, you're going to start seeing fragmentation. Many people know that this fragmentation is bad, but why is it bad? And what is the best way to get rid of it? Come find out from the person who wrote the tools for detecting and removing index fragmentation. In this session, you’ll learn how fragmentation occurs and why the process that leads to it can often be as damaging to performance as the fragmentation itself. You'll also learn how to test for fragmentation, the different methods of removing it, and everything you need to consider when putting together an appropriate fragmentation-management strategy.

Speaker Bio: Paul S. Randall
Paul’s an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. He spent 9 years working on the SQL Server team, writing DBCC CHECKDB, and ultimately responsible for the entire Storage Engine. In 2007 Paul left Microsoft to co-own and run SQLskills.com, and has been a SQL Server MVP ever since. When he's not tweeting, blogging, or helping someone recover from a disaster, he's likely to be underwater somewhere in the world with his wife, Kimberly L. Tripp.

PDF Presentation (.pdf 3.1 MB)
Code Demos (.zip 7 KB)

May 2018

Black Arts Index Maintenance
The most common reasons to do Index Maintenance are to defragment indexes to ensure that they continue to provide good performance and to control how much space they occupy both on disk and in memory.

Ironically, thousands, perhaps even millions of people are unwittingly destroying the full potential of their indexes and are actually injecting guaranteed performance and space usage problems into their indexes by using current, widely-accepted “Best Practice” Index Maintenance methods. To top off the atrocities, all of this results in hundreds of thousands of totally unnecessary log file entries, slowing performance even more.

In this SQL “Black Arts” session, Jeff Moden graphically demonstrates why and how current “Best Practice” Index Maintenance methods are causing all of those performance problems and two very simple methods to “auto-magically” fix those problems (including automatic adaptive Fill Factor determination) and virtually eliminate all bad page splits in the process.

Speaker Bio: Jeff Moden
Senior Database Administrator and SQL Mentor, Proctor Financial, Inc., Troy, Mi, USA
VP of the SPID (SQL PASS In Detroit) SQL Server User Group, Livonia, MI 48152

With more than 45,000 posts, 3 dozen 5 star articles and 430 thousand likes, Jeff Moden is a strong contributor on SQLServerCentral.com where he coined the term RBAR (Row By Agonizing Row). Jeff has more than 2 decades of experience with SQL Server and is mostly self-trained in what he calls the Black Arts of T-SQL. He’s known worldwide for his informative articles, high performance T-SQL coding methods, and methods of mentoring. His dedication to helping others earned him the MS SQL Server MVP award for nine years and the RedGate Exceptional DBA Award in 2011. His mantra is "Performance is in the code".

June 2018

Stored Procedure Optimization Techniques

Kimberly will discuss stored procedure caching and the potential for performance problems due to a problem known as parameter sniffing. There are some common solutions and while they can drastically improve performance, they can also cause other problems. Sometimes a hybrid solution is needed.  From this session, you’ll know the best way to write procedures so that stable requests are cached and unstable requests are not (giving you the most optimal performance without wasting resources).

Speaker Bio: Kimberly L. Tripp
Kimberly’s an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection and numerous other events around the world; she’s also been a Microsoft SQL Server MVP for over 10 years. She has over 25 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal.

Powerpoint Presentation and Code (.zip 2.8 MB)

July 2018

Answering the Question, "What Happened?" with Query Store
One of the most highly anticipated new features in the SQL Server 2016 release was Query Store.  It's been referred to as the "flight recorder" for SQL Server because it tracks query information, namely plans and runtime statistics.  If you've ever had to drop everything to troubleshoot a sudden drop in performance, then you've probably already realized the value of this feature.  In this session we'll walk through Query Store with a series of demos designed to help you understand how you can immediately start to use it once you’ve upgraded to SQL Server 2016 or 2017.  We'll review the different options for Query Store, look at examples of what gets collected, dig through the included reports, and see how you can force SQL Server to use a specific plan for a query.  Get ready to make troubleshooting easier with this feature that’s included in all editions of SQL Server 2016 and higher!

Speaker Bio: Erin Stellato
Erin Stellato is a SQL Server MVP and lives in Cleveland, OH. She has over 15 years of technology experience and has worked with SQL Server since 2003. Her areas of interest include statistics, performance tuning, Extended Events, and helping accidental/involuntary DBAs figure out how SQL Server works. Erin is an active member of the SQL Server community, has volunteered for PASS at the local and national level, and is a regular speaker at conferences like SQLintersection and the PASS Summit. Outside of time with family and friends, she enjoys running, spinning, movies, and all things chocolate.

Powerpoint Presentation and Code (.zip 832 KB)
Please rename the *.dmc files to *.cmd. Many email servers and browsers flag *.cmd as security risks, so we've renamed them here so you can get them.

August 2018

Query Optimization Explained in 15 Demos
You've got the hang of writing queries and creating indexes, but once you hit F5 you have no idea what's going to happen.

  • Why did I get that plan?
  • Why didn't you use that index?
  • Why are you slow?
In this session, we'll look a little deeper into query optimization, and why SQL Server makes the choices it does.

This will be fast-paced, demo-heavy and slide-light.

Speaker Bio: Erik Darling
Erik Darling started using SQL Server after a dispute with Excel over a vlookup. Since then he's been a developer at a market research company and a DBA for an eDiscovery shop. He enjoys arguing with the Optimizer and contributing to the First Responder Kit.

PDF Presentation (6.7 MB)

September 2018

Dr. DMV's Troubleshooting Toolkit
Dynamic Management Views and functions allow you to easily see exactly what is happening inside your SQL Server instances and databases with a high level of detail. You can discover your top wait types, most CPU intensive stored procedures, find missing indexes, and identify unused indexes, to name just a few examples. This session presents, demonstrates and explains a complete set of diagnostic DMV queries that you can easily use to detect and diagnose configuration and performance issues in your SQL Server instances and databases. This session goes into exhaustive detail on how to interpret the results of each of the diagnostic queries, including relevant background information on how to properly configure your hardware, storage subsystem, operating system, SQL Server instance and databases in order to avoid performance and scalability issues.

Speaker Bio: Glenn Berry
Glenn has worked as a SQL Server professional for many years in a variety of roles, including stints with NewsGator and Avalara. His expertise includes DMVs, high availability, hardware selection and configuration, and performance tuning. He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. Glenn is heavily involved in the SQL Server community, and is a frequent speaker at user groups, SQL Saturdays, SQLintersection, and the PASS Community Summit. He is the author of the book SQL Server Hardware, and he wrote chapters for SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2 books.

PDF Presentation (675 KB)
Code (26 KB)

Back to Top