Posted on

Dear SQL DBA: Dealing With a Lack of Control

This week I discuss a question that I’ve gotten in many forms over the years – a lot of the scenarios are so specific that it’s hard to keep them anonymous, but they can be generalized as a bigger problem: how do I deal with being responsible for things that I can’t fully control?

DBAs have huge responsibilities, so this is a common, frustrating scenario. In this episode, I talk about a few times that I’ve experienced this in my own life, what techniques didn’t work, and what strategies were more effective.

Attend the Dear SQL DBA podcast — live! Register here to get an invitation.

Got a question for Dear SQL DBA? Ask away.

Subscribe:  If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app:

My notes for the session

Everyday Dilbert moments

The Bug Nobody Caused

Committing to an SLA I couldn’t meet

The Case of the Weak KPIs

Techniques that haven’t worked for me

“This is not my responsibility”

If other people keep tagging you and saying “you’re it”, just saying “not it” or “I’m not playing this game” doesn’t stop them from continuing.

Techniques that can work

First: chill

Being responsible for things you can’t control frequently triggers stress

Stress triggers knee-jerk responses

  • Knee jerk responses don’t go anywhere
  • Not taken seriously
  • Generally don’t have thoughtful, supporting data

You need to be more strategic

Find a way to de-personalize the issue

Think outside your current role

If you were the CTO of the company, what would you want to see happen?

Don’t limit your thinking to what you can do now

Do what you can

Outline and document the limitations to what you’ve done

“Here’s what I can do for you” (even if it’s to try to help escalate)

Sometimes… sketch out an approach to narrow the gap

Even if it’s not you who could carry it out

Ask for help to narrow the gap

Who do you think could help make this happen?

Raise the flag high when needed

Security risks

Risks of data loss

Ethics breaches

Posted on

Fake News about Auto Parameterization/Simple Parameterization

I saw a question on Twitter today that took me down a little rabbit hole. And when I go down rabbit holes, I like to blog about it! There’s a TLDR at the bottom of this post if you’re short on time.

Here’s the question:

Simple parameterization leads to insanity

The thing about simple parameterization is that it’s not simple.

This is also known as auto-parameterization, and sometimes it’s not automatic — in other words, sometimes it looks like it happens, but it doesn’t actually happen.

In Klaus Aschenbrenner’s blog post, “The Pain of Simple Parameterization“, he wrote:

In general SQL Server only auto parameterizes your SQL statements if you deal with a so-called Safe Execution Plan: regardless of the provided input parameter values, the query must always lead to the same execution plan.

There are times when SQL Server just doesn’t think it’s safe to simply parameterize your query, and that can be SUPER confusing when you’re looking at queries.

Here’s what simple parameterization looks like when it works

I run two these two statements against the BabbyNames database…

SELECT FirstName FROM ref.FirstName where FirstNameId=76682;
SELECT FirstName FROM ref.FirstName where FirstNameId=86055;

In the actual execution plan, I see that the literal FirstNameId values have been replaced with @1.

Further, in the properties of the leftmost operator on the plan, I see that StatementParameterization type = 2.

Click for a larger image

In Query Store and the plan cache, the text for my query gets recorded as:

(@1 int)SELECT [FirstName] FROM [ref].[FirstName] WHERE [FirstNameId][email protected]

That single plan is show as having two executions. In other words, it’s re-used.

Simple parameterization sometimes doesn’t happen… but looks kinda like it did in your execution plan

Let’s change our query a little:

SELECT FirstNameId FROM ref.FirstName where FirstName='Grant';
SELECT FirstNameId FROM ref.FirstName where FirstName='Kendra';

When I run these new queries, here’s what my actual execution plan looks like:

Hmmmm… this is different! I’ve got the @1 again in my query text, and it’s even in the parameter list on the left.

But notice that this time, StatementParameterizationType is 0.

Last time, that was set to 2!

This didn’t really get parameterized

If I look in Query Store and my query plan cache, I find two queries and two plans.

The queries are formatted differently, they look like this:

SELECT FirstNameId FROM ref.FirstName where FirstName='Grant'
SELECT FirstNameId FROM ref.FirstName where FirstName='Kendra'

The parameter didn’t make it in there at all.

TLDR: Sometimes, it looks like something has been simple parameterized in an actual execution plan, but it hasn’t!

Just because I see an @1 being substituted in for a literal value in that text hint at the top of a plan doesn’t mean that simple parameterization has actually happened.

And for the record, I’m not sure that this is the exact scenario Grant was asking about, because my second example here isn’t technically a ‘trivial’ plan. But I do think that this is one of many reasons to use explicit parameterization practices, and not to rely on simple or auto-parameterization, because it just doesn’t work in many cases.

If you’d like to play around with these examples, the code is in this gist.

And thank you Grant for posting this question, I needed it to get me out of a blogging dry spell!

Posted on

Dear SQL DBA: Do DBAs Need a College Degree?

Our question this week comes from a database administrator who’s excited to be on a healthy career path, building their work experience, getting certified, and working with a more experienced mentor.

But they’re worried: will lacking a college degree block their career growth down the line?

I talk about my experiences helping managers hire DBAs, and also check out current job listings in three locations in the United States to answer the question.

Attend the Dear SQL DBA podcast — live! Register here to get an invitation.

Got a question for Dear SQL DBA? Ask away.

Subscribe:  If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app:


Intrigued by the free turkey I mention?

Here’s a direct link to the fun-looking DBA job in Bend, Oregon. This post is not sponsored by NAVIS  🙂


Welcome to Dear SQL DBA: do database administrators need college degrees? My name is Kendra Little from

I’m launching SQL Seminars!

Before I go ahead and get started one little word from our sponsor — that is me actually! Kendra Little at SQLWorkbooks. I just this week have launched new live seminars.

Two of them are: Learning Indexing by Solving Problems — that’s an eight-hour course that will take place live online across two different days, and I have dates scheduled in both June and September for that one.  I’m also doing Conquering Blocking and Isolation Levels, which is another eight hour course that takes place online over two days. Right now I have these on sale for more than 50% off — they’re two hundred and forty-nine dollars USD each.

I have a package where you can buy all the online seminars at a deep discount, it’s called the SQL Symposium

If you hit up you can get details on all of the seminars. Prices will be going up soon! You can see exactly how long you have left on the website.

I’m so excited about doing those courses — I’ve had a lot of requests for them, so I’m excited to go ahead and get them launched.

This week’s question

All right, this week’s question for the podcast came from a user who said: I’m currently finishing my Oracle DBA certification path and I love it, and I’m very excited to start my career as a DBA. There’s a Senior DBA and my current place of employment who is going to mentor me as well. I have fourteen months of hands-on experience with SQL Server and Oracle, but here’s the thing: I don’t have a college degree. I have about 60 hours of college credits. Do you think my education will hinder my chances of getting a job as a DBA even with my certifications and hands-on experience?

This is an interesting question, and I don’t know about you, but I do have a college degree — I have a bachelor’s degree and I have a master’s degree. But my bachelor’s degree is a super fruity humanities degree, and my master’s degree is in philosophy. But I got those a long time ago, so I was like um okay 60 hours, how much of a college degree is that? I didn’t even remember. I looked it up, typically a bachelor’s degree is a hundred and twenty credit hours. That does usually take four years full-time, of course we all know folks who have done it in a longer time, right?

