Creating a Backup Maintenance Plan (Video)

Maintenance plans are a great way of getting started with a backup solution for your environment.

While maintenance plans don’t offer great flexibility, particularly when it comes to managing backups across many different servers, they’re often a good starting point because they’re easy to create and simple to understand.

In this demo video, I walk through creating a skeleton maintenance plan that contains 3 subplans to perform full, differential, and transaction log backups. The only thing left to do is schedule each of the automatically-generated SQL Agent jobs to meet your backup and recovery needs.

Capture Deadlocks 24/7 with Extended Events

Deadlocks can be notoriously difficult to reproduce in our development environments.

Most of the time:

  • There are multiple actual users involved with the problem.
  • The production workload, both in terms of volume and/or composition, can’t be realistically reproduced in development either.
  • The tools we used to need to use to capture deadlocks (server-side trace, SQL Server Profiler) are prohibitively expensive to run all the time, or even for a short time, or we had to mess around with trace flags.

Here’s where Extended Events come to the rescue, and really makes us able to be proactive to deal with deadlocks.

Extended Events allows us to capture deadlock graphs continuously, as they happen, 24 hours/day, 7 days/week, with very little performance overhead. Not only that, but the system we’re going to set up is able to survive an instance restart! This is huge in terms of ease of management, and being able to have this information available when it’s needed can be way more than half the battle.

Here is the meat of the script that sets up our event session for a SQL Server 2012 instance:

	ADD EVENT sqlserver.xml_deadlock_report 
	ADD TARGET package0.event_file
			FILENAME = N'\\CentralFileShare\MyTargetFile.xel',


Note that the database engine service account needs to be granted write access to the target location.

For ease of multi-server management, I recommend setting up a centralized file share to store all the output files. The full script was written with this in mind, and uses the server name as the file name automatically.

Security note: Deadlock graphs may contain sensitive information, such as data values in DML statements. The target location should be secured appropriately — consider using subfolders in the target path to separate by server if a single folder isn’t okay for your environment.


So, now that we’ve captured some deadlocks (yay?), how do we figure out what happened? Here is a first start. It reads the output and returns the event data (in this case, a deadlock graph) as XML. This script can be used for all types of Extended Event file targets, not just for deadlocks.

	CAST(event_data AS xml) AS EventXml
			'.xem', /* Required for 2008, optional for 2012 */

XPath can be used to further break things down, or the event data can be copied as text and saved as a .xdl file, which we can then open up in Management Studio to see the pretty deadlock graph. If you have a centralized management box, this script could be the basis of an Extended Events aggregation and reporting system. (Hopefully, you aren’t dealing with that many deadlocks, but this type of system could be used for other purposes.)

In a future post, we’ll look at how to analyze deadlock graphs.

Enable Instant Data File Initialization (Video)

Any time a new portion of a database file (data file or log file) is created, by default, SQL Server writes out zeros — to start with a clean slate, so to speak.

As the name implies, instant data file initialization is a feature that allows SQL Server to skip the zeroing process for data files. Log files are always zeroed out (because of the way they work internally).

The reason why you’d want to turn on this feature is simple: skipping the zeroing process does less I/O, thereby speeding up the process. This gives many advantages:

  • Recover faster: The tempdb database must be recreated on instance restart. This could be particularly important if you have an availability SLA to maintain.
  • Restore faster: Data files are initialized before the actual backed-up data pages are copied into the data files.
  • Better response time: If a data file has to auto-grow because of user activity, the user won’t have to wait as long for the operation to complete.

Now, of course there is a tradeoff here, and in this case it’s security-related: because no zeroing of the files happens with this setting turned on, it may be possible (through erroneous SQL Server behaviour) to access the previously-written data on the disk, which could be absolutely anything. While this is a very, very small risk, it may be too much for your environment. If that’s the case, this setting should be turned off (which is the default setting).

