Replication on SQL Server – podcast episode by Steve Stedman.
October 9, 2024
- Recording Date: 10/9/2024
- Topic: SQL Server Replication
- Host: Steve Stedman
Steve Stedman discusses SQL Server replication, highlighting its use in synchronizing data between servers. He covers snapshot, transactional, and merge replication, explaining their functionalities and limitations. Snapshot replication provides a one-time copy of data, but can be inefficient during updates. Transactional replication is useful for offloading reporting workloads but doesn’t allow changes on the subscriber side. Merge replication facilitates bi-directional data flow, but requires a rowguid column and can lead to conflicts. Stedman also addresses push vs. pull subscriptions, emphasizing their impact on SQL Server Agent jobs. The session concludes with a Q&A on parameter sniffing and a preview of the next episode on SQL Server performance.
Watch / Listen Now
Podcast Transcript – Replication
Podcast Ep 4- Replication
SPEAKERS
Steve Stedman
Steve Stedman 00:16 Hey everyone, and welcome to this week’s podcast. I’m Steve Stedman, your host, and this week’s topic is on SQL Server replication. But before we get into that, I wanted to share a little bit of news with Stedman solutions. We’re continuing to grow. We added a couple new managed services, computers this or SQL Servers this week into one of one of our client agreements, and that’s always a big deal to get a few more servers that we’re monitoring. And then the team that’s we have working on database health monitor, they are working on a new report that will show file size over time for all database and log files on your SQL Server. And this is a pretty handy thing. And just this weekend, Eric asked me about something we had a server we’re working on where it was getting low disk space certain times of the day. And he asked, Well, is there any way we can see what’s taking up that disk space, or what was using up that disk space at that point in time? And currently, we log that information into the database, health monitor database, but we don’t yet have a report to display it. So we’ve been working on that for about a week and a half, and what you’ll be able to do is go view for any one of your database files, whether you have multiple data files or single file, or you have logs, go and view and be able to look at over time. Are those log files or data files growing? If, unfortunately, if you’re using something like Auto Shrink. Are they shrinking things like that to be able to see what the patterns are there and to see what’s actually taking up the space with those files, something very handy to add, and I’m really excited that the the team is getting those added in.
So one of the things I also wanted to mention this week was where to watch or listen for our podcast. We don’t have yet have it up on a podcasting platform, but it’s all available on YouTube, on my channel, as well as at the Stedman Solutions website. If you go to Stedman solutions.com click on the Home menu, and then on the drop down, there’s a link there to the podcast. You can view all of our previous three episodes, and then this episode will be there, available as soon as it’s been edited and ready to be published.
All right, so moving into this week’s topic is SQL Server replication. This is a slide left over from my replication course presentation. We’re not really talking about the course here, but that coupon is still good if you want to get in for a 50% off deal on the course. But what we are talking about is SQL Server replication. And SQL Server replication is one of those features that I’ve done a ton with. I love working with it. There’s really cool things you can do, and I’ve seen some pretty amazing things that our clients have been doing with it as well.
But what we’re going to cover today, we’re going to cover today. We’re going to talk a little bit about replication, how it works. We’ll cover SQL Server snapshot replication, SQL Server transactional replication, and SQL Server merge replication, and we’ll go in. We’ve got a bunch of details we’re going to cover on all of those. But overall, basically, replication is one of those things. It’s a way of synchronizing data from one SQL server to another, and it’s different than things like log shipping, because, for instance, with log shipping, you’re just taking an entire database and replaying the backup transaction log backups off to another site. And even though you can have it viewable, it’s something that you can’t really use very well when things are being restored. It’s different than availability groups, because with availability groups, yes, you have your data synchronizing across multiple SQL servers, but one of only one of them is ever active. Now with replication, what we’re doing is we’re synchronizing data from one SQL Server database, or even part of a SQL Server database, meaning just a handful of tables we can do and synchronizing that over to another SQL Server, and in varying ways, snapshot is just a one time picture of that data that gets moved over there transactional replication. It starts with a snapshot, and then everything that changes after that gets moved over as part of transactional replication. It’s similar to what you might think with transaction log, log shipping, but it is different in that the data is actually, I mean, you can use both sides, and then there’s merge replication, which is you move the data all over there, and then once it’s moved, data can be changed on either side and pushed back and forth so that changes can be integrated and brought in from multiple locations, a lot of different ways, different reasons, you can use replication there, but it’s one of those things that I think it’s often misunderstood, because it takes a lot to really make sense out of it, a lot of understanding to be able to use it. And I think that a lot of. People jump in without understanding all of the details on replication. And my best suggestion, if you’re going to be using SQL Server replication, make sure you have a really good development or test environment set up so you can set up things, try things, test it, and see what’s going to happen and find out how much data is going to move, how long it’s going to take, how big of a publication Can you set up now, when we get into replication, one of the key things is the components and how it all works. And Microsoft, when they set this up originally, and this has been this way for years, probably, gosh, SQL Server 2000 maybe, maybe older than that. I don’t recall exactly when it started, but I’ve been using it since SQL server 2005 and it’s had this concept all along that is publishers, distributors and subscribers, and the publisher and the distributor can often be on the same server, and oftentimes we see that, but they don’t have to.
So your publisher is the primary database that the data is being published from or being sent from, and with merge replication, it’s also the one the data is coming back to. So the publisher is like the central hub of the data that’s being go, that’s being sent out, like a newspaper publisher, and then the distributor, that’s the component that’s able to pick up the changes, pick up the tracking, pick up what needs to be moved back and forth, either way, on the different types of publications and moving the data around. Like I said, distributor and publisher are often the same server, but they don’t have to be. You could have, if you had many different publishers on different servers, you could have one distributor that they all use together, then the subscriber. That’s what we often refer to as the like the client sites or the the other destination where the data is being sent to. With this in replication, for any set of data that you’re publishing, you generally have one publisher, and then you can have many subscribers. And whether it’s snapshot replication, which is just taking a one time picture of that data and sending it, or whether it’s transactional replication which keeps pumping the changes from the publisher out to the subscriber, or whether it’s merge merge replication which the data is moving both ways, you can still have many, many subscribers worked in environments anywhere between one to 20 different subscribers on These different replication models. And seems to work well. We had one project we were working on where the idea was, we wanted to have about 700 subscribers, and it was one of those things that, after doing some investigation, we found out that, yeah, that’s just not possible. With SQL server. You can keep adding different subscribers. But I did a test where we set up one publisher that had about 150 to 200 subscribers. And even with a very small table, with very small amount of changes, there was so much overhead and synchronizing all that data that it just wasn’t, wasn’t really usable with that many subscribers. So usually anywhere, I don’t know, you don’t want to overdo it, but anywhere up to 10 or 20 subscribers, you can have pretty good success, and you might be able to go a little bit further than that, depending on the size of the data that you’re working with there.
Now snapshot replication. Snapshot replication is similar to the idea of like taking a full backup of your database and restoring it, and then when you need the data to be updated, you would take a full backup of that and restore it again. The difference between doing that with just database full backups and restores versus a snapshot snapshot replication is that you’re scheduling it or you’re setting it up to only be it can be a subset of the database. It doesn’t have to be everything in the database. You can set up snapshot replication where you can say, of these 100 tables I have in a database, I want to synchronize just two of them. And what I want to do is I want to have that take a snapshot, copy it over to the server, to this other server or other database, and then have that happen on some time interval, meaning I’m going to use this for reporting day old data is okay. So I want this to run every night, at midnight, and move these two tables, or these 20 tables, from this publisher off to this number of subscribers. Be moving off to one subscriber. Can be moving off to 20 or 30 subscribers. But it’s basically just taking a one time picture of that data and moving it from the publisher out to the subscriber. I shouldn’t say one time, but a point in time picture that data that’s being moved out there, and it can happen on a frequent interval. Now, the thing is, when, when snapshot replication gets sent out with a new snapshot, it ends up wiping out the tables and recreating them oftentimes. Or you can change how they’re set up to be to just refill them. But the idea is, it may be wiping out the data that’s there while that snapshot is being regenerated. So one of the drawbacks is, somebody hears about snapshot replication, they say, Oh, great, I’ll use this. And what I’ll do is, I’ll take a snapshot that gets moved over there every five minutes, and then gets then what. You run into, though, is while that snapshot is being moved, that subscriber database is not really usable because it’s being recreated with that snapshot. So usually, if you have data that you want to synchronize, often, you shouldn’t really use snapshot replication. And you know, in fact, in my career, I’ve done a lot with merge replication. I’ve done a lot with transactional replication, and I don’t think that I have ever found a scenario where snapshot replication was a perfect was a solution for the client. Transactional replication, absolutely. Merge replication, absolutely. But transactional replication, yeah, doesn’t or sorry, snapshot replication. What isn’t really the right, right solution for a lot of the things that people want to use replication for. And I mean, realistically, a lot of the times when people want to do that, they’ll just do like a backup and restore their database regularly instead of doing snapshot. It’s not one of the stronger parts of replication.
Next, let’s talk about transactional replication. With transactional replication, it uses a snapshot in order to be able to establish that database, establish that initial copy of the data, and then it catches changes by reading the transaction log, it catches changes that happens, that happened to all the data that’s being synchronized over time. So let’s say you have five tables that you’ve set up with transactional replication. What it’s going to do when it gets first initialized is it’s going to make a snapshot to move all the data over for the first time, and then once that snapshot has been delivered, it’s going to keep track of all of the changes that are happening on the publisher, and send all of those changes out to each of the subscribers by catching that through the transaction log. So and that can be set up on different intervals, where it could sync almost continuously, or you could have it sync less often, but it really just keeps track of those changes and moves them in one direction, from the publisher out to the subscriber. Now, well, the problem you run into that is that you can’t really go making changes to that data on the subscriber, because anything you change may get overwritten from the publisher. Again, not really a safe thing to do to go making those changes, but it’s great if you’re in a situation where you need to do some kind of reporting, and you don’t want to put the look sometimes reporting, you don’t want to put the load on the primary database. Worked in an environment where the client had a lot of load on their OLTP databases and they weren’t able to run the reports that they wanted to run against those OLTP databases because they were just too heavy of a load on the database. So what they had set up was it set up transactional replication to synchronize all the data from one server off to the other server, from one database off to the other database on a different server, in order to make it so you could run those heavy reporting queries against that replicated database that was fairly up to date, instead of having to run it against the live production database. And I’ve worked, gosh, since seeing that environment probably 10 years ago, I’ve worked with other clients probably 15 times where we’ve set up something similar to that, where they’re in a situation where they’re some type of reporting is putting too much load on that primary database that we need to offload it. So we offload it off to another database using transactional replication. Now, transactional replication doesn’t require a lot of a lot of changes to the database, I think all it requires is that you have a unique primary key on your tables so that you can use that to identify what’s being updated or what’s being moved off to the replicated side, where merge. Replication takes a little bit more to get it configured and set up. Transactional replication, it’s pretty quick and easy to get set up when you’re first using SQL Server replication, when you’re setting it up, you just want to make sure you understand how much data is being transferred. I guess this would apply to all types of replication, but how much data is being transferred, how often it’s being transferred, and how big that connection is between the two servers. Now, if you’re in same data center or the same office, and you’ve just got those configured side by side on a 10 gigabit network, or whatever high speed network you’ve got. Yeah, might not be that big of a deal, but if you’re having to take like, 75 gigs of data, and every time it re initializes, it has to copy that 75 gigs from the primary server out to the or from the publisher out to the subscribers. That’s a big chunk of data that you have to move across the network. And if that network connection is going through a VPN across the internet, maybe somewhere on the other side of the planet, where they’ve got a slower internet connection, well that might take a little bit longer. It. It’s initializing side by side. We’ve seen some scenarios where you test it locally, and it works great. We can reinitialize replication in like 10 minutes, and then you try and synchronize to a client site that’s out at some distant location on a slow network connection, and it takes more than 24 hours. So you want to make sure that wherever you’re trying to synchronize the data to that they at least have a fast enough internet connection that you’re able to get that done without waiting too long. And usually, what happens when that’s being reinitialized? It might take that 24 hours to copy that database at that client site isn’t really usable because the data is only partway there while it’s being reinitialized. So anyway, transactional replication, it’s great for situations where you have central data that you need to push out close to real time to multiple subscribers, and I’ve seen it work really well in a lot of environments. And as far as kind of taking care of it over time, transactional replication is pretty straightforward, merge replication.
So merge replication, the difference here from the other types is that you’ve got data going both ways, meaning, data updated on the publisher gets pushed out to the subscribers, and data updated on the subscribers, gets pushed back to the publisher with merge replication when use, when it initially gets set up, it does take similar to a snapshot, where it takes an entire copy of the data, and it moves it from the publisher out to the subscribers. And then once that’s been established, it’s then moving data in both directions. Now merge replication is great. I always take like the store scenario where, let’s say you’ve got some store chain and you’ve got a few dozen branch locations that all need to have the same inventory data, like a car part store, and you want to see, okay, we need an oil filter for this car. Who has it in stock. We don’t have it in stock at this location, but let’s go look at what other locations have it in stock real time right now. That’s an environment where merge replication would work really well. If all the locations are synchronizing that data with merge replication, you’re able to see real time. Yes, that part is in stock there, and then if somebody was to buy it, and that data was to synchronize through from the point of sale system through the database, you’d be able to see that, yeah, that’s out of stock now, and that part is no longer available. So merge replication takes a little bit more work, and it has a few more requirements over transactional replication or snapshot replication. The key thing here is that it really needs to have what they call a row good ID, so it’s a unique identifier that identifies every row in the database. Don’t worry, if you don’t have one, SQL Server will try and add it, but that is one of the things that when that gets added, that does change your original database where, when you’re working with snapshot or transactional replication, it’s not actually changing the database structure in any way with merge replication. Also, instead of doing something like the transactional replication does where it reads the transaction log to see what’s changing, merge replication accomplishes this through a series of triggers and tables that it uses to track what changes have occurred and what made what data needs to be sent in different directions. So merge replication generally puts, I think, three triggers on every single table that’s involved in replication. So you just need to be careful that you know if you’re going to use merge replication that it will be modifying your database in some way like that. Now I think merge replication is my favorite. And I think if you’d asked me 15 years ago, before I had as much experience with merge replication as I do now, I don’t think I would have said that, but now that I understand how merge replication works and some of the things that can happen, like conflicts, meaning, if you’ve got your publisher and then you have two subscribers, take a simple environment, like you just have store b out there with two subscribers, if store a and store B, say, change the same record at the same time, like a customer, and they change something like an expiration date or a credit card or something like that, well, if they both change at the same time, that comes back to that central server, publisher, and the publisher has to figure out which one of those changes are we going to take. And usually, if you’re synchronizing frequently enough, you don’t run into a case where you get changes from two different locations on the same row. But it can happen, and when that happens, it throws it into the list of conflicts. And a conflict means, well, we just assume that one piece of data was correct, but we don’t know for sure. So you have to go into SQL Server, into the view conflicts for a specific publication, and see if there are any conflicts. And if there are, sometimes you have to go through and pick which one are you going to accept, and it’ll tell you, Well, this data came from server A and this data is what existed in the publisher. And. We went with, or SQL Server went with this one, but and this one was the non successful one, and you have a choice to say, okay, yeah, that’s fine. Let’s take what you did, or let’s force it to go to the data that was not accepted originally.
With that oftentimes, when we’re working with merge replication, we’ll set up some basic rules and scripts that will do auto resolution on a lot of those things. And honestly, SQL Server does a pretty good job resolving those. Usually, we run into the most difficulty with conflicts when it’s a scenario where you’ve got a store site, for instance, that’s offline for some amount of time, and it comes back online and it updates, like, two days worth of data when the other system has been updating data all along, and you can get into some stale data that’s trying to be put back into the system. So merge replication is great for moving data in both directions, but again, you’ve got to have a good connection, and you’ve got to make sure that you keep track of things like conflicts a little bit more with snapshot and transactional replication, as opposed to merge replication, those don’t have any kind of a conflict resolver, because there are no conflicts. It’s only going in one direction. When you get into that two direction, bi directional type of data, that’s where you get into the conflicts.
So next with replication, I want to talk a little bit about push and pull subscriptions. And this is one of those things that I see a lot of people get caught up on it, thinking, Well, what server is going to be doing all the work? But that’s really not the right way to look at it. Push or Pull subscriptions really just define where the job is going to run. That kicks off the work. Okay? So if the subscriber for any of the replication types needs to reach out to the distributor to get to the publisher for anything, all that work has to happen on the subscriber. The subscriber looks at what they’ve got, and they go and ask the publisher for whatever information is needed. For instance, with merge replication, it says these changes have occurred. Let’s push these back to the publisher. Well, that works always going to happen on the subscriber, independent of whether you you’re using Push or Pull. The difference is, with pull subscription, it assumes that the subscriber has a SQL Server Agent or other job running locally that’s going to trigger that work to happen. A push subscription means that the publisher has the SQL Server Agent job that’s going to reach out to the subscriber and trigger it to go do the work. So the only thing that’s really happening, different between push versus pull, is where the SQL Server Agent job is being run that triggers it, and if it’s being run on the publisher, it’s not really doing any more work than just the agent job. It just reaches out to the subscriber and says, Okay, do your thing. Now, when I say do your thing, I mean, do your subscription do whatever work is needed there in order to synchronize replication at this point, depending on the replication type, that may vary, but it’s just where the SQL Server Agent jobs is being run to kick it off. Is the only real difference between push versus pull subscriptions. Now with that, a lot of people ask, Well, what version of SQL Server do you need to work with replication. Well, pretty much works with all versions of SQL Server, meaning standard enterprise web and even SQL Server Express, however, you cannot run the publisher or the distributor on SQL Server Express, because those have to have SQL Server Agent jobs that run to maintain it all. Now you can run subscribers, and I work in environments where we have 30 or 40 subscribers that are all running on SQL Server Express, and that’s where, if you’re going to use push replication, push subscriptions, you then have all of the jobs running on the publisher on the SQL Server Agent that reach out to the subscribers and trigger them to do the work, rather than each of the subscribers having to have SQL Server Agent running there, because the SQL Server Agent is not available on SQL Server Express Edition, but it works great with SQL Express as long as you’re not dealing with two large amounts of data, as long As the database is capable running on SQL Server Express replication works fine with it there too, just not on the publisher or the distributor. Now you might say, well, what if the connection is down and I’m doing push subscription, meaning the distributor and the publisher are triggering the subscriber to go do the work. Well, how is it going to trigger go, to go do the work. Well, it’s not. But the point is, if your connection’s down, there’s no reason for it to go trigger any work that it’s going to talk to the subscriber or it’s going to talk back to the publisher with because it’s not to be able to do that, do its job anyway. So when it’s down, yeah, when the connection is down, the it doesn’t do anything, and it’s not going to do anything anyway, even if you had. Uh, everything running out at each of those subscribers. So there’s really no functionality difference there, other than where the SQL Server Agent jobs are being run with push versus pull. Replication.
Okay, so that kind of wraps up our overview on the SQL Server, SQL Server replication, I know, like I said, I’m reusing the slides from our replication course, and you can get the replication 50% off. Code is still good, but with that, we’ve got we’ve covered transactional replication, we’ve covered snapshot replication, we’ve covered merge replication, and some of the different pros and cons around each of them and the benefits of using them, I would make sure that if you’re considering using replication, that you really learn how to use each of these different types, so you can figure out what’s appropriate for you, or talk to someone who can help you. And that’s something that we do quite a bit. Was just on a call, actually an hour ago with a client, where we were talking about, well, what is the right way to do SQL Server merge replication to meet their specific needs. Replication is a great feature in SQL Server, and it really has not changed significantly in any way since SQL server 2005 which I guess is a testament to say that it was working back then. So it just keeps on working today.
Okay, today’s giveaway. So for everyone who is attending this class, this session live, this is where you get a free bonus for showing up and watching our live stream. Today, everyone who’s intended attending live will receive free access to our interview questions course. This is a class where I go through and talk about different questions that may come up on an interview for a SQL Server Developer, SQL Server DBA. And this is a class that’s great if you’re the applicant looking for a job, so you can kind of practice, and, I don’t know, tune up or get get in shape on some of these questions, so you’re ready to answer them. But it’s also good for the IT manager or the hiring manager that’s trying to hire that developer or DBA to make sure that they know the right questions to ask. I’ve seen too many times, and even myself applying for jobs where you show up and the person interviewing you doesn’t know how to interview for a DBA role, and they just ask you questions about like, well, what is the database and boring stuff like that, where this is one that if you’re the manager or you’re the person looking for the job, you’ll get something out of it. And on every one of the interview questions, there’s three levels of answers where we talk about this is the sort of off the cuff, quick answer, if you’ve just heard about it, this is what you’re going to say it is. Then there’s kind of the medium answer that’s, well, if you’ve maybe used it a little bit, this is how you’d answer the question. And then there’s the in depth answer that would be like, if you’ve worked with this for 20 years. Here’s the kind of answer you would give about all those different topics, and we’re adding to that course over time, and I think we’ve got somewhere around 30 or 40 interview questions there today that cover a lot of the common stuff that you might get asked on a SQL Server interview, or that you might need to ask if you’re the hiring manager. So for everyone who is watching live today, it is October 9, 2024 and it is currently 11 o’clock Pacific Time, we have a three hour window here. So for the next three hours until two o’clock Pacific time, just email Shannon at Stedman solutions.com Shannon’s my assistant, and she will give you a free coupon code that’s basically 100% off, totally free on the SQL Server, developer and DBA interview questions. Class, no strings attached, but if you take the class, you go through it, I’d love feedback at the end to see what people think, let me know if you liked it or didn’t like it, or what you got out of the class. I would appreciate that, but that’s a limited time thing. So if you’re watching this rerun later on the podcast, and you’re just hearing this and it’s like a month later, don’t, don’t, don’t ask for the free coupon code. You’re only good till October 9, today, at 2pm Pacific Time, 5pm Eastern and if you email Shannon, she will get you that free coupon code for this class we’ve added.
I’ve added a new section at the end of each of these podcasts, and the idea is ask Steve Q A, and if this is one that was submitted to me, and if any, and I’ll give it my best shot to answer each week. But if you want to email me, Steve at Stedmansolutions.com and tell me you have an ask, Steve Q, a question to put in at the end of the podcast. If it makes sense. It’s appropriate for the podcast. We’ll put it in at the end and see and do, do our best. I say our best today, so I’m going to do my best, but if we have a guest on on we may involve them in answering it as well, too. But the question that came in this week is, what is parameter sniffing in SQL Server. Okay, this is a concept that when you’re looking at a query plan or a compiled plan for a query typically relating to stored procedures, the first time that stored procedure gets run, it gets compiled with a specific. Plan. And what happens is the parameter sniffing concept is it looks at what parameters are being passed in, and it uses that to best optimize that stored procedure as in order to be able to return the results. But unfortunately, what happens is people will write these big stored procedures I kind of refer to as like one procedure to rule them all, meaning it’s a stored procedure that has a ton of different if statements in it, and it does like 30 or 40 or even four or five different queries. And depending on what parameters are passed in, it does a whole bunch of different things. Now that’s a perfectly fine programming technique, but the problem you run into with SQL Server is that, let’s say, the first time it’s run, you pass in parameter one, which says, run one set of queries. The next time it’s run, you pass in parameter two, says run a different set of queries. Well, when parameter two is passed in, your query, may your procedure may not run as efficiently as it did when it was compiled with parameter one, because a different set of queries are being used, a different set of indexes may be needed, things like that. So what happens is you end up with this procedure that depending on what parameters were passed into it when it was first compiled, you end up with a completely different plan that may be really inefficient in some cases and really great and others. So one of the cases where I’ve seen with the client that was having this happen is that their system would be running great, and then that query get pushed out of memory. The next time it got loaded back in, it would get compiled with a different plan, and it would completely bog down with all kinds of weight statistics, specifically around CX packet and CX consumer, which are around parallelism, which was a for me, it was kind of a telltale that something wasn’t it wasn’t using the right plan in this case. So there’s a number of different ways you can deal with this, and there’s kind of the overall way, which is to tell SQL just don’t pre compile this procedure, and that’s just a parameter you put on the procedure that says with recompile, that says every time it’s going to get run, recompile it with the current set of parameters. Now the gut, the gut reaction A lot of people have is, oh, gosh, I’ll just go put that on all my procedures in order to make sure that this never happens to me. And that’s a bad thing to do. And the reason is that having compiled procedures is a really big performance improvement overall on your SQL Server. And if you were to just tell it to not recompile any of your procedures, or sorry, to always force a recompile of all your procedures, then you’re going to put a whole lot more load on your SQL Server. So you’d only want to use that with recompile option on a procedure when it is proving to be a problem, and usually I would only use that as a temporary patch while we figured out exactly how to fix it. Now, the clue I usually run into when I’m looking at parameter sniffing and SQL Server is procedures that have if statements that are going to do a very different set of queries based off of some parameter being faced being passed in. And let’s say you’ve got a simple scenario where if parameter one, or if parameter equals one, do this, if parameter equals two, do that, or parameter equals three, do something completely different. And what I would do in that case is then take those three different things and move them out, those three different queries, and move them out into three different procedures of their own. You can still use one procedure to rule them all, or one procedure that gets called that says, if it’s if the parameter is one, call this procedure, if it’s to call that procedure, if it’s three, call this other procedure. And what that will allow things to happen is that the each of those three procedures will be compiled based off of the right set of data being passed into it, rather than the top level one, which would just get compiled assuming it’s calling these other procedures. So that’s one way to do it. Another way to do it is you can take some of the stuff in that procedure and force it into dynamic SQL, where you’re building a string or varchar that contains SQL, and then you’re passing that to store procedure like SP, execute SQL, so that each time it’s being run, it’s being run with a different set of data in there, but because it’s being passed through as dynamic SQL, that when it’s getting sent with different parameters, it’s getting compiled based off of that set of parameters. So three ways to deal with it. There’s a few others, but the primary quick knee jerk reaction is to put an option recompile on the offending procedure as a temporary measure while you do one of the other two. And the other two would be to break each of the distinct areas out into procedures of their own. And then the other one would be to use dynamic SQL to generate the queries based off of what parameters were passed in and then execute that dynamic SQL.
What I want to do is talk about our next episode. We’re going to be two weeks away, so I’m going to be traveling next week. We’re going to be two weeks away from our next episode. And at our next episode, we’re going to talk about SQL server performance on episode five of our podcast, and talk about some. Things you can do to track and monitor and work on SQL server performance tuning and at least understand it better as to what’s going on with your SQL Server.
Also want to remind everyone that this is a regular podcast, and you can watch or listen on my YouTube channel @stevestedman.com or not Steve Stedman YouTube, at Steve Stedman on YouTube, or you can go to Stedmansolutions.com and click on the home link and then drop down to podcast and watch all of our previously recorded podcast episodes there as they continue to grow and evolve over time. So thanks for watching. There’s no questions. I think that wraps it up. Hopefully everyone knows a little bit more about bit more about SQL Server replication, and in two weeks, on our next episode, we talking about SQL server performance, right? Have a great day. 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.
If you want to learn more about SQL Server Replication, check out this link to lead you to our replication course!
Visit our main Podcast page for more episodes.