SQL Server 2022 – QAT Backups

One of the amazing aspects of my job at Pure Storage, is that I get opportunities to work with new and emerging tech, oftentimes before it is available to the general public. Such is the case with SQL Server 2022, where I got to help test QAT backups for SQL Server 2022.

TL;DR

Using QAT compression for your native SQL Server backups will give you better compression with less CPU overhead, than “legacy” compression. So I get smaller backup files, faster, with less CPU burn. What’s there to not like?

Tell Me More About This Q… A… T… Thing!

So QAT stands for Intel’s Quick Assist Technology, which is a hardware accelerator for compression. It’s actually been around for many years, but most regular folks like myself never got exposed to it, because you need a QAT expansion card in your server to even have access to its powers. And for us SQL Server folks, we had nothing that took advantage of QuickAssist Technology… until now thanks to SQL Server 2022.

In SQL Server 2022, Microsoft has introduced QAT support for Backup Compression. And as I demonstrated in this blog post, your backup files are essentially byte-for-byte copies of your data files (when not using compression or encryption). And I don’t know about you and your databases, but the SQL Server environments I see these days, database sizes continue to grow and grow and grow… so I hope you use compression to save backup time and space!

But I Don’t Have QAT Cards In My SQL Servers

I said earlier that QAT has been around for a number of years, available as expansion cards. But because SQL Server had no hooks to use QAT, I strongly doubt that any of us splurged for QAT cards to be added into our SQL Servers. But there’s two things coming that’ll change all of that…

First, SQL Server 2022 has both QAT hardware support AND QAT software emulation. This means you can leverage QAT goodness WITHOUT a QAT expansion card.

Second, the next generation of Intel server processors will have QAT hardware support built in! So the next time you do a hardware refresh, and you buy the next gen of Intel server CPUs, you’ll have QAT support!

Third, if you cannot get the latest snazzy CPUs in your next hardware refresh, QAT cards are CHEAP. Like, less than $1k, just put it on a corporate charge card cheap.

IMPORTANT – QAT hardware support is an Enterprise Edition feature. But you can use QAT software mode with Standard Edition. And if you stay tuned, you’ll come to find that I’ve become a big fan of QAT software mode.

How’d You Test This Andy?

In my team’s lab, we have some older hardware lying around that I was able to leverage to test this out. Microsoft sent us an Intel 8970 QAT card, which we installed into one of our bare metal SQL Servers, an older Dell R720 with 2x Xeon E5-2697 CPUs and 512GB of RAM.

Database being backed up is a 3.4TB database, with the data spread across 9 data files across 9 data volumes. The data volumes were hosted on a FlashArray and the backup target was a FlashBlade.

To test, I used the above database and executed a bunch of BACKUP commands with different combinations of parameters. I leveraged Nic Cain’s BACKUP Test Harness to generate my T-SQL backup code. If you haven’t used it before, it’ll generate a bunch of permutations of BACKUP commands for you, mixing and matching different parameters and variables. I was particularly pleased that it also included baseline commands like a plain old BACKUP, and a DISK=NUL variant. I did have to make some modifications to the test harness to add in COMPRESSION options: NO_COMPRESSION, MS_XPRESS (i.e. legacy COMPRESSION), and QAT_DEFATE.

Sample BACKUP command used

Tangent: Backup READER & WRITER Threads

So I’ve always known that if you specify more output backup files, that’ll decrease your backup tremendously. But I never quite understood why, until I started this exercise and Anthony Nocentino taught me a bit about BACKUP internals.

In a backup operation, there’s reader threads that are consuming and processing your data, and there are writer threads that’s pushing your data out to your backup target files. If you run a bare bones basic BACKUP command, you get one READER thread and one WRITER thread to do your work. If you add additional DISK = ‘foobar.bak’ parameters, that’ll give you more WRITER threads; 1 per DISK target specified. If you want to get more READER threads, your database has to be split across multiple data VOLUMES (not files or filegroups).

