Month: September 2017

Just Check ALL the Boxes – SQL Server Installs

  By

Today I ran into something on a client server I unfortunately see too often.  The DBA goes through the trouble of configuring and setting up alerts\operators but doesn’t really understand what the options in the configurations mean. So unfortunately, that means they take the CYA (cover your ass) approach and they check all of them. Now, not only have I seen this with alerts but also with things like security configurations as well. My advice is to always in to take a second and research what each option is before you check the little boxes, especially when it comes to… 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

Please Don’t Do This! Default Index Fill Factor

  By

Please, please, please Admins do not leave your default index fill factor at 0. This means you are telling SQL Server to fill the page 100% full when creating indexes. This also means you are forcing it to a new page when additional inserts are done. These are called PAGE SPLITS which can take time to perform and is a resource intensive operation. Having a high fill factor will cause more index fragmentation, decrease performance and increase IO. If you find that this is how your system is configured, all is not lost. You can correct this by changing the… Continue Reading