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