Blog

How to Enable Instant File Initialization (IFI) for SQL Server – Video

Why wait on data file growths? Learn why “Instant File Initialization” is a good setting for SQL Server, how to set it using the Local Security Policy in Windows, and see a demo proving that it can help your performance in this 10 minute video.

Short on time? Check out the table of contents below the video.

Table of Contents:

01:43 – SQL Server and IFI – what it helps (and doesn’t)
02:40 – How to open the local security policy and check the SQL Server Service account
05:00 – Performance test: creating a database without IFI enabled
07:00 – Enabling Instant File Initialization for SQL Server
08:20 – Performance test: creating a database now that IFI is enabled
10:00 – Go to BrentOzar.com/First-Aid to get our SQL Server Setup guide and sp_AskBrent®

Not Everything Should be Automated

How did I ever get this to work????

How did I ever get this to work????

Some people hate repetition. They want to avoid manual tasks at all costs.

I used to be part of the cult of automation. I thought that everything should be made efficient. Why should I do something twice, if I can figure out how to do it once and then just run a script or push a button?

I don’t feel that way anymore about a lot of things.

One thing I do often is write scripts to reproduce problems or demonstrate how something works. Very often, there’s pretty similar setup steps in these scripts. I’ve written the following code hundreds of times:

/* Recreate the test database with prejudice */
USE master;
GO
IF DB_ID ('IAMNotAProductionDatabase') IS NOT NULL
BEGIN 
     ALTER DATABASE IAMNotAProductionDatabase SET SINGLE_USER 
       WITH ROLLBACK IMMEDIATE;
     DROP DATABASE IAMNotAProductionDatabase;
END
GO

CREATE DATABASE IAMNotAProductionDatabase;
GO

It’s often useful to back up a demo database and restore it to avoid a long setup process. So I also frequently retype commands like this:

BACKUP DATABASE IAMNotAProductionDatabase TO DISK=N'S:\Backup\IAMNotAProductionDatabase.bak' WITH COMPRESSION, INIT;
GO
RESTORE DATABASE IAMNotAProductionDatabase FROM DISK=N'S:\Backup\IAMNotAProductionDatabase.bak' WITH NORECOVERY, REPLACE;
GO

/* I like to do this in a separate step because I am neurotic like that */
RESTORE DATABASE IAMNotAProductionDatabase WITH RECOVERY;
GO

I could use snippets in SQL Server Management Studio to reduce the typing. I could use a third party tool which auto-completes statements.

But I don’t. I re-type it every time. I was slow at first, but I’m much faster at it now.

These are commands I care about. Sure, they’re simple, they’re basic, they’re nothing fancy. But they’re really core to my profession and my job. I like being able to type these commands quickly and not wonder for too long about the syntax. I like not having to worry if I remembered to set up snippets in whatever test environment I’m using, or if it has third party tools installed.

I’m terrible at memorizing things, and this helps.

I’m not against automation. For production monitoring and responding to incidents, setting up tools and processes that help you work faster and avoid having one person as a single point of failure is awesome. Just don’t apply that principal to everything in your work.

There are some tasks that are fine to repeat, if you want to get to know them by heart.

Brent says: to learn more, check out our list of tasks you probably shouldn’t automate, and read the comments too – lively discussion in there.

Jeremiah says: I’m a huge fan of automation when you understand what you’re automating, why you’re automating it, and what can go wrong when you’re not watching. Otherwise, you need to be really careful.

Erik says: Just about everything I’ve automated has stemmed from a .sql file full of trial, error, and documentation. Know your processes, and make sure someone is made aware if they start failing.

How to See Recent Autogrowth Events on Databases in SQL Server

There are a few little gems built into SQL Server Management Studio that are easy to forget, but they sure can come in handy.

I sometimes generate a little formatted report of recent growths for a database, and how long they took. It’s really easy, and it’s built right in. Here’s how:

  • Right click on a database name in Object Explorer
  • Select Reports
  • Then Standard Reports
  • Then Disk Usage. Voila, the Disk Usage report appears!
  • To see recent autogrowth or autoshrink events, click the little plus sign under the pie charts.

