SQLPerformance.com

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

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

SQL Sentry's Aaron Bertrand (@AaronBertrand) digs into the left anti semi join, comparing NOT IN, NOT EXISTS, OUTER APPLY, EXCEPT, and OUTER JOIN methods.

Aaron Bertrand ◆ December 27, 2012

27

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.

Paul Randal ◆ December 17, 2012

11

SQL Server 2012 Service Pack 1 & Cumulative Update 1

Recently, Microsoft pushed out Service Pack 1 for SQL Server 2012, and they quickly followed up with Cumulative Update 1 for Service Pack 1. The reason for this is that the service pack – due to longer development and regression testing cycles – did not contain any of the fixes from RTM Cumulative Updates 3 & 4. Since many people have waited – based on mostly speculative bias at this point – to even start testing SQL Server 2012 until Service Pack 1 was released, I thought it might be useful to cover a couple of the scenarios you might come across.

Aaron Bertrand ◆ December 12, 2012

6

TRANSACTION_MUTEX and Multi-Session Transaction Access

I recently encountered high TRANSACTION_MUTEX accumulated wait time on a client system. I couldn’t recall a case where I saw this wait type as near the top of the “high waits” list and I was curious about what factors could push this type of overall wait time.

The Books Online definition of TRANSACTION_MUTEX is that it "occurs during synchronization of access to a transaction by multiple batches." Not many areas within the SQL Server engine expose this type of functionality, so my investigation was narrowed down to the following technologies:

Joe Sack ◆ December 10, 2012

6

Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts

Aaron Bertrand (@AaronBertrand) discusses several aspects of DBCC CHECKDB and offers suggestions to minimize the impact this crucial operation can have.

Aaron Bertrand ◆ November 29, 2012

27