I know folks who’ve done it quicker as well, but of course if our questioner has 60 hours and it generally takes about 120 hours, then yeah that would be a lot of work — especially if you’re working full-time and are trying to finish it up at night, that would not be a trivial thing to do.

How much of a hindrance is that going to be?

Anecdotally, I do know quite a few DBAs who are gainfully employed and who don’t have college degrees

Anecdotally, I know it can happen out there– but the question is how much will this hinder me?

I decided to take a look and sample some job listings out there and just look at what are the listings saying. I looked at listings in a couple of different areas: I looked first at some job listings near me. I live in Oregon in the Pacific northwest, and I was curious: around me what are people saying about DBA job positions, what do they want?

Because of course, a job listing is a lot like an ad on a dating site: you may not end up with your ideal match.

Some listings around Portland, Oregon, USA

But in terms of what listings are saying, there’s a job near me at a health plan. I found this job online, but I was at a SQL Server group in my area recently and I met someone there who works at this place, and talked a little bit about the job as well! For the DBA job, they would like to have it a bachelor’s degree in computer science information systems or similar or an equivalent combination of Education and experience. That position is focused on SQL Server. There’s also a software company downtown, and I know a lot of bright folks who’ve worked there, and this software company — they don’t list any degree requirements at all. They want five years of experiences as a DBA, and then if you’ve used lots of different technologies, they’re really interested in you, too. They want a DBA but they also want someone who’s interested in different NOSQL technologies and learning lots of different ways to store data.

Some more jobs around me in the Northwest: there’s a database developer at an IT and analytics firm and they again said we want a bachelor’s in engineering or computer science — and folks when they’re listing degrees they are listing degree areas. They aren’t just saying a bachelors, they’re really generally saying computer science or related area, or equivalent experience. There’s a gift card startup around here too that’s looking for a DBA slash system engineer. They want you to also be in their system and engineering team, there’s some app support, and again bachelor’s degree in computer science or related field or equivalent experience.

A lot of the companies in the Pacific northwest just say in the job listing “or equivalent”. They seem to be really open to people who have the right experience if, you don’t have a degree it’s no big deal.

There’s a tech company in Bend, Oregon — and if you’ve never been to Bend, Oregon it is a lovely place! I go there to ride bikes sometimes. It’s a wonderful place to visit. They want somebody with a four-year degree in computer science or equivalent, and their job description actually mentions that they give all their employees free turkeys every year on Thanksgiving. You can also have pie if you don’t want the turkey. They celebrate Halloween and they mention this in the job description. You know, when you’re looking at job descriptions even when you’re not looking for yourself, it’s a little depressing — but then occasionally you come across these little gems where you’re like, oh you actually seem like a fun company! And they’re saying you “or equivalent experience.”

There is also a cloud software company in Oregon, they are hiring people remote. I think they were headquartered in Texas, but they have the ad listed in Oregon so that it would come up for candidates here, since it is a remote job. And they wanted a lot of cloud experience as well as database experience, but again no degree listed. There were some desirable AWS certifications and the like in there of course, but it generally may not take you four years of going to school full-time to get an AWS certification. Maybe a little bit easier there.

In my area in the Pacific Northwest in the United States, it looks like there’s a lot of “or equivalent.”

Senior DBA listings around Washington, DC

OK, so I said — all right, my guess is just from having consulted with people around the country and around the world, that there are gonna be different job climates in different locations where some are more restricted.

I’m gonna look at some listings in Washington DC, because it seems like a pretty conservative area. I lived there for a few years when I was in high school and a lot of people care a lot about clearance and rules, so if they care about clearance, do they also care about degrees? I also looked more at Senior listings. I put the Senior keyword in there. Here’s a government agency position, and they’re hiring a Senior DBA. They don’t require a degree, but for their SQL Server DBA they want 10 years of experience managing and modifying SQL and Access databases, and they also want 10 years of working with Visual Basic macros. You don’t have to have a degree for that but you need to enjoy working with Microsoft Access and Visual Basic macros, which isn’t your usual DBA thing. Some job listings are like that.

There’s a government contractor that did say bachelor’s degree, they didn’t say “or equivalent,” but they didn’t put any special wording on it. Now they do require top-secret or s SBI clearance — I didn’t look up all the acronyms on these jobs — but they do require the candidate to have some clearance ,and my guess about this job is that if you have the clearance but you don’t have the degree, the degree I’m guessing is gonna be really negotiable. I know this based on my own experiences, because by the way you know I mentioned I have a really fruity degree in humanities. All of these job listings are saying you know we want somebody with a degree in engineering or computer science, and I still managed to get jobs over the years!

So I kind of know that when they have this in a listing, they don’t always mean it — and when they do really mean it they often say that in the listing. For example, in the DC area there was a recruiter with a job listing for a Senior DBA. The listing was for a fairly traditional on-call role out there, and they wrote bachelor’s degree HIGHLY DESIRED and they put HIGHLY DESIRED in all caps, because a lot of the candidates know that for this type of job, for database administrator jobs, the degree requirement is often very squishy, if it’s there at all. And not only do they put HIGHLY DESIRED in all caps, they listed it fifth among the whole list of requirements for the job. They put it pretty high up there.

This is a case where I actually think whoever’s doing this hiring does really care. This is the first listing that we’ve gotten to, right? They probably really care about that.

There was a Senior Database administrator at a university — this position said a bachelor’s and/or master’s degree in computer science or related IT field, that was in their requirements. This one actually kind of made me laugh. It didn’t make me laugh because of the particular University or anything like that, it made me laugh because recently I was chatting with a university in my area about the potential of me working as an adjunct and possibly — and this is really loose right, this is not like me getting an actual offer or anything like that — but potentially teaching a course on Introduction to Databases. The reason that they’re interested is because I have a lot of work experience with databases and I have experience teaching. I have hands-on real-world experience and I know how people actually work as DBAs and database developers, not that I have a bachelor’s degree or a master’s degree in computer science or a related field. So the fact that there’s a university who’s hiring a Senior database admin and they’re like, Oh we’d really like you to have a master’s degree in computer science, I’m like well you may not actually need that to teach it, do you really need it to do the job? But some folks think so.

Now how hard is this requirement? This one could be squishy — it could be a nice to have for them, it’s hard to know.

Other listings in the DC area: there was a Senior DBA for a government contractor that had BS in computer science or a closely related field. They didn’t put it in the main job requirements, they put it in additional job requirements — and whenever I see it that far down whatever, is in additional I kind of think of as a nice-to-have. They really haven’t put it in the main requirements. There’s a Senior SQL DBA for a different government contractor, this is another one that they wanted an active secret clearance. They even listed a specific SQL Server certification they wanted someone to have, but they didn’t list a degree requirement. So not all government contractors even are listing this as a requirement, and if they do very few of them are really emphasizing it.

I finally looked at one more area, I said how about Southern California?

I just made the area really big I said look at Southern California.

