Hmmm… What’s This?

Published On: 2017-09-20By:

OK So, I am doing some digging and peaking around again in SQL Server and came across a database option called Date Correlation Optimization Enabled = False. Honestly, I had no clue what it did, so I took it as a learning opportunity to look into it and do a little research. Who knows, it may actual help me solve one of the many problems I run into day to day for clients.

Syntax

ALTER DATABASE DEMO
   SET DATE_CORRELATION_OPTIMIZATION ON;

So, What Does It Do?

According to MSDN – The DATE_CORRELATION_OPTIMIZATION database SET option improves the performance of queries that perform an equi-join between two tables whose date or datetime columns are correlated, and which specify a date restriction in the query predicate.

How many of you read what MSDN says and thinks “wuuuuuttt, English please”? I do.

In English

Basically, it uses a foreign key relationship key between tables in SQL Server to enhance performance of date and date time queries when the dates fall within a certain defined range of each other (correlates). Ok that’s cool, but what’s the big deal? The power really comes in for things like reporting, validation, and data warehouses. With this option turned on, SQL Server maintains statistics between correlated columns and creates improved execution plans that reads less data.

Let’s See It in Action

Consider this, all internet orders that are received have a must send out by date (due date) of 10 days after order is received. Therefore, the OrderDate and DueDate are correlated, related to each other.

Here is a query you would normally run.

SELECT  *
FROM    InternetOrders AS i
        INNER JOIN InternetOrderDetail AS d ON i.InternetOrderID = d.InternetOrderID
WHERE   i.OrderDate BETWEEN '20170801' AND '20170901';

Without DATE_CORRELATION_OPTIMIZATION turn on the optimizer would create a plan just like anything else, however with it set to ON the optimizer can make more granular execution plans.

Here’s how

With each INSERT, UPDATE and DELETE between these two tables SQL Server is gathering statistics which helps the optimizer infer the query to be more like the one below. This is where the power comes in. The optimizer can better narrow down the records it needs to read and therefor returning faster results.

Here is the way SQL interprets the dates now that correlation is turned on and statistics are being gathered. It based on those statistics it can now infer that each DueDate is exactly 10 days after the OrderDate.

SELECT  *
FROM    InternetOrders AS i,
        InternetOrderDetail AS d
WHERE   i.InternetOrderID = d.InternetOrderID
        AND i.OrderDate BETWEEN '8/1/17' AND '8/15/17'
        AND d.DueDate BETWEEN CAST('20170801' AS DATETIME) + 10
                      AND     CAST('20170901' AS DATETIME) + 10;

Depending on the number of records in the two tables this can be a VERY significant decrease to execution times.

The Caution

You should not enable DATE_CORRELATION_OPTIMIZATION in update-intensive database environments. SQL Server keeps all the correlation information in statistics form, this means with every INSERT, UPDATE and DELETE you gain additional overhead.

As always, be sure to test it before you use it in production.

I’m speaking at SQL Saturday SAN–This Weekend!

Published On: 2017-09-19By:

SQL SaturdayAlthough this year has been pretty busy and I haven’t been speaking a whole lot this year, I’ve got a couple of sessions coming up this weekend at SQL Saturday San Diego!

I’ve got two sessions on the schedule; the first one is an introductory session to SQL Server Analysis Services Tabular modeling, and the second one is a bit of a more advanced (call it Intermediate) session where I discuss and demonstrate managing databases using Database projects in SQL Server Data Tools.

The Tabular presentation is designed for folks who are new to SSAS in general or the tabular flavor of it. I focus mostly on the development process of these apparatus and how to move from raw data to a model that is useful for business users to explore on their own.

In the SSDT session, I discuss some of the advantages of utilizing database projects to help manage your database schema in Visual Studio. This presentation also has a lot of demo time in it, and I help explain how to start from scratch and manage what I fell is the most important part of schema management: deployments.

We (DCAC) are also sponsoring, so if you are in the southern California area this weekend, come on out to SQL Saturday, say Hi, and learn some new SQL Server stuff!

Come find me at Microsoft Ignite

Published On: 2017-09-18By:

Did you miss the PASS Summit Attendee Orientation? The webcast was recorded.

Published On: 2017-09-15By:

If you were planning on attending the PASS Summit Attendee Orientation webcast on September 14th and couldn’t make it for whatever reason, have no fear, it wa20141105_004131s recorded and the recording has been posted online. If you forgot to register (or if you did) just to back to the registration page and fill it out and the video will magically appear.  It’s that easy. The video is about 45 minutes long give or take. We used a new Skype service to broadcast the video so there were some issues at the begining (which I cut from the video before I uploaded it) so you don’t have to watch nothing for 10 minutes.

If you’re just looking for the information on Speaker Idol and the Karaoke Party, that info is linked here. The tickets for the Karaoke party are going fast so get signed up for a wristband if you’re going to be in Seattle on Tuesday night.

Denny

The post Did you miss the PASS Summit Attendee Orientation? The webcast was recorded. appeared first on SQL Server with Mr. Denny.

1 2 3 343

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.