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.

Posted on

Register for My Free Session on Execution Plan Forcing

I am excited to be giving a free online session as part of the Idera Live Virtual Conference, 2018!

Here are the details on my session:

To Force Plans, or Not to Force Plans, That Is the Question

Wed, May 16, 2018 – 9AM Pacific / Noon Eastern

We have new ways to force execution plans in SQL Server: in SQL Server 2017, you can even let SQL Server temporarily force query plans for you and test if it works well!

In this session, you’ll learn:

  • How forcing plans with Query Store compares with the older technology of Plan Guides
  • How to tell when a plan has been forced
  • What “morally equivalent plans” are (and why they’re a good thing)
  • How to see if a query has a high variation in performance
  • How to decide, “Should I force this plan?” and “Should I let SQL Server force plans for me?”

Register for this free session here

But wait, there’s more

I’m not the only one giving cool sessions that day — check out more free sessions at the Idera Live Virtual Conference.

Hope to see you there!

Posted on

Dear SQL DBA: I Want to Work for Myself (video with captions and transcript)

Our question this week comes from an IT pro who would like to be self-employed. In this episode, I talk about three big things you need to think about to start working for yourself, and two possible paths to get there.

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 the Dear SQL DBA podcast and YouTube show. I’m Kendra Little with, and on today’s podcast, our topic is Dear SQL DBA: I Want To Work For Myself.

For the Dear SQL DBA podcast, I take questions from folks, database administrators and developers usually, and they give me all sorts of questions.

This one is someone who said,

I currently work for a larger corporation as a consultant. There are lots and lots of processes, and all of the tickets I work on are emergencies and I’m getting really burnt out. “I have an IT focus. Is it possible to get a job like a freelance developer but with an IT focus?

This is a question that, it’s a great question, and more and more freelancers find different ways to do their jobs but freelancing isn’t easy. I will mostly be focusing on working for yourself in The United States today. There’s different people who do this around the world and with different healthcare systems and different tax laws, it does vary.

My experience with this is all in The United States, and in The United States, freelancing isn’t easy. I don’t get the impression that it’s super easy anywhere but we do have some of our specific challenges.

Three big questions

One of the big things to look at first when you’re thinking about starting to work for yourself is three big questions.

What are the products or services I’m going to sell? When I say products here, I really mean what am I selling? They may be all service based. There may be some product I actually deliver something, but what am I going to sell? Who am I going to sell it to and then how will I go about running the business? So, we’re going to dig into each of these three things because before you get started, you need to know what you want to do with all of these.

What do I want to sell?

Will I build some tools that I will need to do my services or am I going to sell these tools to people? What types of processes do I need to build my services? Even for an initial consulting job, what kinds of scripts do I want to use? I can’t just use anyone’s code from the internet. I need to look at their licensing.

What kind of tools do I want to use for the product that I’m offering, how long is it going to take me to set up that initial offering? What do these products cost?

And are there products I can sell that once I build them first, they will continue to make money for me without me actively being involved in them? This isn’t always the case but sometimes, there are products you can build where for an initial effort, those products can continue to generate money with relatively low maintenance or input on your part, where all of the work is done by a tool you built, perhaps. It could be a variety of things but you’re not going to have to be personally delivering the product. Sometimes that’ll be a yes, sometimes that’ll be a no, but you really just want to know the answers for these.

Who are my customers?

And then in terms of who you’re going to sell them to, there’s different ways to find your customers.

Some businesses run on customers that find them and come to them and maybe this works based on a blog, or maybe there are free tools that are given out. For instance, these days I write a free quiz every week. I have quizzes on my website, I also blog on my business website, I give away some courses for free.

Doing these things so that when people are having a problem and they search in a search engine on the internet, you want to have them finding blog posts by you, or finding tools that you have if you want to have this type of relationship where the customer starts using your tools and then there’s perhaps something else that they can upgrade to that costs money with you. It takes a while to build up a following so that enough people are finding you, right? We have to start and we have to build this up.

There are businesses, however, where it’s marketing is done by, okay, we’re going to identify the customers, we’re going to purchase a mailing list from someone or we’re going to purchase a list of potential customers from someone and then we are going to reach out to these people. Course, we have to figure out how are we going to identify with them, who is going to be going out and engaging with them, how much time is that going to take?