Here we found some of my favorite style of job listings. There’s AAA of Southern California, who is hiring a Senior DBA — and this happens sometimes, you’ve got folks who’ve in the HR department who maybe haven’t updated their job listings in a while, so they actually want somebody to have quite a few years of experience with SQL Server, they want a bachelor’s degree in computer science or equivalent experience. So we’ve got the equivalent experience here, but then they also want proven experience with Query Analyzer and Enterprise Manager. But they do want you to have worked with recent versions of SQL Server. I think someone wrote that job listing about 18 years ago and then it’s just been recycled over and over and over again. It doesn’t necessarily mean it’s a bad job, but it makes me laugh every time I see that. I kinda wanna be like maybe we should update the wording here?

There’s a health plan in Southern California that does want a bachelor’s degree. They even say in business administration, computer science or related field, which I actually thought I was interesting that they broadened that. They want a minimum of eight years of experience in IT consulting, business analysis, or a related field. This is another one of those jobs where I’m like: okay I suspect that you care more about the eight years of experience. There is an Oracle application DBAs for a freight tools company that is open to equivalent experience. They do want 10 plus years of overall experience with a minimum of five years working with Oracle apps.

There’s a Senior DBA for an enterprise financial company, and in that case they said quote BS in information systems or relatable field is a required. It “is a required.” This was a recruiter listing, and recruiter listings are kind of famous for being grammatically questionable, so I’m not sure that the hiring manager would like that “is a required” was used in their listing, but apparently this is another example of — actually in this case they do really mean it, enough that they’re actually saying in there, “no this isn’t squishy.”

What I saw in these listings matches up what I’ve seen as being part of the hiring process, being on the hiring end. It is really tricky to hire a great database administrator.

Managers do like college degrees, the people doing the hiring are hanging on to what I see as a kind of old-fashioned way to tell if someone is gonna be a good candidate. I am a person who actually has — I spent a bunch of time getting my bachelor’s degree and then getting a master’s degree. I certainly personally see the value in going to college, but I don’t think it’s the only way to show whether or not you can be a great member of a team and whether you can be effective in a technical environment. Whether you can communicate well. I also think that if managers really are valuing experience — and when it comes to database administration managers really do value experience, and in part this is because there is no great degree in becoming a DBA.

Computer Science is not the same thing as database administration. Database administration, maybe there’s a couple of classes specifically on that topic, and with database administrators there’s a lot of influence on problem solving with very specific technologies and working as part of a team. You don’t necessarily get trained on that as part of a computer science degree. So the fact that managers like both these, things they like college degrees and they really like experience means that they go through and they start interviewing different candidates.

Well, different candidates aren’t necessarily going to be a great fit for their team both due to technical skills as well as communication, and how the team works together. So even when you have qualified candidates, it’s hard to find the right person. Usually there’s something that’s got to give, and typically managers will take experience and someone who fits technically with the team, and whose communication skills are good — typically they’ll go ahead and take that person even if they don’t have a college degree.

This was a very non-scientific survey I did of the listings, but the listings that I saw said, “We are open to equivalent experience. It would be great if you had a college degree, but we will look at equivalent experience which includes all sorts of life experience and work experience.” When people really seem to want a college degree, they actually say so. I saw all of two of those listings in my look through here today. I suspect that even those if you had a college degree and it wasn’t in computer science, they would take that as well. I think what they’re saying is we would love it if you had a degree in computer science or engineering but we really need you to have a degree because the management at this company won’t budge on that one.

There are some cases where I have found — and it has been a few — where management just has this idea that everyone has to have a four-year degree and they won’t budge on it.

In many jobs security clearances or certifications, sometimes they care more about that than they care about a degree.

So for our questioner…

The question was: I’m on this great career track and I’m feeling really good about it, and I’m loving it. I’m a little worried, is my not having a degree gonna rule out some jobs? Yeah, lacking a degree is gonna rule out some jobs, but in my experience and with what I see in the market, it’s gonna be a pretty small amount of jobs.

It’s more prevalent in some locations than in other locations, like here in the Pacific Northwest I looked around at the job listings and “or equivalent experience” was everywhere.

I wouldn’t really worry about it, because our questioners in the position where they’ve got a mentor, they’re building that hands-on experience, and they’re making connections with other people. Those connections you make with other people, with people who you work with actively, with people at a local user group that you attend, with people that you meet at conferences — maybe it’s a free SQL Saturday or maybe it’s a database conference, those connections are often gonna land you a job! In most of those cases, even if they had been really wanting someone with a college degree, if they’ve interacted with you and they know cool things that you’ve done, and they’ve seen how excited you are about data, and they have a real excitement for you — even in those cases where they actually really want a degree — for someone they know, often it’s not gonna be a big deal to leap past that on the requirements list.

I do think there’s a deeper question here

I’m looking at all these listings — I thought it was interesting that there’s so much about computer science degrees on these database administrator job listings. I think there is a long term bias in terms of thinking that a college degree is proof that it makes someone smart. And as someone with a master’s degree, I can tell you that it’s not proof that I’m smart at all! But my master’s degree isn’t in computer science.

If I had a computer science degree, would I be a better DBA?

I’ve had some room to think about this recently because my partner in life has gone back to school to study computer science — Jeremiah Peschka has a degree in English. He had a bachelor’s degree in English that he got long ago in a place far away. After he got that bachelor’s degree in English, he worked as a developer and he got into database administration. He got into consulting and teaching and worked a lot with data.

Now he’s gone back to school and is working towards getting a PhD in computer science. But with just a bachelor’s degree in English, you don’t just walk up and say: I would like PhD in computer science please. You need to do the equivalent of — if you’re familiar with medical school, you know that some folks can do a post baccalaureate, what’s called a post bac. If they don’t have the pre-med courses they can go back and just study up on those courses, then go to med school. There is something similar you can do with computer science, where you don’t have to get a whole second bachelor’s degree. You can take required undergraduate computer science courses and then after you pass those levels, move into masters and doctorate levels. I’ve gotten to watch this. I haven’t certainly done all of the work, I haven’t done ANY of the work, but I’ve watched as he’s gone along this journey of doing courses on algorithms, courses on logic, courses on intro to databases, on programming and all this stuff. You certainly learn a lot in a computer science program.

Does it make you a better database administrator? Well, yes, but I also think that you could get those the same things that you’re getting about critical thinking and understanding some of the internals of databases, I think that you can get that from certain work experience.

We all know that not all job experiences are exciting, and not all job experiences teach us a lot. Some jobs definitely involve more problem-solving, more creativity, more thinking and give us more opportunities to learn than others. I think that from what I’ve observed, yeah you can learn a lot from getting that college degree, but the relevant experience that you can get in life and that sometimes you can get just by challenging yourself and outside of your main job, learning things, taking on challenges getting yourself educated, figuring out how you can talk your manager into letting you go to that conference, for example… That you can build those skills out of there as well. The number of courses that are in a computer science program that are specifically related to database administration, those also are not a lot. I personally I would be interested to hear an argument that says yes DBAs should have computer science degrees, and I would interest be interested to see the argument of why, because at this point in time I personally just don’t see the logic to it when it comes to the field of database administration.

So, I as someone who knows a lot of DBAs who don’t have college degrees, as someone who looks at these job listings and sees the “or related experience” and sees that there’s just a few job listings that are saying, no really we really do want a degree — I wouldn’t worry. I still wouldn’t worry too much about it.

If you’re a long ways from a college degree, I wouldn’t freak out about it if you want to be a DBA. It’s connecting with other DBAs, building your skills and experience and landing those jobs to build up your experience, especially more and more emerging recent technologies in SQL Server. Those are the things that will continue to serve you in advancing your career and landing jobs as a DBA.

