- Host: Steve Stedman
- Topic: 10 Things Every DBA Should Know For Performance
- Recording Date: October 31, 2025
- Listen on Spotify!
Stedman SQL Podcast Season 2 Episode 28 10 Things Every DBA Should Know For Perfomance
In this episode of the Stedman SQL Podcast, Steve Stedman walks through often overlooked SQL Server configuration settings that can significantly improve performance. Topics include TempDB setup, file growth settings, alerts, index maintenance, fill factor, backup compression, RCSI, and the importance of monitoring with Database Health Monitor.
If you want to deepen your SQL Server knowledge, November is Stedman SQL School month with daily class promotions at Stedman.us/school.
Watch the episode to learn practical, high-impact adjustments you can apply to your SQL Server environment and how to monitor results using Database Health Monitor at DatabaseHealth.com.
Podcast Transcript
Steve Stedman 00:15
Hey everyone and welcome to this week’s episode of The Stedman SQL Podcast. This is season two, episode number 28 and I’m your host, Steve Stedman, and today I wanted to share some things to consider what related to SQL Server and overall performance. If you missed last week’s episode, please check out season two episode 27 where George and Mitchell discuss some of the latest features in Database Health Monitor. You can find that at Stedman.us/podcastYouTube. I’d like to welcome everyone.
Some news, November is Stedman SQL School month where we have promotions going on every single day of November, on our classes or each day and each week is a different promotion. So if you want to upgrade your SQL skills, then these classes can help you with that. You can check out Stedman SQL school at Stedman.us/school or you can go to Stevestedman.com my blog to check on the latest promotions for this month.
This week’s topic, we’re going to talk about 10 overlooked SQL Server configuration changes and things you can do to help boost database performance. Why this matters? Well, misconfigurations can degrade performance, but a few of these little changes can help along the way. So the goal here, the target audience is DBAs, IT professionals or anyone managing a SQL Server database.
The first thing we’re going to take a look at is temp TB configuration. Some of the key points here is that we need to optimize temp TB for performance. Now, one of the things to remember is that TempDB, there’s a lot of things that go into TempDB. And what goes into TempDB? Well, there’s the obvious things, like creating an attempt table or creating a table variable, they all go into TempDB. But then a lot of other stuff goes down there too. Like if you’re using RCSI, the RCSI version store goes to TempDB. Or if you’re running a query and it has to spool some information from one table into another in order to do a certain kind of a JOIN. Well, some of that will use TempDB. There’s a lot of different things that everything, basically, everything you’re doing hits TempDB in some way, usually and with TempDB, if TempDB is overloaded or slow, well, that impacts everything else that runs on your system. So one of the things we like to do is we like to configure TempDB to have multiple files, and when we do that, we usually have one file per core, up to about eight files, typically Max, to reduce contention. Now on newer versions of SQL Server, sometimes we’ll go a couple of files more than that, maybe up to 12 or 16, but you don’t want to have too many overall. Also we want to keep TempDB on fast storage. So if you’re in an environment with tiered storage, where you have cheaper storage for maybe your backups, and fast storage for things that matter, TempDB should be on that fastest storage that you have available there. And it’s also a good practice, just like regular SQL Server databases to separate the log and data files so you can avoid IO bottlenecks. Every time anything gets written to TempDB, it has to get written to both the log and to the data file, and having those in different places can help quite a bit. So TempDB is used heavily, and if it’s not set up right, it can really slow systems down. So take away on TempDB is to go check out your TempDB setup and ensure it’s on a dedicated high speed storage and the other thing is, we can use Database Health Monitor. Database Health Monitor is an application that I’ve built over the last 14 years or so, and there’s a number of reports in there that we can go look at related to TempDB. Okay, so let’s take a quick look at some of our Temp DB reports. I’m going to go just look at one of our production servers. We have AZ Stedman, and I’m going to go look at TempDB. I’m going to expand the historic section, and from there, I’m going to look at some things like file size over time with TempDB and here we can see there are four or three TempDB files on this specific server. There’s not a lot of cores on it, so we don’t have we don’t have that many TempDB files total. We have three data files and one log. We look at things like IO by hour. We can see that TempDB doesn’t have a lot of IO on this specific system, and can see it’s running pretty typically, around two and a half megabit per second, and a little bit different over the last couple days, but generally it’s kind of sitting at the same place. So this is a sign of a All green. Good, good. TempDB database. TempDB is certainly not the slowdown in this environment. If you’re seeing numbers here with two or three digit up in the hundreds, four or 500 something like that, then you know you’ve got serious problems with TempDB.
So the other thing we want to look at with that is TempDB allocation. And this is a new report that was added recently, where it shows what TempDB is being used for here, and actually did a restart and did some maintenance on this. So this was this server was down for a little bit for maintenance. But what we’re seeing here is that with the version store, with SQL Server, version storage, using a fair amount of data here we can see over this. This, this last bit of time where, normally, if we look further back, let’s go back, like seven days. We can see, like the green is version store, the tan bit is internal objects, and the blue is user objects. So we’ve got some information there on what might be going on in TempDB. And we can also look at the use by hour. And here we’re looking for spikes. And in this case, it’s just showing on a scale of one to 10, with 10 being the most used, what areas when TempDB is used, being used the most, and then TempDB high usage queries. With this we can also go and see, here are some of the queries. We can go click on one of these and see, well, this is one of the queries that’s using something in TempDB. The allocation in gigabytes is point seven three, so it’s using less than a gigabyte. But if you look at a report like this and you see that TempDB is using things like 60 gigabytes or even 15 gigabytes, well for a specific query, well maybe you should do something to optimize that specific query. All right, lot going on there with TempDB.
Another thing we can look at is trace flag, 3226 for log file management. Now this is one of those that I find really kind of, I don’t know, annoying in the basic SQL Server setup. So what happens in a fresh install of SQL Server, if you haven’t turned this on, is that every time a backup is run, a full differential or log, it writes into the SQL server error log, a message that says the backup was run well. But it also logs that in MSDB, so you can know the backup is run and it ends up really just kind of filling your SQL server error log up with a whole bunch of stuff that is not errors. So one of the first things I will do on a SQL Server is to turn on trace flag, 3226 and in order to keep all of those error messages from going into the SQL server error log. Now the thing is, I like the error log to catch things that are errors. So when there’s something that’s going seriously wrong, I can go and look at that error log and find out why it’s wrong. And if I go and look at that log, and all I see is file was backed up. Our database was backed up. Database was backed up many times a minute. Well, that’s going to hide your real error messages. So plus, there’s a lot of IO going on, depending on how often you might be running backups. So for instance, working with a client that had log backups that ran every minute on over 30 databases, that would mean that there were 30 log entries per minute into the log. And if you do the math of number of minutes and an hour, and hours a day, and 30 entries per log. That’s over 43,000 log entries every day that aren’t really needed. And when I say they aren’t really needed, well the thing is, you can go and get the history of that out of MSDB, rather than your error log. So really, the key of why this matters is preventing unnecessary log flooding, keeping the logs lean and manageable, reducing excess IO and making it so it’s easier to find problems when they happen. The way you set this is you can go into the SQL Server Configuration Manager and set a startup parameter of trace flag- t3226 and you go to Stevestedman.com, my blog and search on 3226 you find a blog post that talks all about how to set that appropriately. So that’s one of those things that I recommend. Just reduces excess IO that’s not needed, and it’s just kind of a waste to fill up your error log with a whole bunch of messages that says your database was backed up.
The next item is separating data and log files. Now this is one of those things that if you’re on a server that’s over provisioned and you never have any IO issues, you never have any performance issues. Well, there are some other best practices is why you should do this, but this isn’t going to make a difference for you on performance. But if you’re on that SQL server where you’re just slamming the disk. You’ve got so much work going on, maybe you’re doing massive imports into some really big tables, and you wipe them out and replace them every day, or something like that. Well that’s where separate data, log files, are going to make the biggest difference. And the thing is here, whenever you’re doing an any kind of a modification, insert, update, delete. The merge stuff like that that’s changing data in your SQL Server. Well, SQL Server first has to write that to your log file before the transaction commits, and then shortly after that, it writes it to your data file. And if we can split the data and the log files out onto two different drives, we can also put the log files on the faster storage, so if you have tiered storage that way, you’re able to split up the disks, or the rights amongst different amounts of IO. And whether that’s a physical disk that you have a limit on how much IO can go on there, or whether it’s an Azure disk that you’re limited on specific IO per disk, well, figuring out how to get the best IO there for those drives can make a big difference.
Now we worked with a customer where we did this, and it turned out that they had provisioned some really slow disk for where we put the logs, and they had to go back in and up the amount of IOPs. I don’t remember what the terminology was in Azure, how they did it, but they had to change the amount of IO that was allocated on their log drive in order to be able to get the performance out of it, and as soon as they up that well, things worked a lot quicker on their entire system. So storage bottlenecks can happen under heavy workloads, and being able to split that up amongst the data and the log files on different drives can make a big performance impact.
Now, one of the things I like to look at is to go look at how much IO is going on with different drives. And you can do that with Database Health Monitor. Also we can go and look at it like IO by drive, and we can see if there’s a drive that contains your data and your logs and your TempDB, and it’s really overloaded on IO, well, you can split that up by moving those things around a little bit. Our next takeaway is on file growth settings. And this is one of those things that a lot of people overlook. But with SQL Server, when you create a database, it has a default of how those database and log files are going to grow over time. So what happens is that, and this is based off of your model database, and when you create a new database, it copies whatever settings were on the model database, and uses those in order to be able to create your new database. So if model database is set to something old school type settings like grow by one megabyte on the data file or even on the log file, or to grow by a percentage, well you can end up getting in trouble there. And I think one of them I’ve seen that’s pretty common is growth by 10% on the log file, and what you end up with is some really disproportionate log file growth as well as data file growth. And when those files are being grown, you’re ending up with blocking that occurs because every transaction, if it’s log file, for instance, every transaction that’s going on has to wait until that log file finishes growing on that database. It gets worse, even if it’s TempDB, because then anything, excuse me, Temp \DB has to wait and scroll. So the recommendation here is to avoid percentage based growth. Definitely don’t go with a percentage of anything, but like 10% can get bad. And part of the reason what 10% is that, let’s say you’ve got a 10 Meg database. You just created a new one. It starts out at that size, growth by 10% takes it to 11 megs. Another 10% takes it to 11.1 and then 12 point something, and so on over time. But then when you get up to, like, I don’t know, 500 gigs in size, well, the 10% of that would be 50 gigs, and 50 gigs is going to take a while to grow on your database file, and there’s going to be blocking that occurs when that happens, waiting for that growth to complete. So what we usually recommend is somewhere, and this depends, again, on how fast your storage is and things like that. If you’ve got really fast storage, you get away with a little bit bigger growth, but usually somewhere around 256, megabytes, up to a one gig growth size. If you’re on slow storage, keep it a little bit smaller. If you’re on really fast storage, maybe you can get away with a little bit more. But the point is, when those files grow, and they probably will over time, that’s going to cause some blocking that’s going to take, maybe take a while if those settings are too big.
Now another thing to consider here with growth is to be able to go and say, well, instead of having your files auto grow throughout the day when maybe transaction mode is heavy, what you can do is during a maintenance window or a downtime or off hours where maybe there’s less load, grow your files out to a more appropriate size. So let’s say you’ve got a 50 gigabyte database file, and you expect that over the next six months you’re going to grow to 60 gigs. Well, instead of doing a whole bunch of small growth events along the way, maybe the thing to do there would be to just size that database out to 60 gigs off hours, and then know that you’re not going to have any growth that’s associated with that. The other thing around this is to keep in mind, in the log files, there’s this concept of virtual log files. And if the growth setting is too small, you end up with a whole bunch of really small chunks called virtual log files inside of your log your log file for that SQL Server, and that end. Stuff causing issues when you’re trying to, like, run big transactions and things like that. Let’s take a quick look. Go back to the screen sharing here, and we’ll go take a look at the virtual log. This is we’ll just look at Temp DB here and see what how it’s set up for virtual log. Okay, so that one’s kind of boring, but you can see that that the log file total is about four gigs, and inside of that four gig they’re all sized about the same amount. Now if we did something like, let’s go look at a different database that might have a different pattern for the virtual log files. We go and look here. We can see on this one. It started out with some small ones, then we got some big ones, and about 123 megabytes. And then over time, a bunch of 50 megabyte growths, and we ended up with about 76 log virtual log files inside of that that one log file for that database. Now, 76 is not bad, but sometimes I’ve seen databases where these growths have been so small that you end up with 1000s or 10s of 1000s, or even hundreds of 1000s of growth or little tiny log files in here that are a little bit harder for SQL Server to really make good use out of Now, part of the problem with that is that that can really slow down the restore time when You’re having to restore this database, if you’ve got 100,000 little, tiny VLF files to create, it’s going to take a lot longer than if you’re creating fewer of them. So one of the things we can do, if we don’t like the pattern in here with Database Health Monitor, we can go in and say, Okay, let’s shrink this file as small as it’ll go. And let’s see how that goes here. And it was only able to shrink down to just the 11 files because the last one is in use. They’re basically serial here, and you can’t delete past the last one in use. And then what we’re going to do is expand the file back out to four gigs. Let’s go 4000 roughly four gigs, and then we can see we end up with what it finishes here we will end up with the same size of log file, but we have much less VLS or virtual log files, or the chunks or pieces inside of that file. And this is not a real fast server run, so that took a little while to do that growth, but you can see now we ended up with 27 instead of like 70 or 80 that we had before. Now I wouldn’t bother doing that if you’re in the kind of 78 or 80 or 100 range, but if you’re anywhere over 500 or 1000 or higher than that, you probably want to look at how you can clean up these log files there, because that will help with performance overall. Okay, so the takeaway on this would be to go check using Database Health Monitor and go check your file sizes and your VLF counts and see if you have any VLS you need to adjust there as well.
Another thing that we look at, it’s kind of indirectly performance related, but is configuring alerts. Now, technically, alerts will not directly impact your performance on a day to day basis, but where they will help is if you have a major outage, if you consider that a performance issue, which most would you want to keep up on those alerts, because with the proper alerting, you can catch things like IO starting to fail, or corruption being introduced in your database, things like that or failed backups. So our recommendation is to set up alerts for severity levels 19, 325, and for levels 823, 824, and 825, and those are all things that may indicate that you’ve got something seriously wrong going on with your server. And if those are happening, you want to know about it. You want to be able to deal with it. Now, keep in mind, though, with severity level 21 there’s one that occasionally comes out about invalid SSPI contacts. It’s usually a red herring. If you see one or two of those, it’s kind of a false alarm. But if you’re seeing like hundreds of them, it might be clue that your server is under attack in some way. So being able to set up alerts for those levels, as well as email notification for failed agent jobs will help overall in being proactive to catch problems before they escalate, and not directly related to making a single query run faster, but it will really reduce the overall impact if you have an eminent outage approaching.
Okay, next I want to talk about index rebuilding and avoiding the maintenance plans. So used to be that I would always write my own SQL Server Agent job to go and do custom index rebuilding. And then sometime back, I discovered the Ola Hallengren scripts to do index and statistics maintenance, as well as other jobs like checkDB. And since using that, we use the OLA scripts pretty much everywhere with all of our clients. And what that allows us to do is to be able to set up better jobs for doing that index maintenance. Now, the default maintenance plans can be overly aggressive, and through their graphical interface. It allows you to just say, oh, let’s do this, and let’s do that. You can end up doing a whole lot of unnecessary maintenance. And an example of this is working with a client. Two years ago, they had index and statistics maintenance running every night. It would kick off at about 11pm and it would go through and rebuild all of their indexes, it would then go through and reorganize all of their indexes, and then it would go through and rebuild statistics on all of their indexes. And that would finish up about 11am the next morning, and this was for a pretty sizable medical clinic. And what we found out was that prior to that 11am time, when it finished, all of those statistics were being rebuilt while people were trying to check patients in, and doctors were trying to do things in the system, and the system was incredibly slow because of that. So our fix for that was we got rid of the maintenance plans for doing the index and statistics rebuilding and reworking, and we replaced it with the Ola Hallengren scripts. And we use some smarter settings to say, only rebuild those indexes that are have a certain amount of fragmentation. Only reorganize those indexes that have a certain amount of fragmentation. Only rebuild statistics if there’s been something changed in that table. And what we were able to do was to take that index rebuilding job from nearly 12 hours and get it to happen in about two hours. They had just as good of indexes, if not better, after making this change on the fragmentation and all that. And the big difference was it got rid of the entire slowdown that was going on in prior to 11am for everyone trying to use their system. Now it still might have been a little bit slow between midnight and 2am but that was a maintenance window that it was okay to slow the system down a little bit that point to fix those things. So basically, the reason this really matters is that blind indexing can really waste a lot of resources. And when you’re using those maintenance plans, and you click something like, Yeah, I’ll rebuild them, and then I’ll reorg them, and then I’ll update stats. Well, they don’t tell you that rebuilding the index gives you the best index, cleanest, tightest index possible at that point, with the best statistics. And then it is just going to clutter it up a little bit more. And then updating statistics after that is just going to go through and wipe out all the work that was done to update and to begin with, and just go do it again. It’s really, really wasteful. So being able to use something like a custom job or the OLA Hallengren scripts that we use are really great in order to be able to do better indexing and improve a lot of the or remove a lot of the negative blocking and issues that happen while those indexes are being rebuilt by doing less work, only doing the work that needs to be done.
Okay, another one that we want, that I want to talk about is fill factor configuration. Now, what fill factor is, is setting on SQL Server indexes that says, when this index gets rebuilt, how much space are we going to leave available in each data page? Now a data page is an 8k chunk, and if you’ve got an index that’s 80k well that’s taking up 10 pages. If you have an index that’s 80 gig, well, that’s taking up 10,000 pages. By doing the math correctly, no, 80 megs would be 10,000 pages. So anyway, you can see that the number of pages used can change quite a bit. And what fill factor says is, fill factor says only when you rebuild, only fill those index pages up to a certain percentage of the space. So a fill factor of 80% says, when you rebuild an index, fill each index, index page up to 80% and then move on to the next one and leave some free space for changes and things that happen later. Now this was a really, really useful feature back in the old days of really slow spinning disks and things like that, but frankly, in today’s world where with high speed storage and SSDs and things like that, it’s not big of it. It’s not as big of an issue. So what we’re finding in modern environments with fast disks, SSDs, things like that, it’s actually better to have a higher fill factor, and we oftentimes go as high as 99 to 100% in order to reduce the number of data pages, and if we can adjust that fill factor and give you 20% less data pages after they’ve been rebuilt, what that means is that any of the queries that are using those indexes could be as much as 20% faster. Now, 80% was a great fill factor 25 years ago, but today it doesn’t make so much sense page splits and things like that that happen as you insert data are not as bad with SSD storage as they were with spinning storage, and we’re just finding that in most cases, we’re able to get better performance by increasing that fill factor number. So proper fill factor can balance the amount of. Of or can make your queries faster by having less pages it has to hit in tables and indexes can reduce the size of your backups, and it can also reduce the size of things or reduce the timing associated with things like index maintenance. So take away on this one, we go to Analyze your workload and see what your fill factor is on your different databases, and go look for a higher percentage or see if you can set them to a higher percentage, to see if that would help. That report would show in would show what indexes have greater than an 80 or, sorry, lower than about an 80 or around an 80% fill factor or lower, and you’d be able to go in and see what you can do to adjust those and improve them. Improve them over time.
All right. Next thing is backup, compression will improve performance. And I say this, I always caveat that with unless your SQL Server is like running at 99% CPU or greater all of the time, and most people aren’t running their SQL servers at that level. If they are, they’ve got bigger issues. But the thing is, if you’ve got a 50 gigabyte database, and you’re you run a backup, well, that backup without compression, is going to be about 50 gigs. Then if you turn on backup compression, you might be able to get that 50 gig database down to 20 or 30 gigs somewhere in that space, depending on what kind of data you have in there, it may compress differently. So what backup compression allows you to do is to run your backups with a lot less IO. Sometimes you’re going as low as 50% of the IO of a full backup. But even if you’re saving only 25% that’s a good savings as well. And what this allows you to do is to speed up the backup process, but it also speeds up the restore process, which is usually the more important time when you’ve got to do that emergency restore and you’ve got some boss looking over your shoulder saying, Is it done yet? Is it done yet? Well, that process will take a lot less if you have compressed backups now, the initial gut reaction that a lot of people have to this is look at it and say, Well, gee, isn’t that going to do more work, and isn’t that going to slow down the backup process by having to compress it and decompress it? And the answer is yes, it would a little bit if you were completely maxed out on CPU. But in a world where you might have a little bit of extra CPU and it doesn’t take much, your SQL Server can compress that way faster than what it takes to write its disk, and by compressing it and writing less to disk, it can actually get done faster than by not doing the work to compress it and writing it to disk there. Now there have been some problems with transparent data encryption if you’re using TDE, where compression did not used to work very well with that, but with SQL Server 2019, and newer, there are some settings on how you run backups that can change it so you do get good compression or reasonable compression, even if you’re using transparent data encryption, it’s a lot of information out there on that. It’s just a setting you change when you run backups, and basically faster backups means less downtime and quicker recovery when you have to do a restore. So the takeaway on this one is to go find out if you are using backup compression and if you’re not, to turn it on and see what kind of a benefit you get. Now, if you’re running, I think, SQL Server 2008 or maybe 2008 or two or older, this won’t be an option for you. If you’re on standard edition. And if you’re running web edition of SQL Server, which is another limited edition. This is not available to you either, so recommendation is to turn on backup compression and monitor and see how much you can save there and see what it changes in the duration of your backups and Database Health Monitor. There’s some reports that can show you the history of when backups were run and how long they took to run, and things like that, as well as the compression, and that’s something that can save you a lot of time. So go check that out. Give it a try if you’re not using it, and see how it affects things.
Next, we’re going to talk about read committed snapshot isolation level. Now this is the best thing that we’ve done with a lot of clients in the last two years for performance and what RCSI does and would it’s been around for a long time, but we only recommend using it on SQL Server 2019, or newer. SQL Server 2022, is even better for that, but RCSI minimizes blocking for read heavy workloads by using row versioning. Now, what that means is that if you, let’s say you’ve got a really busy system, and you’ve got some table that’s, I don’t know, think of it like maybe a login table where it tracks your username and password. In the last time logged in. I only bring that one up, as I’ve seen that a number of times with a lot of different clients. And if, as you log in, it’s querying the first once to say, Does this username and password match? And then once that login completes, it’s going and doing an update on that table to say, for this user, update their last login date to be this well, without RCSI recommitted snapshot isolation, what would happen is that while those updates were being done in order to set that last login date, well, people would be blocked from being able to read the table, and if maybe that it’s a really big table, or there’s a lot of update activity going on that can really bottleneck that whole logging process, in that case, and by being able to use RCSI, well, it makes it so instead of blocking, what it does is it keeps the state of the rows in what’s called the version store in TempDB. So if you’re doing an update, and even if it takes a little while to run and I’m just trying to query the table, updates will never block queries with RCSI, because RCSI keeps the state of it before your update was started and just gives me the results immediately. Updates do block updates. So if you have 50 people all trying to update the same table because they’re all logging at the same time. Well, you’re still gonna have issues with that, but it’s not going to block the first half of it in that example, where it’s going to verify, for instance, that your username and password is valid. So with RCSI, it is a game changer overall on performance. We have a podcast episode, Episode 14, that we did this year. Check that one out, but there are some caveats around it, and it is not ideal for long running transactions, or for if you’re in an environment where somebody leaves open uncommitted transactions. Now that’s not a good thing to do anyway, but let’s say you’ve got RCSI and you’ve got a whole lot of transactions that run and finish quickly. Well, your TempDB version store is going to go going to grow a little bit and then shrink, and then grow and shrink based off of how much versioning it’s needing to keep for those transactions that are open. But if you’ve got some long running transaction, like you’ve got some import process that keeps a transaction open for like an hour or two hours, or something like that, or longer, well what’s going to happen is that the version store is going to have to keep around all of the changes that are occurring during that period, so that any queries that are accessing that table are able to access it of the before state before that transaction was started.
So it’s one of those things that we have seen a little bit of trouble with RCSI, where if somebody leaves a transaction uncommitted, even if it’s something that’s not, maybe causing blocking because of what’s open, well, that version store needs to be kept around until all transactions are committed and closed out. So couple times we’ve implemented processes for going in and like looking at a reporting user and terminating a session if a reported user is running longer than an hour, things like that. But other than that, RCSI has had a massive impact for read heavy workloads, meaning lots and lots of reads that are occasionally being blocked by some kind of an update or a change. So take away on this is that RCSI, first you should go check out our podcast where Derek and I talked for, I don’t know, 45 minutes about different things related to RCSI. That’s season two, Episode 14 of our podcast, and that’s available at Stedman.us/RCSI. And the takeaway on that would be, go try it out and test it in a non production environment and see how it works for you. And then consider putting it into a production environment. Once you’ve tried it, studied it and learn more about it, you want to make sure that your TempDB is performing well enough in order to be able to use RCSI. But it’s one of those that has really made a huge impact. I mean, we had one client that we worked with that they were getting so much load, they were getting to a point that about once a month they would get blocking that would jam up their system for a whole bunch of users for 30 or 40 minutes. And we’d have to manually go in and stomp on some blocking queries to clean that up. We turned on RCSI, yeah, they still have a little bit of blocking that occurs with updates, but it clears in usually 30 seconds to a minute, rather than blocking for extended amounts of time, RCSI has been a massive win for all the customers that we’ve applied to Okay.
So the next one is monitoring, and you need to use a database tool to monitor or track performance. The tool we make is Database Health Monitor, and you need to be able to monitor key metrics to support performance. How often? I mean, gosh, let me jump back developer I worked with Gosh, 20 years ago now, he was this. He was he was funny. He was this kind of guy. We’ll just call him Wayne, but what’s his name? But. Wayne would go. And every change that he deployed to the website, he talked about, oh, wow, this is so great, and everything seems so much faster. And the reality was, even if the change slowed things down, as long as he talked about how much better it was, how much faster it was, the time the deploy was occurring. Well, everybody thought good stuff was happening, but then we get some monitoring in place that we can track and see what’s going on, and we suddenly realized that, well, and I’m not gonna blame Wayne on this part, because there were other people involved, but that things were happening, that as code was being deployed, the system would slow down. We would end up with more weights on the system. We would end up with more blocking, we would end up with more deadlocks and things like that. And basically, without having a way to monitor this, you can just say, everything’s great and nobody’s for the wiser. And you can say, well, I’ve made this change, and the system is so much faster. But, and again, Wayne wasn’t a bad guy. Wayne was just super optimistic on everything that was going on there. So I’m not trying to bash on him on that one by any means, but that’s one that and I’ve seen this happen with other clients we’ve worked with where you don’t know if something you changed made a difference. And like just yesterday, we had a client we were working with where we deployed an index, or we gave it, we found the missing index, they added it, they deployed it, and then the question was, well, how do we know if it’s making any difference? Well, that was an index that we don’t have direct access to see the queries that are being run against it to be able to tell if they’re faster. But what we were able to see was that on that database that the index was added, there was reduced IO on an hourly basis that started at about the time that that index was added.
So the key here is, if you’re not monitoring these things, if you’re not tracking wait stats to see if they’re getting better over time or worse, if you’re not monitoring blocking queries, those kind of things, you’re not going to know if your systems getting any better or any worse, and if it is getting a little bit worse every single month, well, you’ll eventually get to a tipping point, tipping point where it’s so bad that you may not be able to quickly react and adjust it. It’s easier to track these things over time, to know as things are getting bad, and be able to work on them proactively, rather than everything’s down and everybody’s blocked, and we’ve got to figure out how to fix it while somebody’s screaming you. So proactive monitoring is really the best way to be able to come up with understanding what your system is doing, and is your SQL Server health improving overall, and is there a performance degradation, or is it improving? And my recommendation would be to go implement some type of health monitoring tool keep your SQL Server optimized. And we recommend Database Health Monitor for that. And you can get that on the download page at Databasehealth.com so the key there is with performance, unless you can monitor and track it, you don’t know if you’re getting any better or any better or any worse.
Conclusion here, we’ve been through 10 different ideas or things that may help improve your SQL server performance overall. We talked about Temp DB configuration, getting it optimized on fast storage. We covered trace flag, 3226 in order to be able to remove excess backup files from going into the SQL server error log, but not backup files, but backup messages. We talked about, separating your data and log files that can be critical in order to be able to get high performance out of them. File growth settings, getting them set correctly and virtual log files on how that’s impact impacted by file growth, configuring alerts so you know if something bad is starting to happen to your system that may degrade or impact performance or take your system out. We covered index rebuilding and not using basic maintenance plans, instead using some custom script or the OLA Hallengren scripts in order to be able to do smart index rebuilding, we covered some fill factor configuration. Go take a look at what your settings are and see if maybe those could be increased a little bit. We covered backup compression. That one is seriously a no brainer. As long as you’re running on a version of SQL server that supports backup compression, turn it on, you will get benefit out of it. Read committed snapshot isolation, awesome on SQL Server. 2019, and 2022, or newer. Go check out our podcast on that at Stedman.us/RCSI and then Database Health Monitor. Go get a tool to monitor your database status and know how you’re going over time. Database Health Monitor is a great, cost effective way to do that. It’s available at Databasehealth.com, and that wraps up this episode. I’d like to encourage everyone watching to go and check out, go back and look at the sections that might apply to you in this podcast, and then go see how they apply to your environment. Maybe it’s as easy as trying one of these things per week. And monitoring the results and seeing how it goes. And the key here is you got to monitor those results, otherwise, if something you do makes things worse, you don’t know. All you know is you flip the switch and you don’t know if it got better or not. So also remember that November is Stedman SQL school classes month, and we have some tremendous discounts all month long on our classes. So if you want to learn more about SQL Server, check out Stedman SQL school at stedman.us/school or go to Stevestedman.com during the month of November, and you’ll see all kinds of promotions there. So this wraps up the episode. Thanks everyone for watching. We’ll see you next time. Have a great day.