Category: Performance

• A Join A Day – Join Hints

  

  2012-12-20 - A Join A Day, Fundamentals, General, Hints, Performance, Series

  SQL Server cannot do an exhaustive search of all possible execution plans when compiling a query. On its quest to find the best plan, it sometimes misses the mark. See how join hints and query hints can help in a situation like that. But be aware, using join hints can backfire. Know the risks before you start using them.

  [more…]

• The Mysterious “sp_” System Procedure Prefix

  

  2012-05-13 - General, Performance, SQL Server Internals

  If you have been working with SQL Server for a while you probably know that you should not select names for your stored procedures that start with the three characters “sp_”.

  Today I would like to take a closer look at all the myths and facts around this prefix. All examples in this article have been tested on SQL 2008R2 and on SQL 2012.

  [more…]

• Tracing the Actual Execution Plan for a single Query

  

  2012-05-05 - General, Performance

  SQL Server offers several ways to get to the execution plan for a particular query. Most of them however only provide the estimated execution plan without actual counts and statistics. In this article we are going to look at a way to get to the actual execution plan of a particular query using a trace. [more…]

• The Unloved Backward Scan

  

  2012-04-29 - General, Performance, SQL Server Internals

  When a query requires rows to be sorted, either directly requested with an [tt]ORDER BY[/tt] clause or because one of the iterators requires it, SQL Server has two options to guarantee that order. The obvious one is to utilize a sort iterator. If the data comes from an index (clustered or covering), SQL Server can also use an “Ordered Scan” of the data. Depending on the requested sort direction, such an ordered scan can be either forward or backward.

  This would hardly be worth an article, if there wasn’t the peculiarity that SQL Server obviously does not like the idea of having to execute an ordered scan that is directed backwards.

  [more…]

• TOP N Sort – A Little Bit of Sorting

  

  2012-04-22 - General, Performance, SQL Server Internals

  Sorting is one of the most often used functions in SQL Server. It is used when creating indexes. It allows us to retrieve data in a particular order. It also is used to filter out repeated rows in the context of a DISTINCT request. The SQL Server Team spent a lot of effort optimizing the sort algorithms to give the best possible performance in each context. This blog post looks at the TOP N Sort optimization. [more…]