Here’s a 30 second video of that in action:

Where Does SQL Server Track Autogrowth Events?

This report reads from the default trace files. Those files can roll over pretty frequently in an active environment, so this only gives you recent events. You can get the same information with a query, or look for slow auto-growths using our free sp_Blitz® script.

When sp_Blitz® gives me a warning that something’s up, I like to get screenshots from this report to go the extra mile. It’s very nicely formatted and shows any troublesome latency pretty clearly.

Finches and Job Roles

Developers – how much operations work are you doing?

DBAs – how much development do you do?

The Separation of Duties

For most of us, we stick to our assigned job role. Developers write code and then throw it over the wall for the ops team to put in place. If there’s a problem, there is inevitably finger pointing. Eventually a developer gets over to an operations person’s cube and the problem gets solved.

It’s rare that we see any cross disciplinary skill sharing.

In Origin of the Species, Darwin notes that the finches in the Galapagos have become very specialized, depending on the island where they were found. The finches were so specialized that Darwin originally miscategorized a subspecies of finch as a wren. He goes so far as to say:

Seeing this gradation and diversity of structure in one small, intimately related group of birds, one might really fancy that from an original paucity of birds in this archipelago, one species had been taken and modified for different ends

What Do Birds Have To Do With Work?

Darwin’s finches all had very different beak sizes and shapes – each finch’s beak had adapted to a different food source. Even though they’re all finches, they worked in very different environments.

What about you? How specialized are you?

I consider myself a developer – I’ve spent most of my career writing applications. Some of those applications focus largely on SQL Server. But I can also configure HA/DR solutions, set up hardware, and plan storage deployments.

One of the problems with overspecialization is that it becomes difficult to survive if your environment changes.

Avoid Overspecialization

I’m a big fan of mixing job roles. Developers should provide operational support for their features. Operations staff should take part in developing tools or even features for the application. Having a well-rounded set of skills makes it easier to survive when your environment changes.

Staging Data: Locking Danger with ALTER SCHEMA TRANSFER

Developers have struggled with a problem for a long time: how do I load up a new table, then quickly switch it in and replace it, to make it visible to users?

There’s a few different approaches to reloading data and switching it in, and unfortunately most of them have big problems involving locking. One method is this:

  • Create a schema for staging
  • When loading tables, create them in the staging schema
  • When load is complete, drop the table from your production schema
  • Then use ALTER SCHEMA TRANSFER to move the staging table into the production schema

This method sounds good, because items being worked on for staging are easy to identify in their own schema. There’s clearly going to be issues with concurrency between dropping the table and transferring in the new table, but the gamble is that will be fast and can be minimized.

ALTER SCHEMA TRANSFER locks the whole schema

The concurrency problem is worse than you might think. When you run ALTER SCHEMA TRANSFER, it locks up everything in that schema, so nobody can use any table– the lock is not limited to the table you’re transferring.

Not sure? Let’s take a look. First, we create database:

CREATE DATABASE LockTest;
GO

The create a schema named LockMe in the database. Create a table named InnocentTable in the LockMe schema, and insert some rows.

USE LockTest;
GO

CREATE SCHEMA LockMe AUTHORIZATION dbo
GO

CREATE TABLE LockMe.InnocentTable (
    i int identity not null
);
GO
INSERT LockMe.InnocentTable DEFAULT VALUES
GO 10

Now create a new table in the dbo schema and add a few rows. We’re going to transfer this into the LockMe schema.

CREATE TABLE dbo.NewFriend (
    i int identity not null
);
GO
INSERT dbo.NewFriend DEFAULT VALUES
GO 20

Alright, now it’s time to do the transfer! Here we go. To make this easy to see, we’re going to leave a transaction open:

BEGIN TRAN
    ALTER SCHEMA LockMe TRANSFER NewFriend

In another session, we just try to query LockMe.InnocentTable. We didn’t run any commands against it, but this query gets blocked and can’t make any progress:

