Hmmm… What’s This?

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

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.

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.

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.

What does this little check box do?

Ever wander around SQL Server properties and wonder what these little check boxes turn on? I do, and I get very tempted to check them. Here is one of those tempting little boxes that seems pretty handy, Use query governor to prevent long running queries.

Syntax

How Does it Work?

It’s simple. This option, available in SQL Server 2008 standard and forward, will prevent long running queries based on run time measured in seconds. If I specify a value of 180 the query governor will not allow any execution of a query that it estimates will exceed that value. Notice it says ESTIMATES which means it will be based on optimizer estimates and not ACTUAL run times. It does NOT KILL an actively running query after designated amount of time.  There is no worries for rollback scenarios or partial data.

CAUTION

This is an advanced option, keep in mind this is a server instance wide option. This will also effect your maintenance queries, so please use with caution, this is not “a let me check this box for fun” option.

But Wait There’s More

Now there is a query “transaction” based option available to us that will limit a specific query. This option will estimate a transaction and prevent it from running if it will go over the boundary we have set. Notice we set the limit before the query and then back to 0 after.

Again, playing with any old check box is not a recommended practice. Make sure you research it first and understand the full impact before checking that tempting little box.

Initial SQL Server Configurations

Wonder if I Do Things Differently?

I am always wondering what other DBA’s do and if I am doing things differently. One such thing is my initial server setups, basically, what I configure for each of my new servers. So, why not blog about it and see what others chime in with after they read this. Keeping in mind that everyone has different requirements and different ways that they like to do the actual configurations.

For now, I am not going to go into what each one of these configurations do and why I choose the value I do. That’s for another time. If you want that information you can always go to Books Online or just Google it. In this case, I am just going to give you a running list with scripts; that I’ve added too over the years based on best practices and experience.

How Does Yours Compare?

I’d really love to hear what others do and if I may have missed something that you like to implement that may benefit me as well.  So leave a comment, tweet to me, or send me an email let’s compare notes.

The List

So here are the basics setups I do on every server post install in no particular order.

* Value varies based on server configuration

  1. Min and Max Memory *

  1. Enable and Configure Database Mail ( This is only to enable, full script will be in later post)

  1. Set Default Database Locations

  1. Set SQL Agent Job History Retentions

  1. Set Cost threshold for Parallelism *

  1. Set Max Degree of a Parallelism *

  1. Set Optimize for Adhoc work loads

  1. Change Number of Error Logs

  1. Create Cycle Error Log Job

  1. Add Additional TempDB Files All With Same Size and Growth Rates *

  1. Set Media Retention

  1. Set Backup Compression Default On

  1. Change to Audit Successful and Failed Logins

  1. Set Default Growths in Model Not Be Percentages

  1. Set AD Hoc Distributed Queries off 
  2. Set CLR Enabled off 
  3. Set Ole Automation Procedures off 
  4. Set Scan For Startup Procs off 
  5. Set xp_cmdshell off
  6. Setup Operators

  1. Set Up Alerts 17-25 and Error codes  823,824,825 (Remember to add the alerts to the operator)

Note: Most of these can be set using GUI as well as the scripts above. Also, in addition to these configurations, I make sure that the server is brought up to the most current stable CU or Service Pack. Everyone’s environment is different, my list may not be right for you.