- Host: Steve Stedman / Mitchell Glasscock
- Topic: TempDB, Data and Log Files
- Recording Date: September 16, 2025
- Listen on Spotify
Stedman SQL Podcast Season 2 Episode 25 Data, Log and TempDB Files
In this episode of the SQL Server Podcast, Mitchell and Steve take a deep dive into the critical files that keep SQL Server running—data files, log files, and TempDB. They discuss how these files work, why their configuration matters, and common mistakes that can lead to performance bottlenecks. You’ll hear practical advice on sizing, placement, and growth settings, along with insights from real-world experiences solving SQL Server performance issues. Whether you’re a DBA, developer, or IT manager, this conversation will help you better understand how to keep your SQL Server environment healthy and efficient.
Podcast Transcript
Steve Stedman 00:16
Hey everyone, and welcome to this week’s Stedman SQL podcast. This is season two, Episode 25 and I’m doing things a little bit differently this time. Mitchell’s joining me, and we’ll, we’ll do the whole podcast introduction in a minute on our topic. But little bit of background, I’m traveling this week, and actually right now, I’m going through the panhandle of Texas, headed toward headed west towards Amarillo. Of course, I’ve already been there by the time this airs, but part of it, part of the way we do the podcast recording, is that we recorded about a week or so in advance, and then Shannon has time to edit it before it goes live. Take out those obvious mess ups and goof ups that we do. Make it sound a little bit better. But this one’s been a particular challenge, because we had, I think this is our fourth attempt, maybe our third attempt, to try to do it. The first one, I had technical difficulties because I was on a different laptop. And then the second one, we had customer issue. And of course, customers always take priority over anything that we have scheduled that’s not as much priority. So we tried it again, and I plan to be somewhere that wasn’t moving today, but my travel plans changed, so hopefully it’s not too bouncy. Gosh, this is a bad chunk of road here, so we’ll see how it goes.
So welcome, Mitchell. So part of Stedman Solutions and the business is making sure that we take the absolute best care of our customers that we can. And part of that is that I don’t ever get like, a true, completely out of office, completely detached vacation for like a week of vacation, because we’re always taking care of customers. So whether I’m traveling on my boat or whether we’re traveling like I am now, I’ve got Starlink, and thanks to Elon Musk and everything that he’s done with Starlink, we’ve got a really solid solution, although my camera might not look so solid right now, wherever we’re at we’ve got, I’ve got good access to help with whatever customer issues come up. Example of that was last night, customer called in. They had an issue they need help with right now, and I was able to help them out. So even though I’m traveling, and it’s technically not a real vacation, I’m getting out and I’m still able to help clients. So like I said, this is season two, Episode 25 quite a few episodes we’ve been through, but this is the first one that we’ve done filming in motion. And for the record, I am not driving right now. It is my lovely wife, Marcia, who is driving, because that would be completely irresponsible, not just slightly irresponsible, for me, to try and do this while I was behind the wheel.
So wanted to talk a little bit about our October promotion, because we’re almost into October now. With our October promotion, we have three one year of our monitoring service, our single server database monitoring service, with any SQL server performance or health assessment that we do. So one of those core products that we provide is health assessment or performance assessment, and for the month of October, if you sign up for one of those, you will get one free year of our monitoring with that, also, we had a little bit of a surprise with one of our recent popular episodes. I always track which episodes are getting the most views and things like that. And season two, Episode 23 where Mitchell and I were on the podcast together talking about Database Health Monitor version three features, was one of our recent more popular episodes. So thanks everyone for watching that. And if you haven’t seen that, check it out. That’s where we talk about all the cool stuff that was introduced in SQL log and Database Health Monitor version three.
All right, so at this point, let’s dive into it. Mitchell joins me for discussion on SQL Server data log and Temp DB files and a little bit of background. Mitchell is our lead developer on Database Health Monitor, and he helps with our SQL Server managed services in our performance assessment. So welcome, Mitchell, thanks for having me. So one of those topics that comes up again and again, whether we’re working with existing clients or we’re doing a performance assessment on a new client, is always how you can get more out of your SQL Server. And every SQL Server we work on has different limiting factors or different bottlenecks that we need to deal with in order to help make things faster, make things run better. And sometimes it’s memory, sometimes it’s CPU, and sometimes, and I would say a lot of the time, it comes down to IO. I. And I think that the IO, or the amount of things that can be read and written from disk, can be so important performance today. So what we want to talk about today is a little bit around data files, log files, TempDB, and why it’s important to lay these out differently, and some of the distribution strategies the real world impact that comes out of that is really faster queries, reduce downtime when you have to do an emergency restore, and better resource utilization. And some of the common pain points we have with that are really just people complaining that their SQL Server is slow, and the constraint ends up being disks. So let’s talk a little bit about the different files. Then Mitch, should we jump into that?
Mitchell Glasscock 05:47
Yeah, I think before we kind of jump into the specific files, I think it’s kind of important that it’s one of the biggest things that we look at when we first start doing assessments, whether we’re bringing on a new client or we’re doing an assessment for companies, is the base foundation, because the data log in TempDB files, these all make up the foundation of a SQL Server. I think it’s important to really hammer home how impactful the layout of everything can be.
Steve Stedman 06:15
Absolutely and I think I don’t know, I kind of equate that to, like, PC gamers, right? I mean, you’re one of those people who I think games more than I do, and if you didn’t set up your gaming PC correctly, you probably wouldn’t be very happy with the outcome of that, right? Right? Right. Now, SQL Server is the same if you don’t get it set up right to begin with. Well, you can adjust it, you can fix it, but you’re probably not going to be happy with the outcome until you get that adjusted and get it fixed. So I think that’s super important to know that getting that foundation right is just so important to the overall performance.
Mitchell Glasscock 06:57
Alrighty. So getting into those SQL server file types the data file Do you want to kind of explain the basics of what a data file truly is?
Steve Stedman 07:06
Yep. So with a SQL Server database, whether it’s a user database or system database or TempDB, you can have one or more data files. These oftentimes end with the MDF extension or n, d, f extension, but they don’t have to be called that. Some people just don’t even put any extensions on them, and they end up with a real funny looking file system. But the point is, these data files, the mdfs or NDFs, contain the stuff that’s in your database, meaning the tables, the stored procedures, the indexes, all those kind of things. And when you insert rows into a table where those rows end up are eventually for long term, for permanent storage, are in that data file, or the MDF or NDF file, but what you’ve got in that database file gets cached quite a bit. So everything that’s being read or written from that data file doesn’t always have to happen immediately when so for instance, when you’re running a query, you may be pulling that data in from memory, rather than from the actual data file every time it’s being run. Now if you’re on a SQL Server that’s squeezed on memory?
Mitchell Glasscock 08:24
Well, you might have to hit the data file every time things are being run, right and then from there, in the case of being squeezed on memory, what we might see, like you mentioned earlier, is more of that IO bottleneck. If everything’s having to run up from disk, if your MDF is on a slow disk, you’re going to have very poor performance in the IO category. Yep.
Steve Stedman 08:42
And I know most of the systems that we’ve worked on for performance help, they usually have much larger databases than they have memory to hold. So it ends up that a lot of queries are bringing things in from disk, from those MDF or those data files. So when that happens, one of the measurements we look at is page life expectancy. And it’s one of those that just kind of gives us a good sort of gut feel of, do we have our is the SQL Server keeping stuff around to reuse it in memory or is it always going to disk? And if we see the page life expectancy numbers getting bigger and bigger and bigger. That usually means it’s caching well. But if we see the page life expectancy numbers dropping to zero here and there as we have big queries, that could be an indication that we don’t have enough memory there, and those data files are getting slammed a lot harder at that point. Okay, did you have anything more to add on data files before we jump into logs match? No, I don’t think so. Okay, so the log files, these are the transaction logs and when SQL Server is running, when, whenever it’s doing a transaction, whether you’re in simple or full recovery model, the entire contents of that transaction, the state so it can. Be rolled back, is being logged into your log files.
So the difference there being that, if you’re in simple recovery model, your log files are being made available or to be reused after your transaction completes. Or if you’re in full recovery model, those that data in that transaction log is being kept around until a log backup occurs. So they’re really important for how your I guess, for the overall operation of SQL Server, because without the log files, the way that they’re implemented, there’s not a way to really roll back or undo a transaction. So when you issue an update, insert, delete, type statement in SQL Server. What happens is that all gets written into the log file before the transaction completes. It may not be completely written to the data file, and that’s why the log files need to be fast because, and we’ll get into the speed here in a minute, but log files need to be fast because they are being written to as part of every single transaction, whether you’re in full or simple recovery model. So if you have databases where there’s a lot of things changing, your log files are probably going to have a lot heavier IO than possibly that you might be expecting there right?
Mitchell Glasscock 11:17
So in a transaction, you’re saying it’s sort of a two step process, where it has to write to the log, and then once it completes that transaction, or commits that transaction, that’s when it goes to that data file. That’s the order of operations there, almost.
Steve Stedman 11:33
So if you say, insert one row into a table, okay, simple, simplest transaction you probably do is insert in a single row, and in doing that, that INSERT statement is not going to complete, or the transaction that it’s in is not going to complete until that INSERT statement has been written to the log file and you commit it. And at that point, even if the data file has not been written to disk yet, which there’s some time that it may take for that to catch up. As long as the transaction is completed, you know that all the data has been written to that log file. So with that, let’s say you did that INSERT statement and you were quick enough to just completely power off the SQL Server right after the transaction log got written to, but before the data file got written to the question that should come up there is, what does that mean for your transaction? So what happens in that case is, when SQL server starts up, it said it has what’s called a crash recovery mode. And sometimes you might see a recovery pending as the status your SQL Server, if it was started up after having the power turned off in a stereo like we just talked about there, where what the crash recovery does. It goes through all of the transaction logs and attempts to verify or replay them to make sure that your database matches what was written in those transaction logs, so that if something did get missed going to the data file, it would get caught during that crash recovery mode that happens there.
Mitchell Glasscock 13:01
Gotcha. So there’s a fallback from AX, maybe a power outage or something. If you’re a very transaction heavy database and you run into a power outage, you know that there’s some fallback point. Yep.
Steve Stedman 13:12
So the whole point is that once the data has been written to the transaction log, even if the data file doesn’t write, it’s permanent data at that point, even if the it’s going to be permanent as long as well. I, I really wouldn’t recommend just unplugging your SQL server right after doing something like that, but it’s designed in a way that it will recover correctly when that happens. So far, what we’ve talked about is we’ve got the data files, whatever extension you may have for them, and you’ve got the log files, and some people name them dot blog or dot LDF. I mean generally ac dot LDF. And this is different than the transaction log backup files, of course, but then we have TempDB files. So what do we know about Temp DB files?
Mitchell Glasscock 13:53
Mentioned their system database that is used for storing temporary objects, sorting and query processing. So pretty much anything you do is going to run through most everything you do in SQL Server is going to run through Temp DB at some point.
Steve Stedman 14:07
What’s interesting with that? And it just happened a couple weeks ago, people sometimes refer to memory tables or in memory only temp tables. And there really isn’t such a thing with like temp tables or table variables, unless you’re doing memory optimized kind of thing, but with most versions of SQL Server, anything that’s going to attempt table or a table variable is being written to the Temp DB files. And with Temp DB, of course, you have data and log files, and there’s recommendations out there for what we need, but we just want to make sure we kind of got the foundation here on these three types of files, because these all go into the conversation of, how do we make the SQL server run faster? So one of the big mistakes that we see time and time again, and I don’t know if we see this more with like Azure virtual machines as well, just because people don’t want to spend the extra money for extra drives with their but one. Of the common mistakes we see is that all of the files go on the same drive. You get your log files on, call your C drive, or your D drive, or whatever drive you to store your data on you. Let’s say you got your data files, you got your log files, you got your temp TB files, all on the same storage. The problem with that is that anytime work is happening, whether you’re writing your log or writing your Temp DB or writing your data files, it’s going to be all written to that same disk at the same time, and you’re going to be constrained by the IO limits that that disk has, right?
Mitchell Glasscock 15:35
So you’re kind of hitting it in a three step operation. If it’s all on the same drive, you’re going to hit Temp DB, those files there, then you’re going to hit the log files, then you’re eventually going to hit those data files, which, especially in a heavy Read Write, it’s going to slow everything down a lot, and that’s why there are drive layout best practices that we try and follow as best we can.
Steve Stedman 15:52
Sometimes the customer may have constraints that make it hard to follow those best practices, but we always push for that, and we always recommend for that. What are those best practices that we like to consider there?
Mitchell Glasscock 16:10
Mentioned for the most general, basic layout, we separate all three different data files. We’ll have one drive for or, sorry for all different file types. We’ll have one drive for data files, one drive for log files, and then one complete separate drive for Temp DB files.
Steve Stedman 16:27
And then the old school way of doing it was you’d have a different drive letter for backups. But we don’t like to do that. We like to make sure your backups are stored off systems. So in for the purpose of this discussion today, we’re not really talking about backups. Those should not be stored on a local drive at all. We’re just talking about data files, log files and Temp DB all being on different drives.
Mitchell Glasscock 16:49
Do you want to kind of get into the workload characteristics of OLTP versus OLAP?
Steve Stedman 16:55
Yeah, that’s an interesting one. So with OLTP, which is your online transaction processing, what you end up with is throughout the day or throughout the workload period, whether it’s day or night or whatever time you have people that are using the system doing work, you sort of end up with this, lots of reads, lots of writes, but sort of A steady stream of data coming into the system and a steady stream of load. And it may spike during the high points of the day and the low and drop between the low points, but it’s kind of a steady stream of lots of changes, a variety of inserts, updates, deletes, things like that. But then you look at OLAP or the different reporting type databases, and what these usually involve is some process that runs nightly or daily, in some way that goes and collects up a gigantic amount of data, I say gigantic because it’s never, nobody’s ever doing this with a small amount. When we see performance issues, they collect up a gigantic amount of data and collated and was sort and sort it and summarize it and do all kinds of different stuff to manipulate that, to get it in the format that they need for the reporting that’s going to be used that day. And what we see with those kind of systems is that at the time, all those big tables are being rebuilt on a daily basis, or re imported or recreated, we see massive amounts of IO constraints around rights. Because, of course, there’s a lot of writing going on, and then once that’s all done, we see a lot of reads happening on these big tables. Because that’s what’s happening with a little lab type environment like that, is there’s a lot of reads going on during the day for reporting or different things like
Mitchell Glasscock 18:39
that. I think the it kind of leads into is, there, would you recommend difference in the certain drive layout or drive types that you recommend versus each one of those processes?
Steve Stedman 18:51
Well, it really comes down to the biggest factor is, what’s your pain point? Where are you seeing pain? And if your version of a big amount of data doesn’t overload the IO on your system to the point that it slows it down to the point it’s painful, then don’t worry about it, whether you’re OLTP or OLAP, if what you’re doing is snappy, it’s fast enough. It’s never a performance issue. This may not matter to you so much from that perspective. Now, there are some disaster recovery scenarios we can talk about, but that’s later, I think. But for the fact of OLTP or OLAP, I mean, really, it comes down to is, are you seeing slow performance there? And if you are, then that’s where following some of the best practices around putting the data and log into empty be on different disks important, but the thing to remember is that they don’t always go on the same speed disks. Now, some environments you have tiered storage, where you have faster and more expensive storage and you have cheaper and slower storage. And what we see a lot is people try and go sort out everything with the cheaper and slower storage. Because they don’t always understand how much IO SQL server is going to have. So what we recommend is that Temp DB, because it’s oftentimes high IO, it’s shared between different databases that it goes on dedicated fast storage, or absolutely the fastest storage that you can get. If you’re seeing Temp DB Performance issues, we generally recommend Temp DB is going to be fast we’re be fast. Log files because of what we talked about, earlier transactions and slowing down the time it takes to get that transaction committed. We want those to be really fast. But for data files, oftentimes that may not need the fastest storage. I mean, and if we had, if you were constrained on budget, which almost every SQL Server we see, is in some way you would want the fastest storage for TempDB, the next fastest for log files, and then the slowest could be data files. So do we want to jump a little bit into the file distribution and configuration section?
Mitchell Glasscock 21:01
I think the general layout of using TempDB is the fastest, log is the next, and then data file is your can be your slowest, is kind of a good summary of how we recommend layouts with Stedman solutions for pretty much any customer that we come across. That’s what we lay out. But we also dive deeper into, you know, we look into which drives are really hitting those IO constraints, if you may. If you have the fastest Temp DB drive, but you’re still having some IO issues, there’s some other areas that we can we kind of dive deep into there with our services.
Steve Stedman 21:36
And that brings us down to two expressions that that I know most senior DBAs are familiar with. The first one is It depends, and because any of the stuff we’re telling you here, it may depend on your actual configuration, your workload and all that. And the other is actual mileage may vary, and that we can tell you that Temp DB needs to be the fastest, but at probably 95% of the time we see that it is, but there is, there are cases where that is not always the case.
Mitchell Glasscock 22:04
Let’s jump into some file distribution and configuration.
Steve Stedman 22:08
So generally, well, I guess which, which one of these you want to take, and which one should I take?
Mitchell Glasscock 22:13
I will start with the data file distribution. So that’s kind of the basis, like we’ve gone over. The basis of your user databases is going to be the data file. It’s where everything’s stored. We recommend using multiple data files for large databases to distribute that IO between the data files, especially if you’re reading and writing a lot to it. You’re unsure, even distribution of data across files is the best strategy that we recommend.
Steve Stedman 22:39
So with that, let’s take an example of you’ve got a big database, and you put it all in one single MDF file. It’s just one big file. There’s not a lot you can do to sort of distribute that over different storage. But let’s say you’ve got a big database, and you put it into two or three or five data files that are approximately that, I mean, proportionally sized, then you’ve got the opportunity that if your drive you’re on is overloaded on IO, you could move some of those, some of those parts of that database, off to a different drive without having to move that whole database file. You can just move parts of it right?
Mitchell Glasscock 23:19
Does that get into kind of the nitty gritty of where, when you’re writing to the specific data file, if you have one large MDF, and that caused some complications in it, sorting through and figuring out exactly where some of those data chunks need to go.
Steve Stedman 23:37
Well, you know, there is the whole data file. What do they call it, the allocation map and things like that. And I think that usually we’re more constrained on the IO from what we see. But that does apply when we’re talking about data file distribution with temp DBS and being able to distribute it out amongst multiple files. We’ll get to that in a little bit later section here as well. But I think that being able to distribute those data files, or to have multiple data files, so that you can get more optimized IO, maybe more writing happening there, and you can certainly move them to different locations if it’s needed. So next, we’ve got the log file configuration, and for people who have come from a different database environment like Oracle. I know there are Oracle or at least there used to be when I used Oracle. A long time ago, there was value to adding multiple log files into an Oracle database, but in SQL Server, there really is not a lot of value. And I’ll show the one case where a log file could multiple log files could be valuable, but generally it’s not valuable. And the reason is that SQL Server will only ever really make use of one log file for a given database at a time. There’s it’s broken up into ldfs or the different pieces of that log file. And or, sorry, vlfs, I said that wrong. Vlfs, the virtual log files that are parts of that and you’re only ever writing to one specific section of that log file at a time. Now, if someone looks at it and says, Oh, well, I’ll add five log files and I’ll hit five times the IO It doesn’t work that way, because there’s only one of them that’s ever actively being written to at once. So the recommendation is single log file for every single SQL Server to get them precise to something that’s a reasonable size, so that you don’t have to be shrinking them and growing them over time, because shrinking and growing of log files is an expensive IO operation, and it causes blocking and locking as we go, as it goes. So if your log file is sized big enough so it doesn’t have to grow and doesn’t have to be shrunk regularly, then you’re going to have the least blocking and locking associated with that. And you want to make sure that your log files are on good, fast, sequential write storage. And of course, it always depends on the workload. But the one scenario that I talked about that I wouldn’t say that would be a reason to have two log files, is that if you were in a position where you had, like, some really fast storage that was limited on space, and you wanted to take advantage of that, but you had the risk of running out of space, and then you could put a second log file over on a different drive that it could roll over into when you do run out of space on one that would be an option. That’d be a real I’ve never, I mean, I’ve seen maybe one scenario in my entire career where that would have been valuable, but it’s sort of the rare fringe case that there could be a valid reason to have two log files. But besides that, it doesn’t make any sense to have more than one log file on a SQL Server database or Temp DB database.
Mitchell Glasscock 26:48
Kind of wanted to circle back to the Getting your log file size correctly. I think another good tip is to make sure that your backup strategy and your recovery model is set properly. We have seen it where recovery model slips through the crack, or someone sets it to full recovery model and forgets about it, and then they don’t they’re not doing consistent log backups, and they see that data file just get huge, take up massive amounts of space on the drive, and they’re trying to figure out what’s going on. And it’s just because someone flipped a recovery model and missed it.
Steve Stedman 27:23
So if you’re in full recovery model and not doing the right backups on it, at what point will SQL Server stop extending that log file?
Mitchell Glasscock 27:34
SQL server doesn’t stop extending it until it’s out of space. Exactly.
Steve Stedman 27:39
It stops when it’s out of space, and if everything on there is on your C drive, well, that could be really catastrophic. You make it really hard to boot your system after that. But what happens if it’s on a data drive or a log drive, and that runs out of space? Well, that SQL Server, that database, basically every transaction, will fail at that point going forward until you free up that space. So not a good spot to be in. We’ve seen that. I’ve seen that a few times where somebody sets it up they don’t have the right backups going in, like, six years later, they run out of disk space, and they come and ask, Well, why did this happen? Well, because every transaction that you’ve written in the last six years events has been stored in that data in that log file and will not go away until you get the right backups in place, or until you flip it into simple recovery model, and then you flip it and you try and shrink it.
Mitchell Glasscock 28:25
And it can be a very time consuming process to shrink that log file if it’s grown for so long. Yes.
Steve Stedman 28:32
And on that point of time consuming process, on shrinking it, you’ve had scenarios, gosh, probably five or six clients over the last few years where something happened and they got into a scenario where one of their log files or even data files got completely bloated out for some reason, process run amok, or whatever, or backups weren’t running right? And we’ve created a script that we call shrink O Matic, and what it does. Because the thing is, you can’t just, let’s say your log file grew to 100 gigs, or, let’s see bigger, like to four terabytes, which is what I saw. Your log file grows to four terabytes. You can’t just shrink all that right away, because it’s really time consuming on a busy database to be able to shrink those files. So what we did is we created this script called shrink Matic that we set it up on a job, and it goes out and it says, Okay, how big is the file? Let’s just try and shrink off a small piece of that, like, say, 25 megabytes with data files or with log files, will shrink off the size of the last VLF for virtual log file, and it just basically slowly chips away at it over time. And couple clients we’ve had that we had to run that where it ran for three or four months to go in and clean up those log files. They got so bloated, and it took a while to get that cleaned up. And if those had been on faster storage, it would have helped, but it still would have taken a very long time to get those done.
Mitchell Glasscock 29:58
Another question, if you. Are, say, someone does let their log bloat out, and then they just try and shrink it immediately, even if it is just 100 gigabytes, and they shrink it, and now they’re stuck in the shrinking process for, say, 2448, a week worth of time in that shrinking process. Are they able to commit any transactions to that database, or is it just halt?
Steve Stedman 30:19
Well, it depends how you’re doing it, and what’s going to happen is, if you just try and shrink the database and there’s active transactions or to shrink the log, I guess the question it varies depending on whether it’s log or database. So which one were you asking for, the log file?
Mitchell Glasscock 30:31
Say someone left their recovery model incorrectly and then realized it a little too late, and they’re like, oh, I need to shrink this. And you just run off and shrink it.
Steve Stedman 30:42
So first off, you’re going to realize I need to shrink it, and your log file is going to be full. So you first off, you have to get it empty. And you get it empty by either backing it up or flipping the database back to simple recovery model and then back to full, or just leaving it in simple recovery model. If you’re not going to back up your logs, you should be in simple recovery model. So once you’ve done that, then you end up with, here’s this really big log file, but it’s mostly empty, or there’ll be some small chunk in there that’s being used. So if you say I want to shrink like 90% of that right now, well that’s probably going to block and get stuck and maybe never finish or give up or report at some point, but if you chip away at it in small pieces, that’s where you can say, I’m going to shrink it by 25 megabytes, or even by 10 megabytes, or whatever the last VLF chunk is, and that’s going to block for a limited amount of time. And then other transactions will process. And these shrink, shrink the next chunk, and it will block for only so much time, and then the next, and then, like transactions go through. So okay, so why don’t we talk about some of the real world tips and common pitfalls? I know we’ve been diving into a few of these that over here with our stories along the way,
Mitchell Glasscock 31:58
before we get into that. Do we kind of want to go over the file layout for TempDB? Just a real quick one?
32:05
Oh, yeah, yeah. Good point. Do you want to take that? Yeah.
Mitchell Glasscock 32:08
So we always set up Temp DB with multiple data files around eight to 12, and we always set them to the same size. We kind of get a general rough idea based on the drive size and the workload of how large we’re going to set those data files for TempDB, and then we set them all up to the same and then we set those growth increments all to be steady growth, not percent growth. So if we are seeing growth, we’re not going to ramp up and get some massive growth later on to avoid uneven usage. And then, like we said, placing Temp DB on that fastest storage available depending on your workload, you may need faster or slower, but that’s all gonna be your mileage may vary,
Steve Stedman 32:53
yep, which brings us back to your initial statement. And we always do it this way. We always do it this way, until we learn something else. That’s sort of our starting point, and then we can learn from that. Figure out how we need to adjust that. Does it need another data file, or does it need bigger files, or who knows what?
Mitchell Glasscock 33:12
From that point going forward, I should say our baseline for what we set up at the beginning before we can learn.
Steve Stedman 33:18
Good deal, and I think that that’s something that pretty much follows Microsoft’s recommendations for Temp DB sizing. And I know somewhere around like 2017 I think they changed the recommendation for the number of Temp DB files. But generally, you never want to have more Temp DB files than you have cores that are doing processing on your server and but then again, if you’ve got a server with 96 cores, you probably don’t need 96 Temp DB file MTV files. So that kind of 10 to 12 range, or maybe a hair more than that, is okay in that environment. So let’s jump into the real world tips and pitfalls then. And I guess one of the things that and this is where we’re going to shamelessly plug database off monitor, obviously, but there’s a lot of performance monitoring and performance tuning that we do where we make it really easy with Database Health Monitor and be able to see what’s going on over time with disks, whether it’s figuring out how your data files are growing or how your log files are growing, or when they got big or when they shrank, things like That, or looking at your desks and determining, are we bound on IO? Are we overloaded on IO somewhere? Do we have a lot of latency things like that? One also, I mean, one of the tips around this is use a tool like database, auth, monitor mock, to monitor and track those things. But then we also want to make sure you have your maintenance tasks automated. And I know you were just doing that yesterday for a client. Mitchell, you want to talk about what we set up when we do index maintenance and those things?
Mitchell Glasscock 34:47
Yeah, so we always go with the OLA Halla grin, maintenance tasks, whether for backups, index maintenance, his scripts are what we use in pretty much every environment that we work on. Hmm and automating them is really handy because it allows us to do more in depth stuff like index rebuilding, and better index rebuilding and backups than what SQL Server Management studios maintenance plans can handle. There’s a lot more customization that can go in to this create it for your specific environment.
Steve Stedman 35:23
And I think one of the big differences there with the OLA scripts versus the standard maintenance solutions or maintenance plans, is that with the OLA scripts, you can really have it just do the work that needs to be done, rather than kind of brute force going through and doing everything. And that can really reduce the amount of time it takes to do that as well as reduce the IO as well. So some of the common pitfalls that we like to avoid, and we usually dive head first into these anytime we take on a performance assessment, but we find out things that are not done right, that we want to change or get fixed in order to improve performance. And the first one would be placing all of the files on a single drive, and that leads to a lot of IO bottlenecks when things are happening, being able to distribute that out amongst multiple higher speed drives is really important. I think ignoring or maybe not even knowing about TempDB is one of those things that can really cause a lot of system wide performance issues. I think that there’s a lot of people who set up a SQL Server and maybe don’t know the best practices around temp TB, and that usually leads to a lot of pain over time associated with that. And then the other thing is over sizing or under sizing files, and which can lead to a lot of fragmentation or growth type delays, like if you and maybe even to make that worse is people who are shrinking their log files on a daily or weekly basis. I’ve seen this happen, where throughout the week, the log file will grow till it’s big enough for a normal workload, and then the weekend comes around, they have a job that shrinks it down to make it tiny, because cleaning up this space is thought to be a good thing, if not in this case, and then it takes time all week for that file to expand, and it’s your SQL Server is not running as fast as it would be if that file was big enough.
Mitchell Glasscock 37:18
I like to think of kind of a double whammy there, if you undersize a file, and you also leave percent growth on as well. Once your operations grow and you’re adding more and more to either your data files or your log files, each time your file needs to grow, it’s going to need to grow bigger and bigger and bigger increments, which can lead to some hidden performance constraints that you might not even know. That started from the beginning of setting up SQL Server.
Steve Stedman 37:44
Yep, don’t even get me started on percent growth. We can talk for 20 minutes on the pains we’ve had on that one. So okay, so we had a question come in on the website around how do I know if my Temp DB has bottlenecks, and I know you’ve been working on a lot of the Database Health Monitor features. Where would you start looking at that?
Mitchell Glasscock 38:09
Mention first thing I would do if I was looking at some Temp DB, or if I needed to figure out if TempDB is a bottleneck, is I’d figure out which drive my Temp DB files are on currently. And then I’d go into the disk latency, by our instance report and look at that drives, read and writes, IO operations, figure out if my IO is really a constraint in that area, or if there might be something a little bit more hidden that’s going on. Another place that I would look is, I’d break down the database tree and look at the Temp DB, historic the Temp DB use by hour, and the TempDB high usage charts. And those are going to be really good indicators of how much Temp DB is actually getting used. How big are these queries that are running through TempDB, and what are the operations that are really demanding a lot from that?
Steve Stedman 39:02
Yep. And with those charts, you to find out things like, what hours of the day Temp DB is a problem, for instance, or what queries are taking up 200 gigs in your temp TB database, and which ones are being squeezed because of that. So, yeah. So really, a lot of the stuff in Database Health Monitor will help you, will help you figure that out. Okay, so I guess then that wraps up. I mean, that gets us kind of the summary here. Let’s just go through some of the key takeaways. We want to make sure that we’ve got cover here. If you can only take away three things, what would be the three most important things you might consider intervention.
Mitchell Glasscock 39:41
I think for me, the biggest one is making sure your drive layout is correct. Having the data log and TempDB files on their own separate drives is the most important thing, and especially getting them off the C drive. Like we said, simple, throwing your recovery model to. The full recovery and not doing log backups, bloating out those log files, and then just all of a sudden locking up your C drive. First of worst case of all, getting that basic layout from the start is going to be the most important. I think next, the type of drives after that layout is going to be really important. Getting TempDB on that really fast drive and your log files on the second most fastest is going to be important in that area. And then monitoring with Database Health Monitor, of course, is another one for me as the developer
Steve Stedman 40:35
there. Yep, and you know, we’ve done some recent performance work that without that proactive monitoring, with Database Health Monitor to go in and really see where those issues are, I don’t think that we would have really fully understood the issues without all the tools we have in there. So that’s been incredibly valuable.
Mitchell Glasscock 40:53
Yeah, just looking at stuff from SQL Server Management Studio, it can kind of be like chasing a ghost. You know that there’s a problem. You know things are running slow, but trying to dive through each panel can take time. You might not see these things we really pointed out with database helpful.
Steve Stedman 41:13
Okay, so I think then that wraps us up. Like to say, join us for our next podcast, which I’m still working on the agenda for that one, so I don’t have that to announce yet, but please subscribe to the podcast. Click like and click that bell icon if you wanted more information on it, but subscribe for more SQL Server tips. Don’t forget that you can watch this episode and all the other episodes on YouTube and on Spotify, and you can use the URL stedman.us/podcast YouTube and stedman.us/podcast Spotify to get to those sites. And as always, if you need any help with SQL Server, you can reach out to us at Stedman solutions, and we can help Mitchell. Thank you for joining, joining me today on this discussion. I think it was a good conversation. Thanks for having me. Gosh, you’ve been on a lot of podcast episodes lately, so definitely appreciate that, and stick around for our next episode. And thanks for listening. Have a great day. You. Steve,
Steve Stedman 42:34
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.