2013 - Page 7 of 7 - SQLPerformance.com

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.

Paul White ◆ February 18, 2013

4

The Halloween Problem – Part 2

In the first part of this series, we saw how the Halloween Problem applies to UPDATE queries. In this post, we will see how the same underlying issue can affect INSERT and DELETE statements.

Paul White ◆ February 15, 2013

4

The Halloween Problem – Part 1

Much has been written over the years about understanding and optimizing SELECT queries, but rather less about data modification. This series of posts looks at an issue that is specific to INSERT, UPDATE, DELETE and MERGE queries – the Halloween Problem.

Paul White ◆ February 13, 2013

9

If you are using indexed views and MERGE, please read this!

Here is some information about an important MERGE "wrong results" bug, involving indexed views, that could be affecting the accuracy of your queries right now, and what options you have for working around the problem.

Aaron Bertrand ◆ February 6, 2013

6

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.

Paul Randal ◆ 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.

Aaron Bertrand ◆ January 18, 2013

5

Generate a set or sequence without loops – part 2

In my previous post, I talked about ways to generate a sequence of contiguous numbers from 1 to 1,000. Now I'd like to talk about the next levels of scale: generating sets of 50,000 and 1,000,000 numbers.

Aaron Bertrand ◆ January 17, 2013

8

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

Aaron Bertrand ◆ January 16, 2013

14

Potential enhancements to ASPState

Many people have implemented ASPState in their environment. Some people use the in-memory option (InProc), but usually I see the database option being used. There are some potential inefficiencies here that you might not notice on low volume sites but that will start to affect performance as your web volume ramps up.

Aaron Bertrand ◆ January 11, 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.

Glenn Berry ◆ January 9, 2013

22