DallasDBAs.com

SQL Server Database Consulting

Accidental DBA

The Story of Jeff

by Leave a Comment

Jeff level enthusiasm, Image by zoegammon from Pixabay

Jeff (b|t) and I have been friends since sometime in 2003. He was the worship leader at my church at the time. Most recently Jeff and his wife have been running a graphics design firm. Websites, book covers, textbook layout, WordPress development, etc.

Earlier this year when the graphics biz was slumping he asked if I needed any help at Dallas DBAs. After some discussion I offered him a 3 month apprenticeship – full time at offensively low hourly rates. I would be teaching him and letting him self-teach through whatever means he could get his hands on.

Inside that 3 months he was already getting on customer servers and doing daily checks (with my guidance of course).  We have a process for this.

He currently has primary DBA responsibility for 5 hourly clients and one Pocket DBA™ client, escalating to me as necessary.

As of this month, Jeff’s efforts are driving enough billable hours that he is COVERING HIS ENTIRE SALARY!  SQL Dork (b|t), the other Junior DBA here has been self-covering for a long time due to only working part-time.

I would like to tell you all of the steps Jeff took along the way…but he already blogged the whole trip.

For your reading pleasure:

The Ironic DBA Files

Thanks for reading!

Kevin3NF

Duplicate Indexes Explained

by Leave a Comment

What are duplicate indexes, and why do I care?

This is an entry level explanation, with an analogy for new DBAs.

Duplicate indexes are those that exactly match the Key and Included columns.  That’s easy.

Possible duplicate indexes are those that very closely match Key/Included columns.

Why do you care?

Indexes have to be maintained. When I say that, most people immediately think of Reorganizing, rebuilding and updating statistics, and they are not wrong.

But..don’t overlook the updates that happen to indexes when the data changes in the columns they are based on. If you have a duplicate index and you add, change or delete a row…BOTH of the indexes are changed. This takes CPU, memory and log space to do.  Magnify by multiple indexes across a databases with tables that have millions or billions of rows and you start feeling this effort.

Duplicates:

Consider the following two indexes:

-- DisplayName only
CREATE INDEX [NC_DisplayName] ON [dbo].[Users]
	([DisplayName] ASC)
GO

--DisplayName plus additional info
CREATE INDEX [NC_DisplayName_Includes] ON [dbo].[Users]
	([DisplayName] ASC)
INCLUDE ([Reputation],[CreationDate],[UpVotes],[Views]) 
GO

If you query the StackOverflow database for me (Kevin3NF) using

Select DisplayName
From Users
Where DisplayName = 'Kevin3NF'

It will use the first index *

If you add ‘Upvotes’ to the Select, it will use the second index.

So…how is this a duplicate index?

The Key column (DisplayName) is the same.

Drop the NC_DisplayName index, and your first query will use the second index because even though there are 4 additional columns, the Key column is still there and this index is better than a table scan.

You also get the benefit of not having to update NC_DisplayName any time data is changed.

An Analogy of Two Doors:

You have two doors:

The First door is simple.  It has one aspect to it…a handle.

The Second door has a handle, as well as all sorts of Included extras.

Both doors lead to the same place.  Assume some people like the simplicity of Door1 and some people really want to go through the Steampunk style Door2.  Both get what they want (destination), by picking the door that suits them.

If you take away Door1 (Drop Index), the folks that prefer it can simply go through the fancy Door2 and get where they needed to be. Door2 has included extras (columns) that the “Simple door” folks just ignore.

It really is that simple.

But…don’t go make an index with all possible columns!  That’s a whole different kind of bad indexing.

Now…this does not mean you need to go blindly dropping indexes.  Research, test, verify which indexes are being used/unused, etc.  Definitely don’t go and drop a Primary Key index just because its a possible duplicate.

I use sp_BlitzIndex for my initial info gathering when doing HealthChecks, Index Tuning, etc. Its free and solid.

Video:

* I have run into a weird issue I am trying to sort out. In my testing, it appears that SQL Server might be basing its index decisions on the Index Creation date, all other things being equal. I will update as I find out more. This was done on SQL 2016, SP2 with the StackOverflow public data dump.

Per my good friend Pinal Dave (b|t), this is a known behavior: ” if two index has same key columns the one created later is used.”

Go forth and research!

Thanks for reading!

Kevin3NF