And then we design, after we figure out, okay, who are our customers and how are we going to connect with them, we have to design a sales pipeline. Because we want to qualify our sales prospects. Depending on what you’re selling them, and in fact, I would say in general, there’s very few businesses where you want to sell your product to just anyone.

You want to sell your product to people who are going to find it useful. Because you know, selling something to someone that’s totally useless for them is going to get you bad reviews and unhappy customers. So, you need to have a process for your customers that you’ve identified to help say, “Is this right for you?” And we’re going to bring you down through the pipeline.

You want to think about when you’re working for yourself– one of the inherent problems with working for yourself, especially if you’re doing something like consulting is are you trying to land really large projects, or are you trying to work on a lot of different projects? How many customers do you want to have? If you want to do the kind of business where you’re working really with one or two customers at a time, the problem with running a company like that is that you become really, really dependent upon those customers, and this isn’t just tiny companies.

I worked for a software company with 100 people at one point where most of our revenue came from one really large customer. And when that customer says jump, it’s really hard to say it’s not a good idea to jump right now, because if they leave, there goes a huge amount of your revenue stream. It can be done, it puts you into different situations in terms of your relationship with that customer. And these are all things that we’re talking about.

You can change these over the life of your business. What we’re talking about here is saying for the initial offering that I want to set up, what do I want to do?

How do I run the business?

There’s also a lot to think about just as to what business do I want to form? Am I going to have partners? How am I going to incorporate this legally? In The United States, you can do this as an LLC, you can do this as an S corp, there’s a lot of different ways. Put some time into researching what these different things mean, and figuring out what you want to do.

You also have to decide how much money you want to invest to get started. Because especially if this is something you’re going to jump into full-time without a customer pipeline, you’re going to burn through money.

You’re not going to have a lot of income coming in unless you get really, really lucky. So, you have to figure out how much am I going to invest and how much risk will I tolerate? Even in small business where things are going well, and you do have good income for a while, there can be these dry spells. It may be based on time of year, depending on the industry you’re using, it may just be a coincidence. I’ve actually seen in the consulting business that there are seasonal dips in consulting where people are like, “Oh, well, it’s summer vacation, “and everybody’s going out with their kids, “we don’t want to do a lot of research into changing things. “we’re just keeping the staff around “just to keep things steady.” If you’re providing more sustaining, regular services for people, that might actually be your higher period if people are out of the office and they want you there to help sustain things.

So, depending on what your industry is, there are going to be periods where things wax and wane and you do need to work that into your risk calculation. When you’re working for yourself, you can’t count on always getting the same paycheck for the right amount coming in. Do you want to have people who contract as your employees? Do you want to have employees? Especially in the initial years of your business, it’s a big decision, and then just, how am I going to run things?

How will I keep track of who my customers are, their contact details, how I have engaged with them, how likely I think they are to buy a service? There’s tools you can use for this, you may want to start using something simple, and then grow, depending on what kind of potential customers you’re starting with, you may need a more complex tool if you actually already have a good base of prospects that maybe you’ve purchased from someone or have some way to access. Need to find ways to invoice people and to track our expenses, very important. We need to do bookkeeping and accounting, and there’s just a lot of mundane little things that come with doing your own thing. Like not only invoicing people, but following up and saying, “Hey, I need to get paid.”

So, there’s lots and lots of different things to start with just on running the business side. How do I get to this path though, right? I need to think about my products, I need to think about my customers, I need to think about how to run the business.

Path #1: The Expert

But okay, how do I even get the ability to attract the customers? There’s a couple different paths that I’ve seen folks take to this and I’m going to outline two of them.

We have a question that fits right in with the first path. The question is, “How do you stand out from the other more experienced consulting folks?”

So, let’s talk about this in the path to the expert. One way to become a successful freelancer is to figure out: these are the problems I want to solve, these are the products I want to sell, and my customers will want to buy them because they want to do something faster, or there’s something they can’t do that I will enable them to do. Or they won’t have to have as, they’ll be able to save costs in some way. Once you identify what this really is, you can work towards becoming an expert and really focus on, especially these things that you want to offer.

Now, you’re not going to dive in with this path. Folks following this path tend to say, okay, I want to work for myself.

