DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Blog

T-SQL Tuesday 121: Gifts received for this year

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Mala (b|t) who has asked us to write about “Gifts received this year”

Over the last year or so I’ve attended several NTSSUG meetings on various topics, with various forms of food provided (usually pizza). Most of the time there’s more than enough leftovers to justify taking home an entire spare pizza, which I’d call a win.

Ed.: Yes, of course things were learned, people were met, networking happened and friends were made…but hey….free pizza is free pizza!

/*Please work*/,

SQLDork

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

T-SQL Tuesday 120: What Were You Thinking?

by 2 Comments

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Wayne Sheffield (b|t) who has asked us to write about “What Were You Thinking?” – things we have seen that left us scratching our collective noggins.

The list is long, so I’ll just go with something I saw yesterday.  And in September. Both created by a full-time DBA at a client.

BACKUP LOG MyDatabase
TO DISK = 'E:\SQLBackups\MyDatabase.trn'
WITH INIT

Yep.  It runs hourly.  And initializes the .trn file each time. Goodbye Point in Time recovery.

Ick.

Bonus points: Duplicate indexes, which I will cover in a future post.

Thanks for reading!

Kevin3NF

PASS Summit 2019 thoughts and wrapup

by Leave a Comment

Holy smokes my brain hurts!  And at the same time it is feeling the joy of learning things I can actually use!

If you are a Dallas DBAs client…you will soon very likely be reaping the benefits of my trip to Seattle for the 2019 PASS Summit.

Its rare, but when I’m really excited I pop for the in-flight WiFi, which is where I sit now typing this up. If you look to my right you can see…clouds.

Stream of consciousness in near chronological order, accompanied by notes where possible:

Day 1

What’s new in SQL Server Tools – Vicky Harp (t) and Udeesha Gautam

Most of this was Azure Data Studio (watch me embarrass myself here), which was clear in the abstract. Very cool tool mostly for devs, but the Notebooks aspect has lots of ways I can send something useful to my clients to review, in one format.  Has a little One Note with code and results feel to it.  Great demo. SSMS is not going away!

Improving Availability with Accelerated Data recovery and Resumable Operations – Pam Lahoud (b|t)

Ever tried to roll back a runaway query that took 4 hours to notice? Reboot the server trying to kill it and that didn’t help?

This is the fix to that nonsense! Off by default, rewrite of the SQL Server Recovery process.  Bang…instant recovery.  Of course there’s some overhead. SQL 2019 and Azure only feature.

Also, “Resumable Index Operations” are now a thing. A cool thing.

After the KeyNote and these 2 sessions, I managed to score a massive headache so no more sessions.

Game night! – sponsored by PASS (Thanks Joe!)

Day 2

DBA Tools – Jack Corbett (b|t)

I’ve already thanked Jack in person twice and online at least that much for this class. If I can only tell you one thing from 3 days of classes its this: “YOU DONT NEED TO KNOW POWERSHELL TO USE THE DBATOOLS COMMAND!!!”  I paid extra for the caps and 2 bonus exclamation points, just for you. That sentence alone has never sunk in to my head.  A whole lot of goodness will flow from that to you.  Thanks Jack!

If you are a junior DBA, you still need to learn the concepts and how to do things in the SSMS GUI/T-SQL, but these tools are a great step for some of the more complex tasks.  Best session of the week for me.

SQL Server in Containers – Grant Fritchey (b|t) – Red Gate vendor session

Awesome – Grant told us early and often that the 1st 2/3 of the session would be vendor agnostic, then move to RG tools.  Many thanks for that clarity!

I tried out Docker 2 years ago in test/dev and had no use case for it. Now I do and saw that some things have gotten easier. I spin up Azure VMs to play with stuff all the time. Now I can accomplish the tasks locally and faster and cheaper! I also learned the difference between images, layers, containers and VMs,  Greatness in a confusing tech stack.

Azure DR strategy – John Morehouse (b|t), with Denny Cherry in the back for additional flavor when needed.

I’ve known conceptually about much of what John presented for a while…but now I have a lot more clarity and insight from a true pro. Plus, some of what I learned at a DeepPockets™ client 2 years ago is out of date or enhanced in Azure

Exhibitor Hall and Beanbag sessions. I got to meet one of my freelancers for lunch so that was cool. Thanks Brendan.

HA/DR – Too many choices – Mike Walsh (b|t)

I go to sessions I know nothing about and sessions I do…this one I knew, and I still learned stuff. Sometimes I go just to see friends speak.  This was that as well as learning. Mike is a great guy running a great company. If you need SQL help, call me first.  If Dallas DBAs isn’t the right fit, Straight Path is.

Game night again!

Day 3 – Friday

