Blog

• Comparing System Objects between SQL Server 2012 and SQL Server 2008R2

  

  2012-07-21 - General, SQL Server 2012

  This post shows a way to compare the system objects between a SQL Server 2008R2 and a SQL Server 2012 instance.

  [more…]

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

• PRINT vs. RAISERROR

  

  2012-05-27 - General, T-SQL Statements

  This article shows a way to use RAISERROR as a PRINT alternative. It also explains in which situations it might be the preferred choice.

  [more…]

• Capturing Parameters of a Stored Procedure Call

  

  2012-05-18 - General, Tracing

  Recently a user on the PSSUG mailing list asked how he could capture the parameters passed to a stored procedure on every execution. In this article I would like to show a few different options to accomplish that. [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…]

• LAG-ging Behind [T-SQL Tuesday #029 – Let’s have a SQL Server 2012 party]

  

  2012-04-10 - General, T-SQL Tuesday

  This article introduces and explains the new LAG function in SQL Server. It also shows how to use it for SQL Server wait time analysis. [more…]

• How to Calculate MAXINT

  

  2012-04-07 - General

  Trying to directly calculate MAXINT for the BIGINT data type in SQL Server will either cause an arithmetic overflow error or produce inaccurate values. This article explains how to calculate this value accurately. [more…]