Press "Enter" to skip to content

Curated SQL Posts

SQL Server Baselines with the TIG Stack

Published 2021-07-15 by Kevin Feasel

Mark Wilkinson combines Telegraf, InfluxDB, and Grafana:

Lots of folks wonder why I would go through the trouble of building out a system when so many vendors have already solved the problem of collecting baseline metrics. The answer at the time was simple: cost. With my setup I could monitor close to 600 instances (including dev) for $3,000 USD per year. That includes data retention of ~2 years! Are there some administration costs as far as my time is concerned? Of course. In the begining things were a little rough as I learned more about InfluxDB, but once things were configured correctly the most work I’ve had to do is to expand the size of the data drive as we started collecting more metrics.

Click through for more info and check out the GitHub repo.

Comments closed

Power BI Report Server and Query Authentication

Published 2021-07-14 by Kevin Feasel

Emanuele Meazzo shows us the power of configuration:

With the end of the IE support for Power Bi (and in general tbh), companies are scrambling finally to move their users from the legacy browser to modern ones; it was about time if you ask me.
However, there’s an edge case where using anything but IE is not as straightforward as it could be; in my case Power Bi RS worked fine for any report in any browser, except with direct query reports that were set up to authenticate via Windows Authentication as the user viewing the report:

Read on to see how to fix this so that it works well in browsers like Edge and Chrome.

Comments closed

Filtering Unique Objects in Powershell

Published 2021-07-14 by Kevin Feasel

Jeffrey Hicks solves an interesting problem:

A few weeks ago my friend, Gladys Kravitz, was lamenting about a challenge related to filtering for unique objects. PowerShell has a Get-Unique cmdlet, and Select-Object has a -Unique parameter, but these options are limited. On one hand, I’d say most things we manage with PowerShell are guaranteed to be unique. Objects might have a GUID , ID, or SID, to guarantee uniqueness. But, and this is Gladys’ situation, sometimes the things we are managing come from an external source. Such as importing data from a CSV file. In this situation, it is definitely possible to have duplicate objects.

This turns out to be a bit harder than expected.

Comments closed

Identify SQL Server Configuration Drift

Published 2021-07-14 by Kevin Feasel

Garry Bargsley won’t let the cattle become pets:

As you sit and wonder about when the next Star Wars movie is going to come out, do you ever get the thought of “I wonder if all my SQL Servers are configured the same?”

Occasionally, I get a thought like that run through my mind. Or I might see something on Twitter or Blog post about something, and it sparks the question.  Not about Star Wars, but my SQL Server environment.

Today something triggered me to confirm that all of my SQL Servers had the default backup compression setting set to enabled.

Garry mentions dbachecks at the end, and it’s a really good way of performing a fairly large number of such checks easily.

Comments closed

Buffering Events in SQL Server

Published 2021-07-14 by Kevin Feasel

Eitan Blumin has a technique to reduce expensive upserts:

Do you find yourself facing performance problems and long lock chains caused by very frequent INSERT, UPDATE, or DELETE statements being executed on a table? Check out this neat trick that could help you out and make all the difference in the world.

Okay, I admit that title ended up being quite long. But I wanted something that could be easily found in search engines by people facing similar problems.

I’ve done something similar, though without the partition switch and instead deleting batches into a temp table. This is a good example of something I like to say about scalable processes in T-SQL: many times, the most scalable technique involves a mental pivot (and sometimes a literal pivot, such as using tally tables to work with string data) of the straightforward answer.

1 Comment

Reviewing the ConstantCare Population Report

Published 2021-07-14 by Kevin Feasel

Brent Ozar surveys the landscape:

Ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates, hardware sizes, or numbers of databases? Let’s find out in the summer 2021 version of our SQL ConstantCare® population report.

Click through for Brent’s findings. It’s interesting to compare these against Steve Stedman’s findings, and these come with the same caveat about population.

Comments closed

Ending Mainstream Support for SQL Server 2016

Published 2021-07-14 by Kevin Feasel

Allan Hirt gives us the skinny:

Today is July 13, 2021. Besides the anniversary of Live Aid in 1985 (had to sneak a music reference in somewhere!), today marks an important date: the end of mainstream support for SQL Server 2016. The last Cumulative Update was CU17 released on March 29, 2021. UPDATE: Thanks to Glenn Berry (Twitter | Blog), he pointed out that Microsoft announced that SQL Server 2016 Service Pack 3 is coming around September 2021 give or take.

Windows Server 2016 is on a similar trajectory. It is out of mainstream support on January 11, 2022 – less than six months from now.

What does this mean for you?

Read on to learn what it means.

Comments closed

Fun with Containers

Published 2021-07-14 by Kevin Feasel

Barney Lawrence has some fun with containers, starting with running SQL Server and ending with something a little more entertaining:

Many of you out there may, like me, find yourselves sharing a home with a miniature Minecraft Addict. I do and in between showing off her latest builds she’s begun pestering me to find a way to play with friends and family. I figured I could maybe save some work and re-use my newfound containerised powers for fun as well as profit and a few quick searches proved me right.

Click through for more.

Comments closed

OpenSearch 1.0 Released

Published 2021-07-13 by Kevin Feasel

Andrew Hopp, et al, announce version 1.0 of OpenSearch:

OpenSearch is a community-driven, open source search and analytics suite derived from Apache 2.0 licensed Elasticsearch 7.10.2 & Kibana 7.10.2. It consists of a search engine daemon (OpenSearch), a visualization and user interface (OpenSearch Dashboards), and advanced features from Open Distro for Elasticsearch like security, alerting, anomaly detection and more.

Click through for the full rundown.

Comments closed

Fun with Powershell Strings

Published 2021-07-13 by Kevin Feasel

Robert Cain shows off some features around strings in Powershell:

PowerShell has some of the best, most flexible string handling of any language I’ve used. In this post we’ll look at some string basics, then some of the features that make it special.

Note in these demos I’m using PowerShell Core 7.1.3 and VSCode. Everything here should also work in the PowerShell 5.1 and the PowerShell IDE.

Read on for types of strings, embedded quotations, string interpolation, and more.

Comments closed