The Ironic DBA—Don’t Be Afraid of the Climb

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Yes, my blogging pace has slowed somewhat, but I’m back with something to share. Last time I wrote about getting back to basics and reinforcing my knowledge of introductory SQL Server concepts. This week I want to go in the opposite direction and share why I think you should regularly push yourself out of your comfort zone.

The view from the top is worth the climb.

Climbing Sucks

I’ve mentioned before that I love mountain biking—I’m not great at it, but I love getting out on the trails. Unfortunately, I also have very low heat tolerance, and so I was almost completely off my bike for the majority of the Texas summer—about 2.5 months. I did sneak one ride in before the worst heat abated about 3.5 weeks ago, but it didn’t end well.

I’ve been able to get back out and do three or four trail rides a week for the last three weeks. The trail I’ve frequented is one of the closest to my house, though it’s not a favorite. There’s not a lot of elevation at this trail, but after so long off the bike even gentle slopes were tough to climb at first. To make matters worse, I’m overweight, have some significant balance issues, and absolutely suck at climbing.

Fast forward to just last night where I went out and rode a different trail—the same one that hadn’t gone well 3.5 weeks ago. This trail is not the longest or hardest in the area, nor does it have the most climbing, but it’s been my nemesis since the first time I put down tires on its dirt. I have a less than 50% completion record at this particular trail, so I’m determined to conquer it.

Last night’s ride was pretty good. I completed the entire trail for the first time in months, and set some personal records on a few segments along the way. But here’s the coolest thing about the ride. At one point I made it to the top of climb that’s typically pretty tough for me and took a quick water break. I immediately noticed that the climb had felt easier than expected, but I figured that was due to intentionally choosing easier gears than usual. I was pleasantly surprised to look down at my gears and realize that I was 9th gear when I thought I was probably in 6th or 7th. (For those who don’t ride bikes, the lower the gear number the “easier” the gear.)

The time I’ve been spending getting back to trail riding regularly is already beginning to pay off. I’m back to feeling comfortable on the bike, my fitness level is increasing again, and my ability to ride harder, longer, steeper trails is gradually improving.

Climbing is Awesome

Tim Chapman presenting “Troubleshoot SQL Server Like a Microsoft Engineer”

“That’s great,” you say. “Good for you. But what does this have to do with SQL Server stuff?” I’m glad you asked!

I recently attended a fantastic one-day workshop that was made possible by my local PASS group, the North Texas SQL Server User Group. After the huge success of this year’s SQL Saturday Dallas, and in particular the response attendees had from Brent Ozar’s (b|t) performance tuning pre-con, the group decided to bring in Tim Chapman (t) in for a one-day workshop entitled “Troubleshoot SQL Server Like a Microsoft Engineer.”

I was looking forward to attending even though I knew the vast majority of the class was probably going to be over my head. I’ve advanced fairly rapidly in my SQL Server knowledge since beginning this journey a little over four months ago, but the reality is I’ve only got four months of experience. That’s actually far too little time and experience to make the most of a class at this level. So why bother attending?

Learning is a climb, sometimes a very arduous climb. Just like some of my trail rides, there are moments when I can’t reach my learning goals and have to take a step back and start over. Some days the learning is smooth and the climbing is easy, boosting my confidence and my progress. Some days, the climb is hard, but not as hard as expected due to the work I’ve put in previously.

It’s for this very reason that I wanted to attend this workshop, and the same reason I’m currently working my way through Brent Ozar’s Senior DBA Class. Both are way over my head and deal with issues I won’t have to worry about being directly responsible for any time soon. But the classes make me climb and reach for a goal. Such classes make me dig deep and recall everything I’ve learned so far and apply it as best I can. Such classes expose holes in my current knowledge set and help flesh out a trail map for what I need to learn moving forward to become the best DBA I can be.

Sometimes the climb sucks, but getting to the top is awesome. Onward to the next peak!

Troubleshoot SQL Server Like a Microsoft Engineer—A Review

As for Tim Chapman’s class itself, let me give you a quick review and share my thoughts and takeaways from the day.

First up, I didn’t know what to expect walking into the class. Still being somewhat new to all this, I sort of expected the class to rapidly accelerate to light speed and beyond to leave me grasping for whatever small tidbits I could glean. That never happened.