If you were paying attention above, you’ll note that my test database consists of 9 data files across 9 data volumes. I set it up this way because I wanted more READER threads available to me, to help drive the BACKUP harder and faster.

Keep in mind, there’s always a trade-off in SQL Server. In this case, the more threads you’re running, the more CPU you’ll burn. And if you’re doing a bunch of database backups in parallel, or trying to run your backups at the same time as something else CPU heavy (other maintenance tasks, nightly processing, etc.) you may crush your CPU.

Tangent: FlashBlade as a Backup Target

FlashBlade is a scale-out storage array, whose super-power amounts to parallel READ and WRITE of your data. Each chassis has multiple blades and you can stripe your backup files across each of the different blades for amazing throughput. When you look at the sample BACKUP command, you’ll see different destination IP addresses. It is through these multiple Virtual IPs, which go to same appliance, but helps to stripe the backup data across multiple blades in FlashBlade.

Test Results

Legend

Compression TypeDefinition
NO_COMPRESSIONNo BACKUP compression used at all.
MS_XPRESS“Legacy” BACKUP compression used.
QAT_DEFLATE (Software)QAT BACKUP compression – Software emulation mode used
QAT_DEFLATEQAT BACKUP compression – Hardware offloading used

Baseline: DISK = NUL

BACKUP summary results – DISK = NUL
All test results are the average of 3 executions per variable permutation.

Remember, when using DISK = NUL, we’re NOT writing any output – all of the backup file data is essentially thrown away. This is used to test our “best case” scenario, from a READ and BACKUP processing perspective.

It’s interesting to see that without WRITE activity, QAT acceleration did help speed up our BACKUP execution vs legacy compression. And QAT does offer slightly better backup file compression vs legacy compression. But what I find the most impactful is CPU utilization, from both QAT hardware and software modes, is MUCH lower than legacy compression!

Note the Backup Throughput column. We actually hit a bit of a bottleneck here on the READ side, due to an older FibreChannel card in my test server and only having 8x PCIe lanes to read data from my FlashArray. The lab hardware I have access to isn’t cutting edge tech for performance testing, rather older hardware meant more for functionality testing. Moral of this story? Sometimes you I/O subsystem “issues” are because of network OR underlying server infrastructure, like the PCIe lanes and subsequent bandwidth limitations encountered here.

The Best: Files = 8; MTS = 4MB, BufferCount = 100

BACKUP summary results – Files = 8, MTS = 4MB, BufferCount = 100
All test results are the average of 3 executions per variable permutation.

I’m skipping over all of my various permutations to show the best results, which used 8 backup files, MAXTRANSFERSIZE = 2MB, and BUFFERCOUNT = 100.

Much like the DISK = NUL results, QAT yields superior compressed backup file size and CPU utilization. And in this case, Elapsed Time is now inverse – NO_COMPRESSION took the most amount of time, whereas in the DISK = NUL results, NO_COMPRESSION took the least amount of time. Why might that be? Well in the DISK = NUL scenarios, we don’t have to send data over the wire to write a backup target, whereas in this case we did. And using compression of any sort means we will have to send less data out and write less data to our backup target.

Stuck with TDE?

I also TDE encrypted my test database, then re-ran more tests. I found it interesting to see how a TDE database wound up taking more time across the board. And I found it interesting that with TDE + legacy compression, CPU usage was slightly lower but throughput was worse, vs non-TDE + legacy compression.

Parting Thoughts

Of course, the above is just a relatively small set of tests, against a single database. Yet, based on these results and other testing I’ve seen by Glenn Berry, I will admit that I’m VERY excited about SQL Server 2022 bringing QAT to the table to help improve BACKUP performance.

Even if you are stuck with older CPUs and do not have a QAT hardware card to offload to, QAT software mode beats legacy compression across the board.

I do need to test RESTORE next, because your BACKUPs are worthless if they cannot be restored successfully. But alas, that’s for another time and another blog post!

Thanks for reading!

