Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and happy to announce that it was just published: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator A big thanks to the contributor and reviewer team – including […]

Deck from “Practical SQL Server Cardinality Estimation”

I uploaded the deck from my session for SQLSaturday #287 Madison 2014 and you can download it here. It was an excellent event!  Great organizers, volunteers, venue, speakers – and a matching big turnout with lots of first-time SQLSaturday attendees.  Grateful for the experience and hope to be back again next year.

MSTVF Fixed Cardinality Value in SQL Server 2014

In SQL Server 2014 CTP2 in the AdventureWorks2012 database, execute the following batch: The first query uses the legacy cardinality estimator and the second query uses the new cardinality estimator.   Both queries are referencing a multi-statement table valued function. Looking at the plan tree view in SQL Sentry Plan Explorer for the legacy CE plan, […]

March Speaking Engagements

If you’re in the area, just a heads-up that I’ll be speaking at the following events: PASSMN Minnesota SQL Server User GroupLocation:    3601 West 76th Street, Suite 600 Edina, MN 55437Date:    March 18, 2014Time:    4:00 PM – 6:00 PM SQLSaturday #287Location: 6000 American Parkway, Building A, Madison, WI 53783Date: March 29, 2014 Time: My session […]

Using the SQL Server 2014 Database Compatibility Level without the New CE

Consider the following scenario: You want to migrate all databases on a specific SQL Server instance to a new SQL Server 2014 instance You want to leverage new SQL Server 2014 functionality and move to the latest database compatibility level You don’t want to enable the new Cardinality Estimator right away (various reasons why this […]

For the New CE, Database Session Context Matters

Testing on SQL Server 2014, CTP2, version 12.0.1524, imagine you have two databases – one called T1 and one called T2 configured with database compatibility levels as follows: Now with database T1, we know that using a compatibility level of 120 means we’ll be using the new cardinality estimator (CE) – assuming we’re in the […]

Troubleshooting the new Cardinality Estimator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

Non-Join Cross-Table Predicate Correlation Changes

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

More on Exponential Backoff

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator In the previous post, I looked at root-level cardinality estimates vs. […]

Comparing Root-Level Skews in the new Cardinality Estimator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version In the previous post I showed you how to start using the new SQL Server 2014 CE model, but […]

Cardinality Estimation Model Version

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator In this post I just wanted to step back and discuss how to enable and identify the Cardinality Estimator version, at least as […]

The CSelCalcAscendingKeyFilter Calculator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series (yeah – I’ve decided to call this a series, since there will be quite a bit to talk about, time permitting): A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II So for this post, I […]

“CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II

At the end of my last post, “CSelCalcCombineFilters_ExponentialBackoff” Calculator I gave the following homework: Homework assignment – add another member column to the mix that has a 0.100 selectivity (for example – county) and compare the estimates using a pre-2014 and 2014 CTP2 compatibility mode. Here is the answer-key.  I’ll start off with adding a […]

“CSelCalcCombineFilters_ExponentialBackoff” Calculator

One more post for the day, just while I have the blogging bug… Per my last post, we saw that the query_optimizer_estimate_cardinality event can provide some interesting information in SQL Server 2014 around how cardinality estimates get calculated. I have a setup I’ve used in the past to demonstrate column correlation challenges (see my post, […]

A first look at the query_optimizer_estimate_cardinality XE event

This weekend I set up a Windows Azure virtual machine running SQL Server 2014 CTP2 (12.0.1524).  I had a local copy of SQL Server 2014 CTP2 in VMware as well, but thought it would be more fun to explore it on Windows Azure (kill two explorations with one stone).  On a side note, I’m really […]

SQLPerformance.com “The Case of the Cardinality Estimate Red Herring”

My fourth guest blog post was published today on SQLPerformance.com: The Case of the Cardinality Estimate Red Herring This was inspired by a case that Erin Stellato and I worked together on a few months ago. While I’ve talked quite a bit this year about the importance of investigating bad cardinality estimates, this blog post […]

New Course: “SQL Server: Troubleshooting Query Plan Quality Issues”

My latest online course, “SQL Server: Troubleshooting Query Plan Quality Issues” was published today by Pluralsight.  The short description is as follows… “Learn how to identify, diagnose, and prevent problems where SQL Server chooses the incorrect query plan for your critical queries, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards.” This 2 […]

Detecting Selectivity Guesses

I’ve been mulling over a potential Microsoft Connect item regarding selectivity guess warnings, but I’m uncertain if it would have enough demand to drive product team attention.  Selectivity guesses, which I talked a little about in the “Selectivity Guesses in absence of Statistics” post, are sometimes referred to as “magic numbers”, “heuristics” or just plain […]

Detecting Cardinality Estimate Issues with sys.dm_exec_query_stats

SQL Server 2008 R2 SP1 and SQL Server 2012 RTM+ includes actual row count statistics in the sys.dm_exec_query_stats dynamic management view.  This includes the total_rows, last_rows, min_rows and max_rows columns, which are intuitively named. One potential use of the row statistics data is to detect cardinality estimate issues where the actual count of rows is […]

Thinking about the inaccurate_cardinality_estimate Event

SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post. Background reading If you’re unfamiliar with this event, there isn’t anything as of today in Books Online, but there are two blog references / discussions: How the query execution […]

Exploring Column Correlation and Cardinality Estimates

Last Thursday I presented a session at the PASS Winter 2012 Performance Palooza.  It was a great experience and I appreciated the opportunity.  The topic was “Troubleshooting Query Plan Quality Issues” and I received a few email questions after the presentation, so I thought I would walk through the full scenario, weaving in a few […]

Simple-Talk Article: “Fixing Gatekeeper Row Cardinality Estimate Issues”

I wrote a new article for Simple-Talk that was published recently: Fixing Gatekeeper Row Cardinality Estimate Issues In the article I talk about a specific cardinality estimation issue you may see for relational data warehouse queries (specifically against star schemas) and a few recommended solutions that may help eliminate or at least reduce the overall […]

Distributed Query Plan Quality and SQL Server 2012 SP1

SQL Server 2012 Service Pack 1 is out today and it fixes an issue that has been around for several versions regarding distributed queries and poor quality execution plans due to bad cardinality estimates when the distributed query principal had insufficient permissions to gather the applicable statistics.  The description of this fix can be found […]

Recap: SQLSaturday #149

Yesterday’s SQLSaturday #149 (Minnesota) was most excellent.  A few comments/observations/recaps: The event was extremely well organized and professional – while still managing to maintain a regional, relaxed atmosphere across 400+ attendees.  The organizers did an absolutely incredible job and should be proud of themselves.  There were several folks involved, and plenty of “orange shirts” walking […]

What Does the Future Hold for Cardinality Estimates and Cost Models in Windows Azure SQL Database?

I’ve been interested in exploring what changes may be coming through in SQL Azure (Windows Azure – “SQL Database” now) around query execution cost models and cardinality estimates being fed to them.  This was motivated by a couple of things… First of all, I saw a blog post from Grant Fritchey where he noticed that […]

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