The ask this month is to share go-to short code snippets with the community. I think its right to start by showing appreciation for some of the widely adopted tooling in the community such as sp_WhoIsActive, the First Responder Kit, Ola’s Maintenance Solution and DbaTools. Without these available we wouldn’t have half the snippets and shortcuts available to help us maintain, monitor and tune the environments we’re working within as effectively as we can.
The Database Restore
One of my favourite snippets uses the First Responder Kit and is for the sp_DatabaseRestore procedure. We need to be able to efficiently restore databases and personally this one stands out as the first time I came to using this, it actually showed up some issues with how we were taking backups at that time.
One of the things it highlighted was that even with backup processes being set up to use Ola’s DatabaseBackup procedure they were using non-standard filenames by specifying the FileName and AvailabilityGroupFileName parameters. This ended up causing all sorts of issues trying to get the restore script to correctly identify the right backup and logs to restore so we worked through the environments reviewing the jobs and stripped them back to use only the parameters we needed to override – and we made sure restores using this script were tested so we had confidence in them.
On to the script itself, below is what I use in a template along with the parameters which covers my typical use cases:
EXEC DbaTools.dbo.sp_DatabaseRestore
@Database = '<Database,varchar,>',
@RestoreDatabaseName = '<Database,varchar,>_Restore',
@BackupPathFull = '\\BackupServer\<Environment,varchar,>\<Database,varchar,>\FULL\',
@BackupPathDiff = '\\BackupServer\<Environment,varchar,>\<Database,varchar,>\DIFF\',
@BackupPathLog = '\\BackupServer\<Environment,varchar,>\<Database,varchar,>\LOG\',
@RestoreDiff = 1,
@RunRecovery = 1,
@StopAt = '20210101120000',
@Execute = 'N';This is more parameters than I’d need at one time, but its easier to comment the odd lines out than have to add things in on the fly. If we’ve got someone who managed to delete a whole chunk of data we’ve got the ability to restore a side-by-side copy at a point in time with the @RestoreDatabaseName and @StopAt parameters. Or maybe we’re restoring to a secondary server for an Availability Group or Log Shipping we can restore the backups and logs and leave it in a recovering state ready for joining or applying future logs by setting @RunRecovery as false.
The parameter which I adore across Ola’s scripts as well database restore above is the @Execute parameter and I always set this as N when saving, sharing or templating any scripts. Being able to have that one last sense check of what’s going to happen has saved me more than enough times.
The Columnstore Statistics
I’ve been interested in utilising columnstore recently (non-clustered in particular) to help with some larger analytical workloads and part of this journey has been understanding the row groups which it uses. There’s the system view sys.column_store_row_groups which shows the row groups and details such as the partition, number of rows present, number of deleted (or updated) records and the current state of the row group. What I’ve found particularly interesting though is the DMV below:
SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE OBJECT_NAME(object_id) = 'MyTableName'
ORDER BY row_group_id
This contains all the information from the system view but also adds details for why the row group was trimmed as well as why the group was moved into being compressed. This can be really interesting to start diving into when creating or maintaining the indexes and may help highlight elements for further investigation. For example you might see some of the groups which aren’t filled and have been trimmed due to DICTIONARY_SIZE which could be caused by a large number of unique values filling the dictionary, such as if a text field were being included in the index. It also shows how the groups are being compressed too and whether its happening naturally though the TUPLE_MOVER or things are being forced through index reorganise operations (not shown above) for example. Full details of the view can of course be found in the documentation.
There’s so much to learn with columnstore and its nuances and limitations depending on your data or the version of SQL you’re using. Whether it’s from the official documentation or some of the fantastic content which Niko has published over the years there’s so much to dive into, but maybe this helps give someone else a jumping off point for their next adventure!
Andy Brownsword 