Free Community Tools: sp_helpExpandView

A few months ago, Brent Ozar called for bloggers to write about about Free Community Tools in the month of September. While it’s been a while since I’ve done any work with it, I’d like to offer up sp_helpExpandView.

Does Anyone Actually LIKE Nested Views?

A number of years back, I joined a company that happened to LOVE nested views. And they also happened to have an ETL/DW loading process that literally took SEVEN DAYS to complete.

“Wow Andy, SEVEN DAYS?!? That DW must have been several terabytes! (circa 2015)”

“Uhh, no… less than 250GB at most?”

Yeah, that thing was small. But the processing was a horrific case study in T-SQL worst practices. And the architect that created it LOVED nested views (and scalar functions… and MERGE… on Standard edition).

I spent a good amount of effort trying to unravel those as part of my efforts to improve performance, and as a result, decided to create my own community tool to help with unraveling them – sp_helpExpandView.

Andy – Why the Nested View Hate?

In a nutshell, after a number of layers of nesting, cardinality estimates in nested view operators go off a cliff. And cardinality estimates play a critical role in execution plan quality, which is why it is important to have good estimates. If you’ve dug deeper into your nested view performance, you’ll see exactly what I mean.

Want to dive deeper into that topic? Then check out my Exploring Why UDFs and Nested Views Hinder the Query Optimizer presentation.

Documentation For sp_helpExpandView?

I’ve written a few blog posts about sp_helpExpandView that you can read, which I’d strongly suggest that you do.

What About Nested Stored Procs?

Secret super-tip… sp_helpExpandView also works with Stored Procedures!

Go Forth and Unravel

I do sincerely hope that this tool helps you unravel your nested views. Sure wish there was a magic tool to rewrite T-SQL to just stop using them, but unfortunately that still requires human intervention. Hopefully you’re a consultant and can get paid by the hour for this work!

Thanks for reading!

T-SQL Tuesday #153: Don’t Forget the Little Things

Welcome to another edition of T-SQL Tuesday!

This month’s edition is hosted by my good friend and former SentryOne colleague Kevin Kline (b|t). For his T-SQL Tuesday topic, he asks bloggers to “Tell us the story of how attending an IT conference or event resulted in an amazing career or life opportunity.

Been There, Done That

The thing is, I’ve already written about exactly that, when I first kicked off this blog after PASS Summit 2013: SQL Family Got Me Here. And nine years later, I’ve been blessed with a wealth of speaking opportunities, and two amazing jobs at SentryOne and Pure Storage.

Don’t Forget the Little Things

I’m not going to write about something that resulted in “an amazing” whatever. Instead I’ll share this story.

One evening, early in the week of my second PASS Summit, a friend was organizing a group to go out to dinner. I asked to tag along and they said sure, figuring the restaurant could accommodate one more. As we departed, we wound up picking up two more folks, who were also newcomers to PASS Summit and the SQL community.

When we got to the restaurant, we learned that they could not accommodate beyond the original reservation headcount at a single table, but could seat us three “tag-alongs” at our own table. Okay, that’s cool. Two of the three of us were relatively new friends and the third, I don’t quite recall if we were just Twitter friends at that point or what… but the point more was that we had a great time getting to know one another. We referred to ourselves at the “kids table.” Coincidentally because it was just the three of us and not a larger group, we managed to order, eat, and pay before the other table had even ordered their dinner!!!

Memories

That story is not an amazing life changing whatever… but it is one that has stuck with me, despite being insignificant in of itself. And the truly amazing thing is, I have many of these that I could share… mundane, but joyous gatherings of good people, who I am happy to have in my life in one way or another… and never would have met had I not gone to one of these conferences and pushed beyond my anti-social shell.

Thanks for reading.

T-SQL Tuesday #152: Consequences

Welcome to another edition of T-SQL Tuesday!

This month’s edition is hosted Deborah Melkin (b|t). Deb asks for each of us to share our personal infamous “rant” as we database professionals all have strong opinions about many things!