One of the first steps to doing that is I need to learn how you’re going to run this business. I need to learn how to attract these customers so I’m going to find a small to medium sized business doing something similar to what I want to do, at least in that industry, and I want to work there for a while. I want to learn from them, how they are running and growing that business, and what works and what doesn’t, and while I work there, I am going to build up a personal blog and a lot of my own intellectual property, and when I’m doing this on my own time, this is not part of the company, this thing I’m doing on my own time, when I’m doing this, I’m really going to focus about building up information that is related to the services or products that I plan to offer.

That intellectual property that you’re building up, speaking at conferences, doing that blog, is what is going to set you out once you eventually strike, it’s going to set you apart rather, once you eventually strike out upon your own as an expert and it’s really that focus on exactly what you offer and your approach to it that is going to set you apart. So, you want to have some personality in there and you want, maybe this is going to give you a, depending on what you want to deliver to people, maybe this is going to give you a focus on, okay, I’ll include some short video clips so if I want to do consulting, so they get a sense of what it’s like to talk to me, for example.

Or, if you’re going to offer something more automated, maybe you want to start building small example services or building small examples of the types of things that you want to do.

There are things we have to really look out for in this path and you have to be careful though. When you’re working at a small to medium firm, I mean most of the times, there’s going to be an agreement that says, “Hey, if you leave us, you can’t take our customers with you.” I’m not going to get into arguing about legal enforceability. You really don’t want to be stealing customers anyway. I’m not saying to get a job at one of these small businesses so that when you go work on your own, you can take the clients, because usually, that is going to light a giant bridge on fire and cause a ton of problems.

I would be pretty open in the interview with a company like this saying, I would perhaps, because at this point, when you’re getting the job for the company, you don’t know. I mean, you may find that you really like working at this little company and you’re like, “I don’t need to go work for myself, this is actually so rewarding, I don’t want to deal with all that accounting and bookkeeping, this is better.”

But I would be open if you are thinking about maybe doing something yourself someday. I would be actually open about that and honest about that because in your relationship with that employer, you don’t want to have a big giant secret that you’re hiding, in my opinion.

Also, that opens up the door to have a conversation with them that says, “If I’m writing blog posts on my own time, who do those belong to?” You want it to be very clear if you’re on the side building up a presence for yourself, you want it to be very, very clear that you own that intellectual property.

Same thing with talks that you write for conferences. You want to know exactly which ones you own and exactly which ones your employer owns. Because if there’s a point where you do set out on your own and you start using these materials, you really don’t want to get hit with legal problems at that point when you’re just starting out your own company.

So, I would negotiate for is it okay for me to own this intellectual property that I write, who owns that?

I would raise the question, and make sure you clearly know the answer to, is it okay for me to moonlight on something? Can I take on clients on my own in hours outside of working hours that are totally separate, can I do work on my own and get paid for that? This includes even, like if you do say a pre-conference session at a conference, these are paid engagements, and so, knowing that even helps, yes, you’re taking on outside employment, is that something that they don’t allow? Because if so, you may not want to work for them if you really are setting the stage to go work on your own. So that’s Path A, or Path 1. I should stick with the numbering system.

Path #2: The Side Hustle

The second path is the side hustle because on Path 1 we were really, we’re doing some blogging on our spare time, but we weren’t really doing moonlighting in our spare time. And maybe it comes up when it comes to conference talk, but we really working a second job. The second path to this isn’t really becoming an expert. The second path for this is working a lot to make it happen, and by a lot, I do mean a lot, there are folks who do this.

Starting a second business in your spare time and scoping your products to fit it to make sure that okay, I am working multiple jobs. If my customer needs me and I’m working my main job, I need to have a way that that’s okay, where they don’t need an immediate response or maybe I have some job where I can stop working it for a while, most IT jobs aren’t like that, they’re full-time jobs, right? Usually, we commit to these certain hours but you need to find a way to make sure that for your side gig that okay, I’m only available during a certain time, or there’s a long enough response time, a long enough service level agreement for you to not be available right away, typically.

So, you start off doing something nights and weekends, you scope your products to fit with that, and you use that experience to refine your product and figure out what works really well, to build up testimonials from your customers, to get all your processes set, to get your accounting set, ’cause you’re doing this in your spare time and as you’re scaling up your business, you’re saying, I’m going to get to certain point where I can leave that original gig and do this side gig full-time.