Thanks so much for joining me!

Now, for some comments

Tom says “or equivalent” allows a lot of wiggle room.  If this person is at the halfway mark, they would probably be better served to finish their studies off.

That’s a vote that says, okay if you’re at the halfway mark, they would probably be better served to finish. I think halfway is just not very far when it comes to a four-year degree. I know some folks–it means a lot to them to finish that degree. If you’ve got a personal investment and you’re gonna love having that degree, then going to school at night can be worth it. I know some folks who’ve done that and it’s really rewarding for them, and I love that. But if it’s not that situation I think you can get a lot of cool jobs without it, but I love hearing your take on it too.

Ryan says if they want Enterprise Manager and Query Analyzer they could be saying that they’re still using SQL 2000. That was the kind of interesting thing about the listing is I didn’t see that in there. I was reading fast, Ryan, I was reading fast. They did mention a lot of more recent versions, but I would certainly ask them — like I would I would say any mention of Enterprise Manager or Query Analyzer in a listing is a big old red flag that that makes you want to pull back if you do talk to these folks, kind of pull back things a little bit. Be like, what so what are the older versions of SQL Server that you’re running? I need to know, we need to talk about whether this relationship is gonna go further or not! Because it’s tricky to manage these old unsupported instances, especially when things go wrong, if they’re critical to the business. But yeah, it is one of those one of those fun things.

Great question here: How can I get fast experience if my current — and I think I’ve got a word missing in here — if my current job doesn’t provide a lot? This is an interesting question and this is I cover this a lot in my junior DBA talks of how do I get a job if I don’t have experience. My main takeaways for this, I’m gonna do a really high-level summary, are: you always want to be honest. You can build some experience in test environments, but you want to always be honest what you’ve learned by testing. You want to make connections with people. Things like going to a local user group, talking to people there. Say hey: I’m just starting out and I’m building up my experience. How did you get into the DBA jobs here? Do you have any advice for someone just starting out? Do you know anyone who’s got junior DBA jobs? Making those connections with people and explaining where you are and asking for their help in your area is going to be your best hope for finding those opportunities.

Because it is really hard landing those first DBA jobs! It was really hard for me, I had many jobs where I kept just taking jobs where I worked with data and trying to build experience in a production environment managing data flows. Edging more and more towards the more technical things, and then eventually landing it. I didn’t know about user groups when I was first starting out and I really wish I’d had that to add in — especially because they’re free and they generally meet in evenings. There’s also virtual groups as well. A little bit harder to make the in-person connection with those, but you can sometimes do that as well.

Comment from Scott: Georgia Tech is offering a masters in computer science for about seven thousand dollars total. I’m still working on my BS but it’s a thought for anyone wanting to go on. It’s all online, Scott says. I think this is really hugely interesting for folks who really are interested in getting master’s degree in Computer Sciences, especially when you have a love for the topic. Maybe you want it for getting a job, but you also want to work more on building your knowledge. Obviously I’m into that. You don’t get a master’s degree in philosophy for practical reasons!

But more and more computer science departments, really good ones are starting to build online programs where you don’t have to fly across the country or take two weeks off, or go at night. You can, for reasonable amounts of money, attend University and build degrees online. I am obviously biased towards online education, I don’t think there’s any secret about that, but I love the fact that more and more this is becoming available. If you don’t want to get a degree but you want to audit courses, there’s a lot of really great big-name computer science programs that are doing more and more free material online. They don’t necessarily land you towards a degree, but they could teach a lot and you could have a lot of fun and learn a lot about yourself in the process. Thanks, that’s a great comment. This is why I love doing this online, there’s things I didn’t think about including that are really important and will be valuable for folks.

We have just run out of time, and I need to wrap this up, get this recorded and ship this podcast episode!

Thank you so much for joining me for Dear SQL DBA. We will be back on May 17th, I’ll be talking about dealing with a lack of control as a DBA. It is really interesting as a database administrator how we often get to be responsible for things but we don’t get to control everything, and there’s interesting things to think about and tips that I’ve learned over time about balancing that and making it work that I’d like to share.

If you have thoughts on that – I would love for you to join in. That’s gonna be recorded on Thursday, May 17th at 1 p.m. Pacific. Hope to see you then!

Posted on

Dear SQL DBA: How Do I Prepare for Certification Exams? (video with captions and transcript)

In this episode, I talk about how to strategize for and prepare for Microsoft Certification exams, using the Database Fundamentals exam as an example.

Want to attend a live recording of the Dear SQL DBA podcast?

It was so much fun to record this episode with an audience! Thanks to everyone who attended, this format is a keeper. Register here to get an invitation.

Got a question for Dear SQL DBA?

Ask away.


If you’d rather listen on the go, subscribe on iTuneslisten on Google Play, or plug this URL into your favorite podcasting app:


Welcome to Dear SQL DBA: a podcast and youtube show for SQL Server developers and database administrators.

I’m Kendra Little from

This episode is all about preparing for certification tests

I get a lot of questions about certifications– so many questions that I already have a podcast episode addressing the question: should I get certified? I still think it’s worth thinking heavily about whether you want to invest the time and money into getting certifications, and figuring out are these going to be meaningful for your career.

Certifications has been a really valuable milestone on my path to learning about a lot of topics in SQL Server, but they don’t have to be — and certifications aren’t required for you to meet your goals, necessarily, when it comes to your career working with data.

In this podcast, I’m going to talk about how I strategize approaching something like a certification exam, but do you go back and check out that other episode if you’re questioning: hey do I need to get certified?

Because this podcast is all about taking the challenge of certification and making the most of it.

One word of warning: I will be talking about an example exam in this in this session, but these things change a lot!

Not only do the exams change, but the certifications themselves change, and the definition of hey what is the certification and what does it cover — and that is something that shifts over time. So, if you’re listening to this episode in the future, don’t just assume that this exam is still the same. You want to take this strategy that I’m giving you. Take out of it what’s meaningful for you and use it as a toolset to approach the exams that you want to take on your own path.

Now, that being said, the example exam I’m talking about that we’re gonna say okay, how can we strategize taking this exam for someone who’s just getting started on their path towards being a DBA — that exam is exam 98 364. The database fundamentals exam. Now, this exam covers a lot of ground, and it doesn’t assume that the person taking it has all of the job experience– but it’s gonna cover all of the fundamentals, or at least a really wide chunk of them.

When we’re approaching an exam, what I like to do is first…

Get a big-picture view and just really start looking at: what are the skills that this exam is measuring? I want to expand each area and get the details out of the exam description, and then I want to summarize it and analyze it in my own format. I like to use a spreadsheet, and I like to do that because I want to assess myself on the different skills that they’re measuring, and also I want to do some analysis on where I may want to prepare and where I may not want to prepare.

Summarizing the exam is really helpful because these exams can be intimidating. The level of things that they cover. the number of things they cover and the level of detail in them may become overwhelming, and if we don’t do that analysis and strategizing about where to invest our time, we may quickly become overwhelmed– and we may become, you know, kind of frightened of the exam. That doesn’t put us in the best place to take the exam.

We want to set ourselves up so that we have good confidence going into it and feel that we have a good chance to tackle the exam