What’s Your Rant Andy?

Those of you who know me might think this post will wind up being about…

  • NCHAR/NVARCHAR vs CHAR/VARCHAR – aka Unicode or non-Unicode
  • Leading or trailing commas in SELECT statements?
  • All flash or spinning rust?
  • Coffee – with creamer or black?

But it won’t be about any of those! Instead, as I often like to do with T-SQL Tuesday themes, I’m going to take a slight twist and talk about what happens after you make a potentially controversial decision.

When X, then Y…

Consequences… ramifications… or afterclaps (my new favorite thesaurus-supplied alternate)… we’ve all had to pay the price of decisions that were made long ago. But sometimes we find ourselves having to make a less-than-desirable decision with only losing options to choose from.

I once worked for an Entity Framework SaaS company that was having horrific T-SQL performance issues. We put out a major release one weekend and spent the subsequent week buried in horrible performance hell. Because all T-SQL code was generated from Entity Framework (stored procedures were banned by a development exec prior to me joining the company), there were no easy fixes that could be implemented (and a Production release typically took 12 – 36 hours, I wish I was joking).

The manager of infrastructure had heard about SQL Server’s missing index recommendations:

  • Infra Mgr: Hey Ops DBA (I was a DB Dev). Are there any missing index recommendations in Production?
  • Ops DBA: Yeah, over 200.
  • Infra Mgr: Creating indexes doesn’t require us to do a Production Release. We can apply these overnight when client use is minimal.
    APPLY THEM ALL!!!!
  • Me: NOOOOOOO!!!!!

I did my best to explain why this was an absolutely horrific idea. But the Infrastructure Manager was desperate for any kind of relief from their performance woes and did not care to listen about how this could make things even worse. I was sidelined and the Ops DBA was ordered to script and implement each and every one.

And what was the afterclap? <giggle>

A couple of our key, gigantic core tables now had over 30 non-clustered indexes on them. Many of which were redundant and/or duplicate indexes as one might expect. A few queries were improved but backup times went through the roof, nightly ETL execution times doubled, and the Production database became horrifically bloated. A month later, I was finally given authorization to go through an index consolidation and clean up effort, which took me another month of workload analysis and index prioritization/consolidation.

… Then Y… but Why?

In the end, the Infrastructure Manager made a less than ideal decision, yes. But that is not what I have a problem with. What I have a problem with is that he refused to listen and understand the ramifications of the decision. He acted in a willfully ignorant manner, choosing not to listen to his subject matter experts, and instead grasp at straws in the hopes of resolving the issues. And the cost in the end was high in terms of cleaning up the bigger mess that was left behind.

Moral of the Story

So what’s the moral of the story here?

I want to encourage you to try and listen and understand the consequences of choosing X over Y. If you really want to save a few bucks and buy spinning disk instead of an all-flash SAN, understand and acknowledge the short term and long term consequences. And sometimes you do need to choose the less-than-ideal option. But if you must, I say do so with a full understanding of the afterclap.

Thanks for reading!

And now for something (sort of) entirely different… Part 2

About a month ago, Deb (b|t) wrote a blog about our newest adventure together: building a shared home lab! And now it’s my turn to blog about what we’ve done thus far.

A Bit of Background

I’m not quite sure how old I was when I built my first PC, but it was definitely greater than 5 and less than 10 years of age. In my 20’s, I had desktop builds that I ran at home as Linux web, DNS, e-mail servers, and file servers. Admittedly, as I got into my 30’s, self-hosting became less of a joy and more of a chore, so I retired most of that. But throughout the years, I’ve built many desktop PCs both for myself and others.

In more recent years, I’ve been using VMware Workstation for my lab needs. My current laptop is a fairly hefty hexa-core X1 Extreme, with 32GB of RAM and 2TB of NVMe SSD. It’s been great for my SQL Server lab needs, but with Deb needing a SQL Server lab environment beyond her laptop, it was finally time to build anew.