In this short video demo, I walk through the steps to enable the feature, and validate that it’s been successfully enabled.

Summary of steps:

  1. Add the database engine service account local group/domain group/service SID to the Perform Volume Maintenance Tasks security policy. (Note: this can also be accomplished using Windows Active Directory Group Policy, if that’s a better solution for your environment.)
  2. Restart the database engine service.
  3. Validate the feature is enabled using the script below. There shouldn’t be any messages in the Error Log that indicate data files were zeroed.


Moving the System Databases (Video)

Occasionally after installation, the system databases need to be moved from one location to another. In this video demo, I show you how to accomplish this task.

Key points:

  • Make sure the database engine service account has read/write access to the new file location(s).
  • The paths to the master database files are in the service startup parameters.
  • The paths to the other databases files are changed using ALTER DATABASE … MODIFY FILE.
  • The database files themselves can’t be copied until the database engine service is stopped.

Two things about security which I didn’t mention in the video:

  • When granting permissions on the new data container, the principle for the database engine service account will vary depending on your environment. In my case, it was a local group, but you may need to use a domain group, or a service SID. It should be obvious from the source data container.
  • You may want to grant the Everyone group permission to list the folder contents starting from the root, and remove the inherited permission on each data container. This will allow you to browse to the drive when attaching a database (for example) in Management Studio, yet limit access to only the account that should see the files in each instance folder.


Three Layers of Fragmentation

What is Fragmentation?

When the logical order of a storage unit doesn’t match the physical order on a storage device, the storage unit is said to be fragmented. In other words, if there are storage units that contain A B C D E, but the physical order is D E C B A, this means the storage device may have to do random access instead of sequential access. This can be a big deal if the device is slow at doing random accesses vs. sequential, as is the case with hard drives of the spinning/magnetic variety.

Layer 1: Fragmented Indexes

This is the most granular level. Your database’s indexes are fragmented when the pages and/or extents are out of order within the data file. The amount of fragmentation can be measured by using sys.dm_db_index_physical_stats.

Potential causes: Random index inserts; updates that require a row’s size to increase.

Solution: Install and periodically run an intelligent index maintenance solution, such as Ola Hallengren’s.

Layer 2: Operating System Files

A database is physically persisted in files in a file system. The files are fragmented when the allocation units that make up the files are out of order in the file system. This is the exact same type of fragmentation you can encounter when saving normal files on your computer.

Potential causes: Multiple file growths on the same drive (smaller growths make things worse); creating databases on an existing drive that is already fragmented; using a file system allocation unit size smaller than 64 KB (the size of one extent).

Solution: Periodically run Windows Disk Defragmenter on the drives that store the database files (this requires shutting down the database engine instance for the duration of the maintenance).

Layer 3: Virtual Machine Disks

If your Windows instances are virtualized, there is an additional layer of potential fragmentation, because the drives in Layer 2 are abstracted — the contents of the drives themselves are files in a file system. And like Layer 2, those files can potentially become fragmented.

Potential causes: Dynamically-allocated virtual disks that grow; creating a new virtual disk on a drive that is already fragmented.

Solution: Only used fixed-size virtual disks, or use a separate area dedicated to dynamically-sized virtual disks. For the fixed-size area, if things aren’t fragmented at all, nothing needs to be done. For the dynamically-sized area, periodically run Windows Disk Defragmenter on the drives that store the virtual disk files (this may or may not require taking the virtual machine(s) offline during the process).


As you can see, there are multiple layers where fragmentation can be introduced, and they all act at the same time. A comprehensive maintenance solution should cover all the layers in your environment. Generally speaking, a layer closer to the physical device is easier to handle, and it’s best dealt with when first setting up the instance of Windows and the database server itself (aka set it and forget it).

From a database administrator perspective, the biggest takeaway is to deploy an automated intelligent index maintenance solution, and proactively grow the database files as needed in larger chunks, instead of relying on small auto-growth increments.