SQL Server – When is a new database appropriate?

I frequently get requests from our developers to create new databases.  This is no biggie, and using the Standardising new database creation using Powershell and SMO scripts takes no time at all to implement.  However, something that usually isn’t considered at the point of a request is: why do we need a new database?

Continue reading

SQL Server – Achieving minimally logged inserts into partitioned table with a computed column as the partitioning key

A while ago, I started a thread on SQLServerCentral discussing tactics to go about partitioning a huge table in respect to index alignment.  During the discussion, Steve Hall pointed out a great way of working with cycling partitions using a computed column that meant some of the maintenance aspects of partition archiving were a nice doddle.  I’ve since incorporated this tactic as a standard method to partition tables that have a rolling window of archiving, and for the most part it works very nicely.

Recently, we’ve had a requirement to move another large heap table into a partitioning structure to ease maintenance on the table, and ensure we aren’t just storing unneeded data forever.  Afterall, querying a 98GB heap with around half a billion rows just isn’t a good idea anyway!  In this instance, however, the old data needed migrating into the new table, which seemed to create a bit of a problem in terms of the logging level. Continue reading

When to create Indexes on Temp Tables

Creating indexes on Temp Tables sometimes appear to be a contentious issue. Amongst a number of things, they prevent Temporary Table caching, as described by Paul White in his blog post on Temp Table Caching Explained.

However, if we have a Temp Table with a large volume of data, and are then going to be manipulating subsets of this data, indexes are going to be beneficial in the same way as a “normal” table: they allow SQL Server to perform seek operations instead of just table scans, which can drastically affect the performance of your queries.
Continue reading

Scripting Indexes using Powershell and SMO

As part of a review of the indexes across my new workplace’s estate, I wanted to get all the indexes scripted up into re-runnable scripts which could then be loaded into source control.  However, considering the number of databases, tables and indexes, doing this manually would be both time consuming and mind numbingly boring; as such, I decided to create a powershell function to do this.
Continue reading