SELECT TOP 1 *
FROM LockMe.InnocentTable

Looking at this in a third session with sp_WhoIsActive, we can see the problem:

Let go of that schema!

Let go of that schema!

Some Bad News: There Is No Awesome Way to Swap In a Table

The locking you see above is a very high level lock. The query can’t even get a schema stability lock on LockMe.InnocentTable.  There’s no magic hint that will get you around it: adding a NOLOCK hint won’t work (it still needs a schema stability lock).

Optimistic locking won’t get you around it either — queries in SNAPSHOT and READ COMMITTED SNAPSHOT need to get a schema stability lock, too.

If You Must Swap In a Table, ALTER SCHEMA is the Greater Evil

Unless you really want to block absolutely everyone who might use the schema (and live with the consequences), this command will cause you pain.

Need to get to the root cause of blocking? Read more on locking here on our blog for free, or check out our SQL Critical Care®.

Forcing Join Order Without Hints

Brent buys lunch for the ladies

The purpose of this post is to show a bit of syntax that often gets overlooked in favor of using query hints to force joins to occur in a particular order. We’ll start by creating three tables. One for employees, one for orders, and one for items in the order.

/*
An employees table! How novel!
*/
CREATE TABLE #Ozars (OzarID INT IDENTITY(1,1) NOT NULL, OzarName VARCHAR(30) NOT NULL)
INSERT INTO #Ozars (OzarName) VALUES ('Brent'), ('Jeremiah'), ('Kendra'), ('Doug'), ('Jessica'), ('Erik')
ALTER TABLE #Ozars ADD CONSTRAINT [PK_Ozars] PRIMARY KEY CLUSTERED (OzarID, OzarName)
/*
Luuuuuuuunch
*/
CREATE TABLE #Lunch (LunchID INT IDENTITY(1,1) NOT NULL, OzarID INT NOT NULL)
INSERT INTO #Lunch (OzarID) VALUES (1),(1),(1),(3),(5)
ALTER TABLE #Lunch ADD CONSTRAINT [PK_Lunch] PRIMARY KEY CLUSTERED (LunchID, OzarID)
/*
Brent called it in, so it's all under his ID. Because that's how restaurants work. By ID. Yep.
*/
CREATE TABLE #LunchOrders (LunchOrderID INT IDENTITY(1,1) NOT NULL, LunchID INT NOT NULL, Lunch VARCHAR(20))
INSERT INTO #LunchOrders (LunchID, Lunch) VALUES (1, 'Just Churros'), (1, 'Box of Wine'), (1, 'Kaled Kale')
ALTER TABLE #LunchOrders ADD CONSTRAINT [PK_LunchOrders] PRIMARY KEY CLUSTERED (LunchOrderID, LunchID)

A SQL celebrity gossip blog got a tip that someone from BOU ordered take-out. Not exactly an earth-shattering event, but querying minds want to know!

So they write a query, and then they look at the plan.

SELECT o.*
, l.*
, lo.*
FROM #Ozars o
LEFT JOIN #Lunch l ON l.OzarID = o.OzarID
INNER JOIN #LunchOrders lo ON lo.LunchID = l.LunchID

And that’s way harsh. SQL went and changed our LEFT JOIN into an INNER JOIN. What was it thinking? Now we don’t know who Brent is having lunch with.

2015-04-28_14-20-22

How in the why in the heck did that LEFT JOIN turn into an INNER JOIN? SQL is full of it today.

Who ordered the KALE?

Okay, we thought about it some. No more INNER JOIN.
We’ll get this done with another LEFT JOIN.

SELECT o.*
,l.*
,lo.*
FROM #Ozars o
LEFT JOIN #Lunch l ON l.OzarID = o.OzarID
LEFT JOIN #LunchOrders lo ON lo.LunchID = l.LunchID

Unless you’re Ernie, that’s wayyyyy too many Brents.

Too many Brents on the dance floor.

Too many Brents on the dance floor.

First of all, ew. But yeah, you can do this, and it will come back with the right results.