Looking at the database fundamentals exam, it has multiple areas, and it lists out for each of these areas how much weight is put on that area in the exam.

The first area that is covered for database fundamentals is this concepts section

Understanding core database concepts is estimated to be 20 to 25 percent of the weight of the exam.

We get a fair amount of detail about what they’re going to ask us about: it says you need to understand how data is stored in tables, you need to understand relational database concepts as well as data manipulation language and data definition language. All of these things together are up to 1/4 of the weight of the exam. All right, so I take out these headings and I put those into my spreadsheet.

I may read some of the detail on here to make sure that I understand what this means as well.

The next section of the exam is creating database objects…

…and this is again 20 to 25% of the weight of the exam. This section, creating database objects, contains questions on choosing data types, understanding tables and how to create them, creating views and creating stored procedures and functions. Now, there’s parts of these that may sound familiar if we think back to the first section. The first section had understanding how data is stored in tables, which is understanding columns and rows. The second section of this says understanding tables and how to create them. It’s very very similar to the first one. It’s more about understanding T- SQL to create a table, but of course to understand the T-SQL to create a table, we need to understand these concepts of columns and rows.

So there is some overlap of concepts in here. If I understand tables and how to create them I am going to understand most likely how data is stored in tables, as long as I understand not just how to– you know create tables, but the fact that they can have rows in them as well.

These sections of this exam aren’t measuring totally separate things…

..and if I understand some concepts well it may cover more than one skill measured in the exam. The first section of the exam is about manipulating data: it’s about selecting data inserting data, updating data, and deleting data. And again, some of these may sound similar because that first section that was big picture already covered a lot of these concepts. To understand selecting data we need to understand relations between tables. To understand inserting updating and deleting data, these are all ways that we can manipulate data.

The next section of the exam is called understanding data storage…

…and this is an example I think of where some of the titles may not mean what I think they mean at first. Understanding data storage– if I only saw those words I might think that this was about how to create files in the file system and how SQL Server pages store data, or how data access to storage is done. But when I look into the subheadings of this topic it says that understanding data storage consists of normalization, primary, foreign and composite keys, and indexes. So they’re talking more about what are the ways in which we design our data structures in the SQL Server. It’s not talking about the Windows file system and how SQL Server talks to the Windows file system necessarily, and that’s part of why I think it’s useful to go in and expand these and pull out the sub headings: because if I simply read the big picture categories the way in which I interpret those words may not be really what they’re talking about. This data storage portion is 15% to 20% of the exam.

Now, there’s one last section on this exam called administering a database

Administering a database is only 10 to 15%, and they only list two concepts or two skills inside administering a database. There’s there’s definitely more to administering a database than this, but in the fundamentals category. the skills they’re talking about are database security concepts and backups and restores. This is a great example of an area where if you tried to master all parts of administering a database to pass the database fundamentals exam, you would be doing way more work than this exam covers. And that might be really really valuable, but we’re really at a big picture level here, and at the fundamentals level they’re really doing some selection of topics and deciding exactly what is fundamental to database administration.

All of these headings that I’ve listed at all of these skills, I take these skills and put them into a spreadsheet that gives me a nice big picture view

Each of these skills is approximately five and a half percent to seven percent of the exam, and as we said there is overlap in some of these skills that were listed. So data manipulation language is 5.6 percent just as an overview topic but then inserts updates and deletes are each seven percent as well. So if I want to work on just a lot of T SQL and understanding how to select and manipulate data, that alone is gonna have quite a lot of coverage on this exam.

It’s worth assessing that, because just looking at individual skills you may underestimate the weight that something has on the exam, so look for redundancy in the areas listed.

I also like to add some columns to this spreadsheet and for each skill that’s being measured, assess: what is my confidence level in this area?

You do need to know yourself a little bit

Do you tend to be overconfident?

Do you tend to be really self-critical?

Try to not be so self-critical or dial it back a little bit. If you tend to be overconfident you want to have a fair idea of this just for your own purposes. What we’re really saying is where are my areas where I’m strongest? Where my areas where I’m weakest? This will help– this will be part of the formula to figure out where I want to study– but not the whole formula because I also want to look at these areas and note separately: how interested am I in this area? So for example, for this exam what I did is I went way back and I thought about Kendra before she was even a Junior DBA. Kendra when she was just starting out. I really loved writing queries– that was one of the first things I did with SQL Server was figure out how to interpret data in the database and make it useful. Looking through these areas I had medium confidence on storing data in tables, and medium confidence about relational database concepts, because I was used to working with tables and joining them. I was a little bit used to data manipulation language because I would use temp tables, but I wasn’t super confident. I had low confidence when it came to data definition language, because I would do a lot of things like select into a temp table and then manipulate the data in there.

I didn’t feel that I knew a lot about data types, and honestly looking back, I would have underestimated myself a lot, I would have said low comfort. but actually I DID know a lot of them, I just didn’t have a lot of knowledge of what I didn’t know in that area, right? Because I only knew– oh I’ve interacted with these data types. It turns out I’d interacted with a lot more data types than I really gave myself credit for. But I would go through each of these areas and rank: high confidence, medium confidence, low confidence– or if I just literally didn’t know anything about it, just put question marks. I knew absolutely nothing about indexes, I knew I didn’t know what an index was at this point in my career, so I would have just put like question mark question mark question mark there. I also knew hardly anything about security. All I knew about security was how to ask to get access to something. I wasn’t at the point where I was a database administrator so I had really low or non-existent knowledge in some areas in these exams, but also there were some areas that were interesting to me– where I had really high interest. And then there were other areas where I had really low interest. Noting those is really really important as well.

Saying that something is low interest, isn’t saying that it’s not important or it is not valuable

It really is a question of what is something that I just I have a desire to learn more about. I find it really compelling, or something that I think might be really useful to me in the short term.

At that point in my career, where I was really focusing on getting a lot better using databases, as somebody who used T- SQL, the areas that were of the greatest interest for me had to do with becoming more and more of an expert at creating objects in the database, and I just really wasn’t the point where I was ready to learn about backup and restore. It was later in my career that that became much higher interest to me. You want to avoid the anti-pattern of being the perfectionist at this point. There can be, for many of us, a tendency to say: I’m just not ready to take the exam yet and to get stuck in this place where we never get started, because we’re like oh I haven’t learned these three things yet. To me certifications can help move your knowledge forward, and a big part of that is just getting to the point where you’re ready and excited to try the exam.

To me, the point of being ready and excited to try the exam is when I feel that I have enough coverage of these areas, and I’m in the right mental state.

But that right mental state isn’t that I’m confident I know everything.

That right mental state is: I see the challenge in the questions and I think I can get past the tough videogame level of this certification exam. There’s gonna be moments when I’m like squinting at the certification screen and I’m trying to get past something, but I want to be having fun while I do it, because honestly for me I am better at exams when I’m in that mood of: okay I think I’ve got what it takes to pass this exam, and I’m gonna go for it, and I’m really going for it. I’m not like at this oh I know everything overconfidence stage.

