Press "Enter" to skip to content

Month: August 2021

Compressing JSON in SQL Server

Published 2021-08-26 by Kevin Feasel

Randolph West has a recommendation:

I’ll also pre-emptively note that if this table was simply an append-only archive table, the row size would not really matter. Unfortunately, this table participates in thousands of transactions per day, and as the original developers used Entity Framework and didn’t think much of using NVARCHAR(MAX), the entire row is coming over the wire into the application each time it is queried.

As I’ve written previously about this kind of thing, this is not a good design pattern. Using the VARBINARY(MAX) data type with COMPRESS in the INSERT/UPDATE queries — and DECOMPRESS in the SELECT queries — is a much better design pattern and dramatically reduces the amount of data transferred over the network. Additionally, SQL Server needs significantly less space to store, maintain, and back up this compressed data.

Read on to see the likely benefits from doing this. I’d say that if your main purpose of storing the JSON is just to pass a blob back and forth, then yes, do compress. If you’re frequently shredding these sorts of large documents within SQL Server…well, probably time for a better data model.

Comments closed

SQL Server Monitoring via Zabbix

Published 2021-08-26 by Kevin Feasel

Reitse Eskens digs into using Zabbix to monitor SQL Server:

In one of the projects I’m working in, we needed to have some sort of monitoring solution on SQL Server, but there wasn’t budget for a commercial monitoring solution. There’s a small number of freeware, open-source solutions but these are all difficult to get working. In this blog I’ll show you what Zabbix has on offer as a default and what you can add yourself.

I’m not the biggest fan of Zabbix, but if it’s what you have, better to use the tools you have than not.

Comments closed

Optimizing String Split and Search

Published 2021-08-26 by Kevin Feasel

Daniel Hutmacher needs things to go faster:

One of the things that sp_ctrl3 does is plaintext database search. If you pass a string to the procedure that does not match an existing object, it’ll just perform a plaintext search of all SQL modules (procedure, views, triggers, etc) for that string. The search result includes line numbers for each result, so it needs to split each module into lines.

I’ve found that this takes a very long time to run in a database with large stored procedures, so here’s how I tuned it to run faster.

Read the whole thing.

Comments closed

Seeking SARG

Published 2021-08-26 by Kevin Feasel

Erik Darling leaves no man behind and is seeking Sarge. On day one, Erik briefs the crew:

SARGability is the in-club way of saying that a search predicate(s) can be used to seek through the key(s) of an index.

On day two, the crew use the power of math to get past a blockade:

Let’s say we’re doing this to audit short questions and answers for quality.

Since SQL Server doesn’t retain any precise data about string column lengths, we don’t have an effective way to implement this search.

Worse, since the Body column is a max datatype, no expression (SARGable or not) can be pushed to the index scan.

On day three, they enter the fetid jungles of tempdb:

In all, the query runs for about 50 seconds. This can be avoided by hinting a hash join, of course, for reasons explained here.

But good luck figuring out why this thing runs for 50 seconds looking at a cached, or estimated execution plan, which doesn’t show you spills or operator times.

Stay tuned for the thrilling conclusion to Seeking SARG.

Comments closed

Dealing with Non-Yielding Schedulers

Published 2021-08-26 by Kevin Feasel

Sean Gallardy breaks up the party:

One of the most common items that will cause a memory dump in SQL Server is a non-yielding scheduler (generally referred to as NYS). What the heck does that mean? Why would it cause a memory dump? Is there anything that can be investigated? Good questions, let’s take a look.

Read on to learn what these are, why they’re not something you want to deal with on a regular basis, and how you can get more information on what happened out of a dump file. Which is also going to be helpful for Microsoft staff to diagnose and correct the underlying issue (if possible).

Comments closed

Full Query Logging in Cassandra 4.0

Published 2021-08-25 by Kevin Feasel

Shelby Carpenter explains a new feature in Apache Cassandra 4.0:

First off, we need to understand what counts as a full query log (FQL) in Cassandra. Full query logs record all successful Cassandra Query Language (CQL) requests. Audit logs (also a new feature of Cassandra 4.0), on the other hand, contain both successful and unsuccessful CQL requests. (To learn about the different forms of logging and diagnostic events in Cassandra 4.0, check out this blog by Instaclustr Co-Founder and CTO Ben Bromhead.)

The FQL framework was implemented to be lightweight from the very beginning so there is no need to worry about the performance. This is achieved by a library called Chronicle Queues, which is designed for low latency and high-performance messaging for critical applications.

Read on for information on use cases as well as how you can configure and access full query log information.

Comments closed

Loops in Powershell

Published 2021-08-25 by Kevin Feasel

Robert Cain takes us through different structured programming constructs in Powershell:

Continuing my series on PowerShell basics, in this installment we’ll talk bout looping in PowerShell. Looping is a mechanism for repeating a set of instructions multiple times, ending once a condition is met. Looping is also known as iterating, as in “iterating over an array”.

Loops continue until one of two things happen. In one type of loop, the loop continues until a certain condition is true. In the second type, the loop will iterate over a collection (also called an array) of objects until the entire collection has been gone through.

Read on for an enumeration of the different types of loops you can create with this language.

Comments closed

Dealing with Corruption around In-Memory OLTP

Published 2021-08-25 by Kevin Feasel

Chris Taylor has a tale of woe:

Late last week (20th) we had an emergency call from a company that had a production database go into Suspect mode and needed help. Now this isn’t a great situation to be in so when they then go on to tell us that the last valid backup they had was from the 12th and the backup job had been failing since then – even less of a great situation to be in

Read the whole thing.

Comments closed