A Fine Slice Of SQL Server
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 closedDavid 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 closedDavid 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 closedTomaz Kastrun asks, Shall we play a game?:
Naaah! not a joke. But quarantine restrictions are getting tighter and you might want to spent playing some useless game with your R engine, whilst programming, doing machine learning or other learning.
Click through for a simple implementation of rock-paper-scissors in R.
1 CommentAndy 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 closedRachel 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 closedJoe 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 closedJeffrey 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 closedTomaz 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 closedBert 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