Tag: Default Database Settings

Synchronous VS Asynchronous Statistics Updates

  By ,

One of the things I’ve been able to implement to help with performance is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. It’s a simple change that can have a big impact when implemented in highly transactional OLTP environments. Notice I said OLTP not OLAP, since data in an OLAP environment tends to not be as dynamic, so it’s rare to enable this in a data warehouse. So, what’s the difference between the two and why does it help? Synchronous (defaulted as AUTO_UPDATE_STATISTICS =TRUE) By default, when Auto Update Statistics is set to True, the SQL Server Query Optimizer… Continue Reading

Hmmm… What’s This? Date Correlation Optimization in SQL Server

  By ,

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… Continue Reading

What does this little check box do? Query Governor SQL Database Setting

  By

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 EXEC sp_configure 'show advanced options', 1;  GO  RECONFIGURE ;  GO  EXEC sp_configure 'query governor cost limit', 180;  GO  RECONFIGURE;  GO 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… Continue Reading

Initial SQL Server Configurations

  By

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… Continue Reading