Press "Enter" to skip to content

Category: Administration

Understanding Azure SQL Database Elastic Jobs

Kate Smith takes us through some important concepts around Elastic Jobs in Azure SQL Database:

It is very important that the T-SQL scripts being executed by Elastic Jobs be idempotent.  This means that if they are run multiple times (by accident or intentionally) they won’t fail and won’t produce unintended results. If an elastic job has some side effects, and gets run more than once, it could fail or cause other unintended consequences (like consuming double the resources needed for a large statistics update).  One way to ensure idempotence is to make sure that you check if something already exists before trying to create it.

This takes some getting used to, but once you’re in the habit, you are much better off. Read on for more details on other key concepts.

Comments closed

Reading CPU Measures from the Ring Buffer

Taiob Ali explains what the CPU and memory measures are from the scheduler monitor ring buffer:

Here is a sample output of XML from sys.dm_os_ring_buffers where WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’. What do those XML elements mean? In order to monitor CPU usages, you need to understand what each element means so you can use the values. I will explain each one in this blog post.

Read on for the list and what each means.

Comments closed

Troubleshooting Chrome + Reporting Services Issues

Wayne Sheffield walks us through troubleshooting a few issues with using Reporting Services in Chrome:

I was recently working with a client with a SQL Server Reporting Services (SSRS) issue. Their company has standardized on using Google Chrome for the browser. However, they were running into issues when using Google Chrome with SSRS reports.

The first issue was that they were receiving a log in prompt to the SSRS server when browsing to it. The second issue was the infamous Kerberos Double-Hop issue. If you’re not familiar with the Kerberos Double-Hop architecture, check out this link: https://docs.microsoft.com/en-us/archive/blogs/askds/understanding-kerberos-double-hop.

I still have bad memories of trying to get Mozilla and (much earlier) Chrome working with Reporting Services. Ugh.

Comments closed

Configuring MAXDOP Correctly

John Welch has a script to check if MAXDOP is configured correctly:

There’s a lot of information on the internet about how to set MAXDOP correctly. Microsoft even provides a knowledge base article with their recommendations. However, if you look at it, there’s a fair amount of information to digest. I’m lazy forgetful efficient, so I wanted to put this into a script I could easily reuse and not have to remember all the details.

Please note that these are just guidelines, and you should consider carefully whether they fit your workloads and scenarios. As is the case anytime you are evaluating system settings, you should test carefully before and after making changes.

Read on for the explanation as well as a link to the script itself.

Comments closed

Slow Record Cleanup

Jared Poche investigates a slow record deletion process:

I encountered a curious issue recently, and immediately knew I needed to blog about it. Having already blogged about implicit conversions and how the TOP operator interacts with blocking operators, I found a problem that looked like the combination of the two.

I reviewed a garbage collection process that’s been in place for some time. The procedure populates a temp table with the key values for the table that is central to the GC. We use the temp table to delete from the related tables, then delete from the primary table. However, the query populating our temp table was taking far too long, 84 seconds when I tested it.

Read on to understand why.

Comments closed

Loading the SQL Server Error Log into a Table

Jeff Mlakar shows how you can load the SQL Server error log into a table:

Why Not Just Use the File System?

When possible I prefer to go into the file system and open the SQL Server error logs in a text editor e.g. Notepad++ or UltraEdit. However, there are sometimes you may have access to a SQL Server instance but not be able to RDP or otherwise scan the file system.

That’s where this comes in – straight T-SQL.

Click through for demos.

Comments closed

Tenant Usage Monitoring with Power BI

Jeff Pries shows us the culmination of several blog posts’ worth of work:

Most of those posts (listed at the bottom of this page) are fairly long and technical — as, initially getting started using a program to read Power BI data via the API can be a bit much.

This post is going to be a bit different. Short and sweet. The payoff for all that hard work authenticating to Power BI, requesting data, downloading that data, and storing it in an easy to use SQL table.

With all of the hard work out of the way, its time to build a Power BI report to explore that great Activity Log usage data.

This is the payoff and it’s quite useful.

Comments closed

Recovering Lost Linked Servers

Taryn Pratt had a post-upgrade problem:

Recently, I kicked off a project to start moving us to SQL Server 2019. During my initial review of our servers, I found quite a few (9 total) that were still running on Windows Server 2012 R2. This meant that I would need to upgrade the operating system and move us to SQL Server 2019. Having completed plenty of SQL Server upgrades, as well as operating system upgrades, I couldn’t possibly make a mistake, right? Wrong…I completely forgot to script out the linked servers on the server I upgraded this week. I screwed up and decided to write about how I went about fixing it.

Click through to understand the problem and solution.

Comments closed

Auditing DDL with Event Notifications and Service Broker

Max Vernon wants to audit Data Definition Language events:

SQL Server doesn’t audit DDL events out of the box, aside from several major events which are captured in the Default System Trace. By “audit”, I mean there is no log of the data-definition-language statements issued against the server. So, if someone creates a table in a database, you can see the table, and when it was created, but details about who created it, and what code they used to create it isn’t saved anywhere. The code in this blog post shows how to configure SQL Server Event Notifications in tandem with SQL Server Service Broker to capture all SQL Server DDL events asynchronously to a database specially configured for the purpose.

Click through for the code and explanation of what’s happening.

Comments closed

Implementing Soft Deletes in SQL Server

Brent Ozar shows how to use soft deletes in SQL Server:

Normally when you run a DELETE statement in a database, the data’s gone.

With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages:

– Easier/faster undeletes
– History tracking (keeping deleted rows around for auditing purposes, although the method I’m going to show here doesn’t do anything fancy like track who did the delete)
– Easier reconciliation during disaster recovery failovers (refer to the Senior DBA class module on recovering from failovers for more info)
– Lower workload for Availability Groups secondaries (neat scenario from Aaron Bertrand)

Read on to see how and what the costs of this are.

Comments closed