I am really happy to announce that Kimberly Tripp and I will be delivering a post-conference in November’s SQLIntersection conference at the MGM Grand. The session is “Queries Gone Wild 2: Statistics and Cardinality in Versions 2008, 2008R2, 2012, and 2014” and will be delivered November 14th, from 9AM to 4:30PM. You can find registration […]
Joe Sack
SQL Server Performance Tuning, High Availability and Disaster Recovery Blog
SQLIntersection Post-Conference Session
Posted on: May 19, 2014 7:13 am
Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator
Posted on: April 14, 2014 10:30 am
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”
Posted on: April 2, 2014 7:41 am
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
Posted on: March 20, 2014 9:21 am
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
Posted on: March 17, 2014 7:11 am
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
Posted on: March 2, 2014 1:53 pm
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
Posted on: March 2, 2014 1:17 pm
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
Posted on: December 29, 2013 3:42 pm
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
Posted on: December 20, 2013 8:30 am
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
Posted on: December 19, 2013 1:26 pm
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
Posted on: November 28, 2013 8:22 am
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
Posted on: November 26, 2013 5:21 pm
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
Posted on: November 25, 2013 7:53 pm
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
Posted on: November 17, 2013 2:09 pm
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
Posted on: November 17, 2013 8:48 am
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
Posted on: November 16, 2013 2:00 pm
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
Posted on: November 16, 2013 9:54 am
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”
Posted on: April 19, 2013 6:28 am
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”
Posted on: January 11, 2013 5:14 pm
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
Posted on: January 10, 2013 3:16 pm
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
Posted on: December 12, 2012 1:15 am
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
Posted on: December 11, 2012 5:05 am
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
Posted on: December 8, 2012 12:35 am
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”
Posted on: November 14, 2012 5:20 am
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
Posted on: November 7, 2012 5:27 am
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
Posted on: September 30, 2012 8:46 am
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?
Posted on: September 11, 2012 11:05 am
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
Posted on: May 10, 2012 4:43 am
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 […]
