Press "Enter" to skip to content

Author: Kevin Feasel

Containerizing an IIS-Based Web App

Published 2020-09-16 by Kevin Feasel

Jamie Wick walks us through containerizing a web application which runs in IIS on Windows:

The basic application documentation provided included a zip file containing the website files along with information that the website was running on a Windows IIS server with passthrough (Active Directory) authentication.

For a Windows Server container to use Active Directory authentication, a Group Managed Service Account (gMSA) must be installed on each server that will be hosting the container. The container’s application must then be configured to run as a Network Service. The last step is to use a Credential File in the docker run command to link the container’s Network Service account to a gMSA on the host.

Note: If you are not familiar with Windows Server containers, Dockerfiles, and the Docker Build process, please refer to this post on Getting started with Windows containers & SQL Server.

Read on as Jamie takes us through the process.

Comments closed

The Cost of Verifying Backups to Azure

Published 2020-09-16 by Kevin Feasel

Matt Robertshaw reminds us that TANSTAAFL:

Within two weeks of backups being written to Blog Storage, we observed a significant upward trend in cost associated with a Storage Account.  When compared to the previous month, there was an increase of c. £270.  After some further analysis, we were able to associate this increase with “bandwidth” charges.  This didn’t feel right – you don’t pay anything to upload data to Azure (ingress), you only pay when downloading data from Azure (egress).

Using Azure Monitor, we profiled the ingress and egress rates for the affected Storage Account and noticed the following pattern:

Each day, c. 150GB of backups were being written to blob storage (in blue), but shortly after, the same amount was being downloaded (in red).  Over this period, we calculated 4TB of data had been uploaded and then downloaded again.  Based on Microsoft’s latest Bandwidth pricing, whilst the first 5GB of egress per month is free, the next 5GB – 10TB is charged at £0.065 per month.  Some simple maths confirms it to be the additional £270 we observed.

Read on for three possible solutions. My preference for an on-prem solution would be to verify locally and then push to Blob Storage / S3. Backups tend to be faster that way as well, as your disk is likely faster than your network.

Comments closed

Using the Geography Data Type

Published 2020-09-16 by Kevin Feasel

Edwin Sanchez takes a look at SQL Server’s GEOGRAPHY data type:

A common application in geography spatial data is called the nearest neighbor query. In this query, you want to know how far or close something is to another object or place.

In my post about the SQL graph, I described how a customer could find the nearby restaurants from the current location. Let’s refer to that example again:

Read on for a primer on the data type and some useful examples.

Comments closed

Using Lightweight Query Profiling in SQL Server

Published 2020-09-16 by Kevin Feasel

Taiob Ali walks us through lightweight query profiling:

With the release of lightweight profiling, you can monitor real-time query progress while the query is in execution. There are a few ways to do this.

– Using System dynamic management view sys.dm_exec_query_profiles which monitors real-time query progress while the query is in execution.
– Using System dynamic management view sys.dm_exec_query_statistics_xml  which returns returns query execution plan for in-flight requests.

Click through for more details, including how to enable it.

Comments closed

The Count Window in Flink

Published 2020-09-15 by Kevin Feasel

Kundan Kumarr takes us through an example of the count window type in Apache Flink:

In the blog, we learned about Tumbling and Sliding windows which is based on time. In this blog, we are going to learn to define Flink’s windows on other properties i.e Count window. As the name suggests, count window is evaluated when the number of records received, hits the threshold.

Count window set the window size based on how many entities exist within that window. For example, if we fixed the count as 4, every window will have exactly 4 entities. It doesn’t matter whats the size of the window in terms of time. Window size will be different but the number of entities in that window will always be the same. Count windows can have overlapping windows or non-overlapping, both are possible. The count window in Flink is applied to keyed streams means there is already a logical grouping of the stream based on all values associated with a certain key. So the entity count will apply on a per-key basis.

