Jeff Moden
Performance is in the code.
Detroit, Michigan, United States
Actions
Jeff Moden is an “Accidental DBA” with more than two decades of SQL Server experience. Jeff’s original article on SQL Server central helped make the advanced technique known as the “Tally” or “Numbers” Table a household word and coined the term “RBAR”, which stands for “Row By Agonizing Row”. He’s written 39 mostly five star articles for SQLServerCentral.com and, having more than 53,000 posts, is one of the leading contributors on that site. Jeff is renowned for being able to teach extremely complex concepts and techniques in such a manner that even beginners can understand without boring the experts. He’s also a 9 year SQL Server MVP veteran, won the Red Gate exceptional DBA award in 2011, has spoken at the PASS Summit, many SQL Saturdays, and many local PASS chapter meetings on many different SQL Server subjects.
His mantra is "Performance is in the code".
Area of Expertise
Topics
"Black Arts" Index Maintenance 3 - LOBs - Defragmented by Default
This is NOT your typical session on indexes.
tldr: If you use any LOBs (MAX datatypes, XML, or even big VARCHARS()) in your database, they're probably killing performance and wasting huge amounts of buffer memory and it's totally unnecessary. We're going to fix all that and make some of your indexes "Defragmented by Default" in the process using a couple of very simple "tricks".
Moere detailed...
If you have LOBs in your tables, there's a really good chance that they're seriously slowing down your queries, using possibly hundreds of times more buffer memory than you need to, causing massive page splits and the resulting fragmentation, and permanently reducing the page density of a huge portion of your clustered indexes to less than 50% and to as little as 1% because of permanently "Trapped Short Rows" that you can't do anything about unless you know "the trick". No amount of defragging will fix it either.
When Microsoft introduced the MAX and XML data-types, they also made a mostly unknown change as to how the LOBs are handled. The hidden collateral damage caused by the change is huge…
1. Non-LOB queries (most of our queries) run twice as slow and require two orders of magnitude more memory.
2. Rampant “bad” page splits, which leads to serious blocking.
3. Permanent physical fragmentation of Clustered Indexes.
4. Seriously Increased memory and disk usage.
5. Increased and totally unnecessary Index Maintenance.
6. Increased log file activity, which also affects query performance, backup storage requirements, and increased backup and restore times.
SQL Server MVP Veteran Jeff Moden shows us how and why the change causes all of these problems and then demonstrates how two simple changes to our tables fix it all. He also demonstrates how the same techniques can be used to make some non-LOB tables “Defragmented by Default”.
Cross Tabs and Pivots - Reporting on Steroids
Another title for this session might be "Converting Rows to Columns" or "How to turn data into information".
Whether you're a Data Analyst, Report Creator, Developer, or DBA, this is an essential skill that you'll need to do in the future if you haven't had to already. Here's what we'll learn:
Primary Goal:
How to convert rows to columns to make a pivoted result, sometimes referred to as a "Cross Tab" and do it in a nasty fast manner using the "Cross Tab" and "Pivot" methods.
Ancillary Learning:
1. Introduction to building large volumes of random constrained data to test with.
2. Introduction to "Minimal Logging" to build the test data even faster and without ticking off the DBA.
3. Introduction to "Pre-Aggregation" as a simple first step towards increasing performance.
4. Introduction to "Divide'n'Conquer" methods for more performance.
5. Seemingly "Duplicate Indexes" for insane performance.
"Black Arts" Index Maintenance - GUIDs v.s. Fragmentation - They're not the problem... WE ARE!
This is NOT your typical presentation on the fragmentation problems of Random GUIDs. No… Instead we’re going to DESTROY THE MYTH OF RANDOM GUID FRAGMENTATION.
In one of the most ironic/heterodoxical turns of knowledge you’re ever likely to experience, we’ll see how THE USE OF RANDOM GUIDS CAN ACTUALLY PREVENT FRAGMENTATION! In the end, you’ll witness the results of some simple testing that clearly demonstrate that you can easily insert literally MILLIONs of rows into a Random GUID clustered index with almost no page splits (not even supposed “good” ones) and LESS THAN 1% Logical fragmentation!
We’ll identify the real problem and the seriously effective yet incredibly simple two-part fix for it. In the process, we’ll prove that Random GUIDs actually behave in a manner like most people expect a good index to behave, especially in but not limited to high performance OLTP environments as well as the benefits of doing so.
We’ll also learn how to use a new tool that I created (included in the ZIP file) to ACTUALLY SEE what an index looks like at the page level for all pages in a single graph.
Then, we’ll use that tool to lay waste to what people are currently calling “Best Practice” index maintenance. We’ll literally see what REORGANIZE does to an index at the page level and why it’s one of the very worst things you can do to your Random GUID and many other types of indexes even if you’re limited to the Standard Edition of SQL Server. We also prove that REORGANIZE CAUSES ORDERS-OF-MAGNITUDE WORSE TRANSACTION LOG FILE USAGE than REBUILD especially for Random GUIDs.
As interesting and totally necessary sidebars, we'll also see how the use of ever-increasing index keys could be (and frequently is) a major source of many of your fragmentation problems and we’ll also see that NEWSEQUENTIALID may NOT the answer that you’re looking for.
"Black Arts" Index Maintenance - GUIDs v.s. Fragmentation - They're not the problem... WE ARE!
This is NOT your typical presentation on the fragmentation problems of Random GUIDs. No… Instead we’re going to DESTROY THE MYTH OF RANDOM GUID FRAGMENTATION.
In one of the most ironic/heterodoxical turns of knowledge you’re ever likely to experience, we’ll see how THE USE OF RANDOM GUIDS CAN ACTUALLY PREVENT FRAGMENTATION! In the end, you’ll witness the results of some simple testing that clearly demonstrate that you can easily insert literally MILLIONs of rows into a Random GUID clustered index with almost no page splits (not even supposed “good” ones) and LESS THAN 1% Logical fragmentation!
We’ll identify the real problem and the seriously effective yet incredibly simple two-part fix for it. In the process, we’ll prove that Random GUIDs actually behave in a manner like most people expect a good index to behave, especially in but not limited to high performance OLTP environments as well as the benefits of doing so.
We’ll also learn how to use a new tool that I created (included in the ZIP file) to ACTUALLY SEE what an index looks like at the page level for all pages in a single graph.
Then, we’ll use that tool to lay waste to what people are currently calling “Best Practice” index maintenance. We’ll literally see what REORGANIZE does to an index at the page level and why it’s one of the very worst things you can do to your Random GUID and many other types of indexes even if you’re limited to the Standard Edition of SQL Server. We also prove that REORGANIZE CAUSES ORDERS-OF-MAGNITUDE WORSE TRANSACTION LOG FILE USAGE than REBUILD especially for Random GUIDs.
As interesting and totally necessary sidebars, we'll also see how the use of ever-increasing index keys could be (and frequently is) a major source of many of your fragmentation problems and we’ll also see that NEWSEQUENTIALID may NOT the answer that you’re looking for.
Ohio North SQL Saturday 2023 Sessionize Event
Jeff Moden
Performance is in the code.
Detroit, Michigan, United States
Actions
Please note that Sessionize is not responsible for the accuracy or validity of the data provided by speakers. If you suspect this profile to be fake or spam, please let us know.
Jump to top