Press "Enter" to skip to content

Category: Administration

Halloween Protection and Non-Clustered Indexes

Published 2023-02-21 by Kevin Feasel

Jared Poche digs further into Halloween protection:

I find myself talking about the Halloween Problem a lot and wanted to fill in some more details on the subject. In short, the Halloween Problem is a case where an INSERT\UPDATE\DELETE\MERGE operates on a row more than once, or tries to and fails. In the first recorded case, an UPDATE changed multiple rows in the table more than once.

So let’s take a look at an example using a publicly available database, WideWorldImporters.

Read on for a case of Jared starting from the known and moving into the unknown.

Comments closed

SQL Server 2022 CU1 and SQL Server 2019 CU19 Released

Published 2023-02-17 by Kevin Feasel

Srinivas Kandibanda and Harvey Mora have announcements:

The 1st cumulative update release for SQL Server 2022 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.

Both of these came out several months later than expected, though with the big GDR that dropped yesterday, it seems like that cleared up the logjam.

Comments closed

Security Updates for SQL Server 2014 and Up

Published 2023-02-16 by Kevin Feasel

Harvey Mora notes that Microsoft has been busy:

The Security Update for SQL Server 2022 RTM GDR is now available for download at the Microsoft Download Center and Microsoft Update Catalog sites. This package includes the new security fixes detailed in the KB Article.

This link is specifically for SQL Server 2022 but there are other posts for 2019, 2017, 2016, and 2014. This includes fixes to several security problems and is something you should install post-haste. Because this is a GDR, you’re also liable to find it in Windows Update if your update admins haven’t shut that channel off already.

Comments closed

Praise for Purvi’s List

Published 2023-02-15 by Kevin Feasel

Andy Yun is a fan:

By sheer coincidence, I had the privilege of being invited to a private SQL Server 2022 workshop taught by Bob Ward last week. And through my job, I also had the privilege of doing some testing work around QAT backups and S3 Data Virtualization during the private preview phase last summer. So while I had exposure and access to SQL Server 2022 for much longer than others, there were many things that Microsoft loaded into the 2022 release that I barely skimmed over or knew were even there.

Towards the end of the workshop, Bob presented a slide called Purvi’s List. Purvi Shah is an engineer on the SQL performance team and as Bob said, “spends her time finding ways to make SQL Server and Azure SQL faster.”

Read on to learn what had Andy so excited.

Comments closed

Data Cleanup in Query Store

Published 2023-02-13 by Kevin Feasel

Grant Fritchey does some housekeeping:

The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:

Even with that limit, there are still several options available to you for when to clean up old Query Store data, whatever the definition of “old” (i.e., time-based or at the user’s whim).

Comments closed

Migrating SQL Server Databases to Aurora

Published 2023-02-09 by Kevin Feasel

Jamie Wick wounds me:

Our decision to move some (not all) SQL databases to AWS Aurora was partially a cost decision, but also an effort to simplify operational management by diversifying our database platforms. Now that second reason might seem counter-intuitive, but here are some details about the situation and how we came to our decision:

Read on for Jamie’s thoughts and tips to make the migration process easier.

Comments closed

Unsupported Characters in Azure SQL DB Database Names

Published 2023-02-07 by Kevin Feasel

Nithya Bondalapati notes a discrepancy:

ARM (Azure Resource Manager) API does not support specific T-SQL resource naming formats and that’s why when Databases are created using T-SQL/SSMS, unsupported characters could get allowed into the names.
Azure Portal UI does not have this issue, because when you use Portal UI for creating a Database, the creation goes through ARM, and hence when unsupported characters are entered in the Name field, it immediately throws error, as shown in the below image.

None of the restrictions or onerous, but Nithya explains what actions fail or don’t quite work right if you have a database with special characters or ending in a period or space.

Comments closed

When to Use OPTIMIZE_FOR_SEQUENTIAL_KEY

Published 2023-02-07 by Kevin Feasel

Chad Callihan explains a feature:

If you’re running into problems caused by concurrent inserts and you’re on SQL Server 2019 or above, it’s worth testing out OPTIMIZE_FOR_SEQUENTIAL_KEY. This may alleviate those issues by handling inserts a little bit differently to reduce contention. OPTIMIZE_FOR_SEQUENTIAL_KEY will “control the rate at which new threads are allowed to request the latch, and favor threads that are likely to keep the throughput high.”

Read on to see how you can enable it but also when it makes sense to enable it.

Comments closed

Power BI Workspace Roles

Published 2023-02-03 by Kevin Feasel

Reza Rad shares some recommendations with us:

Power BI workspaces are not like the old days when we had Edit access and View access only. You have more options for roles in a workspace, and in my courses, I have found that many people have chosen the incorrect role without knowing what the role does. In this article, I’ll explain all the roles in the workspace, and what is the best way to set them up to have a secure workspace.

Click through for the article, as well as an accompanying video. Or a video and an accompanying article, if that’s how you roll.

Comments closed

Power BI App Audiences and Workspaces

Published 2023-02-02 by Kevin Feasel

Marc Lelijveld looks at an update to Power BI:

Back in August 2022, Microsoft released a feature to have multiple audiences for Power BI Apps, or if you will deploying multiple versions of the same app where you can actively show and hide content for different audiences. A very welcome feature that has a big impact on the number of workspaces people require to have to reach the same goals. In this blog, I will elaborate on the impact of multiple audiences for Power BI Apps, as well as the impact on workspace governance.

Read on for Marc’s thoughts on the topic.

Comments closed