SELECT o.*
, lol.*
FROM #Ozars o
LEFT JOIN (
       SELECT l.*, lo.LunchOrderID, lo.Lunch
       FROM #Lunch l 
       INNER JOIN #LunchOrders lo ON lo.LunchID = l.LunchID
) lol
ON o.OzarID = lol.OzarID
2015-04-28_15-04-31

WHAT SCALARS ARE YOU COMPUTING? WHAT FRESH HELL DID YOU SPAWN FROM?

We can even try an OUTER APPLY. That’s a little nicer looking as a query…

SELECT o.*
, lol.*
FROM #Ozars o
OUTER APPLY (
       SELECT l.LunchID ,
              l.OzarID, 
              lo.LunchOrderID, 
              lo.Lunch
       FROM #Lunch l 
       INNER JOIN #LunchOrders lo ON lo.LunchID = l.LunchID
       WHERE o.OzarID = l.OzarID
) lol

… But same yucky plan.

Hi, I’m a cool trick.

SELECT o.*
, l.*
, lo.*
FROM #Ozars o
LEFT JOIN #Lunch l --They see me LEFT JOIN...
INNER JOIN #LunchOrders lo  --Then INNER JOIN...
       ON lo.LunchID = l.LunchID --Then write both my
       ON o.OzarID = l.OzarID --ON clauses

Nicer plan and a little less CPU than the others, on average.

Pleased to meetcha!

Pleased to meetcha!

This is an interesting concept to play with. With more than a couple JOINs, you can start using parentheses to group them together, like so:

SELECT o.*
, l.*
, lo.*
FROM #Ozars o
LEFT JOIN (#Lunch l 
INNER JOIN #LunchOrders lo 
       ON lo.LunchID = l.LunchID)
       ON o.OzarID = l.OzarID

If you switch the order of the ON clauses in the second to last query, you’ll get an error. Take a guess why in the comments!

/*
Clean me up, buttercup.
*/
--DROP TABLE #Ozars;
--DROP TABLE #Lunch;
--DROP TABLE #LunchOrders;

Brent says: Okay, first off, I don’t normally drink an entire box of wine for lunch, but I had to wash down all that kale. Second off, judging by these execution plans, SQL Server intercepted my wine delivery.

Kendra says: I’ve found parentheses join hints twice in production code in SQL Server. In both cases, nobody knew why they were there, what would happen if they rewrote the query, or even if they’d been used on purpose or if it was just an accident. If you use this technique, document your code heavily as to what you’re doing and why, or you’ll be that person everyone grumbles about.

Linked Servers in SQL Server – Performance and Security [Video]

Linked Servers are easy to set up in SQL Server, but they can have major problems with security and performance. Join Kendra to find out what may be wrong with your Linked Servers, and your options for fixing them.

Got limited time? We got your back. Check out the table of contents for the video below, if you want to skip around.

Linked Servers – Table of Contents

01:55 – Problem #1: sloppy security
03:28 – Problem #2: Questionable queries
04:24 – Hey, why are you filtering AFTER?
05:05 – The “Collation Compatible” Setting
06:34 – What can go wrong?
07:26 – Problem #3: Terrible Statistics
08:03 – Thankfully, Microsoft finally documented this
08:53 – But… we have a conflict!
09:53 – SQL Server 2012 SP1 offers relief
10:47 – Security option: this isn’t perfect
12:07 – Impersonation/Kerberos: this is harder than it looks
14:35 – Reduce security risks
15:56 – Learn more: BrentOzar.com/GO/Link

Free Training on Indexes, Clusters, Availability Groups, and Database Mirroring

Handwaving comes at no extra charge.

Handwaving comes at no extra charge.

Want to brush up your skills on SQL Server over the next few months? We’ve got free webcasts coming your way from May to August! Don’t miss out, register today and get these free events on your calendar right away.

How to Configure Quorum for SQL Server Clusters (Sponsored by Idera Software)

Friday, May 22, 2015 11:00 AM – 12:00 PM Central

