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

Advertisements

SQL Server – Another reason why using SELECT * is bad for performance

I think it’s fair to say that most database professionals will assert that SELECT * is bad practice.  For lots of reasons: code maintainability, it’s lazy, prevents being able to properly use covering indexes.  However, a recent Linked In post by Mike West made me look into whether there was any performance concerns when using SELECT * versus selecting a column list when there aren’t any supporting covering indexes.

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