Again with this one, you really have to make sure that you’re not secretly doing that work ’cause you’re employer is going to find out. The more successful your side gig gets, the emplo– But also, you need to be able to be open about this. You need to be able to have a with and say, hey, you know, this is what I do and this is how I do it well to be open with not only your existing customers but to have a way to communicate with potential customers.

The biggest problem with this is also that it’s really hard, it’s really time consuming because we’re dependent upon the revenue from our initial job. While we’re building up the new one, we are literally rebuilding our career as we are working it.

What about contracting?

I’ve had some folks ask me also what about contracting, can contracting be a stepping stone to make this process easier? I personally have been a contractor before. I was a contractor at Microsoft and these terms for folks outside The United States, these terms might work a little bit differently. In The United States, typically, a contractor works for an agency, the agency has a bunch of clients and they hire on contractors for short to medium term engagements, the contracting company takes a cut of your income and typically in The United States, you earn an hourly rate, you may or may not get any benefits for this, so you may still be buying your own health insurance separate from that.

The thing about my contracting gig is I actually, for me, it ended up in me getting a full-time job at Microsoft. At that time in my career, what I really wanted was a “real DBA job.” I wanted to be a full-time DBA working on production systems. I’d essentially been a junior DBA and only gotten to work in non-production systems up to that point. And I got this contract gig, and I also was like, I wasn’t sure what it would be like to work at Microsoft. I wasn’t sure if I’d like it, so it was kind of, you know, like a good short term opportunity to do that, and I could sustain risk at that point. Like if it didn’t work out, I could pick up another contract, I was okay with that, and my only dependent was a giant rabbit at that time. So, relatively low risk. It ended up getting a full-time job at Microsoft.

Looking back on it, it would not have been a good job in terms of going out to work on my own because I hadn’t negotiated a great rate. And a lot of that was I didn’t even know how to negotiate, and not knowing how to negotiate is a hindrance to when you’re working for yourself. I just wasn’t ready at that time. Also, I worked a lot of hours for that job and I needed to be available to work an on-call schedule that could be very erratic. And if I had tried to do that job and another job at the same time, I would have gotten burnt out. So, it just would not, that contracting job wouldn’t have worked for that.

If you can do contracting in a way where you can negotiate a really, a good rate, that allows you to, within a limited scoping hours of work, say okay, I’m earning enough here that I can do a side hustle, or I’m earning enough here that I have time to blog. I have time to build up my expertise, and this contracting job allows me to go on the expert path, you can do this.

But you really want to make sure that it is the type of job that isn’t going to burn you out. Then here’s the other thing, with contracting jobs, the contract can come to end at any time. The reason this is attractive to employers is you’re not a full-time employee. So, if they run out of budget, your gone. So, the biggest problem I think with contracting is you get the sort of uncertainty of what is my paycheck going to be next month? Without the benefits of working for yourself. You might be able to fit it in with this, but I don’t think it’s a magical way to speed up the process if that helps, it’s another job, but it’s a job with some uncertainty built into it. So, it can be a little tougher than having a more predictable full-time job if you’re trying to do the side hustle route.

I know this sounds hard and may sound a little bit discouraging. But it is hard. There’s a reason that not everyone is doing this.

There’s a reason that you see people strike out on their own and then take a job at a full-time company again. It doesn’t always work well, and you will, when running your own business, you will have to go through a process of saying, “Oh, this didn’t work out. I wasn’t charging enough money for this / I need to change my product or I need to raise my rates,” or, “I’ve lost clients and I need “to figure out a way to find new ones,” or, “I goofed up at this client. I need to see if I can save this relationship with my client and they may leave me.”

Goofing up may or may not be your fault. Goofing up can be a communication thing. It could have been a problem with a contract, it could have been in part someone else’s fault who works with the client, right? But things are going to happen and mistakes are going to be made, that’s just life.

So, you constantly have to be really persistent when working for yourself and say, “Okay, how can I make this better?” And “How can I get past this?” So, it’s hard, and there is a path from being really burnt out to being a consultant who works for themselves.

The first thing I would do is address the burnout problem

The problem of I am working for someone else and I am really burnt out, this is not something you want to start a company from, ’cause it’s a hard process.

