Press "Enter" to skip to content

Category: Administration

New(ish) VLF Status: 4

Published 2018-09-20 by Kevin Feasel

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group:

At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes:

  • 0 = the VLF is not active (i.e. it can be (re)activated and overwritten)
  • (1 = not used and no-one seems to remember what it used to mean)
  • 2 = the VLF is active because at least one log record in it is ‘required’ by SQL Server for some reason (e.g. hasn’t been backed up by a log backup or scanned by replication)

A few weeks ago I learned about a new VLF status code that was added back in SQL Server 2012 but hasn’t come to light until recently (at least I’ve never encountered it in the wild). I went back-and-forth with a friend from Microsoft (Sean Gallardy, a PFE and MCM down in Tampa) who was able to dig around in the code to figure out when it’s used.

Read on to uncover the mysteries of the VLF status of 4.

Comments closed

What sys.dm_exec_query_stats Can Miss

Published 2018-09-19 by Kevin Feasel

Matthew McGiffen takes us through a scenario where sys.dm_exec_query_stats did not give a complete view of what was running on SQL Server:

I got less than 50 rows back so figured I had everything covered, but the total elapsed time across all the queries was less than ten minutes, I knew the server hadn’t been rebooted for about a month so potentially that could be as little as 20 seconds of query execution a night. Even if the full 10 minutes was from the last 24 hours that still didn’t account for the long run times.

So, I convinced myself it wasn’t the queries and started looking at other things they could check. It was a long list. Lots of theories revolved around the idea that something else was running on the host at the same time.

Click through for the rest of the story.

Comments closed

Forcing MAXDOP In Azure SQL DB

Published 2018-09-17 by Kevin Feasel

Arun Sirpal shows us that you can change MAXDOP in Azure SQL Database:

In this quick post I will show you my parallel plan and how I use MAXDOP = 1 to suppress parallel plan generation so the operation will be executed serially. (Disclaimer – I am not saying this is the right thing to do, merely using it as an example of tweaking this setting, to be honest in 10 years I have changed MAXDOP = 1 twice). I executed a query in Azure. You can see the classic operators such as gather streams and repartition streams.

This change will affect all queries hitting that database, so it’s a coarser tool than changing cost threshold for parallelism (not allowed) or setting MAXDOP per-query (allowed).

Comments closed

Comparing Instance Configurations With sys.configurations

Published 2018-09-17 by Kevin Feasel

Jana Sattainathan has a script to compare two SQL Server instances’ configuration settings:

Steps:

1. Create a Linked Server to 2nd instance on 1st Instance
2. Create an empty table to hold the comparison data
3. Insert the 1st instance sys.configurations data into the table
4. Insert the 2nd instance sys.configurations data into the table using Linked Server
5. Compare to get the differences
6. Cleanup – Drop Linked Server and the temporary comparison table

Click through for the script.

Comments closed

Table Swaps With Triggers

Published 2018-09-12 by Kevin Feasel

Jay Robinson walks through the process of making a breaking change to a large, active table with limited downtime:

I can only recall one time in the past several years (at least a decade) that I’ve found triggers to be useful. It involves data migration.

The problem: You have a massive, high-activity table. Let’s call this Table_A. You need to make significant changes to it. For example, the clustered index needs to change. How do you accomplish this?

I’ve used a similar process with good success in the past.

Comments closed

A Look At Automatic Plan Tuning In SQL Server 2017

Published 2018-09-11 by Kevin Feasel

John Sterrett shows us how to review automatic plan tuning suggestions:

SQL Server 2017 Automatic Tuning looks for queries where execution plans change and performance regresses. This feature depends on Query Store being enabled. Note, even if you don’t turn on Automatic Tuning you still get the benefits of having access to the data. That is right. Automatic Tuning would tell you what it would do if it was enabled.  Think of this as free performance tuning training.  Go look at the DMVs and try to understand why the optimizer would want to lock in an execution plan. We will actually go through a real-world example:

Click through for the example.  I wouldn’t automatically trust these automatic results, but my experience has been generally positive.

Comments closed

Migrating Azure SQL Databases Between Resource Groups

Published 2018-09-07 by Kevin Feasel

Arun Sirpal shows us a method for moving Azure SQL Databases between resource groups:

In my mind there are a couple of ways to move a database across resource groups. They vary from scripting to just using the Azure portal. I am going to use the Azure portal and do the following.

  1. Export a database in resource group X to a storage account Z.
  2. Import the file from the storage account Z into a database that is in resource group Y.

It’s just like a “backup and restore” strategy, all with the assumption that you are working within the same subscription ID.

Read on for a step-by-step demonstration on how to do this.

Comments closed

Changing Language In SQL Server

Published 2018-09-05 by Kevin Feasel

Jeff Mlakar shows how to switch languages in SQL Server:

The SET LANGUAGE command allows us to choose a language for a session. By session here I mean by SPID. Each query tab you open in SSMS is another thread to the database and receives a SPID. This can be called by almost anyone who has permissions to access the database because it only requires membership in the public role to execute.

Now let us change the session language to Russian.

You can change the default language for all sessions, as well as switching language for a specific session.

Comments closed

What To Do After Installing SQL Server On Linux

Published 2018-08-31 by Kevin Feasel

Manoj Pandey has a few tips for what to do after installing SQL Server on Linux:

Here are some of the best practices post installing SQL Server on Linux that can help you maximize database performance:

1. To maintain efficient Linux and SQL Scheduling behavior, it’s recommended to use the ALTER SERVER CONFIGURATION command to set PROCESS AFFINITY for all the NUMANODEs and/or CPUs. [Setting Process Affinity]

2. To reduce the risk of tempdb concurrency slowdowns in high performance environments, configure multiple tempdb files by adding additional tempdb files by using the ADD FILE command. [tempdb Contention]

3. Use mssql-conf to configure the memory limit and ensure there’s enough free physical memory for the Linux operating system.

Some of these are common for Windows and Linux (like multiple tempdb files) but there are several Linux-specific items here.

Comments closed

Catalog Your SQL Servers: The SQL Undercover Catalogue

Published 2018-08-29 by Kevin Feasel

David Fowler announces the SQL Undercover Catalogue:

Where Can I Get the Catalogue From?

The Undercover Catalogue is available from our GitHub site.

What Does the Catalogue Store?

The Undercover Catalogue stores all manner of useful information on your SQL Servers,

  • Instances –
  • Databases
  • Logins
  • Users and permissions
  • Agent Jobs

with many more modules planned in future releases.

Check it out and I’m sure they’d love feedback.  Also, read on for where this toolkit is going.

Comments closed