For our lab needs, our priority was at 128GB of RAM and at least 8-10 CPU cores for parallelism and running multiple VMs. I also wanted NVMe storage (since I’m still too new at Pure Storage to get my hands on a decommissioned FlashArray). I am fortunate to have a Micro Center near me, so I was planning on just sourcing parts and building a desktop machine to meet our needs. I had a number of friends offer their suggestions on Twitter and one privately offered me a used DL380. I hadn’t really considered a rack server, mainly due to space and heat concerns, and it would probably be overkill. While some might run their home labs 24/7, we’d probably only be turning ours on on-demand, negating the need for 24/7/365 durability and longevity. In the end, I opted to stick with building a desktop vs the DL380, mainly because it is what I was comfortable with and I’ve known PCs my entire life.

Hardware: So What’d Y’all Buy?

  • Intel i7-12700K Alder Lake 3.6GHz Twelve-Core CPU
  • ASUS Z690M-PLUS Prime microATX motherboard
  • G.Skill Ripjaws V 128GB DDR4 RAM
  • 2x Samsung 980 Pro 2TB NVMe SSD
  • Cooler Master ML240L Water Cooling Kit
  • Thermaltake Versa H17 mini-tower case
  • G.Skill 750w power supply

Software: So What Will You Be Running?

Now that we had hardware, the next question most think about is what operating system? Many would default to Windows but because this machine is intended as a home lab, the more important question became which Hypervisor? Do we remain with Windows 10/11 and keep using VMware Workstation for our VM needs? Or embrace Windows fully and learn Hyper-V? Considering so many organizations run their SQL Servers on VMware, I decided to do some research about a full-blown VMware installation.

Let me preface this by saying that I’m a VMware novice at best. I’ve encountered it almost daily for many years, but never had the opportunity to truly manage or work with it until I got to Pure. I’m familiar with general virtualization concepts, which I’ve used with VMware Workstation. But I’ll also admit that until very recently, I had no idea what the actual difference was between vSphere, vCenter, and ESXi (platform, management software, and hypervisor). And the other key thing I never knew was that ESXi is essentially a standalone OS (on top of Linux I think?). But I knew as part of this endeavor, I wanted to use it as a gateway to learn more VMware.

I started to research vSphere costs and discovered that there’s a vSphere Essentials Kit that’s reasonably priced and geared towards small businesses. I thought that that’d be perfect for us, even though the fact that it’s good for 3 ESXi hosts meant it was a bit of overkill for us since we’d only have one ESXi host. Then I discovered that there’s a FREE version of ESXi, good for a single host! The key drawback is that vCenter is not available. I figured I’d go with that, and if we ever had a need for vCenter functionality, we’d make the investment in the Essentials Kit.

Behold: We Have a Home Lab!

And with that, we now had a machine running ESXi. In my next blog, I’ll write about some of the first things I did, to get the lab operational and some of the hiccups I ran into along the way. Until then, thanks for reading!

Pure Storage FlashArray

A Peek Inside a SQL Server Backup File

Confessions First

In all of my years working with SQL Server, I never really thought about the actual contents of a SQL Server backup file. Sure, it contains data from a given database, but despite my love of storage engine internals, backup file internals is not something I’ve ever had any interest in looking into.

Until now…

The Premise

This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.

Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.

Why Do You Even Care?

There’s a bit of Pure Storage related background here, so feel free to skip this section if you don’t care about why I’m writing this.

On FlashArray, we de-duplicate your data behind the scenes across the entire array. So if you had three SQL Servers (Prod, QA, Dev) all attached to a given FlashArray, and each instance had an identical copy of AdventureWorks, it would almost completely dedupe down to one copy on FlashArray.

Along those lines, a single database will have many places where deduplication can also occur within it. Think about how much repetition occurs within a stereotypical dataset. Things like dates, product IDs, product category IDs, etc. are all duplicated throughout a typical dataset, thus ripe for FlashArray to dedupe within your data file too.

