Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but…
-
-
One of the hardest things you can do as a blogger is to come up with a post topic. Do you make it simple for newbies, technical, or something personal? After figuring out a topic, now you have to write. However, there is a difference in what you say and what will people actually want to read. Blogging is not easy, but without it, all of our google searches to help solve problems would be much less fruitful. It’s important to put your experience into written words to help others, and let’s be honest lots of us use out blog posts to remind ourselves of how we did the first…
-
In this post we will pick up where we left off in Part 1, if you haven’t read that please go back and do so. Now that we have encrypted our columns, it’s time to take a look at how we decrypt them inside SQL Server Management Studio or through our applications. You’ll be surprised to see how easy it is. Verify Your Setup First, let’s verify that the table is still encrypted, and nothing changed after you ran through the Part 1 examples. To confirm, simply query sys.columns, script out the table, or query the data to check that the Birthdate column is still encrypted. USE [AdventureWorks2016CTP3] GO CREATE…
-
Encryption has always been intriguing to me but seemed like it could be a very complex process to set up. However, SQL Server has made it very simple when they introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, AE is configured on a column level and not database level. Additionally, Always Encrypted is available in Standard (and Express) Edition, starting with SQL Server 2016 SP1. You can easily encrypt a social security number (SSN) which is considered very sensitive within the United States or Salary column in a table with just a…
-
This week is Thanksgiving in the United States, so I thought it fitting to write a quick blog on what I am thankful for as a DBA. These are in no particular order and feel free to respond with something you are thankful for. I’d love to hear it. Glenn Berry’s Diagnostic Scripts- (B|T) Used these for years. Really a great set of scripts and explanations that we all should be grateful for. Ola Hallengren’s (B) Maintenance scripts. Index Optimization, Backup, and Integrity Checks for all! They have become an industry standard and continue to get better and better. RCSI (Read Committed Snapshot Isolation) –My Readers can stop blocking Writers! Thanks…
-
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 run the next statement under, followed by the execution of…
-
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 “hey this is a cleanup job” SCORE! Thinking that maybe…
-
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 used in my environments and others. Top (in no particular…
-
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 will automatically update statistics when data has met a threshold…
-
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 something and not have to change it for two years.…
