Do Not Pass GO!

  By

What is the GO statement and why is it so important to use? When do I have to use it? When do I not use it? These are questions that have passed through my head from time to time while writing T-SQL within SQL Server. First What Is It and When Should I Use It?   The GO statement lets SSMS (the interface) know when it’s the end of the batch. It basically defines the scope of what you are trying to send to the Database Engine. The below example sends two separate statements. The first statement changes the database context to… Continue Reading

What Are These Backup Settings All About?

  By

I ran across a client the other day that had these Backup and Recovery options set like the picture below because it is defaulted this way. The Database Administrator didn’t know what they should configure them as so he left them alone. I find this is the case with a lot of options. For the most part leaving the defaults can be just fine, but other leaving others cause leave you missing out on some good features. Let’s start from top to bottom. Default backup media retention in days. Now the first things that comes to my mind is that… Continue Reading

Quick Model Database Tidbit

  By , ,

Are you using your Model Database to its full potential? I am finding more and more that Database Admins are not using the Model database to its fullest potential and some not at all. What is that Model Database for? The model database is basically the default setup (template) for all other databases created on a SQL Server instance. All databases created after install will inherit the properties of this database. Why Configure It? Using the model can insure consistency within your environment and is a quick way to automate your database setups. Below is a list of things I’ve… Continue Reading

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

Does Your Code Have a Preamble?

  By ,

Okay, here is a pet peeve of mine, I think every stored procedure, function, view etc. should all contain a block of code I refer to as a preamble. If yours doesn’t I strongly recommend you start adding it. It drives me crazy when I see code with no documentation of any kind telling me what it is for and when it was written or changed. Why? A preamble documents the use, need, and changes for the code. It also leaves bread crumbs as to how why and what you did. I don’t know about you but I may code… Continue Reading

Why I Go to Summit Each Year?

  By , ,

This year will be my 6th PASS Summit that I will have attended. Some people have asked me why I still go, and what I get out of Summit that I don’t get from attending and speaking at SQL Saturdays. That’s an easy one for me to answer, but a long answer at that. Networking First and foremost, it’s for the networking. Getting to meetup with so many other like minded people is gratifying. This networking allows you to exchange ideas, war stories, and downright geek out with others that know what you are talking about and don’t have their… Continue Reading

SQL Sequence vs Identity Column

  By

Let’s take a look at what a Sequence is in relation to an Identity Column in SQL Server. Did you know Sequence even existed? I didn’t until I was asked about them. It’s amazing how much you can skip over and never notice in SSMS. See this little folder, ever notice it under Programmability in Management Studio. Yep it’s there, SQL Server has this very handy thing called Sequences. Sequences are a relatively new feature that have only existed since SQL Server 2012, but have long existed in Oracle (where there a no identity columns). What is a Sequence? Per MSDN,… Continue Reading

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