A first look at SQL Server 2012 Availability Group Wait Statistics

I wrote an article for Simple-Talk that was published last Monday: A first look at SQL Server 2012 Availability Group Wait Statistics AlwaysOn Availability Groups have their own host of associated wait types, so I thought I would investigate the various wait stats that accumulate based on different scenarios.  I see this topic evolving over […]

Tale of the Inconsistent UDF Logical IOs

This post was motivated by an email question I got this week. Imagine you have the following scalar UDF: CREATE FUNCTION dbo.RemoveYear (@date datetime) RETURNS datetime AS BEGIN DECLARE @removeyear datetime = DATEADD(year, -1, @date);   RETURN(@removeyear); END; GO Now – aside from the fact that this function doesn’t really need to exist in the […]

Selectivity Guesses in absence of Statistics

Let’s say you have a heap table with 1,000,000 rows in it.  Let’s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics). What kind of selectivity guess would the optimizer make for a query like the […]

Being Mindful of Cursor Lock Overhead

This post is just a reminder to be attentive to the locking overhead of your Transact-SQL server cursors. For example, the following cursor is using default options in SQL Server 2012 to iterate row-by-row through the Employee table.  I’m declaring a variable and populating it with the BusinessEntityID from each row (and I’m not doing […]