- Host: Steve Stedman / Shannon Lindsay
- Recording Date: January 28, 2025
- Topic: Ace your next SQL Server DBA interview
Stedman SQL Podcast Sn 2 Ep. 5 Ace that next interview
In this week’s episode of the SQL Server Podcast, we dive into DBA interview questions—what to expect, how to prepare, and how to stand out! Whether you’re applying for a SQL Server DBA role or hiring for one, we break down common technical, performance tuning, and disaster recovery questions that every DBA should know.
Podcast Transcript
Steve Stedman 00:16
Well, hello everyone, and welcome to the Stedman solutions SQL Server podcast. We’re on season two, episode number five, and I’m your host, Steve Stedman, and today, Shannon Lindsay will be joining me as my co host. Welcome Shannon.
Shannon Lindsay 00:30
Thank you.
Steve Stedman 00:32
News on Stedman solutions, February, we have our performance tuning promotions month. There’s a number of things that we have access to. If you go to the short URL of HTTPS, stedman.us/feb, Just Feb, there’s several links there to truly sign up for our free performance tuning email course, which has about one email a week for a whole year on performance tuning information for you to learn on. Or you can purchase one of our online performance tuning courses, or consider a performance tuning assessment with remediation, where we come in and work in your server and get it all in shape for you. So that’s at stedman.us/feb so I’d like to welcome everyone, and this week’s topic is SQL Server interview questions and answers. All of us, sometime throughout our career, have to end up sitting in a job interview, whether a consultant talking to a client, or whether you’re a candidate looking to fill that next DBA or database developer role, one of those things that most of us don’t do it all the time, fortunately, and it can take some practice to get it right. So we’re going to go through some practice interview questions here, but if you want more of this, you can check out our SQL Server Interview Questions and Answers class prepare for your next interview. It’s good for both the hiring manager and the candidate, and for a short time through the end of February, I’m offering a 50% off coupon code if you go to the link stedman.us/interview there is a coupon code of interview, Feb 2025 interview, Feb 2025 is the coupon code, and that’ll get you 50% off that interview question and answers class, just for watching this podcast. So as we go through this for each of the questions, Shannon is going to be the hiring manager, and I’m going to be the candidate looking for a SQL Server job. I’m going to follow the format that I’ve used in the class, which is three answers, the basic answer, which sort of, sort of shows that you know enough to know that that might be a SQL Server term, the advanced answer, that means you’ve worked with it a little bit, and the pro answer, which probably means you’ve dealt with it for quite amount of time, and you know that you know it inside and out. So going into this right now, I would never say this in an interview, but I’m a bit nervous. I hope I get the job. So over to you. Shannon,
Shannon Lindsay 03:05
Alright. First question, welcome to your interview process. What is the difference between union and union all and in what scenario might you use each
Steve Stedman 03:19
Oh, wow. So this is one I always like to talk as we go through it, not just to directly answer the question, but to kind of talk about history and stories on it. But union and union all are operators in T SQL. They’re used to combine the results of two or more select queries. So when you do that, though, the queries have to return the exact same columns and format of columns, and what it does is it takes everything from, let’s say, one query, and combines that entire result set with another into one big result set. Now imagine this sort of like if you had two spreadsheets in Excel, and you copy the entire contents of one and pasted it in below the entire contents of the other one. That’s That’s how the union and union all work. However, the difference is the union all says, just take everything from both result sets and merge it into one result set. And if there are duplicates, they’ll they’ll still be duplicates after the union all is done. The straight union, without the all it goes through and illumine eliminates duplicates. So the basic answer around that is it combines two queries result sets. One of them removes the duplicates, and the union all does not and scenarios you might use this is if you’re trying to pull data from two or three or four different types of queries into one result set in order to present it back to a user. That’s my basic answer. Now we’ll roll into the advanced answer, which might show you know a little bit more than just what they are. So the one thing you want to keep in mind is that the union all is simply grabbing the results of both sets. And combining them into one set. It’s faster than the union, because the union all doesn’t have to do the work to go through and figure out what are the duplicates and should those duplicate values be removed. And when it’s looking at duplicates, it’s basically looking at the entire row. So if your union, if your queries are returning back 50 columns, the union has to go through and compare all 50 of those to look for duplicates, where the union all just brings it all together and displays the results back. Yeah, that’s sort of the medium advanced answer there. And next pro answer, again, the union all is faster because it doesn’t have to remove the duplicates. The union has to remove the duplicates. But in order to remove the duplicates, SQL Server first has to sort the entire result set for the two or more queries that are being combined, and then remove the duplicates when they’re being combined. And it’s the sorting and the reordering of all that that really takes the time. On a very small set of data, you may not notice the difference between the union and the union hall, but on larger sets of data, this can be a huge difference when it’s doing big sorts. An example of where I’ve seen that is like in a development environment where you have a very small development database. You’re testing your code, everything works great. You push it into production, and all of a sudden all the tables are 1000 times bigger than what you hadn’t felt in development. And suddenly things really slow down, not just because they’re bigger, but because that the union is doing. The union is removing the duplicates in the union. All can change the performance there quite a bit, but you have to be aware. I mean, there may be cases where you don’t really care about the duplicates, or you’re going to do a distinct or a sword later. Well, you can let the duplicates go through. Or if it’s just, I mean, it might be that you need the duplicates and in which case you use the union all, or it might be that you have to get rid of the duplicates when you do it, in which case you go with the union. Man. So very powerful, very handy things to use, and they’re ones I don’t use that often, but when the case comes up where you really need them, they can be handy. Oh, one thing I should throw in as well. The very beginning, I said, it combines two or more select queries. Everything I just described is all about taking results from query one and query two and combining them. But you can have five or six or 10 unions altogether where you’re taking 10 different queries and combining the results of all of them. In that case, each one is going to get slower and slower as the data set gets bigger and bigger going through that union process.
Shannon Lindsay 07:35
What are partition tables and why might you use them in SQL Server?
Steve Stedman 07:44
Okay, so for the topic of partition tables, what I’m talking about is the actual SQL Server table partitioning. And there’s other ways you can manually do your own partitioning, but I’m talking about the SQL Server way of doing it, and this is where you can set up tables that have a partitioning scheme or a partial partitioning function associated with them. And instead of having, like, one big table that has, I don’t know, millions and millions of rows or years and years of data, and it splits it up into conceptually smaller tables that all have subsets of data. So it splits them into manageable pieces so that the storage changes on how it’s laid out, but the table still appears as one table when you’re looking inside a SQL Server. Now each piece of the partition is stored separately, and it can be accessed and managed collectively or individually, and it’s a table level event, and where you might want to use them is, let’s say you’ve got an orders table, and you’ve got 25 years of orders in that table, but most of the time you’re querying the current your year, or maybe the last year worth of data. Well, you may set up a partition table, or partition that table so that it’s partitioned by year. So if you’re filtering based off of a specific year, you’re going to eliminate those other partitions when your query runs. So it’s only querying a subset of the entire table when that happens. So that’s the basic answer. Moving into the advanced answer. Well, the thing that this really can help with quite a bit is performance. And when you have really large tables, well, usually it gets slower to query them, so you end up putting a lot of indexes on them, or a couple of really big indexes to cover these big tables. Well, the bigger the table the bigger the index, and with partitioning, it basically breaks the storage up into a bunch of smaller pieces and allows you to have lots and lots of smaller indexes, which, given the right query details, can be faster than going through one really big index. So some of the reasons around. This would be performance optimization. You’re going to be able to get faster queries if you’re able to do the partition elimination by having the right where clause when you’re joining or filtering on it. It makes your data management more efficient. Partitioning makes it easier to manage and maintain those large data sets. You’ve got a table where the index rebuilding takes forever, or statistics updates take forever. Well, splitting it up with partitioning may help with that. Because you’re doing indexes or statistics on a bunch of smaller pieces rather than one really big piece, you’ve also got high availability and disaster recovery scenarios. I mean, each of the partitions can be spread across different file groups, and if you had to do a restore, well, you could do a File group level restore rather than a full database level restore, depending on where you had issues. And then additionally, you can also do load balancing with this. What I mean by that is that if you have one big data file, or even if you have multiple data files, and you split them out across different disks or drives, well you don’t really have a lot of great control over what’s going where, when you do that, but with partitions, you can set it up and put specific partitions on specific data files and shift load off of one drive or one data file onto another driver data file for healthy performance. Okay, that’s my advanced answer. The Pro answer would be talking about giving examples and going in further depth around like, database of records, community, like I mentioned earlier, but going over like 25 years, and partition the table to ensure that your faster queries are running against recent data. And if you’re, for instance, let’s say you’re a manufacturer and your shipping product? Well, probably most of the queries you have to run about getting products out the door and delivered to your customers are recent dates, not going back 20 years. So you can really trim down the amount of work that’s being done by using partitions to only focus on those recent areas. And additionally, you could move the older partitions to slower and less expensive storage if you were constrained on drive speed because they’re not being used so often. Well, maybe slower, less expensive storage might be okay. It’s only when they need to be queried for, like, long term data there that you have to get that slow storage. So it’s a pretty good feature in SQL Server, and it’s one of those things that can make a big performance impact on really, really big tables. I’d never considered on small tables, though, well, unless I expected them to grow much larger,
Shannon Lindsay 12:38
How do you implement error handling in SQL Server stored procedures.
Steve Stedman 12:45
Oh, yeah. So this is a good one. So error handling in SQL Server stored procedures, it’s one of those things that I’ve seen it done really well, and I’ve seen it done really poorly, and I’ve seen people try to do the right thing and mess it up. But basically what you end up doing is you use a try and a catch statement to wrap around your code so that in the event that there’s any exception handled or throw any exception throwing any exception thrown, you can catch that. And when I say I’ve seen it done well and I’ve seen it done poorly, when I’ve seen it done poorly, is when people just catch and bury the exception or hide it so you never see it. The basic answer would be, you just use the try catch around your entire procedure and you catch anything that’s going on there. Now the more advanced answer is, I would use functions like the error underscore number function or the error underscore message function in the catch, so that when an error is encountered, it would display more detail about specifically what error number and error message was hit. I was playing around with this the other day, showing an example of a divide by zero error, wrapped it in a try, catch, and I was able to suppress the error, which meant nobody knew what happened. But by using those error number and error message functions, I was able to display exactly what the problem was, and it made it a lot easier to debug. Okay. Now moving on to the pro answer. Well, before I even think about the try catch, I’m going to also go in and validate all the parameters being passed in to confirm that they are set or they meet the expectations, meaning, like the temp table is being used to verify it actually exists, or if a parameter is being passed in at some user ID. Well, I want to make sure that that’s not zero or null or other things that may be invalid in that scenario, and make sure that they’re actually valid before I try and run queries against them. Now, with the try catch logic, like we talked about earlier, with the error number and the error message, I would want to catch those things and log them to a table, meaning, most of the time, the developer is not the person who’s running the stored procedure when it crashes, but if you log all those results to a table, where you can go back later and find those messages as the developer. Upper and figure out how to fix them. Then there’s some then you can be, I mean, you can be proactive, well, I guess not proactive at that point, and still be reactive because they already occurred. But you can be going out and catch those, maybe before people complain about them, and figure out how to get them fixed if there’s problems. The other thing I would do, I’ve seen, sort of the quick and dirty ways you put at the top of the procedure, your try statement, and at the end of your state, your procedure, the catch statements, and then anything, anywhere in that procedure crashes, you get an error message that said, Yep, the procedure crashed. Well, I would consider doing multiple try catches in larger procedures around different areas. So when it crashes, you kind of get a better idea of exactly where it crashed. I mean, if you’ve got a sort of procedure with 1000 lines in it, which I wouldn’t recommend, it’s not a great practice. But if you do, it would be great to know that it happened in the first 10% of that, or the second 10% or the last 50% rather than just knowing it was somewhere in that 1000 lines of code. So using try, catch, validating parameters, using the error number and error message functions and then logging to a table and considering multiple try catches, that would be my approach.
Shannon Lindsay 16:07
Next interview question, explain how you would configure SQL Server disaster recovery plans?
Steve Stedman 16:18
Wow. This is a good one. I’ve been through a few SQL Server disasters that we’ve had to recover from that may have not been planned very well to begin with, fact that we were able to succeed. In the end, I’ve been through some that we were planned really well too, and it made a big difference. So the first thing I would do is to understand the needs and what type of disaster scenarios you’re planning for. If you’re just planning for anything that could ever happen, well, your disaster recovery plan better have a really big budget, because the anything plan is going to take a lot more work, a lot more redundancy, a lot more things like that than your specific scenario. So let’s take a specific scenario of we want to consider disaster recovery, if an earthquake happens and our primary data center collapses, well, that’s one thing we could do, for instance, if we had backups going to the cloud. And again, remember, I’m on the basic answer here, not the advanced answer, but if we had backups going to the cloud, well, we could set up something like log shipping, go into a cloud based server, or log shipping, where those backups are available to be restored somewhere else. Now that does give you disaster recovery that in the event that something happens that destroys that primary data center, you’re you have a way to go spin up another SQL server somewhere else and get it running. It doesn’t really help you on getting it done quickly. So that’s where we get into the advanced answer. And with the advanced answer, this is where I’m going to talk about things like implementing availability groups. Now, availability groups is a really great way to have two SQL servers that talk to each other and synchronize all their data on every single commit, the data gets synced across two different SQL servers, and that’s great to take care of a server failure or a reboot, but still, if your whole facility is knocked out that has both of those servers in your availability group, you’re still going to be down, so you Need to still have regular backups and track the success of those backups. Now, the pro answer is, I’m going to consider geo redundancy, and that means including cloud solutions or remote locations, depending on what meets your needs with some of our clients, we’ve, we’ve recently, well, not recently, it was about a year ago now, but we’ve implemented log shipping with Bail over to a second data center in a different city. And in fact, we’ve used that when they had a crisis at their primary data center, and we were able to get them up and running very quickly at that second location with geo redundancy. I’m going to consider things like log shipping or availability groups or other options like that, so that we’re able to get the database running somewhere that’s geographically different from where you’re at. Meaning, if there’s some natural disaster, whether it’s a hurricane or floods or earthquake or whatever it may be, that impacts your primary location, you need to have a way to get it up and running at the second location. And then some key things to keep in mind around this are testing, testing, testing, I might say testing a couple more times. But the point is, if you’re not testing it, you don’t know if it’s going to work. And if your first time ever doing a disaster recovery failover to a second location is the real event. Boy, it’s not going to go as smoothly as if you practice it once a quarter or even once every six months. The other thing you want to do is keep monitoring and maintaining it to make sure that, yeah, let’s say we build this out today, but in six months from now, something with your network changes and. And suddenly it’s no longer able to sync. Synchronize the data from your primary location your secondary location. Better make sure you have alerts in place to catch that, so you don’t go a year later and find out that, gee, your DR site hasn’t been sinking for the last six months, and you just had a failure at the primary location, and you’ve got no hope of getting the secondary online because there’s no fresh data there. So really the pro answer is geo redundancy, practice testing, monitoring and maintenance. And it’s one of those things that really just understanding the business and the recovery time and recovery point objectives so that you can come up with the right solution. And for some people, that may be log shipping. For other people, it’s replication. For some it’s availability groups, but just understanding the needs and figuring out what’s going to work with the environment you have in place. Yeah,
Shannon Lindsay 20:53
What’s a feature in SQL Server Management Studio that you use frequently and that may not be well known to others?
Steve Stedman 21:03
One of them I like to use is Control R, and this is when you run a query and you get your result grid down below the query that was just run, but it covers up part of the screen where your query has been running or where your query has been editing. And oftentimes people will click on that slider and move it up or down to give you more room to edit your query. But Control R is just a quick way to show or hide the results panel. That’s one of those. It’s a basic answer. And if you hit Control R and you get rid of the results panel, you can see all your code again. Hit Control R again, it brings it back, and it’s kind of a quick way to flip back and forth there. There’s been times I’ve been doing presentations and at the end of the presentation, somebody comes up and ask me a question, and I’m thinking, Oh, it’s gonna be exciting. They’re gonna ask me something about whatever my presentation was on, but they asked me, no, how did you get that results panel to show and hide the way you did Control R It’s a quick way to show and hide that results panel. And what’s even interesting is, once I I’ve got that down, I knew it all. I mean, I’ve been doing it for years, but it’s so difficult to watch someone else who doesn’t know how to do that, because everything seems like it’s in slow motion when they’re trying to flip back and forth between those panels. The more advanced answer that I would give is the SHIFT ALT arrow keys, or Shift Alt mouse selection, for block selection, which basically when you highlight your cursor and drag and it selects an area, or you use your mouse with the shift button down to select an area. What the SHIFT ALT does is it allows you to block select, meaning you’re going to do column selection and maybe take a chunk out of this middle of a code block, rather than taking everything row by row, like the traditional clipboard selection does, another one that’s in the advanced section there that I consider is registered servers. Registered servers is a way that, if you go to, I think, the View menu and SQL Server Management Studio, and turn on registered servers, it’s a way to define a grouping of a bunch of known servers inside a SQL Server Management Studio. So every time you type it up, or you open it up, you don’t have to type in a new server name to connect to. You can just go to your list and connect to those existing server names you already know about. And then finally, the pro answer, and this is one of those two that I use this all the time is using so let’s say you’ve got a bunch of objects that you want to script out, like you’ve got 50 tables, and you want to create a script out to create scripts for them. You could the old way, right click on each one of those and script table as create to a new query editor window and do the next one and the next one and the next one. And then 50 times going through that, go back to all 50 of those new windows and copy them and paste them all together into one big window or same thing, if you’re scripting out indexes or scripting out jobs. Well, the thing is, that is a massive pain, and I don’t think anyone has enough finger strength and probably can survive without carpal tunnel syndrome if you’re doing that for very long. So the idea is, if you want to script out a bunch of objects, whether they’re tables or indexes or views or whatever it may be, you click in the tree view in Management Studio on that object type, like on the word tables at the top of the section, you hit the f7 key in SSMS, and that brings up the Object Explorer details, which opens up a second panel, which has your entire list of all of your tables. And you can go through and multi select them in there. And then, once you’ve multi selected all the ones you want to script, you can right click and say script as a new into a new query editor window, and you get all of them scripted out, rather than just one by one. And we did a migration recently where we had to move a whole bunch of SQL Server Agent jobs off of one computer to another computer. And you go into SQL Server Agent, hit f7 click on all of you want to script out, right click and say, script them. And it’s easy, way easier than having to do that command. Nearly once for each individual object. Yeah, so that’s my that’s my things that I use that it seems like not are not apparent, not not everyone knows about them. Control R for showing hive the results panel, Shift Alt and arrow, or Shift Alt and mouse, click for block, selection a registered servers. To list out all your SQL servers, you don’t have to be typing the names and every time and the f7 for the Object Explorer, and then the ability to multi select objects and script them out on one script. So
Shannon Lindsay 25:31
Sure that saves some time. What are the considerations for updating stats and SQL Server? And how often would you do that?
Steve Stedman 25:44
Oh, this is a good one. Now, if you were cool enough to have a time machine and go back to like mid 1990s when a lot of the rules that Microsoft seems to have were made up around index rebuilding and statistics maintenance, well, I think I would have a very different answer at that point in time than we do today. But since the mid 90s, when a lot of these rules were kind of made up, or suggestions were made up, a lot of things have changed, and I think that updating statistics is an important part of index maintenance, and it’s something that you want to be doing regularly. And by regularly, I would do it every day if you can, but it that often depends a lot on your system load and different things like that. Okay, that’s the basic answer. Now the advanced answer, this is where I would get into my opinion that updating statistics is generally more important than things like defragmenting indexes and without Well, updated statistics, the SQL Server query optimizer can’t make good decisions about how to access your table. Now what that means is, let’s say you’ve got a table, and you put 1000 rows in it, and that might fit on a few dozen data pages. Well, SQL Server is going to look at that, and they may say, Oh, that’s a small table. It’s really quick. We’re just going to always do a full table scan on it. But then, over time, that table grows and you’ve got 10 million rows in it, your statistics haven’t been updated. SQL Server still thinks it’s a small table, and then it tries to run a query doing a full table scan and finds out, oh, that’s not the right way to do it. So updating the statistics basically helps update SQL Server to know how big, how big specific tables are, the specific layout of data and specific columns indexes, so that it knows, like the density of different types of names or different types of values or different types of dates, so they can make the right decision when you’re querying on whether to do full table scans or seeks, or Index Scans or seeks. So that’s the advanced answer. Pro answer is simply, I would recommend updating statistics every day. A lot of people might have a process today where you’re updating statistics, and it might run for eight or 10 hours, and that’s not always a good thing. So I’d make sure that whatever I was using to update my statistics daily. And I don’t like the SQL Server default maintenance plans for this because they tend to do way too much work, but using a script that will allow for a time limit on how long you’re going to update statistics. So for instance, let’s say we’re going to run it for three hours, and at the end of the three hours, I will stop, and then we’re going to do as much statistics updating in that three hours as we can, and then we’ll pick up tomorrow night with the next batch. Also, I want to make sure that when I’m doing statistics updating, that I’m only updating those tables that have changed. Now, one of the things that the default maintenance plans in SQL Server do is they kind of lead you into Okay, let’s do a rebuild of some indexes, and let’s do a reorg of some indexes, and then let’s and then let’s go update the statistics on all those indexes. Well, after rebuilding tables, the statistics get updated automatically. So there’s no reason to rebuild statistics after rebuilding an index on a table, because they’re already as fresh as they’re going to be. So just keep in mind that only rebuilding those tables that have a significant numbers of changes, and there are DMVs you can query to get information about that to make decisions on what indexes are actually going to be used. There’s the OLA halogen Maintenance Solution that I use frequently that does a lot of this stuff for you, if you set the parameters correctly, and it works pretty well, but I do whatever I can to avoid the default maintenance plans the SQL Server, because they don’t do a good job of this. Now let me go through a real world example here that we got called in on a performance tuning situation to help them out. Real world example, a medical clinic, their patient reporting database had hundreds of tables in it, but it had one large table that was over 50% of the entire database. And that one large table contains images, pictures, things like that, and several other columns. Now their statistics job was being run to rebuild statistics at 10pm every night for. And it was running the next morning, still running until about 11am so the staff were complaining about the system being so slow, from when they arrived in the morning until 11am we started looking into it, and we found out the problem was that statistics job was putting a massive load on on this big table and other tables during the time that they were trying to work. So the key is you want to make sure you schedule those statistics to run it off hours. Now the solution that we implemented was that we only rebuilt the statistics on tables that had changed significantly and not those that had little or no changes. Second we made a change so that that one large table was pulled out of our normal statistics maintenance scan. And instead of scanning it with full scan, we chose to use a percentage of 10% of the statistics being rebuilt, meaning it was going to scan 10% of the table to build the statistics, rather than 100% of it. And then finally, we set a time limit of six hours, meaning that if the rebuilding would finish around if it started at 10pm it would finish around 4am at the latest. And then after testing, we confirmed that this helped a significant deal. The queries were still running great. They got rid of all the performance issues they were having in the morning, and the statistics were typically being rebuilt in just under two hours, because the key the changes we made, made it so that could happen a lot faster. Even though we gave it a window of six hours, it generally only needed two hours to get done, and all of the performance issues were resolved with that. So that’s that’s what I consider a win in doing regular daily statistics maintenance, and it’s important to do it frequently, but it’s also important to not be wasteful on how you do it. And it’s also important for this to happen so that your queries will run as fast as they possibly can.
Shannon Lindsay 31:49
How do you manage and resolve blocking and blocking issues in SQL Server?
Steve Stedman 31:56
Wow. Okay, so keep in mind here, when we talk about blocking and locking is, I’ve got three levels of answers I was I’m doing on all of these. One is the basic answer, and that’s just go out and scan DMVs like DM Tran locks and DM OS waiting tasks, which are queer, which are DMVs, you can go and find thing about, find things about queries that are stock or block your weight. There’s also the SP underscore, who to where you can go and find blocking queries, or find all the queries that are running to show what is being blocked. Now, the more advanced answer is, well, first we need to have a good understanding of blocking and blocking now locking. A lot of people see locking and blocking in the database and think, Well, this is a bad thing. Well, it’s a bad thing when it causes performance issues, but it’s a good thing when it keeps two people from overriding the same piece of data at the same time and getting unexpected results. So walking is basically the mechanism in SQL Server used to keep multiple users synchronized across the same piece of data. It prevents data integrity problems such as dirty reads or lost updates, meaning without locking. If you were to update a row the same row I was updating, it would be unpredictable whose result we would get out at the end of it, it would probably be whoever finished last. So locking is important so we get reliable results like that. Now blocking, blocking occurs when one connection has a lock open, like you doing an update in a row inside of a transaction, and a second connection wants to go in and update the same thing or change the tables. If you’re updating a row and I’m trying to do something like alter the table or drop the table or even delete the row that you’re altering, blocking is going to occur to prevent me from doing that, because that would be a bad thing, if I can be changing the data while you were changing it. So the other thing I would do is I would use tools like database health monitor to track and find blocking queries, and I would turn on the email notification and database health monitor for blocking queries. This is something we use a lot of the time with our clients. In fact, just today, we had a couple emails come in from one of our clients where they had blocking occurring. One of our team members got on the on, got on with that query in SQL Server Management Studio, figured out why it was slow and worked for the client to get it resolved. So being able to find them, catch them, and address them is important and using tools like database health monitor. And there’s also a component I built inside a database health monitor called the SQL performance monitor, you can turn on, and we’ll start capturing all the blocking queries as they happen, along with things like missing indexes and how much load is on the server and things like that, so you can get a better picture into what’s going on. Now, more of a pro answer if you’re on a newer version of SQL Server, is, I might consider things like READ COMMITTED snapshot isolation level. And what that does is it makes it so that you can read what a table looked like before someone changed it, while someone is actively changing it, so you. Writes don’t block reads and reads don’t block reads, but writes do block writes with recommitted snapshot isolation level. Now I would not I think this came out in like 2017 generally. We’ve used it with SQL Server 2022, and 2019 and had really good luck with it. We had one client that just had so much blocking going on from all these reporting queries they were doing, put them on RCSi, and they have had no blocking reported in the last year and a half since we turned that on for them. They did, however, have way more temp DV load, which is a side effect of RCSi, but we planned for that, made sure that they had fast enough storage for their temp DV and that they also had big enough location for the temp TV. So the totally wrong answer, which is where you just walk out of the interview, if someone gives you this answer, is that with locking and blocking, we just use the no lock command everywhere in our queries. Okay, that means the person has a fundamental misunderstanding of how queries work and what the no lock command does. So don’t go with the no lock as your answer. Ever on this one should say ever, very rarely. It should be used sparingly.
Shannon Lindsay 36:12
How do you use SQL Server, Configuration Manager?
Steve Stedman 36:16
It’s one of those things that the first time set up, and this is the basic answer when you’re doing the first time set up a SQL Server. A SQL Server, you’re going to use it quite a bit to go in and configure things like Port ports startup options and which services are running and which are set to auto start things like that. The more advanced answer is that I would use it anytime that I want to start and stop services. It’s much faster and more obvious than using the control panel Services section, this is one of those things I get impatient when I’m watching people do things slowly and painfully, and when I’m helping someone and they bring up the control panel services, and they have to scroll through like 200 different services and eventually find the right SQL Server Agent task, SQL Server, or SQL Server service, and tell it to start or stop or change the settings on it. It just adds way more time with the SQL Server Configuration Manager. It’s only showing you those services that are relevant to SQL Server, not all the other stuff that might be running on your server. You also use it to see what services are running. Can see things like, is your SQL Server Agent running? Well, we can use it to start to stop that, and also use it to monitor it and see if things like, oh, there’s a couple out of there. There’s SSIs and SSRS and different things in there as well, using it. And then the pro answer, and I generally do this on all the servers I’m working on, is I will pin, pin the SQL Configuration Manager to my taskbar on the SQL Server, so that if I’m connecting there, I have quick access to just click on it and get right in to be able to see what’s going on if I need to start or stop or change what’s going on in the service. You also use it to adjust things like trace flags that are needed during the startup of SQL Server. And you can use it to adjust different ports that SQL Server is running on or to adjust the protocols being used, such as name, pipe, shared memory or TCPIP and oftentimes those may need to be adjusted when specific network things change or happen. All right, how’d I do? Do I get the job?
Shannon Lindsay 38:16
Well, I would say so, if you went with all the pro answers, then yes.
Steve Stedman 38:27
So I think what I really tried to show there was on each one of these there are different levels of how things can be dealt with, and just because you ask a question and somebody knows what that thing is, it doesn’t necessarily mean that they really know how to use it, or really know how to get the most value out of it.
Shannon Lindsay 38:47
Google Search before.
Steve Stedman 38:50
Yeah, and I guess that’s the difference if you’re doing this in person, versus, like on Zoom or something like that, or teams. It could be that someone’s over in chat, GPT, typing in each of these questions and just reading it back to you, and that’s not going to be a real good answer. I mean, a real good feel. So one of the things that I like to use the most to show when I really know something in an interview is to show examples. This is what I did. This is when we did it. These are the problems that happen, and this is what we learned from it. This how it made things better. And just share those kind of stories. And that goes to show that, yes, you’ve actually done this, rather than you read about it once, or you heard about it in some blog post one day. All right, well, I think that wraps it up. Thanks for interviewing me today. Thanks for watching and watching and listening. We hope that everyone’s enjoyed this episode. Keep in mind that we have the interview. I’m going to jump up and get that URL ahead and top my list here. Hold on. URL of stedman.us/interview with the coupon code of interview, Feb 2025, and yeah, if you want more episodes like this, take a look at our YouTube podcast channel. You. With this episode and all of our previous episodes. This was season two, Episode Five, or our 17th episode since we started the show, and you can get that at stedman.us/podcast YouTube. Anyway, that wraps it up. Thanks for watching, and have a great day. You. Steve, thanks for watching our video. I’m Steve, and I hope you’ve enjoyed this. Please click the thumbs up if you liked it. And if you want more information, more videos like this, click the subscribe button and hit the bell icon so that you can get notified of future videos that we create.