But much like the data that resides in each of our databases, there’s a great degree of variability too. You may have a database where practically everything is unique. You may have a database that stores binary data. The list goes on and on. So while we see a certain average deduplication ratio with SQL Server databases, that’s AVERAGE. And often our customers want to know what THEIR database will yield.

And this is where a backup file comes into play.

One trick that Anthony taught me is to provision an empty volume on FlashArray and take a single uncompressed, unencrypted backup of your database and stick the file there. Because the backup file contains a byte-for-byte copy of your data, as materialized in your MDF/NDF files, its dedupe yield will be very close to that of your MDF/NDF files.

Great way to test, huh? Unfortunately the individual I was speaking with was not confident about the underlying byte-for-byte composition of a backup file. So I decided to test, validate, and document it!

Testing Setup

Using SQL Server 2017, I created a simple database with a single table and inserted some data.



Then I created an uncompressed, unencrypted backup file. Finally, I shut down SQL Server’s service and copied the MDF and BAK to another location to begin analysis.

All code I used can be found here: https://github.com/SQLBek/PureStorage/tree/main/backup_test

So What’s Inside?

To quickly analyze differences, I found a cool piece of software called Beyond Compare that has a “Hex Compare” feature – perfect for binary file comparison!


Click for high-res


To give you a quick overview, the left sidebar shows an overview of the two files, with red lines/blocks to designate some kind of difference in the file. In the example screenshot, the left is the MDF file and the right panel is the backup file. This is the beginning of each file, so you can see that there are some differences present here.

Why Is More Than Half Red?!

However, look closer at the sidebar. The first half has very few differences. But what about that second half that’s ALL RED?


Click for high-res


At least that answer is easy. All of those 00’s is simply extra empty space that has been padded at the end of the MDF file. And because it has nothing, it has been omitted from the backup file. I could have truncated the data file first, but I kept this here to illustrate that one’s data file may be larger than the backup file due to this nuance.

Okay, Let’s See the Data


Click for high-res


As for the data itself, that’s present in the 2nd quarter of the MDF file or final 3rd of the backup file. And you can see from this screenshot that the backup file is in fact a byte-for-byte copy of the MDF file!

Takeaways

First, I hope that this is enough to prove that data in a database are re-materlized byte-for-byte in a backup file. Sure, there’s some differences in other metadata, but what I care about in this exercise is whether the data itself is identical, which it is.

Second, if you are still in doubt, I’ve published everything to my github here. If you look inside backup_test.sql, you’ll find some extra code in the form of DBCC IND and DBCC PAGE commands. Instead of searching for data, try using DBCC IND and find a different data structure like an IAM page. Then use DBCC PAGE to look at the raw contents and use the hex editor to search for the matching binary data in both the MDF and backup file. I did that myself and found it cool that those underlying supporting pages are also materialized identically.

Third, if you see a hole or gap with this analysis, please let me know in the comments. I did this to learn and validate things for myself, and I definitely want to know if I made a goof somewhere!

Finally, I hope you enjoyed this and stay curious.

Thanks for reading!

T-SQL Tuesday #150: My First Tech Job

Welcome to another edition of T-SQL Tuesday! This month’s host is Kenneth Fisher and Kenneth asks for bloggers to write about “Your first technical job”

Something A Little Different

I suspect that most will write about their first jobs after college. But for me, I’m going to go further back to senior year of high school.

Took Economics as an elective and it was a really awesome class. Got along really well with my teacher, who knew I was a computer nerd. At some point, she told me that her (adult) daughter needed a copy of WordPerfect and a CD-Rom drive installed in her home desktop and asked if I’d be willing to do the job for her. Sure, why not? I was already acing that class, so it’s not like I needed extra credit or brownie points. She said she’d pay me but never said how much. I didn’t really care; I was eager to help.

