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.

Locked out of a SQL instance? Two methods to get back in without restarting the service

Hopefully you don't lock yourself out of your servers frequently. It does happen from time to time, though, and it's handy to know how to get out of the jam quickly.

The following methods will allow you to fix things while the SQL instance stays online and operational. Note that they do rely on there being an active login for NT AUTHORITY\SYSTEM (the local system account) in the SQL instance. This login is created by default, but it can be removed or disabled. (Edit: on 2008 R2, the system account login is sysadmin level, while on 2012 it has been restricted.)

If you don't have a local system account login in the SQL instance (or another suitable login you can use or impersonate), you will have to resort to the method that involves restarting the instance in single-user mode, and use either SQLCMD or a query window in Management Studio to fix things up.

Warning: be extremely careful when using either of these methods, as you will have the ability to run commands as the local system account.


The Built-In, Slightly Complicated Way

Note: service Interactive Mode is deprecated; I tested that it works on Windows Server 2008 R2 SP1. It also works on Windows 7 if you have instances installed on your workstation. (Edit: This method does not work on Windows Server 2012 RC. Use the other method.)

  1. Open a command prompt on the SQL server. We're going to create a Windows service that launches a command prompt instance running as the local system account. Do this by typing the following command (no outer quotes, the spaces are important) and pressing enter: "sc create RecoverSqlPassword binPath= "cmd.exe /k start" type= own type= interactive"
  2. Assuming you got a success message in the previous step, start the service by typing the following command and pressing enter: "sc start RecoverSqlPassword"
  3. While the command in the previous step will say it failed (this is okay), in the task bar, you will see a new window called Interactive Services Detection trying to get your attention. Bring it into focus and click the View the Message button. This will switch you to a different desktop with a command prompt window and another Services dialog that allows you to return to your own desktop. (While you can switch back and forth between the desktops as much as you want, unfortunately you can't copy/paste between them.)
  4. In the command prompt window, either use SQLCMD or browse to and launch Management Studio to fix the login issue. When you're done, close/exit the command prompt and click the "Return now" button.
  5. Clean up by deleting the service that was created in Step 1 by typing the following command and pressing enter: "sc delete RecoverSqlPassword"


The Not-Quite-Built-In, Easier Way

  1. Download the SysInternals tool PsExec and unzip the .exe file to a suitable location on the SQL server.
  2. Open a command prompt and change directory to the aforementioned location.
  3. Run the following command to start a command prompt process as the local system account: "psexec -s cmd.exe" (note: unlike the other method, this starts the process within the same command window, and without a desktop switch).
  4. Either use SQLCMD or browse to and launch Management Studio to fix the login issue. When you're done, either use the "exit" command to return to the original command prompt context, or just close the command prompt window entirely.

Index maintenance can help meet your performance SLAs

Last week, Brent Ozar told us to stop worrying about index fragmentation and get on with our lives.

He makes good points, particularly about taking full advantage of the ridiculously low price of memory right now. This makes external fragmentation a thing of the past, while the server is running. SSDs make fragmentation a thing of the past, full stop, so if you're running those, you can abandon this article now. (See you in the next paragraph!)

I want to bring up an important point that wasn't mentioned in the article: if your SQL Server instance is turned off, deliberately or otherwise, all caches, including the buffer pool are now cold, and have to be reconstituted from somewhere -- that somewhere is from disk.

This is crunch time: the data needs to be read from disk as fast as possible into the buffer pool so it can be served directly from memory the next time it's requested. This could be the most critical period of time if you have performance SLAs to meet, because as Brent mentioned, random read performance can be orders of magnitude slower than sequential reads1. Putting in place an intelligent index maintenance process helps the system perform more sequential reads, and thus improve performance. The longer the reads take, the more your SLAs are under pressure, or perhaps broken entirely.

That brings up another important point: if you're ever negotiating performance SLAs, make sure to take cold starts into account during the discussion. The business may be okay with relaxed SLAs in the 10 minutes following a restart, for example. But maybe they aren't okay with that: index maintenance could play a part in meeting the business requirements without investing in more expensive technologies. In any event, if you're going to agree to something, this is definitely one aspect you want to put in writing. Moreover, the process should be tested -- now, before you agree to anything, and periodically in the future -- to make sure that you can keep your word, and give you a proactive look at how long you have until the SLA can no longer be met.

The last thing you want during a failover is a flurry of heated questions from a CIO standing over your shoulder, but if you have a written document you can point to that says "this is what was agreed," and that's how the system is performing, that's the end of the discussion right there.


1 The transfer rate cited -- under 2 MB/s random reads -- is a somewhat misleading example because no one would set up shared storage with a 4 KB stripe size. At least I hope they wouldn't... A more typical stripe size is 64 KB, which would yield a higher transfer rate than the cited number, but would still be much slower than sequential reads.


Finding unattached database files using xp_cmdshell

Sometimes database files get left behind in the file system, maybe due to choosing to restore to a different location, or someone detached a database and forgot about it. Maybe there are rogue files in your SQL Server data folder.

To find out if there are any, here is a solution that uses the extended stored procedure xp_cmdshell to look in an instance's data and log folders for files that don't also appear in sys.master_files. This solution does not require that you have direct permission to the database files, but you will need sysadmin privileges in the instance. For safety reasons, the script only lists unattached files, it does not actually delete them. That part is up to you.


EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;

DECLARE @HkeyLocal nvarchar(18) = N'HKEY_LOCAL_MACHINE';
DECLARE @MSSqlServerRegPath nvarchar(31) = N'SOFTWARE\Microsoft\MSSQLServer';
DECLARE @InstanceRegPath sysname = @MSSqlServerRegPath + N'\MSSQLServer';
DECLARE @dataFolder nvarchar(512);
DECLARE @logFolder nvarchar(512);

EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @dataFolder OUTPUT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @logFolder OUTPUT;

DECLARE @cmd nvarchar(1024);

DECLARE @files table (physical_name nvarchar(MAX));

SET @cmd = N'dir "' + @dataFolder + N'" /s /b';
INSERT INTO @files(physical_name) EXEC xp_cmdshell @cmd;

SET @cmd = N'dir "' + @logFolder + N'" /s /b';
INSERT INTO @files(physical_name) EXEC xp_cmdshell @cmd;

DELETE FROM @files WHERE (physical_name IS NULL) OR (RIGHT(physical_name, 4) = '.cer');

			FROM @files
	) f
				FROM sys.master_files mf
				WHERE mf.physical_name = f.physical_name


EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;