Press "Enter" to skip to content

Category: Administration

Accelerated Database Recovery in SQL Server 2019

Published 2020-11-10 by Kevin Feasel

Jamie Wick walks us through the key concepts with Accelerated Database Recovery:

Beginning with SQL Server 2019, Microsoft has redesigned the database recovery process (ie. crash recovery and rollback) to improve availability and performance. This new feature is called Accelerated Database Recovery (ADR).

Prior to SQL Server 2019, recovering a database (after a crash or restart) consisted of 3 phases that followed the ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) recovery model. The phases of this model are Analysis, Redo & Undo. The Analysis phase begins with the last successful database checkpoint and forward-scans the transaction log to determine the state of each transaction. The Redo phase begins with the oldest uncommitted transaction (that was active after the checkpoint) and rolls-forward, bringing the database state to the same point it was at immediately prior to the crash. The Undo phase then goes backward from the end of the transaction log to reverse all transactions that were active (uncommitted) at the time of the crash. With this process, the database recovery time is roughly the same as the longest running query that was active at the time of the crash.

Click through for the full story.

Comments closed

DBCC CHECKDB on Large Databases

Published 2020-11-06 by Kevin Feasel

Aaron Bertrand shares some thoughts on CHECKDB:

We have a lot of data. Some of that data is stored in large databases (dozens of terabytes each). In some shops, this is an excuse to not run integrity checks. We are not one of those shops.

But we don’t run full CHECKDB operations in production; we have a set of servers dedicated to testing our restores and running checks. We follow a lot of the guidance in these articles:

– CHECKDB From Every Angle: Consistency Checking Options for a VLDB

– Minimizing the impact of DBCC CHECKDB : DOs and DON’Ts

– Minimize performance impact of SQL Server DBCC CHECKDB

Read the whole thing, even if you aren’t dealing with 30+ TB databases.

Comments closed

Creating Users in Azure SQL Database

Published 2020-11-06 by Kevin Feasel

Kenneth Fisher takes us through a nuance in adding users to Azure SQL Database:

Awesome! I did say I preferred code didn’t I? I am noticing a slight problem though. I don’t actually have a login yet. So I look in object explorer and there is no instance level security tab. On top of that when I try to create a login with code I get the following error:

Msg 5001, Level 16, State 2, Line 1
User must be in the master database.

Read on for the whole process.

Comments closed

A Primer on Multi-Server SQL Agent Administration

Published 2020-11-05 by Kevin Feasel

Mikey Bronowski gives us a glimpse at the power of MSX/TSX:

The MSX (master server) can be used to define SQL Agent jobs that then will be pushed over to one or more TSX (target servers). It is possible to see the server is either MSX or TSX when looking at SQL Agent in SQL Server Management Studio Object Explorer. The master server can be identified by (MSX) next to the SQL Server Agent. It will also have two subfolders under the Job folder: Local Jobs (for regular jobs) and Multi-Server Jobs.

This is something woefully few database administrators take advantage of. As the number of servers you have to mange increases, ensuring consistency in SQL Agent jobs becomes more and more difficult. MSX/TSX admittedly has some rough edges, but the tool does a lot.

Comments closed

FAIL_PAGE_ALLOCATION in SQL Server

Published 2020-11-04 by Kevin Feasel

Eric Cobb diagnoses an ugly issue:

I recently ran into a situation where a new SQL Server would crash hard every time it would get under a load.

Here is a synopsis of what we were seeing:

This is a physical server and has 512GB of RAM installed. We have SQL Server 2016 installed, and fully patched (SP2 CU15 at this time). When load testing the server, it would start throwing errors such as:

“Failed allocate pages: FAIL_PAGE_ALLOCATION”

and

“There is insufficient system memory in resource pool ‘default’ to run this query.”

and

“Failed to allocate BUFs”

It would then write a memory dump to the log, and in most cases the server would become completely unresponsive and would have to be rebooted.

Read on to learn under what conditions this happens as well as the solution to the problem.

Comments closed

Considerations Before using SQL Server on Containers

Published 2020-11-04 by Kevin Feasel

Joy George Kunjikkur wants you to slow your roll a little:

It is easy to get started on development and simple testing using SQL containers. It was discussed in the previous post. But before putting into production and start developing real applications we had to make sure the below things at least.

Read on for those considerations. I think they are reasonable and generally agree with the bottom-line conclusion.

Comments closed

Query Processor Ran Out of Internal Resources

Published 2020-11-02 by Kevin Feasel

Andy Galbraith troubleshoots a problem:

 Unfortunately a common error in many of our client environments is this:

—

Error: 8623, Severity: 16, State: 1.

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.–There are many potential causes for this, and the text of this particular error is very well-written because the primary cause is exactly what is listed – a complex query.

Read on to see how to find this complex query, as well as a few examples of complex queries.

Comments closed

Purging the SSIS Catalog

Published 2020-10-30 by Kevin Feasel

Peter Schott extends Tim Mitchell’s work:

I wrote about cleaning up the SSIS Catalog a while back, but needed to revisit this recently for a different use and needed something that can run in an ongoing manner. My earlier post still works, but I recently adapted some code from Tim Mitchell to create a stored procedure that can do that cleanup. Tim wrote the majority of this. I adapted it to wrap it in a stored procedure to handle varying batch sizes for the deletes as well as to ensure all of the tables are deleted in smaller sets of rows.

Here is the code to create the stored procedure. If your SSIS Catalog is not named SSISDB, adjust accordingly.

Click through for the script. It’s interesting to note how frequently cleanup processes for functionality in SQL Server is inadequate for the task at scale. I’ve regularly seen people write these sorts of things for SSISDB, Query Store, replication, ML Services (though that, at least, was changed), etc.

Comments closed

SSAS Performance Counters to Monitor

Published 2020-10-30 by Kevin Feasel

Steven Wright takes us through a set of SQL Server Analysis Services performance counters we should track:

That said, I wanted to provide some updates with more of a focus on Tabular mode, as it has clearly become the new standard for how most organizations deploy SSAS. I recommend referring to that original series for a focus on Multidimensional mode, but much of the information provided in this blog post will be applicable to both modes. I recommend referencing Allen White’s blog post on SQL Server counters to learn more about many of the Windows-level counters that apply across the board, as I won’t speak to them here.

Let’s dive into the 15 SSAS performance counters you should be monitoring.

Click through for the set, as well as explanations for why.

Comments closed