Home Archives Authors Categories Latest Builds About Search

Category: SQL Performance

The Halloween Problem – Part 4

The Halloween Problem has a number of important consequences for SQL Server execution plans. In this final part of the series, we look at some of the tricks the optimizer uses to avoid adding an Eager Table Spool to every update query that might encounter the Halloween Problem.

  ◆  February 20, 2013
15
The Halloween Problem – Part 3

The MERGE statement (introduced in SQL Server 2008) allows us to perform a mixture of INSERT, UPDATE, and DELETE operations using a single declarative statement. The Halloween Protection issues for MERGE are, logically enough, mostly a combination of the requirements for the individual operations performed, but there are some important differences and a couple of optimizations that apply only to MERGE.

  ◆  February 18, 2013
4
Database smarter with deeper insights for Microsoft SQL
Trimming More Transaction Log Fat

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.

  ◆  January 22, 2013
8
Generate a set or sequence without loops – part 3

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.

  ◆  January 18, 2013
5
Generate a set or sequence without loops – part 1

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).

  ◆  January 16, 2013
14
Selecting a Processor for SQL Server 2012

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.

  ◆  January 9, 2013
22
Trimming the Transaction Log Fat

In many SQL Server workloads, especially OLTP, the database’s transaction log can be a bottleneck that adds to the time it takes a transaction to complete. Most people assume that the I/O subsystem is the real bottleneck, with it not being able to keep up with the amount of transaction log being generated by the workload.

  ◆  December 17, 2012
11