I’m at the: I think I can tackle it competitive stage. Not competitive with other people, but really competitive almost against a video game boss type thing in the exam. I’m competing against the challenge that’s been set up for me. Looking at how I’ve scored myself in my imaginary report writing Kendra, approaching the fundamentals exam, if I highlight areas where I ranked myself as either medium or high comfort level, looking at the percentage is given to each skill on the exam comes out to about 42% coverage of the exam. Honestly, that’s not so bad! That’s a pretty confident little Report Writer Kendra, who’s like oh you know I know something about these other areas, even areas where I have kind of low confidence, I’m gonna be able to possibly make some guesses in those. But I think I’m gonna be able to make a really good guess if I have medium or high confidence in that area.

42% coverage isn’t bad, but I might want to bring that up a little bit

I might want to get to the point where I have medium confidence in some other areas on this exam, but I’m not gonna try to cover every single area on the exam, because I just want to get to that competitive fun-loving part. I need to pick out other skills that I want to level up, and here is where that interest ranking comes in. Areas where I have low confidence, but I have high interest: these are things that are actually going to be valuable to me to learn at this point in my career, because I have either an excitement about them, or I think they may be useful to me, but I’m not very confident about me. Those areas are the ones where I want to go into those and say: yeah okay I’m not super confident about creating tables with T-SQL, I want to build that up because I have high interest.

Same thing with creating views and creating procedures and functions. I’m excited about learning those and preparing for this exam has brought to light: hey here’s something I’m excited about that I don’t have medium confidence in. That is an exciting thing to go out and learn to me! So I want to avoid those areas where I have low confidence, but my interest is also low. Those, hey maybe later on my interest is going to be higher in those, but I’m not gonna pick those out to tackle right now for this exam. If little Report Writer Kendra tackles those areas where she has lower confidence and higher interest, and she if she gets those up to medium or high level interest, then I’m gonna have 67% coverage of topics on this exam. And at that point I’m gonna say: okay I’m gonna go ahead and give this a try and see how I do, because I have a good level of coverage, and confidence enough that I feel good enough about to try and tackle this thing!

My study style for these areas where I have low confidence and high interest, my particular study style is very hands-on.

This is just how I learn. I have always learned something better if I go out and research it, and write down notes. I have to do something– I have to either write down notes, or I have to write a demo script. Learning for me is the best when I have a problem. Maybe it’s is me creating the problem, or maybe it’s just me saying okay I want to learn how to create functions, so I’m going to look at some examples of functions online and then I’m gonna use a demo database and come up with my own function, and then make sure I know how to use it. My tools for preparing for these are things like sample databases, evaluation– or Developer Edition, rather, of SQL Server, which is now free. Evaluation edition is free – but it expires, so Developer Edition is awesome.

Books Online and online documentation and search engines and blogs– but actually not just reading for me, really getting my hands on things helps me learn. Adapt that to your own style of learning, of course. If saying something out loud helps you learn, then you want to say things out loud. I really really– when I’m preparing for these exams– like to get myself into the mindset of approaching a game. Maybe it’s a video game, maybe it’s some sort of competition for you, but for me when I’m approaching these questions with exams I want to learn to read the question critically.

And this is something that sometimes folks like to do practice exams for.

I don’t think that the practice exams all have to be official practice exams

For example, on SQLWorkbooks, I have a bunch of free quizzes– and I am NOT claiming that these free quizzes are anything like certification quizzes. But they are free, and they do give you a chance to read the questions critically, to practice eliminating answers where you’re like: okay I know that one’s not it, and I know that one’s not it, so I’m down to two, and now I’m gonna –how I can decide between these two? You also can help get into the habit of saying: I’m going to look at– gonna run through all the questions on this quiz, and I’m gonna kind of set myself, that I’m gonna keep timing in mind while I do it. So, if one question is really tricky, I’m gonna table it and I’m gonna come back to it later.

Now, my quizzes don’t have the functionality– I don’t have hundred question quizzes, so I don’t have the functionality of marking a question for review– but in many of these online exam situations you do have that ability– which being mindful of the time and not getting stuck on a question is a really important skill. And that’s something that any practice quiz can help you with if you get used to: okay I’m gonna go past this question and I’m not gonna let it get to me, I can come back to it at the end. Just that practice of being comfortable with that and having it not ruffle you too much can be very very useful.

Timing is a huge part of being quite being successful, not only in video games, but also at quizzes.

Calmness and preparing for the test ahead of time can also help you out a ton

I have learned the hard way that anytime I’ve got something that is sort of an important thing that I want to do well coming up: it might be an exam, it might be an important meeting, it might be a long flight, it might be any critical situation: I want to do my best at that, I may want to start thinking about preparing for it a week in advance, or sometimes even more. Doing things like saying: I’m going to start getting to bed at a consistent time so that the night before the exam I have this evening ritual where I wind down where I go to sleep, where I’m not staying up all night. Because for me, just getting and having a routine going into the exam where I’m calm, where I’ve been doing healthy things like walking my dog and working out, it puts me into just a much better place to take the exam. A much more confident place and a more relaxed place. I am also personally into meditation.

Meditating the morning before a big event makes a huge difference for me.

It helps me remember to breathe during the exam, it makes it easier to not get stuck on things like tough questions, to not have them bug me and get into my head. Because a lot of the folks who have problems with exams — times when I’ve had problems with the exams have been times when I’ve gotten into a place of fear, where I’ve gotten really nervous, and I’m like oh oh I didn’t do well on that question! Having a question haunt you during an exam, or a series of questions haunt you can really be tough. But if you have that mindset of saying: all right, I got knocked out of that question, maybe, I’m not sure if I got it right, but okay I’m gonna tackle this next one! And now my mind is fresh, my mind is clear, I’m ready to get this. I’ve got my shoulders down and deep breath — self-critical thoughts have gone past me.

That is the place where you’re more likely to rock that exam. Building up these habits of getting to a clear mental space– and having had a full night’s sleep really can help you rock out that exam and stay in that mindset of: hey I am going to take this exam down, and get past this confidently. Knowing that the exam doesn’t mean anything about you is really really helpful when it comes to having a great time during the exam, to me.

The perfect exam taking– if I got to you know level 100 of exam taking, I would be in a place where I could take an exam that I did terrible at, and still have a good time, and still learn something from the experience

I’d be able to say, after this, okay, maybe that was a disaster, but what did I learn from the questions it asked me? I’m gonna learn some things about the subject matter itself just from analyzing the questions they asked me. And what did I learn about what I want to do? Maybe I’ve discovered that despite my research, this exam is actually not related to what I want to do, and that, whoops I maybe I don’t want to pursue this!

But maybe what I’ve learned is, okay, I’m gonna get my results back and it was it was Cathrine Wilhelmsen who pointed out– she said hey you know, you get back the skill results, an analysis of where you were strongest and where you were weakest. If this is something you want to pursue, you can use that to redesign the areas you want to study in. You can take that list and you can rank it: high interest, medium interest, low interest. It has just ranked you on your confidence levels, or rather your your skill levels, right? So you can take that and use it as your map to your study plan. But really, the sign of a great exam is learning as much as you can from that exam, and using it to move forward. At the end of the exam, you want to be in place where you don’t use the exam to judge yourself. I know it’s something– I know it’s hard to say this exam means nothing about ME. Look at the exam results as meaning something about what to DO next.

If you pass the exam, maybe you want to take another exam next. Maybe you don’t. But if you don’t pass the exam– same thing. Maybe you want to use that to redefine your study areas, maybe you don’t. It’s all up to you and it really is completely about choosing your own adventure.