I’m curious if there’s a combination of count + time, triggering when you hit X elements or Y seconds, whichever comes first.

Comments closed

Trying Out Redis

Published 2020-09-15 by Kevin Feasel

Paul Brebner walks us through some of the basics of Redis:

It took me sometime to work out what Redis really isn’t, and is!

The Redis documentation says what it is not:

“Redis is not a plain key-value store…”

And what it is:

“It is actually a data structures server, supporting different kinds of values.”

So, it (really) is a fast in-memory key-value store, where keys are always strings, but the value can actually be a number of different data types, with different operations supported on each data type. It’s also distributed (using the cluster mode, and supports replication). And it’s got two types of disk persistence (which makes it more like a database), and a caching mode. See the FAQ for more details.

Redis can be extremely valuable as a cache, though persistent Redis can introduce weird problems at scale.

Comments closed

Using an Azure VM’s D Drive for tempdb

Published 2020-09-15 by Kevin Feasel

William Assaf shows how you can use the temporary D drive on an Azure VM to host tempdb in SQL Server:

Moving your SQL Server instance’s TempDB files to the D: volume is recommended for performance, as long as the TempDB files fit it the D: that has been allocated, based on your VM size. 
When the D: is lost due to deallocation, as expected, the subfolder you created for the TempDB files (if applicable) and the NTFS permissions granting SQL Server permission to the folder are no longer present. SQL Server will be unable to create the TempDB files in the subfolder and will not start. Even if you put the TempDB data and log files in the root of D:, after deallocation, that’s still not a solution, as the NTFS permissions to the root of D: won’t exist. In either case, SQL Server will be unable to create the TempDB files and will not start.

Read on for a few options and William’s thoughts on the relative merits of each.

Comments closed

MAX Versus LASTDATE in DAX

Published 2020-09-15 by Kevin Feasel

Alberto Ferrari explains why you might want to use MAX() instead of LASTDATE() to find the latest date in a table with DAX:

Many DAX newbies use LASTDATE to search for the last date in a time period. Or they use NEXTDAY to retrieve the day after a given date. Although these functions do what they promise, they are not intended to be used in simple expressions. Instead, they are table functions designed to be used in time intelligence calculations. Using them the wrong way leads to inefficient code. Moreover, using these functions in ways they were not designed for is a telltale sign that the developer still does not grasp certain details of DAX.

In this article, we elaborate on the topic in order to understand what these time intelligence functions do; we also want to understand the reason why it is so easy to confuse them with simple math over dates. We want to elaborate on this topic through examples. Therefore, instead of starting with boring theory, we start by looking at a calculation that – although it works perfectly fine – is inherently wrong.

Read on for that explanation.

Comments closed

Finding Indexes Not in Use

Published 2020-09-15 by Kevin Feasel

Dennes Torres takes us through a few iterations of a query to find indexes not in use:

It doesn’t matter if you are trying to remove indexes for good reasons or just to work around a bad environment, let’s see in more details how to find which indexes doesn’t have enough usage to justify their existance.

First, some basic definitions, without going into many details:

Index Seek: That’s the best and desirable use of the index. It means the index tree is being used to go directly to the records we need.

Index Scan: Not so good as an index seek, so it could be better. However, sometimes even an index scan is good, a non clustered index scan means the pages of that index are smaller an better for a scan than the pages of the clustered index. There are many variations that makes an index scan good, but most times you don’t need to reach this level of analysis, you may reach your objective only analysing index seeks.

Update: When the fields are updated (update/insert/delete) all indexes which contain those fields need to be updated as well. Indexes are a balance: We increase performance on reading and suffer a bit more when writting. The problem is when the writting happens more than the reading.

Read on to see Dennes’s query evolve and bring important information to the table. For example, it’s not just how often a particular index gets used; it’s also how important the queries are which use this index. An index may only run once a month, but if it turns the most important report the CEO cares about from running in 4 hours to running in 4 seconds, you bet that index is staying.

Comments closed