Category: SQL Server Internals

• Partitions, Boundaries and Filegroups

  

  2012-07-08 - General, SQL Server Internals

  This post shows a way to link table and index partitions in SQL Server to their filegroups as well as their boundary values. [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…]

• 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…]

• Optimizer, what if I had more CPUs?

  

  2012-03-27 - General, Performance, SQL Server Internals

  In the March issue of SQL Server Pro Itzik Ben-Gan wrote about an undocumented DBCC command that was recently discovered in the wild by Eladio Rincon (http://www.sqlmag.com/article/sql-server/options-affecting-parallelism-141505). Itzik closed the article by expressing his hope to get this statement documented [more…]