DallasDBAs.com

Explaining SQL Server in plain english

Header Right

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

Stop Saying “It Depends”

by Leave a Comment

 

Dear fellow DBAs…something popped into my head the other day and its been brewing ever since.

We joke about saying “It Depends” all the time, when people ask us for something:

  • How long will that restore take?
  • Why is the database slow?
  • Do we need more RAM?

All of these are legitimate questions, and NONE of them have enough info. So, we typically reply with “It Depends.”  Now, you probably do the right thing and ask the appropriate questions to move the conversation along to where it needs to be.

But some other DBAs stop right there with a smirk and a smug satisfaction level that comes from blocking someone from the other team.

Don’t be THAT DBA

After all, don’t people do the same to you?:

  • How much to fix my car? (It Depends on what the diagnostics say)
  • How long until the doctor sees me? (It depends on what’s going on with the previous patient)
  • How much to paint my house? (It Depends on how many rooms, paint type and inside or outside)

Instead, I am challenging every DBA to skip right on past the It Depends and move to the probing questions:

  • How big is the database you want restored?
  • Can I reproduce the slow query?
  • Have we done a health check/index analysis on our current memory?

When your first response to a question from a non-DBA is “It Depends”, its a blocker for them and sets up a possible confrontation.

When you ask them a question (which you were going to ask anyway), you come across as interested in helping them achieve their goal.

They very often don’t know what they don’t know…so help them.  If its a recurring request like restores, or installs…give them a checklist/build sheet so they can fill in the blanks.

Everyone will be happier and the DBA team will look fantastic!

Thanks for reading!

Kevin3NF

TSQL Tuesday: Changing your mind

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 Alex Yates(b|t), who has asked us to write about “Changing Your Mind“

My best technical example of this is something I wrote about in January 2017, so I invite you to read the original post:

PowerShell for DBAs – why I changed my mind.

In addition to the 2 main reasons I gave then, I am adding a 3rd:

dbatools.io has become a very solid and almost all-encompassing way to manage SQL Server and will be a required skillset in 3-5 years at any large SQL shop.

That’s it for this month!

Thanks for reading!

Kevin3NF