Press "Enter" to skip to content

Category: Administration

Stopping and Starting Virtual Machines in a Resource Group

Dennes Torres walks us through a script to stop or start all virtual machines in an Azure resource group:

Some tasks on azure are easier if we automate them. The Azure Portal provides us the cloud shell, which we can use for this kind of automation.

I was making some experiences with SQL Server Always On, so I created three VMs inside a resouce group. Every time I want to start some experiment I need to start all three VMs and, in the end, stop all three again.

Read on to see how Dennes is able to accomplish this.

Comments closed

Fixing Availability Group Issues with Alerts

Wayne Sheffield automates a few problems away:

The first of the Availability Group issues to discuss is that, for whatever reason, data is no longer moving between the primary replica and a secondary replica. This puts the Data Movement in a Suspended state.

If the data movement remains suspended for too long, you might have to take some undesired actions to get things back in sync. Things like removing the database from the AG, restoring log files, then reattaching it to the AG. When the data movement becomes suspended, we want to get it flowing again as soon as possible. Let’s have SQL Server try to get the data flowing again.

Read on for more, including a second issues that Wayne helps solve.

Comments closed

Reviewing the Windows Event Log with Powershell

Jess Pomfret takes us through an improvement to the old Get-EventLog cmdlet:

Recently I was tasked with troubleshooting an incident on a SQL Server at a certain point in the past, the issue being a high CPU alert.  It’s hard (without monitoring solutions set up) to go back in time and determine what the issue is.  However, one thing we can check is the windows event log to see if there was anything happening on the server at that time.

Now, you probably know that my favourite tool of choice is PowerShell, so let’s take a look at how we can use Get-WinEvent to see what was happening in the past.

Get-WinEvent is the newer revamped version of Get-EventLog, and there are two improvements I believe are worth mentioning. Firstly, with the introduction of filter parameters we can now find certain events much easier, which we’ll talk about a little later. Secondly, the performance of Get-WinEvent is much faster than using the legacy command.  I believe this is due to the filtering happening at the event engine instead of within PowerShell.

Those are some rather welcome improvements.

Comments closed

Enabling SQL Server Optimizer Hotfixes

John Morehouse takes us through the step-by-step for enabling optimizer hotfixes in SQL Server:

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance.  There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.

Let’s take a look at the three options.

Read on to learn more. There is some potential risk of regression with new optimizer updates, so standard rules around testing apply.

Comments closed

Halloween Problem and Inserts

Jared Poche continues a dive into the Halloween Problem:

I would have expected us to scan the temp table, then have a LEFT JOIN to the base table. The Table Spool is the red flag that we have an issue with the plan, and is frequently seen with Halloween protections.

The index scan on the base table seems to be overkill since we’re joining on the primary key columns (the key lookup isn’t much of a concern). But we’re likely doing the scan because of the spool; it’s SQL Server’s way of getting all relevant records in one place at one time, breaking the normal flow of row mode operation, to make sure we don’t look up the same record multiple times.

Read on to see the execution plan as well as Jared’s fix.

Comments closed

Working with SQL Server Configuration Files

Jamie Wick takes us through an underrated part of the SQL Server installer:

The ability to use a parameter file (configurationfile.ini), for automating the installation of SQL Server, has been around for many years. However, each release of SQL Server has had different parameters that could be included in the file. Here are some directions on how to find or create a parameter file, along with the parameter values that are supported by each version of SQL Server.

I appreciate the fact that every installation of SQL Server generates one of these and even points it out to you as you go through the installer wizard. And Jamie has gone a step further by giving us an Excel spreadsheet with all of the available settings and their defaults.

Comments closed

Enforcing Database Practices with Policy-Based Management

Nisarg Upadhyay hits on one of my favorite features Microsoft doesn’t care about anymore:

In this article, I am going to explain how we can enforce the SQL database best practices using Policy-Based Management. The policy-based management feature of SQL Server was introduced in SQL Server 2005. This feature was useful because it helps database administrators to define and enforce the database policies based on the organizations’ requirements.

Back when I was a DBA, I really appreciated Policy-Based Management. Unfortunately, although my job has changed a few times since then, Policy-Based Management hasn’t.

Comments closed

Finding and Downloading SQL Server Updates

Andy Levy combines dbatools and KBUpdate:

Another of Chrissy LeMaire’s (blog | twitter) projects is KBUpdate. Compared to dbatools it’s a pretty compact module, but it’s incredibly useful – it’ll seek out information about KB updates and even download them for you! She’s also rolled these functions into dbatools for convenience, so we don’t need to install or import that module separately.

Read on to see how Andy ties it all together.

Comments closed

Latching in SQL Server

Dan Jackson explains the concept of latching in SQL Server:

To start with, a basic definition: ‘Latches are lightweight synchronization objects, that are used by the storage engine of SQL Server to protect the internal memory structures’. Compare this with locks in SQL server, which are a transaction level construct to manage concurrency, latches work at the thread level to maintain data integrity within the internal memory structures. They are not exposed outside of the SQL Server Operating System (SQLOS). They are only managed by SQL Server itself, not by users (unlike locks that can be overridden via lock hints or changing isolation level). It is useful to keep in mind that a single transaction can use multiple threads at the same time.

Latching is a funny concept to me, in that I think people say “This must be a latching problem” far more than there actually is, but when there is a proper latching problem, it usually winds up being a pretty big deal.

Comments closed