Press "Enter" to skip to content

Author: Kevin Feasel

Replaying Workloads with WorkloadTools

Published 2019-06-21 by Kevin Feasel

Gianluca Sartori shows an example of using the WorkloadTools application to replay a workload, including where the analytics server cannot directly access the production database:

Regardless of the method that you decided to use, at the end of the replays, you will have two distinct sets of tables containing the workload analysis data, sitting in different schemas in the same database or in completely different databases.

WorkloadViewer will let you visualize performance over time, as we have seen for a single workload analysis, but this time it will be able to show you data from both workloads, so that you can compare them.

This sort of production load testing is both important and difficult; WorkloadTools makes it easier.

Comments closed

Troubleshooting Query Performance Changes

Published 2019-06-21 by Kevin Feasel

Erin Stellato walks us through a troubleshooting guide when users complain about poorly-performing queries:

This is tale of troubleshooting…

When you unexpectedly or intermittently encounter a change in query performance, it can be extremely frustrating and troublesome for a DBA or developer. If you’re not using Query Store, a third-party application, or your own method to capture query data, then you probably don’t have information about what query performance looked like when things were good…you just know how it’s running now. I was working with a customer of Tim’s last week that had been chasing this exact problem for, in their words, years. They had recently upgraded to SQL Server 2016, and the problem was still occurring.

Strangely, “blame the network” didn’t appear in Erin’s post, so I don’t know if it’s comprehensive.

Comments closed

Workarounds for Updating Stats on Secondaries

Published 2019-06-21 by Kevin Feasel

Niko Neugebauer wants statistics updates on tables running on readable Availability Group secondary nodes:

Let’s list the basic known details for the possible solution(for the Enterprise Edition of the Sql Server that is):
– We can make the secondary replica readable and read the same data on it. (Not that you should do that by default, but if you really know what you are doing …)
– We can copy our object into the TempDB (yeah, your Multi-TB table is probably not the best candidate for this operation), or maybe into some other writable DB.
– We can write results in the shared folder between the replicas (let’s say in a text file into a File Share)
– We can export the BLOB object of the statistics out of the SQL Server
– We can import the BLOB object of the statistics into the statistics

Read the whole thing.

Comments closed

Warning on Azure Consumption

Published 2019-06-20 by Kevin Feasel

Daniel Hutmacher doesn’t want you to have any Azure billing surprises:

I wrote this quick-and-dirty script to let me know if I happen to forget to turn off a P15 instance, or if I configure a service with a super-expensive performance tier without realizing. Maxing out your free Azure credits may be depressing enough, but emptying your credit card could really put you in the hurt locker.

So, here’s a Powershell script that warns me before any of this happens. It uses the Azure Consumption API to check how much money we’ve racked up on a subscription so far, and if any single instance exceeds, say, 50% of that total cost, it sends a notification to a Slack channel.

The wallet you save may be your own.

Comments closed

Running Confluent Platform with .NET

Published 2019-06-20 by Kevin Feasel

Niels Berglund shows how you can install Confluent Platform as a Docker container and use the .NET client against it:

What we see in Figure 16 are the various project related files, including the source file Program.cs. What is missing now is a Kafka client. For .NET there exists a couple of clients, and theoretically, you can use any one of them. However, in practice, there is only one, and that is the Confluent Kafka DotNet client. The reason I say this is because it has the best parity with the original Java client. The client has NuGet packages, and you install it via VS Code’s integrated terminal: dotnet add package Confluent.Kafka --version 1.0.1.1:

Definitely use the Confluent client. The others were from a time when there was no official driver; most aren’t even maintained anymore.

Comments closed

What Public Permissions Get You

Published 2019-06-20 by Kevin Feasel

Jason Brimhall shows all you can do by default with the public role in SQL Server:

It is amazing what some people will do that just doesn’t make sense. Granting permissions to the public role is one of these cases. That behavior also explains why there are documents and procedures for hardening the public role (here and here).

If necessary, I recommend locking down your public role. It will make your job a little easier and give you better rest at night.

Read the whole thing.

Comments closed

Ownership Chaining and Temp Stored Procedures

Published 2019-06-20 by Kevin Feasel

Kenneth Fisher wants to see how security for temporary stored procedures works:

With normal stored procedures there is something called ownership chaining. Without going into a lot of detail about what it means, let’s say that you run a stored procedure. SQL is going to check the permissions to see if the stored procedure can update that table right? Well, who’s permissions? Yours? Well, yes, if you have permissions you are fine. But you won’t always. If you don’t then SQL is going to check the owner of the stored procedure (dbo?) and see if they also own that table. If so then we’re golden, perform the update. That might seem scary but it’s pretty normal.

What was scary (at least to me) is the question “How is that handled for a temp stored procedure?”

Read on for the results of Kenneth’s tests.

Comments closed

Configuring the Windows Subsystem for Linux

Published 2019-06-20 by Kevin Feasel

Drew Furgiuele is too cool for Windows shells:

Microsoft has provided a native Linux experience for Windows, called the Windows Subsystem for Linux, or WSL. If you haven’t heard of this feature yet, here’s the short version of what this means:

– “Install” a Linux distribution of you choice into your Windows 10 environment, which
– Enables you to run common Linux command line tools, like grep and sed, which is something your Linux using friends and co-workers have been bragging about since like, forever, and
– Gives you access to other Linux applications and commands, available via your chosen distribution’s package manager, and oh before I forget
– Gives you an honest-to-goodness native SSH shell experience on your machine without the need for a third party application

Sounds cool, right? Well, it is. 

It is.

Comments closed

Scripting Objects with SSMS

Published 2019-06-20 by Kevin Feasel

Michelle Haarhues shows two ways to script out database objects in SQL Server Management Studio:

There are a multitude of reasons why users script existing objects within SQL Server.  Depending on the reason will dictate whether you are scripting one object, a few objects, or the entire database.  There are different tools within SQL Server Management Studio (SSMS) that will help you create object scripts.

Click through for the two methods.

Comments closed