Press "Enter" to skip to content

Month: November 2022

Tips for Large Table Data Archival

Published 2022-11-18 by Kevin Feasel

Aaron Bertrand follows up on a prior post:

As soon as you realize your growth rates are higher than expected, you need to plan to buy or allocate more disk space. There is no way around this—more data means more disk. You can delay the inevitable for a little bit with better compression, but this is not a long-term fix, and it can impact query performance in different ways (trading CPU for I/O).

Once more disk is in place, you can plan your growth better.

Click through for some guidance on how to plan that growth.

Comments closed

“The Function Requested Is Not Supported” Errors on Availability Groups

Published 2022-11-18 by Kevin Feasel

David Fowler troubleshoots an issue:

Checking the logs on the secondary, it was littered with ‘Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).’ messages. The primary server wasn’t able to authenticate with the secondary, but why? Everything looked ok as far as I could see.

Click through for the fruits of David’s labor.

Comments closed

Transaction Log File Autogrowth in SQL Server 2022

Published 2022-11-18 by Kevin Feasel

William Assaf mentions a welcome change to SQL Server 2022:

Starting with SQL Server 2022, transaction log file growth events up to 64 MB in size can benefit from instant file initialization (IFI). As usual, the transaction log is otherwise unable to benefit from instant file initialization. 

This should be a big performance improvement if your transaction log files unexpectedly grow. Of course, you should try to avoid autogrowth events altogether. 

The prior default of 10% autogrowth has led to so many problems over the years. I’d like new database files (MDF and NDF) to have a similar default as well.

Comments closed

How to Disable a SQL Agent Job via Powershell

Published 2022-11-18 by Kevin Feasel

Tom Collins gets a question:

I have a  SQL Server Agent job  in a disabled status. The disabled status is  applied to the SQL Agent Job and the associated schedule.  The problem is I’m logged on as a full administrator onto the Windows Server – and as BUILTIN\Administrator is defined on the SQL Server , but am able to manually override the SQL Agent Job.   

Is there a way to Disable the SQL Agent Job where I’m not able to manually override the job?

Click through for the answer.

Comments closed

Diagnosing Customer Rebalance Time Issues in Kafka

Published 2022-11-17 by Kevin Feasel

Danica Fine and Nikoleta Verbeck continue a series on Kafka performance troubleshooting:

At the surface, rebalancing seems simple. The number of consumers in the consumer group is changing, so the subscribed topic-partitions must be redistributed, right? Yes, but there’s a bit more going on under the hood and this changes depending on what kind of rebalancing is taking place.

Read on to learn more about how Kafka performs rebalancing and what might affect performance.

Comments closed

SQL Server 2022: Forcing Optimized Plans

Published 2022-11-17 by Kevin Feasel

Matthew McGiffen wants you to put yourr wallet and your best plans in the bag:

I’ve been reading Bob Ward’s book “SQL Server 2022 Revealed” and one of the new features in Intelligent Query Processing (IQP) jumped out at me. Not because it’s going to rock my world but rather it reminded me of an idea I had when I first started playing with Query Store when it came out.

I thought it would be cool if the execution plans cached in Query Store could be used to populate the plan cache after an instance restart, removing the need for all queries to be recompiled. I even raised a suggestion on Microsoft Connect for it to be considered as a new feature.

Click through to see what Microsoft came up with along these lines.

Comments closed

Invoke External REST Endpoints from Azure SQL DB

Published 2022-11-17 by Kevin Feasel

Rob Farley is impressed:

This internal procedure is new in Azure SQL DB in 2022. I think it presents a significant change to the way we do things in the world of SQL, and makes some other tools a whole lot more useful as well.

sp_invoke_external_rest_endpoint lets me send data to a REST API from within a stored procedure. Invoking an HTTP REST endpoint – as simple as that. And while I know you’re probably thinking, “But I can send data to a REST API from anywhere – why do I need to do it from within a stored procedure?”, I want to describe a few scenarios to you.

I like having the functionality, though would want to control how frequently my teams would use it. The reason is that this potentially makes your database the a domain boundary (when thinking in domain-driven design concepts).

Comments closed

Query Cost Normalizing

Published 2022-11-17 by Kevin Feasel

Tibor Karaszi does the math:

There are plenty of articles out there recommending you to up the “cost threshold for parallelism” configuration option. Most of you already know this, if not fire up your favorite search engine and spend a few minutes reading about it.

My aim here is to do a totally non-scientific test if I can translate these fictitious seconds to what they correspond to on a reasonably modern hardware – which is my laptop from 2020.

This is one interesting approach to back into a cost threshold for parallelism.

Comments closed

Continuous Backup for Cosmos DB

Published 2022-11-17 by Kevin Feasel

Manvendra Singh wants a backup:

This article will explore Continuous backup and steps to configure it for a new Azure Cosmos DB account or an existing Cosmos DB account. Azure Cosmos DB is a fully managed and highly secure, NoSQL database service on the Azure cloud that is designed for modern-day application development. It automatically runs backup for its databases on separate Azure blob storage at regular intervals without affecting the performance, availability, and provisioned resource units (RUs) to ensure data protection from a data recovery standpoint which can be needed in case of data corruption, deletion, or wrongly data updates.

Click through for the process and some limitations.

Comments closed