Tim is an excellent presenter, but beyond that he’s an excellent teacher. So many technical people who are very smart, know their stuff, and have years of experience often don’t realize just how far beyond the average newbie they really are. But not once did I feel like the class was moving too fast for me to keep up, and a great deal of this is due to how Tim broke everything down logically, built concepts upon one another, and didn’t make assumptions that everyone in the room knew more than they really did.

Having said that, there definitely was a certain level of foundational knowledge required in order to make the most of the class, and thankfully my studies thus far proved to be enough. There were a few concepts and pieces in SQL Server that I’d never heard of before (namely triggers and forced query plans), but by and large I was familiar enough with SQL Server, relational databases, nomenclature, and SSMS to never get completely lost.

In a nutshell, here’s what Tim covered in roughly seven-hours-worth of sessions:

  • An Overview of Troubleshooting
  • Queries and Waits
  • Extended Events
  • Query Store
  • Most Common Problems

One of the most encouraging things Tim shared as a new DBA was this concept: “Often, there are 5 or fewer things to look at for a server to solve problems. Like the Pareto Principal: 20% causes 80% of the issues. Today’s Goal: Identify and master the 20%.”

This is why so much of the class was understandable and relate-able. The truth is, to know how you fix the other 80% of problems possible in SQL Server means you’ve either gained years and years of experience, know how to ask Google and StackOverflow the right questions, or both. Stay in the DBA field long enough and you’re sure to run into something rare, an edge-case that very few people have seen. But beyond those edge cases, the vast majority of the problems we see are repeated regularly. Getting a grasp on these underlying issues that regularly rear their head will help me be a solid DBA while giving me the foundation to troubleshoot those rare issues.

Big thanks to Tim for helping me put together a more direct path to follow on my self-guided learning. If any of you reading this ever have the chance to take this class from Tim in the future, I highly recommend it. I’d like to sit through it again myself sometime in the future when I’ve got more knowledge and experience in my tool kit.

Also thanks to NTSSUG for investing in your members and bringing Tim in to teach us. I’m looking forward to more opportunities in the future.

That’s all for this post. Join me next time for the next episode in The Ironic DBA Files.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



The Ironic DBA—Back to Basics

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

It’s been a few weeks since I added anything to this series—though I did contribute my first-ever T-SQL Tuesday post a couple of weeks ago. The reasons for my silence are actually pretty simple. I’ve been busy.

Is Your Isolation Concurrent?

My main daily task since coming on board here at Dallas DBAs has been immersive self-study. I spend the vast majority of my time reading blog posts, books, and watching videos about all things SQL Server. I recently enrolled in Brent Ozar’s training classes and have been learning a great deal. I typically watch one or two videos a day there, and spend a lot of time aftewards doing follow-up reading in an attempt to reinforce what I’ve just consumed.

There’s so much to learn!

Before enrolling in those classes, I spent a few weeks sort of ambling all over the place without any specific step-by-step process as to what I should be studying. I had sort of gotten to the point where I had learned enough that it was getting hard to determine exactly what I should learn next, so my focus was rather fuzzy.

Along the way, I spent a few days going down the rabbit hole of concurrency and isolation levels, which is really useful stuff to know if you’re serious about being a top-notch DBA. It’s good stuff, and I’m glad I read up on it, but 90% of what I had read was cart-before-the-horse type stuff. I needed to keep it simple and go back to basics.

Build that Muscle Memory

I wrote in my last Ironic DBA post about the basics of finding and reading error logs. Nested within that simple write up was a truth I needed to remind myself about and keep coming back to: Keep learning about how navigate and use SQL Server Management Studio (SSMS).

Case in point: I currently have three clients whose servers I review daily, and one client who receives a weekly review. I’ll be picking up one or two more clients in the near future. I’m almost daily presented with an “I’ve never seen this before” moment, which is a learning opportunity. It’s not uncommon for me find a new-to-me error and spend a bunch of time Googling and checking reliable sources in an attempt to figure out what’s going on.

More often than not, my difficulties in figuring out what’s going wrong are equal parts not knowing where to look in SSMS and not knowing about the error itself. I can learn from mentoring or reading what is causing an error, but knowing how to troubleshoot it is largely knowing how to navigate SSMS effectively.

Seriously, I think the best piece of advice I can give my fellow newbie DBAs is do everything you can to learn about using SSMS. Learning how SQL Server works under the hood, how relational databases work, how to write and troubleshoot queries, and things like indexing, statistics, and monitoring are all critical to your career as a DBA. But none of that matters if you don’t get familiar with the tool you will use most often.

