Home Archives Authors Categories Latest Builds About Search

Category: T-SQL Queries

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.

  ◆  February 13, 2013
9
Database smarter with deeper insights for Microsoft SQL
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
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.

  ◆  January 11, 2013
14
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:

  ◆  December 10, 2012
6
The Benefits of Indexing Foreign Keys

Primary and foreign keys are fundamental characteristics of relational databases, as originally noted in E.F. Codd’s paper, “A Relational Model of Data for Large Shared Data Banks”, published in 1970. The quote often repeated is, "The key, the whole key, and nothing but the key, so help me Codd."

In this post, Erin Stellato (@erinstellato) discusses the benefits of adding indexes on top of declared referential constraints.

  ◆  November 20, 2012
16
Ten Common Threats to Execution Plan Quality

The quality of an execution plan is highly dependent on the accuracy of the estimated number of rows output by each plan operator. If the estimated number of rows is significantly skewed from the actual number of rows, this can have a significant impact on the quality of a query's execution plan. Poor plan quality can be responsible for excessive I/O, inflated CPU, memory pressure, decreased throughput and reduced overall concurrency.

  ◆  November 13, 2012
11