Starting something really hard isn’t going to solve your burn out. So the first step I think is actually figuring out I need to get into a place where I’m not unhappy, because either becoming an expert or doing the side hustle, having your main job making you really stress out and unhappy, not going to help you succeed on this path.

So, you may need to change your job, you may need to change your habits. Exercise, sleep, how you handle stress. You may need to change things in your personal life, you may need to work on your ability to say no to people and to be able to explain to your manager, “Okay, here’s why I can’t work every night, “and here’s what we’re going to do to solve this problem.”

Because when you start your own company, all these things that stress you out, of all of my customers think their issue is number one, or I have to give someone bad news, or we can’t make this deadline because there’s 18 things about this project you didn’t tell me about, things you will have to constantly deal with, things that are out of your control causing you problems when you work for yourself.

And yes, as someone who works for yourself, you do have the ability to say, “Okay, I don’t want to do this work,” and we’re going to end this relationship with my customer, but it comes with a very clear financial penalty and you have to be able to work through that and you have to be able to work through the things where things don’t work out and we have to start over again without it causing stress and burnout.

The truth is that being your own boss can mean having a terrible boss

You know, my boss doesn’t go away on the weekend. She’s there with me in the morning, she’s there with me late at night, and sometimes, she just won’t shut up about all the stuff she wants me to do and she wants to know why I haven’t gotten it done yet. That is the truth about working for yourself.

So, this thing of being burnt out really is important to solve in order to actually enjoy having your own company. And it may not be something you can solve immediately but it is really worth figuring that out and then, from a more healthy mental space of not being completely burnt out, figure out do I want to go more along this expert route and how am I going to find a small to medium sized company doing something similar to what I’m doing to work at and what is my plan to start trying to find these jobs, start trying to figure out what I can do to work for these folks?

Or, do I want to do more of the side hustle gig and what is my plan to make that actually work in my life so that I can still sleep? So, I can still leave the house occasionally, and so, that I can really not get burnt out again. Really, really tough to figure it out but you absolutely can figure it out.

PSA: Changes in webcasts

I’ve got another question that I’m going to hit up. Before I hit up the question, I do want to just say there have been some changes going on in terms of the webcast that I offer. I no longer offer free signups for my technical webcast. I’m going subscribers only on my technical webcast.

But, there will be lots more Dear SQL DBA podcast episodes. Those are now free and open to everyone. So, in the next one, we’ll be talking about what the deal is with certifications in the next episode, also, do SQL DBAs need college degrees, and lots more stuff. So, I would love it if you can join me for any of the events going forward, but yeah, i have been changing things up.

Choosing your area of focus

Like I said, you have to constantly reinvent things in order to keep things going. So, the question is, “What areas of SQL Server “do you think deserve attention nowadays “and therefore can be an area of expertise for exploration?” That’s a great question and really interesting things when it comes to SQL Server these days. There’s a lot, if you’re at all interested in cloud technologies, there’s a lot of momentum going still for what parts of our applications can we move to the clouds, to the cloud, it really is clouds because we may want to use more than one cloud, we may want to use more than one service, but can we move to the cloud, how much can it save us on cost? And things change rapidly in the cloud. So, becoming an expert on things as they emerge can be worthwhile.

There’s always a risk though that you’re going to learn something new with some new cloud technology that no one’s going to eventually adopt, maybe because it’s just too expensive, right? So, with all of these things, I do think that, yeah, if you’re looking at a new area to specialize in right now, and you want to do consulting and training, the cloud can be really interesting. The cloud is really hard for training though because of the rapid rate that it changes. And I do say if that’s something you’re interested in because I think you really have to like it and be interested in it for it to work. One example of where interest is, and aptitude as well, is really important is things like learning data science.

So yes, learning data science, very, very interesting field, emerging great area for building expertise, but you have to like statistics and math and you have to be willing to kind of stick through it and see where it evolves and learn how what is offered in SQL Server compares to what’s offered elsewhere, right? I know some folks in a college-level course on artificial intelligence who got signed up for this course and, man, apparently it was just a whopper. “This sounds really cool!” And then near the end of the semester, everybody was just like… “This, this is overwhelming!” Like, there’s literally a fire hose pointed at me. So, these areas really are taking off and really are getting a lot of interest, but… Do look at your own… And I don’t love working with statistics, for example, so I will not be going deep down the data science path just because I took a college course on statistics once and I did okay, but I really kind of dreaded doing my homework. It wasn’t something I looked forward to, so, whereas on the other hand, when I first started working with relational databases and queries in SQL Server, I just wanted to work on it all the time.