SSMS is the tool that will make everything else you learn make more sense because it is where you can see all the magic happen—or not happen in the case of job failures, deadlocks, and other nasty stuff. Let’s be honest, the tool is not intuitive, and in 2019 it feels very long in the tooth—like using legacy software because there’s nothing else better. As a graphic designer and lifelong Mac user I find the software clunky and confusing, and constantly think about ways the GUI could be vastly improved.

The problem with that is it would blow the mind of every long-term DBA out there. Can you imagine how lost the majority of career DBAs would be if Microsoft suddenly released a whole new interface to SQL Server? Even if they created a GUI that was objectively better, many DBAs would feel like they’re starting over and it’s a frustration they just don’t need to deal with to get their jobs done. So, I agree that the best course of action is to identify simple ways to tweak the current GUI to improve the tool without blowing it up and starting from scratch.

So embrace SSMS for what it is and what it does. Despite it’s weaknesses, it’s the most powerful tool in your DBA tool kit.

That’s all for this week. Join me next time for the next episode in The Ironic DBA Files.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



The Ironic DBA—It’s Hip to Be Square

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

Last week I reviewed how far I’d come and some of what I’d learned so far on this journey. You [hopefully] noticed the titles of the previous episodes were all Star Wars-esque titles, and the episodes were numbered. All of those posts were a generalized recap of what had happened two or three weeks previously. Going forward, my contributions will be more timely—as in what I’ve learned or experienced within the last week or so—and hopefully continue to add value to my fellow apprentices in the #sqlfamily.

 

Brackets Not Feeling the Love?

There seems to be some generalized hating on the use of square brackets in TSQL code among both new and seasoned DBAs. What are square brackets? I’m sure if you’ve spent almost any time in the SSMS query editor you’ve seen something like this:

[StackOverflow2010].[dbo].[Users]

This commonly happens when you use SSMS to create your scripts for you, or you drag and drop a database or table into the query editor window. The example above is a demonstration of a three-part naming convention for the Users table in the StackOverflow2010 database. What this shows is [NameOfDatabase].[Schema].[TableName]. The periods, or dots, are concatenating the three parts of the name together.

I get why some folks don’t like the brackets as they tend to muddle your script’s readability. To see a great example of this in action, check out Michael J. Swart’s post Remove SQL Junk (Brackets and Other Clutter). In most cases, you don’t need square brackets in your scripts, but there are instances where you will need them. That’s mostly beyond the scope of this post.

Cool side note: One of the tidbits I learned during this investigation into square brackets is that a database can have tables with identical names as long as each table’s schema is different. That’s something for you apprentice and accidental DBAs to be aware of for the future.

An Example of Nifty Bracket Usage

I mentioned last week how I have been working through the Stairway to T-SQL course at SQL Server Central. While playing with the GROUP BY clause scripts, I became annoyed at the inelegance of the table header output. Here’s an example:

USE tempdb;
GO
SELECT StoreName 
     ,SUM(TotalSalesAmount) AS StoreSalesAmount
FROM dbo.SalesTransaction  
GROUP BY StoreName;

The script above renders this result:

I decided to test the waters and “fix” this by using square brackets for the column header names:

USE tempdb;
GO
SELECT StoreName AS [Store Name]
     ,SUM(TotalSalesAmount) AS [Store Sales Amount]
FROM dbo.SalesTransaction  
GROUP BY StoreName;

Which rendered this:

Ahh, satisfying.

It’s a super simple addition, and one that does not detract all that much from the script’s readability. What is does do in my opinion is make the output more reader-friendly.  Essentially, consider if you might want to use square brackets when you want to use spaces or special characters in your column headers. If you want to go crazy, you can take using square brackets to rename columns a whole lot deeper.

As a side note, you may have to use square brackets if you have a table or column name that uses a word that is reserved in SQL DML. For instance, if someone was wicked enough to name a table or column “Index” then you would have to use square brackets around the name in order to create scripts that function properly.

But no one would ever be that evil, would they?

That’s all for this week. Join me next time for next episode in The Ironic DBA Files.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



The Ironic DBA Files—Episode 9: The Rise of the Clients

by Leave a Comment

Welcome back to The Ironic DBA Files, a series where the newbie DBA on staff at Dallas DBAs chronicles the ups and downs of fast-tracking a new career as a DBA.

