DaveWentzel.com            All Things Data

January 2012

Automatic Database Snapshot Creation Script

Whenever I'm writing or executing scripts that might be destructive I of course wrap my code in appropriate transaction control blocks.  However, transaction control blocks (BEGIN TRAN/ROLLBACK/COMMIT) may not always be possible if you are running code across .sql files, manipulating foreign keys, setting up replication, needing a custom grain of transaction control, etc.  In these cases Database Snapshots work great.  

A database snapshot is kinda like a live, queryable copy of a database that is transactionally consistent as of when the snapshot is created.  The purpose of this post is not to get into the guts of how database snapshots work.  One of the nice features of a database snapshot is that you can RESTORE your live database back to the snapshot with one simple RESTORE command.  You probably woouldn't do this on a production database, but it is valuable for dev databases.  

However, MS, in my opinion, could've made the CREATE syntax for database snapshots a little bit easier.  The big problem is if your database has more than a few db files you have a rather lengthy CREATE DATABASE statement.  This stored procedure simplifies the process.  You pass it @SnapshotName and it builds the code for you to create a database snapshot.  It assumes your "sparse" files will live in the same directory structures as the underlying files.  It then creates a RESTORE command that you can run later, if needed, to revert back to your snapshot.  

The script will generate output similar to the following...

I use this script all the time in my dev environments.  I hope you find it useful too.  


Check Out This Tool, PAL

PAL is the Performance Analysis of Logs Tool.  I've used this recently and found it works really well and is simple to set up.  What you do is collect your PerfMon log files (blg format is fine).  Then you install PAL (laptop is fine) and the "SQL Server threshold file", which is provided by Microsoft at codeplex.  The tool then identifies issues and finds correlations between counters.  It will then produce various reports that highlight possible performance problems.  

The beauty, really, is the threshold file.  If you use the MS-supplied threshold files you can rest-assured you will find performance issues vs. Best Practices.  So, if you are really confused as to what the current, industry-accepted level of Context Switches/sec is, just use the threshold file.  You can also customize the threshold files to meet any needs that you may have for your hardware or application, or if you just don't think you agree with some of the MS-supplied values.


When NOT to index a Foreign Key

It's common for people to want to index all of their foreign key columns.  In general this is probably a good idea and is well documented by many reliable sources.  However, there are quite a few cases where an index on a foreign key column probably is not useful.  

Assume your database has a series of tables with standard "auditing" columns (CreateUsrID, CreateDate, LastUpdateUsrID, LastUpdateDate) where the UsrID columns refer back to a Usr table (UsrID).  

This is a common design pattern.  In most cases I've seen foreign keys created on CreateUsrID and LastUpdateUsrID that refer back to Usr(UsrID).  DRI in this case is probably a good idea.  But are indexes on CreateUsrId and LastUpdateUsrID really needed?  Indexes probably won't be needed in this design if:

  1. You rarely fetch data from the tables by searching for rows Created or LastUpdated by a given UsrId.  In other words, you never fetch data by searching for the parent key rows and then joining to get the child rows.  
  2. You rarely DELETE or UPDATE a UsrID from the parent table.  In other words, you do not benefit from having cascading DRI.  In our auditing example, if you rarely update all of UsrID 12's rows to UsrID 14 (change the row ownership from Mike to Bob) then an index on the foreign key column again won't be needed.  

So there you have it.  If you aren't traversing your model from parent to child rows or using cascading DRI, then an index on a foreign key column probably won't help you.  


The Perfect Replacement for BACKUP LOG WITH NO_LOG or TRUNCATE_ONLY in SQL 2008

The WITH NO_LOG and TRUNCATE_ONLY options of BACKUP LOG were deprecated with SQL 2008.  The rationale is that if you really want to use these options just switch your database to SIMPLE recovery mode.  It's a good solution, but lots of people have deployed lots of scripts that expect those options to be available in a worst case scenario (such as totally out of disk space).  Furthermore, changing your recovery mode will break your log chain.  

Another place where people have used these deprecated options is development envs.  If you want your scripts to be as close to model as possible (ie, you do log backups in both PROD and DEV, but you don't actually keep your DEV log backups) then you really don't want some envs in SIMPLE and some in FULL recovery mode.  I've seen lots of scripts that read metadata to determine if NO_LOG or TRUNCATE_ONLY should be used.  

Frankly I've never used NO_LOG or TRUNCATE_ONLY, there's a much better solution that avoids cryptic SQL 2008 error messages and accomplishes the exact same thing...use the NULL file.  In Unix operating systems if you send something to the file called /dev/null essentially the data is discarded and the operation reports success.  This trick works in SQL Server as well.

Problem solved.