Skip to content

GO

My CheckDB Script

From time to time I get asked about checkDB, and there are many solutions out there, but I have one that I generally use that is very simple and does the job.

The script below created a stored procedure in the DBHealthHistory database that can be used to check as many databases as you can get through in a specific time interval. Here it is set to 5 minutes, but that usually gets extended for large databases.  If you set the job to daily, and the job doesn’t get through checking all the databases today, it will pick up where it left off and check the rest tomorrow.

 

Depending on the number and size of your databases you may want to run this more than once a day, or for a longer period than the 5 minutes.

Note: the 5 minute limitation is checked before starting the next check, so if you have a database that takes hours to check, that will kick off in the 5 minute interval and run until completion.

 

Read More »My CheckDB Script

Instant File Initialization (IFI)

One way to improve performance on SQL Server is with IFI or Instant File Initialization.

Normally files are zeroed out on a database growth during an AUTOGROW, RESTORE, CREATE DATABASE or ALTER DATABASE. This is done by SQL Server when the file grows, it runs through that file and writes zeroes to the entire new allocation in the file. The zeroing process can take a great deal of time, the Instant file initialization process skips this zeroing, and just allocates the file. This works since SQL Server will just write each 8k page to disk as they are used, thus overwriting the uninitialized file.

Running some tests on a local virtual machine running SQL Server 2012 (similar results tested on SQL Server 2014 and SQL Server 2016), here is what I found.

  • Creating a new database (10GB file size) took 2 minutes and 6 seconds, with IFI enabled, it took just 4 seconds.
  • Autogrow of a data file witha 1GB growth size took 13 seconds without IFI, with IFI it took less than a second.

 

For IFI to work, the user account that SQL Server is running as needs the “Perform volume maintenance tasks” policy to be enabled.Read More »Instant File Initialization (IFI)