DBA Fundamentals Configuring TempDB

in this module we're going to talk about configuring tempdb we're going to talk about why its configuration matters like an internal bug i would consider it in sql server how you configure a new sql server an existing sql server where you should put the files and then a few places to go for additional learning tempdb is kind of like a scratch area if you're a systems administration administrator you can think of it as say a swap space for sql server it's a place where it goes and scribbles things down that it needs to go and keep track of for example when queries are allocated workspace memory in order to run they may not have enough memory granted to them so they need a scribbling space to go and sort their data keep track of data for parallelism all kinds of stuff that place for tempdb or that place for sql server isn't the swap file it doesn't go out to the operating system swap file although sql server's memory can't swap but sql server tries to not go there on purpose it would rather go into this system database called tempdb tempdb is a database but the way that we use it is different see all those things over there on the left hand side they happen all the time continuously 24 7 oh goodness i had to mute myself there no don't worry i'm not contagious it's just that i have asthma i may have other things that are contagious but it's not the thing that you're worried about so i don't know that went to a dark place really fast or a funny place really fast all those things over on the left hand side they happen all the time constantly 24 7. you can run a thousand ten thousand queries per second all of which which need to do all of those things which means that sql server is constantly hitting tempdb but it's accessing it just like it would a database and this causes problems if i think about how often new objects are created and old objects are dropped that almost never happens in system databases it's not like you've got users in there constantly going create database or create table drop table create table drop table like kermit over on the keyboard it might happen in user databases sometimes i see workloads where people actually create new tables and drop tables inside user databases but it's still fairly unusual to see but in tempe this happens all the time all the time 24 7 sql server is in there like kermit at the keyboard going create table draw table create table drop table just continuously creating and dropping objects in order to support all of the stuff that i've got over there on the left hand side so as a result this causes a problem for sql servers internal architecture it was just never simply designed to handle creating and dropping thousands of objects per second it was never designed to handle constantly growing and shrinking thousands of objects per second that's just not behave normal behavior for what sql server would consider a user database or a system database it's just that they decided early on that they wanted to use a database to support all these activities well in every database there are a couple of special pages there's a page free space page which says which says basically for each of the spaces inside this area of the database there's this much free space available on each page so if you were going to look to add rows to an object here's where there's going to be space available for that object and then there's these s-gam pages shared global allocation map which says that if you're just going to go create a few rows for a brand new object here are some extents like 64k areas where you could go and add those and there would be space available for you this is super rare internal stuff this is about as internal z as i'm going to go inside that entire course and you don't need to know the contents of these things you don't really need to know what they need to do but just knowing that they're there this starts to complain or explain why sql server has a bottleneck with this every time that you go and create and drop objects inside a database you have to temporarily get locks on those objects those pages and sql server needs to grab really lightweight locks but there are only two of these objects per file per unit a size don't worry about it but just as the file grows larger you start to get more of these 8k pages well the problem here then is that whenever i'm trying to create and drop objects constantly these become bottlenecks inside tempdb all the queries a system processes whatever that want to go create or drop objects or load data or remove data from objects they need to go get a latch which is a really quick lightweight lock on those two pages on the pfs page and on the s-cam page this locking problem that only exists really inside of tempdb there are edge cases where you can see it in a user database but it's extremely rare it's just extremely common to see it over in tempdb these latching and locking problems are not about disk storage you don't have to get faster disks to solve this problem and indeed faster disks won't even help you when you're facing this problem the problem is that both of these pages are actually stored up in ram we're not waiting to read them from disc we're getting locked boy that went off topic real fast but you know you just can't not do that whenever you go whenever you get up like that so this is why you have this surely there'll be a clip of this somewhere um so anyway as ellen would say anyway these two things are up in memory now i just immediately want to do it again but now i'm so self-conscious of that these two ak pages are up in memory and this i'm never giving this session again without having 8k pieces of paper on my desk because otherwise i'm going to constantly be doing that joke i've never done that joke before trust me when i say this i don't sit around doing that joke i watch tick tock videos with it but anyway even though those two ak pages are up in memory uh we still end up having locking and latching problems on them now the fix is gonna seem really weird in order to solve it we have to add more temp db data files so if we add more tempdb data files we're going to have more of these pfs and s-gam pages and sql server will use them roughly evenly so fixing this problem avoiding this problem with tempdb is just a matter of adding several small files now hold on for a second if you if you've done any kind of googling before about how you configure sql server you probably read advice about that you should create a file for each core if you have a whole bunch of cpu cores the old advice when microsoft first discovered this problem the old advice was to say you should create a data file for every cpu core well that ended up causing problems you can have too many tempdb data files so they ended up revising that and they said oh you know what you just need a few tempdb data files all of a sudden a siren goes by my my office house it's not like i have a separate office i'm at my house so you just need more tempdb data files it's not about the exact number it's just about having masima sima cmos so in order to fix it in order to fix it what you're going to do is you're going to create between four and eight equally sized tempdb data files somewhere between four and eight some blog posts that you'll read will say you should use four some posts will say you should read eight i don't know where these two original numbers came from i'm fine with either one i don't care whether people create four or whether they create eight the exact number is less important to me than just creating massimasimasimas in the mastering server tuning we go into more details to how you go about monitoring tempdb to figure out whether you still have latch weight contention even after adding either four or eight files we show you how to measure that it's happening and then how to go about finding the queries that are causing it now when you when you go in and add mossy mossy moss if you're not sure about or what people will do is they'll say oh i must need much larger space no it's not about space they just need to have four or eight equally sized files so if you're dealing with a server that you're not sure how big the file should be just look at the existing data file size and i'll tell you how to go through and divide that here in a second then just make sure that the file sizes are equal because you can still end up with conte latch weight contention sql server does will use data files based on how much empty space they have so if i have one of my data files that's way larger than the other that one will end up becoming hot getting much more activity on it simply by the fact that its data file size is larger so that's why they need to all be equally sized so how do you reconfigure an existing sql server it probably only has one tempdb data file today what you do is look at the size of that divide that by four that's assuming you're going to use four data files if you use 8 then divide it by 8. then go shrink the existing data file size all the way down or to whatever target size you're going to use then add three more equally data equally sized files and then restart the sql server service restarting the service just guarantees that sql server is going to use all of those files equally you don't have to restart the sql server service when you do this it's just that you're going to get equal tempdb utilization across those pfs and scam files after you restart the server so it's not mission critical that you go restart it right away now where do you put these files sometimes people will say things like it always has to be on solid state it always has to be local well let's go through a few pieces of this one at a time first does it need to be on solid state well as i record this it's the year 2020. nothing should not be on solid-state storage in the year 2020. my phone and your phone have flash storage so should your database server sql server is two thousand dollars u.s per core per core per cpu course two thousand dollars so if you have an eight core sql server even just with standard edition we're talking about sixteen thousand dollars u.s hell yeah it should be on solid-state storage everything involving sql server should be on solid-state storage two the advance should it be on its own volume should you have a dedicated drive just for tempdb the reason why people will usually think of that is because if you tempty b grows by default people can do all kinds of nasty things in tempdb if they wanted to do a denial of service attack on your sql server they could simply load stuff into tempdb until it fills up there's not outside hackers doing that that's your business intelligence department that goes about giving you those denial of service attacks they'll go through and build these big huge ginormous temp tables they won't realize that it's actually filling up a disk and next thing you know your sql server is unusable because tempdb is full so the the advantage of putting it on its own volume is that you can say that when it grows it won't and it fills up the drive it won't impact other databases i never really bought into that because when it fills up stuff stops working so am i really saving anything i'm not too sure about that i get why people want to do it i'm just like if you really wanted to stop it from growing you could turn off auto growth and that achieves the same result so i'm not really sure if that's what you wanted to do the next two bullet points on there though different i o profiles for configurable storage that can help sometimes you can have storage that allows you to tune caching and access differently depending on which volume it's on so you may want to for example turn off read caching on tempdb and i'm just making this up but if your sand vendor says it wants tempdb on its own volume and it has different access methods from there sure knock yourself out in the cloud you can also possibly get a separate i o limit so with azure uh and amazon it's really common to see people put different things on different drives because different drives can have different iops different throughput limits the caution i would give you there though is that if you carve your sql server up into a whole bunch of really tiny volumes generally with both amazon and azure the size of the volume also dictated dictates its performance so if you use a whole bunch of 10 gig volumes or 50 gig volumes you're going to get really crappy performance across all of them in this class this class we're talking about servers at 100 gigs or less at 100 gigs or less i would really want everything on its own one vol everything on the same big one volume because you want to max out how much i o performance you can possibly get local nvme storage or ephemeral storage up in up in amazon's cloud or azure cloud this lets you use a separate bandwidth pipe for your user databases and a local bandwidth pipe for tempdb that just gives you additional free storage because even though people will say things like oh i don't really care about tempdb the perf the i don't care if it disappears on restart you care in the sense that if it isn't there and if the folders aren't there sql server won't create the folders so you just got to make sure that if you use local or ephemeral storage if the server restarts that the folders get created before sql server tries to distort them tries to start them otherwise the wonderful advantages of local and ephemeral storage is that they're insanely fast really low latency and in the cloud typically this lower latency storage is totally free i could go on for two three four hours on tempdb and there are people like bob ward who actually do but in the in the case of this fundamentals class that's really all you need to know in order to get tempty b started there are trace flags that you can consider playing around i'm going to link to these in the fundamentals in the resources page for this module two trace flags that you could consider applying for sql server 2016 and earlier for the scope of this class for the fundamentals class i'm not concerned whether people have that on or not it's not that important of an issue the third one for sql server 2019 sql server 2019 added this new feature called memory optimized tempdb metadata that's supposed to reduce the problems of contention intempd even further it's a version one feature and as of this recording there have been five cumulative updates for sql server 2019 out of those five three of them had corruption issues one of them involving in-memory optimized tempdb metadata there may come a point in time where i start recommending this feature but as of this recording july of 2020 i do not i would like to see a little bit more stability to the point where not every other cumulative update involves corruption issues after that i might consider using new features like this

Channel: Brent Ozar Unlimited

Views: 3,235

Rating: 5 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: YGMCUZ9oBV0

Length: 16min 45sec (1005 seconds)

Published: Thu Mar 11 2021