Jan 222013
 

In my previous post on streamlining the operations of the transaction log, I discussed two of the most common causes of extra log records being generated: dead weight from unused nonclustered indexes and page split operations (that cause index fragmentation). Assuming you’ve read that post, I mentioned that there are more subtle problems that can be detrimental to transaction log performance, and I’m going to cover these here.

Continue Reading »
Jan 182013
 

Earlier in this series (Part 1 | Part 2) we talked about generating a series of numbers using various techniques. While interesting, and useful in some scenarios, a more practical application is to generate a series of contiguous dates; for example, a report that requires showing all the days of a month, even if some days had no transactions.

Continue Reading »
Jan 162013
 

There are many use cases for generating a sequence of values in SQL Server. I'm not talking about a persisted IDENTITY column (or the new SEQUENCE in SQL Server 2012), but rather a transient set to be used only for the lifetime of a query. Or even the simplest cases – such as just appending a row number to each row in a resultset – which might involve adding a ROW_NUMBER() function to the query (or, better yet, in the presentation tier, which has to loop through the results row-by-row anyway).

Continue Reading »
Jan 092013
 

Since Microsoft revamped the licensing model for SQL Server 2012, it is especially important to do some thoughtful analysis before you decide exactly which processor to use for a database server that will be running SQL Server 2012. The move to core-based licensing for SQL Server 2012 Enterprise Edition means that a careless decision about precisely what processor you will be running on can cost both a great deal of money and a great deal of performance and scalability.

Continue Reading »