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:
Numerous licensing changes were introduced in SQL Server 2012; the most significant was the move from socket-based licensing to core-based licensing for Enterprise Edition. One of the challenges that Microsoft faced with this change was providing a migration path for customers that previously used Server+CAL based licensing for Enterprise Edition prior to SQL Server 2012. Customers under Software Assurance can upgrade to SQL Server 2012 Enterprise Edition and still use Server+CAL licensing (also known as "grandfathering") but with a limitation to 20 logical processors, as documented in the SQL Server 2012 Licensing Guide.
During some restore operations in SQL Server, the transaction log redo step might be taking an unusually long time. Depending somewhat on the version and edition of SQL Server you've installed, you may be able to increase performance by tinkering with the readahead performance for the redo operations. To do this, you should use the MAXTRANSFERSIZE parameter of the RESTORE statement. For example, if you set MAXTRANSFERSIZE = 1048576, it'll use 1MB buffers.