Press "Enter" to skip to content

Category: Administration

Monitoring Power BI Queries with Log Analytics

Published 2022-01-24 by Kevin Feasel

Chris Webb continues a series on using Log Analytics:

It’s actually very easy to build a simple KQL query to look at query activity on your datasets: you just need to look at the QueryEnd event (or operation, as its called in Log Analytics), which is fired when a query finishes running. This event gives you all the information you need: the type of query (DAX or MDX), the duration, the CPU time, the query text and so on. The main challenge is that while you also get the IDs of the report and visual that generated the query, you don’t get the names of the report or visual. I wrote about how to get a list of visual and report IDs here and here, but how can you use that information?

Read on to see how.

Comments closed

Understanding the Transaction Log

Published 2022-01-21 by Kevin Feasel

Paul Randal has a new series:

With this post, I’m starting an occasional series on the transaction log and how it works and should be managed, and I’ll touch on all the problems above over its course. In this post, I’ll explain what logging is and why it’s required.

Basic Terminology Around Logging

When I’m talking about any mechanism in SQL Server, I find there’s a chicken-and-egg problem where I need to use a word or phrase before I’ve explained it. To avoid that problem in this series, I’m going to start by explaining some terminology that needs to be used when discussing logging, and I’ll expand on many of these terms as the series progresses.

This post starts off with some of the basics and it’s always good to get the occasional refresher on the basics.

Comments closed

Testing Change Data Capture

Published 2022-01-21 by Kevin Feasel

Jeff Iannucci needs to test Change Data Capture:

I’m not sure how many of you use Change Data Capture (CDC) on your instances, but I’ve had to support it for a while now and I thought I’d share a little script I use fairly frequently to help troubleshoot the capture and cleanup, as well as some advice for resolving issues.

Click through for the script, as well as some additional notes on CDC.

Comments closed

Measuring Cost Threshold for Parallelism Effectiveness

Published 2022-01-20 by Kevin Feasel

Michael Swart gets out the ruler:

The configuration setting cost threshold for parallelism has a default value of 5. As a default value, it’s probably too low and should be raised. But what benefit are we hoping for? And how can we measure it?

The database that I work with is a busy OLTP system with lots of very frequent, very inexpensive queries and so I don’t like to see any query that needs to go parallel.

What I’d like to do is raise the configuration cost threshold to something larger and look at the queries that have gone from multi-threaded to single-threaded. I want to see that these queries become cheaper on average. By cheaper I mean consume less cpu. I expect the average duration of these queries to increase.

Read on for Michael’s results, and I appreciate somebody actually testing and measuring rather than pulling a number from a hat.

2 Comments

Finding your SQL Server Product Key

Published 2022-01-19 by Kevin Feasel

I share a tale of woe:

I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM, but needed to get the product key. There are a few places where you can find Powershell functions to get this product key, with Ryan @ Ryadel’s post being the most well-known. That method covers versions from SQL Server 2005 through 2014 (if you follow the notes in the blog post), but breaks on 2016.

Click through for a script which works for SQL Server 2016 and later.

Comments closed

The Benefits of Indirect Checkpoints

Published 2022-01-19 by Kevin Feasel

Aaron Bertrand explains why it’s probably a good idea to enable indirect checkpoints:

At Stack Overflow, the upgrade to SQL Server 2019 was not as smooth as expected. It introduced long recovery times and high CPU on a secondary, during synchronization activity after failover, patching, and network connectivity testing. This symptom wasn’t present during similar activities and workloads under SQL Server 2017.

Aaron points out that if you have databased created in older versions of SQL Server, enabling indirect checkpoints can provide a performance boost to certain activities around log writing., including backup times.

Comments closed

Automating Azure SQL DB Maintenance with Data Factory

Published 2022-01-18 by Kevin Feasel

Hiten Bhavsar works around the lack of SQL Agent:

As we know, it’s crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly.

Click through for the process and try not to think too hard about this secretly being maintenance plans all over again.

Comments closed

Cannot Open User Default Database

Published 2022-01-17 by Kevin Feasel

David Alcock backs out of a problem:

This error isn’t to do with my login as such, it’s still there with sysadmin role membership so I don’t have to do anything too drastic like restarting SQL Server with the -m or -f startup parameters and recreate it. The error message is telling me that my logins default database cannot be opened, which is more than likely because I’ve deleted it.

Click through to see how David got out of this issue. This is a big part of why I highly prefer not to change the default database from master for logins..

Comments closed

Testing Failover Group and TCP Connectivity with Managed Instances

Published 2022-01-14 by Kevin Feasel

Niko Neugebauer has a pair of connectivity tests for us. First up is failover group connectivity:

When you set up a failover group between primary and secondary SQL Managed Instances in two different regions, each instance is isolated using an independent virtual network. Replication traffic needs to be allowed between these VNets.

To allow this kind of traffic, one of the prerequisites is:

– “You need to set up your Network Security Groups (NSG) such that ports 5022 and the range 11000-11999 are open inbound and outbound for connections from the subnet of the other managed instance. This is to allow replication traffic between the instances.”

Click through for a SQL Agent job script which helps with the test. Meanwhile, you can also test TCP connectivity from a managed instance:

In this post we shall focus on helping you determining the TCP connectivity from SQL Managed Instance against a given endpoint and port of your choice.

If you are interested in other posts on how-to discover different aspects of SQL MI – please visit the  http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.

There are scenarios where it would be nice to be able to test if a SQL Managed Instance can reach some “external” endpoints, like Azure Storage as an example.

Check out both posts.

Comments closed