Please please please don’t take negative exam results to mean that you are not good enough

There are a lot of super smart people out there, just tons of super smart people who haven’t passed these exams the first time. Maybe they haven’t passed these exams ever.

These exams are a challenge, and they can be part of your journey to your career. They do not have to be.

And they do not have to define you. They’re an achievement you can have, but they aren’t something that judges you, and they aren’t something that says what you are worth.

You are worth a lot regardless of whether you’ll ever take a certification exam.

Now, getting more for your buck

There is definitely a path– like if you are saying okay these exams are expensive, and I want to do them, but I want to make sure that I get the absolute most out of the certification experience– I believe that the way to do that is when you are studying for the exam, when you find those areas where you say, okay, I I have low confidence but I have high interest: start a blog and write about those things as you learn them. That will not only help you learn them and keep you on track, but if you’re consistent, building that blog that blog is going to be the thing that helps you get a job moreso than the certifications ever will. Because just the fact that you’ve passed a certification or a set of exams for a certification, that tells your employer may be that you’re persistent– but sort of at a high level. A distant level, right? They don’t know a ton about that.

But if you blog about those areas while you’re learning about them, and you do it consistently, that tells your employer: hey how do they approach problem-solving, and in great detail! They can see in sampling your blog posts how you’re analyzing something, why you think something is interesting, what your writing skills are– and a little bit about your personality.

That blog can absolutely boost your career in a way that the certification by itself does not

It’s a little bit like getting to watch somebody practice for the Olympics rather than just hearing how they ranked in different events. Seeing how someone is working out and how they’re attaining and sharing knowledge tells you a ton about that person, and that can absolutely be a worthwhile thing to list on your resume and to share with potential employers, as well as your colleagues in the community– it’s about you and about your interests in SQL Server.

Thank you so much for joining me for this episode of Dear SQL DBA.

I have some upcoming live episodes that I’ll be recording: on May 2nd I’ll be talking about the question, “Do DBAs need college degrees?” Then following that we’ll be talking about dealing with lack of control as a DBA, we’ll talk about being a woman in technology on the internet, and training resources for SQL Noobs is coming up on June 13th. That one’s gonna be a ton of fun. So check out, you can register for free recfording sessions for the Dear SQL DBA podcast, and there’s lots of other fun stuff on the blog. I already mentioned the quizzes, so if you want to get into a quiz taking frame of mind, check out that top menu bar at, it is under the fun section.

Thanks so much folks! See you in a podcast episode soon.


Register here to attend a live recording session.

Ask a question for Dear SQL DBA here.

Posted on

Query Store Cleanup Can be Part of a Blocking Chain

Forgetfulness can lead to learning something new. This is a bit of a nightmare when it happens in production, but a treat when it happens in an isolated test system– and that’s how I learned this.

I left a bit of blocking open on my test VM, and forgot about it.

I was using the BabbyNames sample database. In one session, I had run:

begin tran
    alter table ref.FirstName add foo int null

I left this session sleeping, its open transaction holding a schema modification lock against the ref.FirstName table.

In another session, I ran:

select * from ref.FirstName

I did my testing with this setup, then went back to editing video, and forgot about it.

I forgot to unblock it.

Later, I came back and wanted to measure something in Query Store

This is an isolated test system, so I went to clean out Query Store as a reset. I didn’t need any of the old information in there, so I ran:


I was surprised when I didn’t see this complete very quickly, as it normally does.

I checked for blocking in sp_WhoIsActive, and found that my cleanup was blocked

I used Adam Machanic’s free sp_WhoIsActive procedure to check what was going on. Here is the blocking chain:

Clearing Query Store created two sessions

Session 40 shows no sql_text, but it appears when I run the QUERY_STORE CLEAN all command, and disappears when I cancel it. It also shows not “sql_command” if I run sp_WhoIsActive with @get_outer_command=1, and it shows no lock information if I use @get_locks=1.

It’s clearly getting locks, because it’s blocking its little friend, session 74, but it’s just not showing what it’s doing.

Meanwhile, session 74 is waiting on a lock on sys.sysschobjs

Although we can’t see the locks directly on session 40, I can see that session 74 (who is blocked by session 40, who is in turn locked by the alter table) is waiting for a shared key lock on the cost index on the sys.sysschobjs system table.

Sys.sysschobjs is documented, it “Exists in every database. Each row represents an object in the database.”

If I connect to the Dedicated Admin connection, I can query the sys.syssschobjs table – and I can read it if I allow dirty reads (seeing the uncommitted data):

USE BabbyNames
select * from sys.sysschobjs (NOLOCK)
where name = 'FirstName';

There is a modified date on this table (column name modified), which was updated around when I started the modification on the table.

I don’t think this is a bad thing – I’m writing this post just to document it

One cool thing in Query Store is that it has some knowledge of the objects in a database. It will tell you if a query is part of a procedure, for example.

That’s a good thing, but it is probably one of the reasons that Query Store reads from the same system tables that may be locked if we modify the schema of objects.

Hopefully, most folks don’t:

  • Have long running transactions that modify objects in production
  • Clear out Query Store data often in production (it’s useful information, and the basis for some cool features)

If you are unfortunate enough to have BOTH of these patterns, you may run into blocking.

This could also occur if you are trying to clear out Query Store when an offline index rebuild is running.

Posted on

Dear SQL DBA Q&A: A Poorly Indexed ISV Database

At a recent conference, two speakers reminded me of something important: when you put effort into learning something or helping folks, don’t simply put your words in private emails or post-it notes on your desk. Whenever possible, blog it as well. It can help other people, and it can also help you remember it in the future!

I’ve been kinda/sorta/pretty good about this when it comes to Dear SQL DBA questions. I am going to start being more consistent about it, and sharing the answers with YOU GUYS, too.

Know that you are always invited to also be the SQL DBA on the other end of the email, too, and chime in with your take in the comments. Feel free to disagree with me, but be kind and respectful to the anonymous questioner– there’s a person on the other end of that keyboard.

Question: I’ve got a poorly indexed database from a commercial software vendor. What should I do?

Here’s the question:

I’ve encountered a performance issue which I believe is related to indexing. Our database (created by a commercial software vendor) includes a table with over 78 million rows… and no clustered index! This table is queried by the software many times daily and the effects appear to be hindering our performance.

There are two non-clustered indices on the offending table: one non-unique including a column called [ColumnA], and the other is unique and includes only [ColumnB] which is the primary key. Both [ColumnA] and [ColumnB] are of uniqueidentifier data type, so I believe that adding a clustered index would hinder performance (please correct me if I’m wrong!).

The problem is that the query which hits this table requests four columns in addition to [ColumnA] and [ColumnB] which are not included in the indices. The estimated query plan shows a RID Lookup (99% estimated cost!) on this table and I really want to get rid of it so we can boost performance, but I’m really hesitant to make any changes to the indices on a table of this size.

Now, I know some of you out there have immediately started itching. We’ve got a heap with 78 million rows (not sure how many GB it is, but that’s a few rows), we’ve got a couple of GUID columns, and we have got what sounds to be a dramatically bad indexing solution.

I do immediately have doubts about whether this table should be a heap or not — and I also immediately wonder if deletes happen against this heap, and if it might have a lot of empty space trapped in the heap from deletes that haven’t escalated, or forwarded records — issues specific to heaps.

