Category: Back to Basics

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

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

VLFs the Forgotten Foe

  By ,

How many of you check the amount of Virtual Log Files (VLFs) your transaction logs have? Working as a consultant now, I see this as something that is often ignored by DBAs.  This is an easy thing maintain and yet so many don’t know how to. Keeping these in check can give you a performance boost not only on startup but with your insert/update/delete as well as backup/restore operations. SQL Server performs better with a smaller number of right sized virtual log files.  I highly recommend you add this to your server reviews. What is a VLF? Every transaction log… Continue Reading

Ooops! Was that me? (Blog Challenge)

  By , , ,

We have all made mistakes in our careers, I thought I’d share one of mine as a quick tip to others so that you don’t make the same one. Everyone has their SQL Alerts setup right? If not, I have included the script below and here is the MSDN link to find out more (https://msdn.microsoft.com/en-us/library/ms180982.aspx). For those who have setup their alerts, how many of you have remembered to set the DELAY BETWEEN RESPONSES setting? When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of… Continue Reading

Run Book, Run!!!

  By ,

How many of you actually have a “Hit-by-the-Bus” handbook? What is that, you ask? It is a document that explains how to execute all your jobs and SSIS packages. In addition, I preference mine with all key elements someone might need, like where passwords are stored, architectures, backup times, where are backups stored, etc… then dig into the job steps. The purpose of this document is so that someone with some SQL skills could step in if needed. You never know when you will be hit by a bus or win the lottery and someone has to take over for… Continue Reading

Hide and Group Columns in SSRS Using a Parameter

  By , , ,

Ever had users come to you and request another version of a report just to add another field and group data differently? Today, was such the day for me. I really don’t like have multiple versions of the same report out there. So, I got a little fancy with the current version of the report and added a parameter then used expressions to group the data differently and hide columns. For those new to SSRS I’ve embedded some links to MSDN to help you along the way. Current Report The report gives summarized counts by invoice date.  It currently has… Continue Reading

Back to Basics: Why not parameterize?

  By , ,

I think sometimes those of us that have been doing database administration/development for a while take it for granted that everyone knows the basics. One such basic is parameterizing stored procedures. This allows us to potentially consolidate multiple stored procedures into a single procedure.  It’s as simple thing to do that many don’t. I try to parameterize as many stored procedures as possible. This not only minimizes the amount of procedures I need to maintain, it in my opinion is a much cleaner way to code. It disturbs me when I see multiple stored procedures that pull the exact same… Continue Reading