Memory Grant Execution Plan Statistics

SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element – but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="11016" RequiredMemory="47368" DesiredMemory="52808" RequestedMemory="52808" GrantWaitTime="0" […]

SQL Server 2012’s RetrievedFromCache Attribute

SQL Server 2012 includes a new RetrievedFromCache attribute in the query execution plan. Let’s say I execute the following query immediately after executing DBCC FREEPROCCACHE: What value for RetrievedFromCache would you expect to see?  In this example, I saw the following attribute value (with the attribute highlighted and StmtSimple abridged for clarity): <StmtSimple StatementCompId=”2″ StatementEstRows=”5″ […]

SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass

  This post shows a few examples of Sort related SpillToTempDb execution plan warnings and the associated SpillLevel attribute.  This blog post is based on SQL Server 2012, version 11.0.2316 and I’m using the AdventureWorksDW2012 database and creating a separate version of the FactInternetSales table called FactInternetSales_Spill: SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, […]

Off-Topic PSA: Beware of Cramming

Unrelated to cramming for certification exams, but back in March I started getting strange text messages in the middle of the night – sometimes multiple times a night.  The texts varied between celebrity gossip and animal trivia.  For example: “Lindsay Lohan’s dad, Michael Lohan, and girlfriend Kate Major are expecting their first child together. For […]

SQL Server Pro article–“Getting Started with Transactional Replication”

I wrote a beginner’s article for Transactional Replication which was published in the July 2012 edition of SQL Server Pro: “Getting Started with Transactional Replication” My last article with them (at the time SQL Server Magazine) was published way back in September 2002!  It was my very first published item – called “Put the Hammer […]

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

The SQL CAT team identified three common patterns for customers who were actively testing and deploying SQL Server 2012 high availability and disaster recovery solutions.  One pattern is to use a multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery.  A second pattern is to use AlwaysOn Availability Groups (AG) for local […]