Drove over to her daughter’s apartment, installed the CD-Rom drive & WordPerfect, gave them a tour of the software, chatted about Sierra adventure video games with her boyfriend/husband, and left. They handed me an envelope and it had $150 in it! To high schooler Andy, that was a TON of money for an hour of my time. And I I suppose that makes that my “first technical job.”

What’s So Significant About That?

Why am I writing about that story? Because that first job was something I enjoyed and I was eager to help. I knew I was getting paid, but I didn’t really care how much.

Flashing forward more years than I care to admit, and I can say that I’ve been blessed with a career where I’ve been able to continue to work with technology that I love, and help people along the way, all while getting paid to do it. It’s taken a great deal of work and there’s certainly been some rough patches, but I appreciate and am grateful that I continue to be able to help people day in and day out.

What About You?

If you’re reading this, then I would challenge you to think over your career. Do you have an earlier story that helped to shape or foretold who you have become today?

Thanks for reading.

T-SQL Tuesday #149: Less Is More… Or Is It?

Welcome to another edition of T-SQL Tuesday! This month’s host is Camila Henrique and Camila asks for bloggers to write “T-SQL Advice you’d give to your younger self.”

Less Is More

I studied Computer Science in college, then my first job out of school I was a combination internet developer and sysadmin. I was taught “less is more” when it came to writing code, as many of us were.

So when I started working with T-SQL, I started crafting gigantic, do-it-all T-SQL statements. So while the statement/query itself might have been many lines of code, it was just one single discrete command. So less is more, right? Better to only send over one query rather than a bunch, right?

… Or Is It?

The “less is more” bias was turned around when I started to learn about the storage engine and query optimizer internals. As I talk about in many of my presentations, I’d rather send a bunch of simple queries to the optimizer rather than one single gigantic one (generally speaking).

There’s many reasons for this but one key one is that the query optimizer’s end goal is NOT to come up with the best execution plan possible for a given query, but to create one that is good enough as quickly as possible. And that the query optimizer has a limited amount of time before it stops permutating and just goes with whatever is the best “draft” it has at that point.

When It Comes to T-SQL

My TL;DR advice… “Less Is More… work for the query optimizer.” Instead, keep your queries simple, even if it means sending more discrete queries to the optimizer. Break it down into intermediate steps. It may be counter to other programming languages but in the world of T-SQL, Less is NOT More.

T-SQL Tuesday #148 – Building Community

This month, Rie Merritt is asking us to share advice about User Groups. I had the honor of running the Chicago Suburban User Group for over 6 years.

Build Community

My advice is something I stumbled upon by complete accident. Prior to our first meeting, we sent out a SurveyMonkey asking attendees what they wanted to get out of these meetings. Networking was a top answer. And on the drive over to our first meeting, I came up with an idea.

When we kicked off, I explained the survey results wanting networking, so I simply asked everyone to introduce themselves to whomever they were seated by. I said just share your name, your role, and what you do. I expected this to last maybe 2-3 minutes tops. Instead it transformed into 15-20 minutes of chatter!

As our group evolved, we offered food but had to locate to a common area to eat (food wasn’t allowed in our classroom). So I’d repeat the “introduce yourself” bit, remind people that this is their time to chat, etc. I always made the joke that “if you’re an introvert, you’re in luck… almost all of us in the room are!” And I said that if you cannot think of anything to talk about, just vent about something you’ve been struggling with at work lately – we ALL have work headaches and challenges.

And it worked. It worked really… really… well! I’m very proud of the community that we built with the Chicago Suburban User Group.

T-SQL Tuesday #146 Round-up! Upending Preconceived Notions

Hello everyone!

First of all, I want to thank everyone who took some time to participate in this month’s T-SQL Tuesday: Upending Preconceived Notions. There’s a fantastic line-up of contributions! I was tickled that many of the topics chosen were things I once held one viewpoint on, later learned more, and evolved that viewpoint.


That was a lot of fun. And I’m happy to say that I learned some stuff.

Thank you again to everyone who took some time to contribute a blog post!

Dear readers, I hope you take the time to read each and every contribution, and come away learning something new too!