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".

Powerpoint Presentation and Code (.zip 14 MB)

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids