Press "Enter" to skip to content

Curated SQL Posts

From SQL to Functional

Published 2021-02-24 by Kevin Feasel

Shel Burkow has the start of an interesting series:

Neither loops nor branches were used in any of the T-SQL work, but that doesn’t mean they aren’t there. They are – visible underneath in the query plans, which describe the process steps (operators and data flow) the SQL Server database engine follows to produce results. A salient example of looping is the Nested Loops operator used in the T-SQL rewrite query plan to match each outer row to an inner row, i.e. perform the INTERSECT. This style of coding, in which the logic of the computation is expressed rather than the lower-level control flow detail, is declarative programming, and is a major theme of this series. (The latter being imperative programming.)

This is a concept I’m really big on: I think functional programming languages are easier for data platform developers to pick up than object-oriented or imperative languages for the reason that both are declarative, so many of the programming metaphors for one apply to the other.

Leave a Comment

Elastic Beats and the ELK Stack

Published 2021-02-24 by Kevin Feasel

Shane Ducksbury explains where Elastic Beats fits in the ELK stack:

After my last blog post about Logstash, Elasticsearch, and Kibana, I wanted to investigate something else I kept coming across during my Logstash research: Elastic Beats.

Beats initially appeared to me to be a way to send data to Elasticsearch, the same as Logstash, leading me to wonder how Beats is different and where it fits in the ELK stack. In this blog, I’ll take a deeper look at Beats to understand how it works, what you might use it for, and how it compares with Logstash.

Read on to learn more about Elastic Beats and how this is quite different from Logstash.

Leave a Comment

A Warning on Using Distributed Network Names

Published 2021-02-24 by Kevin Feasel

Allan Hirt has a warning for us:

DNNs are supported as of SQL Server 2019 CU2 and require Windows Server 2016 or later. I wrote more about them in my blog post Configure a WSFC in Azure with Windows Server 2019 for AGs and FCIs. Go there if you want to see what they look like and learn more.

Right now, I cannot wholeheartedly recommend the use of DNNs for listeners or FCIs if you are using Enterprise Edition. Why?

Read on to learn why.

Leave a Comment

Deploying Bacpacs to Azure SQL Database via Terraform

Published 2021-02-24 by Kevin Feasel

John Martin shows how to deploy a database schema (in bacpac format) via Terraform:

It’s all well and good deploying Azure SQL Database resources as we did in the previous post. However, databases tend to work a little better with a schema and some data in them. One of the options for getting data from an on-premises SQL Server database into Azure SQL Database is via a bacpac. This is, at its core, an export of the schema and data into a single file which is then created and loaded to Azure SQL Database. Much the same as a MySQL dump operates.

Read on for one way to do this.

Leave a Comment

Memory Grant Feedback in SQL Server

Published 2021-02-24 by Kevin Feasel

Deepthi Goguri hits on one part of Intelligent Query Processing in SQL Server:

In this part, let us focus on the Memory Grant Feedback feature released in SQL Server 2017 for Batch mode execution and in SQL Server 2019 for Row mode execution.

Memory Grants are used by the SQL Server for the Hashes and Sort operations. SQL Server optimizer uses the statistics information and allocate the memory needed by the query before the query executes. When the query is executed, SQL Server uses the allocated memory to process the query for the hashes and sorts. If this memory grant is not enough to process the query, data will use tempdb spilling to disk. When too much memory is allocated based up on the estimates, we can effect the concurrency as all other queries requires memory grants to process the queries as well. Bad estimates can effect the memory grants allocated to the queries. Too much or too little memory grants is bad.

Read on to see how Memory Grant Feedback helps the optimizer out with queries over time.

Leave a Comment

Documenting dm_db_missing_index_group_stats_query

Published 2021-02-24 by Kevin Feasel

Erik Darling does a good deed:

When I was checking out early builds of SQL Server 2019, I noticed a new DMV called dm_db_missing_index_group_stats_query, that I thought was pretty cool.

It helped you tie missing index requests to the queries that requested them. Previously, that took a whole lot of heroic effort, or luck.

This has long been a pain point for index-based query tuning. You can guess what the types of queries can look like based on the suggested columns, but one risk of that guessing is that index order matters but the columns get returned in the order in which they exist on the table, not necessarily the order in which they would be most useful.

Leave a Comment

Updating Word or Powerpoint with Power BI Data

Published 2021-02-24 by Kevin Feasel

Stephanie Bruno solves an interesting problem:

In my case, colleagues are required to develop reports every quarter with the most updated data we have and with a lot of commentary included. For example, they create the same 60-page document every quarter with the same tables and charts, and then modify the narrative depending on the data. For this reason, paginated reports may not be the right solution because the narrative varies so much. They also have a very particular format for the charts and tables that is hard to reproduce in Power BI. Finally, the data may be changing up to the day before the report is due. Their process in the past was to export the data they needed from Power BI, open it in Excel, copy and paste to another file where they had all of their charts built, export/copy/paste from more visuals, tweak the charts, then copy and paste the charts into their Word document. Then work late into the night doing this a few more times as the data is updated.

To help free them from this tedium, we worked out a new process to get their Word and PowerPoint files automatically updated in the format they required, using our good friend, “Analyze in Excel.”

Click through to learn how.

Leave a Comment

SSD Bit Rot

Published 2021-02-23 by Kevin Feasel

Jonathan Kehayias explains why SSDs aren’t good long-term backup devices:

Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number of external drive caddy’s with SSD’s that I had replaced and just had sitting around. Ultimately I started to use those SSD’s for storing information that I didn’t really need to have on my laptops and when they were full, they ended up in my desk drawer, where they have sat, unplugged and “safe” for the last 7-8 years. Or so I thought. With cheer competitions season in full swing, one of the things I love to do is shoot photos of my kids and their teammates competing, and storing RAW files that are 25-35MB per photo when you shoot 1000+ photos in a weekend across four different teams starts to take up a lot of space, so I figured I would pull out the old SSD’s and see what was on them that was worth keeping, delete what wasn’t and I could then move last years RAW files over to them and archive them for safe keeping. WRONG!!!  Of the four SSD’s I had stored data on, 100% of them had data loss due to a phenomenon known as bit rot. One of them wouldn’t even show up in Disk Manager in Windows and had to be low level formatted and reset using diskpart’s clean command due to partition table corruption.

This is a nasty scenario. Read on to learn more about how you can detect the issue and keep in mind the 3-2-1 rule: 3 copies of your data in at least 2 storage media, at least 1 of which is offsite.

Leave a Comment

Tips and Tricks for VS Code and Azure Data Studio

Published 2021-02-23 by Kevin Feasel

Steve Jones shares some advice:

I really like Visual Studio Code. The more I use it, the more I get comfortable in it, and the more I appreciate the design and flow of using the editor. I didn’t feel that way early on, preferring the comfort of Visual Studio, but now I most often use VS Code.

The other day I saw an article from one of the PMs for the product, with a number of tips and tricks. I like some of these, and have already found a few to be handy in my work. 

Click through for more info. Switching to a new IDE can be challenging, but searching out tips and tricks like this can smooth out the transition period bumpiness and potentially unlock efficiencies you wouldn’t even have known about.

Leave a Comment