Press "Enter" to skip to content

Category: Wacky Ideas

Fun with Disabling Joins

Published 2021-03-12 by Kevin Feasel

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

Published 2021-02-22 by Kevin Feasel

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

Published 2020-09-28 by Kevin Feasel

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

Published 2020-09-04 by Kevin Feasel

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

Published 2020-08-13 by Kevin Feasel

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

Creating Nonsense Documents with Powershell

Published 2020-08-10 by Kevin Feasel

Jeffrey Hicks has a nonsense generator:

Today I thought I’d share my PowerShell solution to a recent Iron Scripter challenge. The challenge was to create PowerShell code that would create nonsense documents, with a goal of creating 10 sample files filled with gibberish. Yes, other than maybe wanting some test files to work with, on its face the challenge appears pointless.  However, as with all of these challenges, or even the ones in The PowerShell Practice Primer, the journey is the reward. The true value is learning how to use PowerShell, and maybe discovering a new technique or command. The hope is that during the course of working on the challenge, you’ll improve your PowerShell scripting skills. And who knows, maybe even have a little fun along the way.

It’s not quite up to the level of quality that you find in post-modern academic papers, but it’s getting there.

Comments closed

Storing SQL Server Database Files in Blob Storage

Published 2020-07-30 by Kevin Feasel

Tomaz Kastrun has a wacky idea:

Storing SQL Server database files in Azure blob storage is a great solution for all the databases that are often migrated between instances, servers, virtual machines, or would have been divided between instances. This scenario also has the positive aspect to it, since the ability to create snapshot backups to Azure is seamless.

Following the steps, we will create a Azure Blob storage, where MSSQL Server database files will reside with MSSQL Server running on-prem. Assuming, that you already have the Azure account (if not, you can get a free Azure account), let’s proceed by opening the Windows Terminal in PowerShell mode.

I’m impressed that it worked and could see it being an option for small demo databases, but I can’t imagine performance would be good enough for a production scenario.

Comments closed

Making a Heap Molehill out of a Heap Mountain

Published 2020-07-22 by Kevin Feasel

Bert Wagner needed to move a large subset of a heap into its own heap:

Recently I had to filter out 1.2 billion records from a 3.5 billion row heap. Don’t ask me why this 3.5 billion row table is stored as a heap.

If the lack of a clustered index wasn’t bad enough, I also had some other restrictions:

– I couldn’t add a clustered index (or any index for that matter) sorted on the key I needed to filter on. It wasn’t my system, and and I needed to access the 1.2 billion records sooner than it would take to get a clustered index approved and added. Even then, maybe the lack of a clustered index on this table is a feature and my request would be denied. Who knows.
– I didn’t have a server that could store all of the data. At first I thought of copying all 3.5 billion rows to my own server and indexing it how I needed, but I didn’t have enough storage space anywhere to do that.
– My connection to the server had a relatively short timeout set on it. This also couldn’t be changed. If I couldn’t copy all 3.5 billion rows because of storage, I also couldn’t copy all 1.2 billion records in one fell swoop because the connection would timeout.

I’m also glad to see that Bert is back in action and look forward to seeing these posts and videos pop up again.

Comments closed

Unsupported Versions of SQL Server on Windows Server 2019

Published 2020-06-26 by Kevin Feasel

Randolph West finds a need and fills it:

Last year when Windows Server 2019 was released I wanted to see which versions of SQL Server I could run on it, testing more the unwritten backward compatibility promise Microsoft has maintained over the last 45 years, rather than what the documentation says.

Speaking of documentation, Glenn Berry has a nifty compatibility matrix to show what versions of SQL Server are supported on each version of Windows Server. For official purposes, this is the list you should refer to:

But I know you’re not here for supported versions, because this post is about what Randolph managed to get running on Windows Server 2019, which as you know is a 64-bit operating system.

SQL Server 6.5 surprised me. This idea also nets my most coveted category.

Comments closed