Press "Enter" to skip to content

Category: Administration

Creating a Better Index Maintenance Script

Published 2019-10-30 by Kevin Feasel

Erik Darling, despite being on Team Profiler, has something important to say:

If you’re the kind of person who cares about various caches on your server, like the buffer pool or the plan cache, then you’d wanna measure something totally different. You’d wanna measure how much free space you have on each page, because having a bunch of empty space on each page means your data will take up more space in memory when you read it in there from disk.

You could do that with the column avg_page_space_used_in_percent.

BUT…

Read the whole thing.

Comments closed

Simulating ON DELETE CASCADE

Published 2019-10-29 by Kevin Feasel

Aaron Bertrand has put together a procedure which simulates what a cascading delete would look like given your existing foreign keys:

A user recently asked about an interesting scenario involving tables with foreign key relationships. They wanted to generate the DELETE statements that would allow them to manually delete from child tables first (perhaps in stages), based on criteria they define, and tell them – before performing the deletes – how many rows the operation would affect in each table. They wanted output like this:

DELETE dbo.ChildTable1 WHERE ParentID < <some constant>; -- This would delete 47 row(s).

DELETE dbo.ChildTable2 WHERE ParentID < <some constant>; -- This would delete 14 row(s).
...
DELETE dbo.ParentTable WHERE ID < <some constant>; -- This would delete 11 row(s).

Click through for the solution as well as several caveats.

Comments closed

Emergency Mode in SQL Server

Published 2019-10-28 by Kevin Feasel

Paul Randal answers a reader question:

I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.

All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.

Read on for an explanation of what emergency mode is and why we need it to run CHECKDB repair operations.

Comments closed

Database Restoration and the Plan Cache

Published 2019-10-25 by Kevin Feasel

Andy Mallon has some tests for us:

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Yikes. That first sentence sounds like it is going to clear the cache for the entire instance.

Read on as Andy tests this and (spoiler alert) changes the documentation.

Comments closed

Accelerated Database Recovery

Published 2019-10-22 by Kevin Feasel

Andy Mallon explains the concept of Accelerated Database Recovery:

Accelerated Database Recovery(ADR) is a new feature intended to speed up the recovery process, which could be very slow, particularly when there are long-running, large transactions. ADR is not just for recovery after a crash, but also helps in other scenarios where the transaction log needs to be recovered–including Availability Group secondary redo and Failover Cluster Instance failovers.

This is one of the most interesting new features in SQL Server 2019.

Comments closed

Upgrading Servers in an Availability Group

Published 2019-10-18 by Kevin Feasel

Thomas Rushton has a checklist for upgrading servers connected by an Availability Group:

This is a checklist put together and followed for an upgrade of a pair of physical SQL Server 2012 servers which hosted a single availability group of several terabytes of data with minimal downtime.

The availability group was configured with synchronous commit and automatic failover.

Minimizing downtime here is great, but it’s not automatic: you still need to do work on your end to get this right.

Comments closed

Deploying CUs to Multiple Instances with Powershell

Published 2019-10-07 by Kevin Feasel

Jeff Iannucci embraces the power of the shell:

This all started because we had some 14 new SQL Server 2017 instances that we were setting up, but we hadn’t yet applied the most recent cumulative update that we are using in our environment.  I started using the Update-DbaInstance cmdlet in the script below to apply to one server, but then I looked at the list of outstanding requests and thought about something Buck Woody once told me.

“You don’t have time for that. You’re going to be dead soon.”

He’s a fantastic fellow, but we should all be grateful he didn’t become a physician.

Click through for the five-line script and an explanation of what each line does.

Comments closed

SQL Assessment API Public Preview 2

Published 2019-10-01 by Kevin Feasel

Ebru Ersan announces public preview 2 of the SQL Assessment API:

SQL Assessment API is a new mechanism to evaluate configuration of your SQL Server for best practices. The API methods are used by means of a SQL Server Management Object (SMO) extension and new cmdlets in SqlServer PowerShell module. API is delivered with a ruleset that is highly customizable and extensible. It can be used to assess SQL Server versions 2012 and higher, both on Windows and Linux.

Read on for instructions on how to install and what has changed since public preview 1.

Comments closed

SQL Server Monitoring with Grafana and Telegraf

Published 2019-10-01 by Kevin Feasel

Denzil Ribeiro shows how you can use Telegraf and Grafana to monitor Azure SQL Database databases:

SQL DB Storage
This is the dashboard for monitoring file size and space used, IO latency, and IO throughput, for each file in the database. When using Standard or General Purpose databases, which use data files in Azure blob storage, storage performance depends on several blob properties, exposed via the FILEPROPERTYEX() function.

Aside from a couple Azure SQL DB-specific steps, it’s basically the same process that Tracy Boggiano has for monitoring on-prem and IaaS SQL Server instances.

Comments closed