It’s go time, ladies and gentlemen. Last week we reviewed indexes and the basic differences between reorganizing and rebuilding those indexes. Now, it’s time for the big reveal:

I’m allowed to touch client PROD servers!

A Funny Thing Happened on the Way to the Server

Thankfully, I didn’t have a ton of nerves logging into a client server for the first time, for two reasons.

First, as a WordPress developer, I’ve been in client accounts and such before. I’m aware that a company’s data can be more critical and sensitive than a WordPress blog, but both are important to that client. It doesn’t matter if it’s a multi-million dollar company or a food blogger’s site, you want to do your best and make sure you’re careful to do things correctly. Plus, one of my former WordPress clients makes seven-figures a year off her blog, so I can literally say her blog is every bit as critical as a company’s SQL Server database.

Second, our approach here at Dallas DBAs is very careful. Before ever logging into a client’s server on my own, Kevin first screen shared with me and I watched as he went through practices and procedures. Then, each day for the rest of the week we screen shared as he coached me through logging in and doing a server review. Only after a week of his oversight did he release me to begin solo server reviews on one client’s server. A couple of weeks later we went through the same process all over again with a second client. I’ll be adding a third and fourth client via the same processes in the coming days.

For now, my one and only task is to log into client servers daily and look for problems. I’m learning how to identify immediate red flag issues such as long waits, locks, and blocks. I’m checking the SQL Server error logs for anything out of the ordinary. I’m ensuring backups are up to date. And I’m learning how to effectively use monitoring tools such as Manage Engine, Solar Winds DPA, and Red Gate SQL Monitor.

As I go through this daily process, I’m learning how to properly report a server’s status to our clients, identify problems, and escalate those problems to Kevin. As an apprentice, it’s not my role to attempt to fix problems, but rather to stay engaged and learn how Kevin approaches making things right so that as I progress I add more and more knowledge to my tool belt.

Does This Look Right to You?

SQL Server Waits
One of these things is not like the others.

This is going to sound bad, but it’s sort of fun when a client’s server “breaks.” Let me explain.

If SQL Server and the underlying hardware always ran flawlessly then there wouldn’t be any need for DBAs, would there? In a way, being a DBA is mostly sitting around waiting for something to go wrong.

That’s not all there is to it. There’s consulting available where some DBAs help clients with architecture and hardware questions. A DBA might be consulted by a client who wants to upgrade their servers or systems. But let’s admit that the main action parts of the job involve putting out fires.

Those fires might be small, such as a client asking, “Why is this query so slow?” or “Can you help us implement a better maintenance schedule?” Other times it’s a 3 AM call from a client who’s panicking because someone accidentally dropped a server, a server died, a restore failed, or something else potentially catastrophic. That’s when a DBA really gets to serve their clients.

For me, that’s really the most exciting part about learning to be a DBA. What I do can have a real, significant impact on a client’s business and well-being. It’s an opportunity to serve.


If you take a look at the graph just above, as well as the graph further up the page, you’ll see an example of how my first week looking at a client’s server went. Long story short, this client had some growing pressure in their server in terms of wait and blocks. There were some regular overnight queries that were taking longer and longer to execute. Eventually this blocking was spilling over into the next hour and affecting subsequent performance.

Wanting the best for our client, I found myself feeling this strange mix of concern and joy over the situation. Concern that we as their DBAs needed to fix this, and joy that we were in a position to help. I had these feelings even though my only contribution to the whole incident was basically saying, “Hey, this looks wrong!” and escalating it to Kevin. But still, I had an opportunity to contribute and it was fulfilling.

In the end, it was an easy fix for our client. They discovered the query that was causing the issue was not critical and simply prevented it from automatically running from within their application. Problem solved. In fact, their server is now performing much better overall than it did before.

What’s the main point of all this, other than simply sharing more about what I’m learning? If you’re out there struggling to figure out what sort of work you should get into, I highly recommend finding that sweet spot where you not only find something you are good at and enjoy doing, but something that also allows you to serve others in some capacity. Serving others is where the long term rewards of any endeavor truly come from.

That’s all for this week. Join me next time for The Ironic DBA Files—Review One: A SQL Story.

Follow me on Twitter at @SQLandMTB, and if you’re into mountain bikes come over and check out my site NTX Trails.

The Ironic DBA Files



Home Blog About Privacy Policy