Press "Enter" to skip to content

Category: Wacky Ideas

Fun with GO and Preventing SQL Batches from Parsing

Solomon Rutzky has an apropos post for Friday the 13th:

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

Read on to see how you can use the GO operator to prevent many SQL Server client tools from even noticing a block of text.

Comments closed

The End of the NFT Bubble(?)

Stephanie Glen has music to my ears:

Non-fungible tokens (NFTs), tradable digital certificates that verify ownership of digital assets using blockchain technology, have dominated headlines in the last several months. The media mania hit a high with the $69 million sale of Beeple’s Everydays:The First 5000 Days. A few months after Beeple’s historic sale at Christie’s auction house, the crypo-art bubble has officially burst.

These sorts of things are a bit too volatile for me to cheer just yet. The blockchain bubble is something I look at and say, this is incredibly dumb. The whole premise of it makes zero sense: you’re wasting resources (and don’t get me started on Chia, the grim reaper for residential SSDs) for nothing. The end product has extremely little to no subjective value—how much would you pay for blockchain outputs?—but burns up resources in the form of energy, increased prices for computer components, and time that could have been spent doing something more productive, like repeatedly turning your computer off and on again: at least there, you gain valuable skills in figuring out how to power down and power up a machine.

I can kinda-sorta get the idea of using blockchain for certain types of auditing trails, but there are still two big problems with it. First is the 50% problem: whoever controls 50% of the compute controls the past, present, and future of the blockchain and can make whatever arbitrary changes are desired. Beyond that, the other problem is, how much better is this than a digest hash of activities written to a WORM drive? Considering how many orders of magnitude less expensive the latter is to the former, there has to be an enormous benefit for it to make any sense. And there’s really not.

Comments closed

Running a Docker Container as a WSL2 Distribution

Andrew Pruski has a wacky idea that just might work:

I’ve been playing around a bit with WSL2 and noticed that you can import TAR files into it to create your own custom distributions.

This means that we can export docker containers and run them as WSL distros!

So, let’s build a custom SQL Server 2019 docker image, run a container, and then import that container into WSL2…so that we have a custom distro running SQL Server 2019.

Read on to see how.

Comments closed

Fun with Disabling Joins

David Alcock gives us a “wouldn’t it be neat to see this?” scenario:

If you’ve ever seen my session on Guillotines and the Query Optimiser I demonstrated how we can use query hints to switch off certain transformation rules in a query. Transformation rules use a pattern substitution which essentially swaps one expression for another. In SQL Server terms the best example of this is a join as although we use the logical expressions such as left join or inner join the physical join type used in the execution plan operator is substituted to be something like a Hash Match or Nested Loop Join.

This means we can force the behaviour of an execution plan by disabling certain rules. Now I can’t think of any practical reason for this and remember we can also use join hints in our queries but this is different as it affects the entire optimisation process for a query and not just one join so please follow in a sandbox environment.

In case you haven’t seen David’s session, it’s available on YouTube.

Comments closed

Fixing Those Pesky Wait Stats

David Alcock keeps us from having to think:

CXPACKET

Attempt to pronounce parallellellellellism correctly then set MAXDOP to 1.

PAGELATCH

Mention TempDB and contention in the same sentence. Delete TempDB

BACKUP_

Delete any long running backup jobs. If wait persists then delete all backup jobs.

Click through for plenty of excellent nuggets of advice which definitely won’t land you on the unemployment line.

Comments closed

Visualizing Seinfeld

Andy Kirk walks through a brand new book:

This project is a compilation of visual analysis I’ve been working on for a couple of years to pursue a curiosity I had about the rhythm and texture of Seinfeld, looking into how it was written and how it was produced.

The musicality of language is something defines the signature comedic talent of Jerry Seinfeld and, co-creator, Larry David. Like music, situation comedy has rhythm and it has texture. The rhythm is shaped by its temporal structures and pacing; textures are formed from the arrangements of characters and locations which amalgamate into the comedy. All orchestrated by the storylines and dialogue emerging from the writers.

It is an exploration of the show’s musicality that forms the purpose of this book. It presents a portfolio of visuals produced, intended for people who share my Seinfeld fanaticism. It is a body of work akin to giving the show something of a sports analytics treatment, investigating how Seinfeld and David utilised their resources – the people, places, and the dialogue – to translate their creativity from the page and onto our screens.

It’s an interesting look at the show and there will be a limited run on physical copies of the book.

Comments closed

Messy Code and Reasonable Expectations

Rachel by the Bay has a doozy of a story:

One day not so long ago, I was in a meeting listening to a team explain why their service had gone down and taken out a big chunk of a business. They were one of those things that has to exist and work in order for the actual “thing that makes money” to go. Think of delivering pizzas, connecting dog walkers with dogs who need to be walked, that kind of thing.

It turned out they had been crashing every time a request came through for a certain part of the country. That is, not all pizzas, dog walkers, or whatever it was were handled identically, so they had their own city or region configurations. Think of differences in pricing, taxes, features, or whatever. Trying to process a request for this one particular region had caused the entire process to die when it hit a new config that was “bad” somehow.

Read on for the story. This sounds like a boundary issue. Boundaries are messy and need thorough examination to handle as many possible points of failure as is reasonable. Taking seriously the point that it makes the code messy, the answer is not “Don’t do the checks,” but rather “Put the checks in a place where their messiness has a minimal impact on the rest of my beautiful code but still does the important work we need them to do.” Failing that, live with the mess and have a working process.

Comments closed

A Trillion-Row Operator

Joe Obbish sets up a challenge:

48 billion rows for a single operator is certainly a large number for most workloads. I ended up completely missing the point and started wondering how quickly a query could process a trillion rows through a single operator. Eventually that led to a SQL performance challenge: what is the fastest that you can get an actual plan with at least one operator processing a trillion rows? The following rules are in play:

1. Start with no user databases
2. Any query can run up to MAXDOP 8
3. Temp tables may be created and populated but all such work needs to finish in 30 seconds or less
4. A RECOMPILE query hint must be present in the final query
5. Undocumented features and behavior are fair game

Read on to see what Joe learned.

Comments closed