“Quorum” is incredibly important to keep your SQL Server online when you use Windows Failover Clustering or AlwaysOn Availability Groups. Join Microsoft Certified Master Kendra Little to learn what quorum is, how to see the current quorum configuration, how to change it, and guidelines for how to configure quorum in three real-world scenarios. Register for this free webcast here.

Triaging Failures in Availability Groups (Sponsored by Idera Software)

Friday, Jun 19, 2015 11:00 AM – 12:00 PM Central

You’ve heard a lot about Availability Groups, but have you ever had to troubleshoot a problem? Microsoft Certified Master Kendra Little will walk you through an example of a failure in an Availability Group and show you how she solved it. You’ll also learn the ingredients you need to set up a test environment and start solving problems yourself. Register for this free webcast now.

Field Medic’s Guide to Database Mirroring: Idera Geek Sync

Wednesday, August 19, 2015, 10:00 AM – 11 AM Central

SQL Server’s Database Mirroring feature is a powerful tool and much simpler to manage than Availability Groups. Join Microsoft Certified Master Kendra Little to learn when Database Mirroring is the right choice, common rescue scenarios, and special setup required for mirrors.

You’ll leave the webcast with tips and tricks to manage Database Mirroring. Click here to register for this free Webcast before spots run out.

Watch Now: Consolidating Indexes in SQL Server

Instant gratification!

Duplicate indexes can be a big problem for your performance and maintenance. In this free video, you’ll learn why they can be such a pain, and you’ll also get to step through three consolidation scenarios. Get out your pen and scratch paper to test your knowledge!

Head on over to the Idera site and watch the free video of the webcast today.

Basic VMware and Hyper-V Terminology for the SQL Server DBA

SQL Server DBAs often need to work with virtualization– and frequently need to work with multiple virtualization platforms. Many platforms have similar features, but they often have different names, and it’s hard to remember what everything’s called.

I’ve got a little cheat sheet for you for two of the platforms! (No offense, Xen fans, please don’t come after me.)

What is it? VMware name Hyper-V name
Management Tools, Free vSphere / web client Hyper-V Manager (snap-in)
Management Tools, Paid vCenter Server System Center Virtual Machine Manager
Automatic migration to another host if one fails High Availability (HA) High Availability (configured via a role in a Windows Failover Cluster)
Moving a VM from host to host vMotion Live Migration
Moving a VM from one storage subsystem to another Storage vMotion Storage Migration
Automatic load balancing DRS (Distributed Resource Scheduler) Dynamic Optimization in VMM (Virtual Machine Manager, a part of System Center)
Rules to keep VMs from being too close Affinity Rules for DRS Availability Sets, part of Intelligent Placement in VMM

Want to learn more about virtualizing SQL Server?

Should You Be a SQL Server DBA? (video)

Have you ever wondered if you should be a SQL Server Database Administrator? If you think this might be the right career path for you, watch this video and take our quiz!

Got limited time or need to restart the quiz? No problem, we’ve got a table of contents with time indicators below.

“Should You Be a SQL Server DBA?” Table of Contents:

00:21 – Getting the wrong job stinks
00:44 – Getting the right job is awesome
01:44 – Get paper and pen, it’s quiz time!
02:11 – Question 1
02:38 – Question 2
03:09 – Question 3
03:22 – Question 4
03:39 – Question 5
03:59 – Question 6
05:39 – Question 7
06:41 – Question 8
07:26 – Time to score this thing!
07:33 – Scoring Question 1
08:45 – Scoring Question 2
10:25 – Scoring Question 3
11:38 – Scoring Question 4
12:49 – Scoring Question 5
13:43 – Scoring Question 6
15:37 – Scoring Question 7
17:25 – Scoring Question 8
19:28 – What does this all mean?
20:29 – Traits of a happy DBA
21:33 – Learn more at BrentOzar.com/go/Career

Brent says: if you think this quiz is just for people who aren’t in the DBA business already, think again. Kendra’s title is “SHOULD you be a DBA?” And, uh, some of us probably shouldn’t. (I love this stuff though.)

css.php