Press "Enter" to skip to content

Category: Administration

Change Data Capture and Availability Groups

Published 2022-02-04 by Kevin Feasel

Jeff Iannucci raises job awareness:

If you’ve ever had to implement Change Data Capture (CDC) for a database in an Availability Group, then you know that the CDC jobs don’t really consider the Availability Group. The capture and cleanup jobs created are set up as if the database exists only on a single instance.

And that’s a problem, because I would guess quite lot of databases are in Availability Groups. Maybe even some of yours. If you have this issue, I’ve put together a step-by-step solution in this post.

Click through for the Microsoft way and the Jeff way.

Comments closed

Disabling Join Types Globally

Published 2022-02-01 by Kevin Feasel

David Alcock shows us the button not to press:

A while ago I presented a session which covered transformation rules that are used by the query optimiser to produce our execution plans. I’m not feeling in the mood for relational algebra this morning so instead I’ll introduce a command that can cause mayhem on an instance SQL Server: DBCC RULEOFF.

DBCC RULEOFF is an undocumented command, that alone makes me want to use it but in order to cover one’s backside please don’t do any of the following in any environment apart from a disposable sandbox that only you use for weird and wonderful experiments in SQL Server, because we are going to break it…

Read on for something you can but should not do.

Comments closed

Reviewing Power BI Query Sessions with Log Analytics

Published 2022-01-31 by Kevin Feasel

Chris Webb continues a series on Power BI monitoring:

In my last post I showed how to use Log Analytics data to analyse Power BI query activity. The problem with looking at a long list of queries, though, is that it can be overwhelming and it can be hard to get a sense of when users were and weren’t actively interacting with a report. In this post I’ll show you how you can write a KQL query that gives you a summary view that solves this problem by grouping queries into sessions.

Click through to see what Chris means by the term “session” and for the KQL to do the job.

Comments closed

Physical Read Double-Counting in Query Stats

Published 2022-01-28 by Kevin Feasel

David Alcock reviews the latest SQL Server 2019 cumulative update:

Microsoft recently released Cumulative Update 15 for SQL Server 2019. It contains a bunch of fixes and some improvements, I get a bit geeky with updates like this and love to have a look through the different fixes to see “Physical reads for read-ahead reads are counted incorrectly (two times) when you run queries. Therefore, the information in sys.query_store_runtime_stats and sys.dm_exec_query_stats shows incorrect values.”

Read on to see what this means and a quick test to see if it works as expected.

Comments closed

Row-Level Security and Parallelism

Published 2022-01-28 by Kevin Feasel

Jose Manuel Jurado Diaz hits on an issue with row-level security:

Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure SQL Database this query is not using parallelism.

During the troubleshooting process we suggested multiple tips and tricks, but any of them made that Azure SQL Engine uses parallelism: 

Being on-premises versus in Azure turned out to be a red herring and the solution was something maybe even more difficult to spot than triggers.

Comments closed

Checking a SQL Server Database’s Master Key

Published 2022-01-27 by Kevin Feasel

Steve Jones is trying to find where he left his keys:

I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.

Click through to see how you can create a database master key as well as how to drop it if necessary.

Comments closed

Combining Transparent Data Encryption and Instant File Initialization

Published 2022-01-27 by Kevin Feasel

Tom Collins performs a test:

Instant File Initialization (IFI) enabled  on  SQL Server, for data files only, leads to faster  execution on  certain file operations, as  it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.

But do these benefits continue  when Transparent Data Encryption (TDE) is enabled ?   

Click through for the answer.

Comments closed

Scheduling Azure ML Compute Instance Start-Up and Shut-Down

Published 2022-01-24 by Kevin Feasel

I have a post correcting a statement I made before:

The single biggest problem I have with compute instances is that there is no auto-stop functionality to them. This is really frustrating because you’re paying for that virtual machine like you would any other, so if you forget to turn it off when you go home for the weekend, it’ll cost you. I wish there were a built-in option to shut off a compute instance after a certain amount of inactivity. Instead, you’ll need to start and stop them manually.

It turns out that you can and so I wanted to write a post to correct the record.

Click through to see how you can do this. You can bet that I’ve got it enabled now.

Comments closed

Negative Blocking Session IDs

Published 2022-01-24 by Kevin Feasel

Bob Dorr explains what those negative session IDs actually mean:

SQL Server may report a blocking session id as a negative integer value. SQL Server uses negative sessions ids to indicate special conditions.​​ 

Click through for the table. Bob also includes information on -5, the “any task/session can release the latch” scenario. This also covers information on the latches themselves and is worth keeping around in case you run into an issue at some point.

Comments closed