So, if there’s areas where you’ve got, you know, people say don’t follow your passion. I kind of think that’s BS and I don’t know if it’s really like, “Follow your passion,” but I do think like if there’s things that you find really rewarding to do, if there’s some way you can figure out how to make money at it, even if it’s uncool, even if, you know, it’s not the newest greatest thing, see if you can make money doing that thing. Because you really like working with it and it’s your life! So, you know, I would consider that first, personally.

I mean, if your passion is underwater basket weaving, there are places you can teach that, it turns out. There are ways to make a living out of that.

So, yeah, part of that I would fit into is definitely aptitude, and I mean aptitude, not just as I’m automatically good at it, but aptitude as I’m interested enough in it to keep wanted to do it when it’s really hard because a lot of these things are really hard and we’ll have to keep at ’em.

It’s really that I like keeping at it and I really want to know it even though it kind of drives me crazy that it’s really hard. I don’t mean aptitude as something that’s just easy.

Thanks for attending or listening to this live podcast session

So thank you, folks, for showing up for this inaugural recorded episode, a live episode of the Dear SQL DBA podcast and thank you for the questions, those are awesome. I hope that you guys have a great day wherever you are and I will be back on April 18th for the next recorded episode.

I personally am about to head to Hawaii with a bunch of my closest girlfriends, so I’m abandoning the homestead and Jeremiah will stay here while I head off to have some sun and fun with 16 of my favorite women friends, and I will then be back later to do lots more stuff with you guys. So thanks a bunch, have a great day, and I’ll see you soon. Thanks, guys!

Posted on

Adaptive Queries in Standard Edition: Interleaved Exec for Multi-Statement TVFs

It’s tough to keep track of which features work in each version of SQL Server, and which Editions support them.

My memory told me that the new Adaptive Joins feature in SQL Server 2017 was Enterprise Edition only…  and that’s correct, but I didn’t realize that the fancy new feature to make Multi-Statement TVFs smarter has much wider licensing.

Interleaved Execution for Multi-Statement Table Valued Functions works in Enterprise, Standard, Web, and Express Editions

I discovered this by accident when I was doing some testing on my Standard Edition instance. I was looking at an execution plan, and I checked the properties of a TVF and …

Function Beaver couldn’t be more excited about this

First, I double-checked and made sure I really was connected to my Standard Edition instance. YEP.

Then I checked the SQL Server 2017 Editions and Components chart to see if this was an accident or not

Sure enough, ‘Batch Mode Adaptive Joins’ and ‘Batch Mode Memory Grant Feedback’ are Enterprise Edition features.

But ‘Interleaved Execution for Multi-Statement Table Valued Functions’ is available in all the editions.

What does ‘Interleaved Execution for Multi-Statement TVFs’ do for you?

Multi-statement table valued functions are difficult to optimize as part of a query: they run multiple statements, so how the heck do you estimate how many rows are going to come out of that?

This new feature lets the optimizer stop and take a peek!

When the optimizer finds a multi-statement TVF that qualifies*, it pauses the optimization process (badass!), and then goes briefly into the execution phase.

(This is the interleaving part.) It can run through that little part of the plan to get a good estimate, instead of making a blind guess.

Good estimates means a much better chance that you’ll get the right joins, memory grant, and qualify for parallelism when needed.

That usually adds up to faster query execution.

*Which Multi-Statement TVFs qualify?

Not all multi-statement TVFs are created equal. If your TVF is part of a data modification, or if your TVF is inside a CROSS APPLY, it doesn’t qualify for this magic — at least not in SQL Server 2017. That may change in the future, this is just the V1.

How do I turn this on?

If you’re on SQL Server 2017, your database compatibility level controls whether or not this feature is available.

If your compat mode is 140, your multi-statement table valued functions are very likely to speed up.

I say ‘very likely’ because there can be some edge cases where better estimates lead to worse performance.

Want to learn more?

If you agree with Function Beaver that this is the best thing since CREATE FUNCTION…