Batch Execution Mode on Rowstore – Niko Neugebauer (b|t)

Learned a ton, saw great demos, super fun slides and WOW! Niko is a brilliant and highly entertaining presenter. If you present, go find something he’s done and watch his style.  Its crazy fun.  And the technical content is very solid.

Best Practices for Branching Database Code in Git – Kendra Little and the Corgi Crew (b|t)

I don’t do much in the Git/OSS world and was hoping this would help me pick some of that up along the way, even though this was not a “learn Git” presentation.  It did, and I now feel good about bringing some DallasDBAs internal scripts into a Git/GitHub type repository so the whole team can contribute to them. Maybe then I’ll start contributing to other open source projects.

Great lunch with a good friend I only see at conferences. We solved a few world problems.  Again.

More bean bag time.

Upgrading SQL Server – Mike Walsh again!

I’ve done more than a few upgrades, but I knew Mike and team at StraightPath have a more formal and streamlined process, so I wanted to steal info from a competitor. Is it stealing if he’s up front giving it away and I’m hiding in the front row?

Tap house for dinner, then back to the Sheraton to pack and chill.

I’m over Colorado now✈ .  If this was readable and no spelling errors, that happened at my desk in Dallas.

Look out clients…your stuff is going to get better.  And YES, YOU NEED TO MOVE TO AZURE OR SQL SERVER 2019. There, I said it.  “Mr. Wait and See” is pushing a product 6 days into formal release!

Thanks for reading!

Kevin3NF

The Value of Training- Summit 2019

by Leave a Comment

I sit here writing this on a Sunday morning with coffee nearby, eagerly anticipating this week’s annual PASS Summit. This is my 5th Summit, and 4th in a row. And yes, I pay my own way happily to this conference every year, to the tune about $4000. That’s for everything – conference fee, travel, lodging, food, airport parking. And yes, I bump for better seats on the plane, to avoid being cramped and miserable for 4 hours each way.

But why do I do this? As a self-employed DBA, that’s a lot of money!

Because training.

I’m 50+ (just added the “+” last month) years old. Things in the SQL world are changing faster than ever. In 1999 with SQL 7 there was no cloud. There was a 5 year gap between SQL 2000 and 2005 releases. And to be quite honest, I never had a mentor guiding me. My entire workday was reactive to whatever ticket came along and Yahoo (before Google) or Books Online to figure things out.

That all changed when I got laid off from Verizon Managed Hosting (Now IBM) in 2015. I re-entered the job market with 15 years of DBA history, but severely lacking current skills, and under developed in other areas. RDX confirmed this by not hiring me after some interview rounds.

I had a conversation with myself and decided I needed to get a LOT more structured, develop a plan to get back on track and be as current as I can. I had been on Twitter, MSDN Forums, and other sites long enough to know who some of the more accomplished folks on the DBA side of the world are – specifically I chose to look at what Brent Ozar (b|t), Paul Randal (b|t) and Pinal Dave (b|t) were doing. I had already met (very briefly) the first two and now call Pinal a friend.

I had always shied away from the deeper performance tuning issues, aside from throwing indexes at things and maybe running sp_updatestats (which worked more often than not). Slow performance is one of the most common problems SQL Server customers have. And Availability Groups. I knew nothing about them at the time. So I took a position as a team lead with a small project team in a deep pockets type company and started learning. I read a lot of things. I watched a few webinars. I went to a performance tuning class from Brent that changed everything (I do best in-person, no distractions) for me.

All of this created a new habit in me – seeking out new knowledge and going deeper than the surface level “just get it back online and close the ticket” work I had been doing for years.

Training is critical!

If the folks you report to aren’t allowing you to get training on the job, I submit that either they are desperately starved for cash, or they just really don’t care about your future nearly as much as they should. So, its up to you to train yourself. Read blogs. Help out on forums. Set up a test box in Azure so you can break things and fix them. If you have a local user group go to it, regardless of the topic. Attend a SQL Saturday. Become a member (for free) of PASS to take advantage of webinars and tons of free video content! If you have a few dollars a month, get a Pluralsight subscription. Take charge of your training and your career!

So here I sit. Looking forward to Summit and possibly my last visit to Seattle (next year is in Houston).

While retirement is still 10+ years off I owe it myself, my employees, and my clients to be as knowledgeable as I can be. 3 days of full immersion in Database Administration, Azure and SQL 2019 features. 3 days of getting my steps in by noon and scanning the Community Zone for an empty bean bag. 3 days of seeing friends I only see once a year. 3 days of networking, talking to vendors (Come sponsor SQL Saturday Dallas, yo!), and having a good time. 3 days of #SQLFamily.

Worth. Every. Penny.

Thanks for reading!

Kevin3NF