Depending on how the table is most often queried, a clustered index might make everything faster.

But not so fast, Kendra

Since this database is created by a commercial vendor, there’s more to think about. We can’t just spring into action.

I wrote a little bit about these types of databases here:

The first thing I would identify is around item #2 in that article: what is the situation with the vendor, what is the support agreement, and how do you work with it? If you go changing around things without first figuring out what that situation is, you might improve performance in the short term, but end up facing a big old problem the next time an upgrade comes out for the software, or end up unsupported if something goes wrong.

It’s a bummer that you can’t just get right to tuning, but I’ve known folks to get really burned by it before.

I have also known cases where folks who explained the problem to the vendor were happily surprised that the vendor had a fix for that situation. (Maybe they have an alternate set of indexes they give to clients at a certain scale, or other changes.)

So it’s worth investigating first with the vendor. Depending on how things turn out there, I would be interested in looking at a test environment for the indexing question, and personally I would test out a clustered index on one of those unique keys.

Posted on

A Case of Inconsistent Wait Stats and Query Duration

You know how they say “don’t sweat the small stuff”?

That’s good advice for lots of things, but when it comes to query tuning – I WANT TO KNOW MORE when something is inconsistent. I just can’t let it go. I go total X-Files: The Truth Is Out There.

The bright side of this habit is that it makes finding blog topics fairly easy.

I was measuring query performance at different degrees of parallelism

I set up a script to measure duration and wait statistics for a few queries. It followed this flow:

  • Set a variable with the “starter” degree of parallelism (DOP)
  • Begin a loop
    • Clean up anything from the last round
    • Record start time and snapshot of waits for the session from sys.dm_exec_session_wait_stats
    • Run the test query at given DOP (using Dynamic SQL)
    • Record end time and snapshot of waits for the session from sys.dm_exec_session_wait_stats
    • Decrement the DOP

I was gathering the data to graph and show query durations along with the amount of CXPACKET and CXCONSUMER waits at different DOPs.

The first weird thing: inconsistent wait stats

Whenever I can, I run a test more than once. I was graphing my wait statistics, and I noticed that on different executions of the procedure, my parallelism waits varied.

Varied more than a small amount.

Luckily for me, I read the release notes for Cumulative Updates for SQL Server most of the time, and after noticing the variation I remembered FIX: CXPACKET and CXCONSUMER wait types show inconsistent results for some parallel query plans in SQL Server 2017.

I checked, and sure enough, my little cloud VM was on SQL Server 2017 CU3, and that fix is in CU4.

I patched to CU5 (latest and greatest for my little test instance, ya know), redid all my testing twice, and admired my now-more-consistent charts.

The second weird thing: inconsistent query duration

I was getting consistent results for query duration when I ran the query in the script above, which started at a high degree of parallelism, and then went to low.

But I found that for one of my test queries, when I took the query and ran it in my own session to look at Query Time Stats in the execution plan, it took much longer.

For example, at MAXDOP 13:

  • Duration was consistently 55-65 seconds in the looping script
  • Running it in a session by itself, I was seeing durations of 2 minutes to 2 minutes 30 seconds

More than twice as long.

Why was it so much slower when I ran it in another session?

It wasn’t about cache/buffer pool reuse

I had plenty of memory on this instance, and I’d made sure to set ‘max server memory (mb)’ plenty high, but could something be causing me to do physical reads when I ran the query in my own session?

This made me realize that my test script wasn’t quite fair to the DOP that ran first — it might have to do physical reads where following queries made use of data in memory. This is a private test instance, so I changed the script to run everything with cold cache, by dropping clean buffers between runs.

I wasn’t getting a different query execution plan

I was getting the same plan with the same cost both in my “slow session” and in the script.


I was running everything from an SSMS instance on my laptop against a SQL Server on a VM in the Azure Cloud. Could I be getting some weird kind of latency on one session?

Nope. And my query wasn’t even returning a result set to my SSMS window — it was putting the results into a table in the cloud.

Was it the Dynamic SQL?

Could something about the Dynamic SQL be making it faster? Or had I made some sort of typo and the Dynamic SQL query was a little bit different in a way that made it faster?

Nope, it really was the same query, and the same plan. When I copied the longer script and adjusted the variables to only run for DOP 13 in my “slow” session, it was also slow.


I sometimes use these to measure queries, but in this case I wasn’t using them in either session.

Was it having Actual Execution Plans on?

Yes, it was.

This query was a reminder of the observer effect: watching something can change its behavior. In this case, “watching” the query by enabling actual execution plans made it take twice the duration.

I had actual plans enabled in my “slow” session, and I didn’t have them on in the session where I was measuring duration and wait statistics in the loop. (If you’ve ever accidentally left actual plans enabled when you’re running TSQL in a loop, you know how much SSMS doesn’t love that.)

I tested this a few different ways, including in another instance of SSMS local to the VM itself, and sure enough – actual plan drags this puppy down, although it’s a relatively simple query.

Was it only graphical plans?

I disabled graphical execution plans in Management Studio, then tested a few varieties of outputting actual plan info:

  • SET STATISTICS XML ON – Actual plan information output as XML in a column named “Microsoft SQL Server 2005 XML Showplan”
  • SET STATISTICS PROFILE ON – Actual plan information output as text across a variety of columns (screenshot below)

I tested these independently. Both of these had the same impact on the query as using the graphic execution plan – the query took twice as long.

I did feel kind of fancy looking at actual plan info in text format, though. We all know that hackers ALWAYS use text interfaces.

Kinda cool how execution count pops out in this view

What about ‘Live Query Statistics’?

I expected this to take a full three minutes, but it only took a bit longer than the ‘Actual Plan’ run in this case: 2 minutes 5 seconds.

Does this happen against different instances? At different DOPs?

I ran the same query against the same data on a local test VM on my MacBook Pro, which has 4 cores.

The duration at MAXDOP 4 on the MacBook Pro:

  • No actual execution plans = 2:41
  • Actual execution plans enabled = 2:50

Hmm, the discrepancy is much smaller on the small instance.

So I retested at MAXDOP 4 on the cloud VM. Note that this gets a different plan – similar shapes, but different costs (much more memory on that instance).

  • No actual execution plans =  2:20
  • Actual execution plans enabled = 3:07

To recap what I mentioned above, MAXDOP 13 on the cloud VM:

  • No actual execution plans =  0:55
  • Actual execution plans enabled = 2:00

Just for fun, MAXDOP 16 on the cloud VM (just in case you thought the issue was 13 being unlucky)

  • No actual execution plans =  1:00
  • Actual execution plans enabled = 2:14

Did I mention I’m compulsive about investigating this? MAXDOP 20 on the cloud VM (all its cores):

  • No actual execution plans =  1:12
  • Actual execution plans enabled = 2:10

Innnnnteresting. The impact of having actual plans enabled for this query is more pronounced on the cloud VM. The skew is greater at a higher DOP, although it’s not linear.

Actual plans are awesome, but make sure they don’t confuse your tuning process by secretly skewing your execution times!

There’s always overhead to using an actual plan.

Frequently it’s minimal, but sometimes it’s really significant – and the amount it varies may differ on different hardware, and at different degrees of parallelism.