Please Compress Your Indexes and Shrink Your Databases If You Use Azure SQL Managed Instance

Shrinking databases in SQL Server isn’t fun – it’s slow, it causes blocking if you forget to use the WAIT_AT_LOW_PRIORITY option, and sometimes it persistently fails and refuses to budge until you restart the instance. You only want to shrink a SQL Server database when you’ve got a good reason and a lot of patience.

If you’re using Azure SQL Managed Instance and you haven’t already used data compression on your indexes and shrunk your databases, you probably have two good reasons to do both of those things: performance and cost reduction.

Continue reading

Error 1119 When Shrinking Database: Removing IAM Page Failed

At times when shrinking a data file in a SQL Server or Azure SQL Managed Instance/Database, shrink operations may persistently fail with the error:

Msg 1119, Level 16, State 1, Line 11
Removing IAM page ([filenumber]:[pagenumber]]) failed because someone else is using 
the object that this IAM page belongs to. DBCC execution completed. If DBCC printed 
error messages, contact your system administrator.

There’s not much documented on this error anywhere that I can find, so I’m sharing my experience with this error.

TLDR; I was not able to get past this without restarting the SQL Server service.

Continue reading

General Failure Failed Forced Plans in Query Store Cause Even Slower Compile Times

This post demonstrates two related bugs with plan forcing in Query Store which increase the likelihood of slower query execution and application timeouts in SQL Server environments.

These bugs are most likely to impact you if:

• You use the Automatic Plan Correction feature in SQL Server, which automatically forces query plans.
• Anyone manually forces query plans with Query Store.
• You have slow storage, which can increase your likelihood of having longer compilation times.

The General Purpose tier of Azure SQL Managed Instance and Azure SQL Database feature both slow storage and Automatic Plan Correction enabled by default. So, weirdly enough, your risks of suffering from this problem are high if you are an Azure SQL customer.

Thanks to Erik Darling for his help in diagnosing and reproducing these issues– and his ‘slow compiler’ query used in this post was incredibly helpful to isolate and narrow down these problems.

Continue reading

Slow Storage Can Cause Slow Compilation Time in SQL Server

Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.

Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.

Thanks to Erik Darling for helping me figure this out, and explaining this all in his video, What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!. For great details and demos, go watch that! I’ll be working through the topic with some simple flow charts here.

Continue reading

The Restore-AzSqlInstanceDatabase Long Running Operation Failed Error on Azure SQL Managed Instance

What’s it like to be a Database Administrator for managed databases in Azure? Sometimes it’s a painful guessing game when a routine, core operation– restoring a database – fails with a most unhelpful error.

In this case, if the restore is run via PowerShell, following Microsoft guidance, the error message is:

Restore-AzSqlInstanceDatabase: Long running operation failed with status ‘Failed’. 
Additional Info: An unexpected error occured while processing the request.

The misspelling of “occurred” doesn’t bring confidence, but we try and try again.

Continue reading

Erik Darling and Kendra Little Rate SQL Server Perf Tuning Techniques

Erik Darling joins Kendra Little to rate different SQL Server Performance Tuning Techniques in episode 81 of the Dear SQL DBA podcast. We share our opinions of… (deep breath)

Recompile hints, Query Store hints and plan forcing, CTEs, Resource Governor, the legacy cardinality estimator, Table Variables, Automatic Plan Correction, Batch Mode, index rebuilds, Hekaton, NOLOCK, page compression, partitioning, filtered indexes, columnstore, join hints, PSPO, indexed hints, indexed views, optimize for unknown, RCSI, adding more memory, restarting the damn thing, scalar UDFs, and Persisted Memory Grant Feedback.

Continue reading

Learn Perf Tuning in 2 Days at PASS Summit 2024 With Erik Darling and Kendra Little

I’m teaming up with Erik Darling to teach you SQL Server Performance Tuning in two days at the PASS Data Community Summit in Seattle.

Erik and I are co-teaching both days of training to give you a strong strategic background on the internals you need to know, along with critical tactical performance tuning techniques. Join us to level up your perf tuning skills!

Continue reading