Six Scary SQL Surprises — Brent Ozar

don’t look see I’ve did the best seat in the house by far because I get to look out at the water and the bridges and pretend like I’m not in here talking to you people man it’s fantastic eventually I’m just gonna be huh yes yes Brent get back to work alright so we’re a little early but I’m gonna go ahead and get started because this place is full anyway today I’m going to be talking about six scary surprises I do a lot of performance tuning consulting I parachute into the nastiest sequel servers you’ve ever seen and I have people pointing fingers at each other trying to figure out what the problem is and often it’s not anybody who’s involved there’s these other weirdo things that are at play here my name’s Brent Ozar I’m a Microsoft Certified Master which just means I make a lot of very expensive mistakes along the way and then when I see other people’s mistakes I can go oh yeah I broke that too once and I can tell you exactly how to fix that hopefully without making as many mistakes as I did when I got started when MVP which just means I like to talk a lot I co-authored 2008 internals and troubleshooting and I co-founded Brent O’s are unlimited with a couple of my dear friends who are actually here today a pass Enda at sequel in the city so today we’re going to be talking about things that are scary and our surprises some things you just look at them and they’re up to no good it’s very clear when you look at these things I don’t want to go anywhere near them sometimes it’s a little confusing you go what it’s not really that scary right how bad can it be sequel server is one of those things when you first look at it everything looks like it’s fine I love sequel server management studio because it doesn’t have any of those big ugly square blocks that Metro has or whatever windows UI they’re calling it today Lisa’s always looked nice and friendly but you have no idea if the servers on fire sequel server knows when things are on fire but all it ever does is just this little stupid play signal when everything’s going to hell in a handbasket we approach it and we think everything’s okay but in actuality we nobody ever dies but we spend long weekends slaving over things that we never should have had to look at it to begin with so I’m going to cover six things today that are broken or can really burn the bejesus out of your servers the first one that I’m going to talk about the change is coming from inside the house it’s based on that old horror storyline you know the call is coming from inside the house so often when we’re looking at you know the happy database movies everything’s fine people work together and nobody has permissions on production and they you know we talk about yeah you’re all laughing already that didn’t take long even before we got further down and I’m certainly not saying that red gate is one of the third party vendors it would only deploy good changes but we have those other vendors right surely not them but all right hi Hugo you can come in it’s okay I dropped the restraining order you’re good he gave me the best crackers aren’t you call those cookies those uh waffles stole waffles oh my god they’re good just cuz I was eating those yes now and now I owe him all kinds of favors because I’ve had his precious Netherlands waffles so we have these weird of third-party apps that make all kinds of changes and sometimes we’re the people who are making all kinds of changes as well when I look at a sequel server it seems like everything’s going fine even when I drill further down into there if I drill inside object Explorer often tables look okay and indexes look okay in this case I’ve got a customers table got dbo customers and I’ve got two indexes here one on customer name and one on date added and because I’m I was almost gonna say I’m a good DBA I’m a I don’t suck as bad as I used to suck I’m decent as a DBA I actually do have these on the right field so I X customer name is on the customer name field I learned the hard way so now I have a query on here select customer name from customers where the customer name equals Brent O’s R but when I look at the execution plan it’s just doing a clustered index scan it’s not using the index hey in about it’s not using the index that it should use for that table and I would have to know what’s going on in order to find out why that’s broken it turns out there are things that can happen behind the scenes with indexes this is way too small you can’t really zoom in on it so I’m going to show you a little deeper here select blah blah blah from sis indexes what is disabled your indexes can be disabled and you wouldn’t have any visual indication inside sequel server management studio even inside when we’re normally trolling through sis indexes we would have to go all the way down to the end but I only just picked out the is disabled field to show you something disabled does not mean that they get a special parking spot disabled means

something a little bit different than that and you’re probably wondering why someone would ever disable an index as it turns out when we want to load a whole lot of data very quickly sometimes people will drop an index but the better thing to do is just disable that index if you disable the index the schema is all set up still there come on down come on down um the schema is all set up there so all you have to do is enable it afterwards and we’re off and running you don’t have to recreate it from scratch unfortunately though this kind of thing is what usually happens someone will disable it or they’ll have an ETL process that disables all the indexes every night loads the data and then re enables it and someone breaks that nightly process so they don’t get enabled again or let’s not tell the third party vendors that we’re doing this but you know how those third party vendor apps you have that they say you’re not allowed to touch anything you’re not allowed to touch the script and they have all kinds of indexes that are utterly worthless you can use this trick in your favor you can disable their indexes is there still there and no one will know and then if it ever gets you in trouble for support you could just enable it again or say I don’t know that must have been the last guy I would never change the schema of your database it’s like a delicate flower it’s wonderful so there’s a few ways that we can get around this little nasty surprise one is to write a DDL trigger I’m a huge fan of DDL triggers I know database administrators aren’t usually big on triggers but these act whenever someone changes the schema of the database whenever someone writes an alter create table or so forth so how many developers do we have in here we have a lot how many database administrators do we have in here and you all sat right next to each other you’re not like pummeling it’s probably still early you haven’t had your coffee yet but as you oh my god holy cow yes come on in come on in she bra she brought a whole backpack full of stuff she’s gonna throw at me later um so what I often see developer what database administrators doing when they’re trying to trap developers they’ll create a DDL trigger so that when anybody does anything in the database like a create index alter index alter table it’ll immediately get logged and or email to them that stuff’s fantastic my SP blitz which we’ll look at a little further down later as well also checks and alerts you if you’ve got any disabled indexes anywhere in any of the databases then also one of the things that I like to set up a metric to monitor on is the number of indexes that are disabled because it should always be zero unless you’re going to do that thing with third-party vendor apps that we talked about and no one would surely ever do now when I say red gates equal monitor metrics red gates secret monitor will allow you to create your own metrics and share them with other people I’m to the point in my life where I don’t want any more points or anything on other sites so will someone please go create this custom metric and you’ll get all kinds of credit amongst the user correct co-create rouga community people see that’s genius where did you come up with that you could say it was all my idea none of that Microsoft Certified Master stuff so you can search for terms to things like unused indexes disabled indexes constraints at foreign keys that aren’t trusted and so forth you can simply click on these off of did I put the URL back up there yes I’ll have this again at the end of the slide sequel monitor metrics dot read – gate com so that you can download and extend stuff this is one of the things I like about sequel monitor a lot is that you can quickly extend this with the hard work of all your other co-workers out here who constantly get burned by all these bad situations another one that I really like and I wish that everybody would set up is number of tables that have no clustered index this is not one of those surprises because we usually see this coming from a mile away but I want to know the instant that someone creates a table that does not have a clustered index so that I can go back and hunt them down I do I’m often the mountain of villain in these monster movies as it turns out any questions on the disabled index thing before I go on yes yes when sequels when we disable the index – sequel server stop tracking metrics on that index yes because obviously inserts updates and deletes won’t hit that anymore so yeah yes in the back so the question was if you go into sequence server management studio and you right-click on a database you get these standard reports since like 2005

service pack 2 and it tracks a schema change history of people who have disabled things change things in the database it’s not always very reliable when we’re going through large database migrations and we’ll have hundreds of changes fairly quickly or we go for a long time and there’s no alerting built-in either that’s the only drawback but then you can get some of this information without setting up anything at all which is nice more questions before we go on to the next monster so the next one is an awful awful cannibal and I run into this a lot it’s not very well understood it was it’s actually better understood in the exchange community than it is in the sequel server community I’m going to show you the world’s simplest graph for sequel server it really boils down to these four boxes here our end users are going to ask the sequel server for data sequel server runs on the CPU he’s either going to get the data from memory don’t you hate it when people touch the laptop screen ah it’s not my screen um so so I can’t have nice things feel sorry for the next presenter haha hold on to me my chocolates out no so the sequel server is either going to get the data out of memory or it’s going to get it off of the hard drives and this really boils down to the size of our memory and the size of our data if we have 64 gigs worth of memory we have a 100 gig table I didn’t say database it’s a table just to keep things interesting or to keep things simple we’re going to scan across that hunter gig table from start to finish if I can’t cash it all in memory the speed of that table scan boils down now how fast I can get the rest off of disk a hundred gigs if we store as much as we can in memory a hundred minus 64 is 36 gigs worth of data that I’m going to have to get off the drives the fastest your query can finish is however fast it takes to get 36 gigs worth of data off the drives it takes a long time to copy 36 gigs of data off of hard drives my session to past this week I relate these down to real-world numbers if you are a doing a brisk walk at five miles an hour which is a lot faster or lot slower than Yanni went on her half marathon yesterday if you were doing a brisk walk at five miles an hour getting data out of that’s like getting data off of hard drives whereas getting it out of memory is the speed of light that’s the same differential there between these two would you rather be walking or going the speed of light so it’s really important to me that I cache as much as I possibly can and there’s a lot of advice out there why you should maximize your memory by not remote desktop again to the server by not running sequel server integration services on the same machine as your server don’t run SSMS from the server remote desktop in somewhere else this is the most important memory that you have anywhere on your sequel server you need to use it to cache data and of course get as much memory as you can afford this used to sound ridiculously crazy expensive but for those of us who are going to pass we’re going to blow them blow more than this on j├Ągermeister this week let alone any kind of performance tuning stuff this used to seem crazy expensive but it’s nothing compared to Pat’s camera bag I mean Pat has more lenses than that in his camera bag but there’s another problem and we kind of have to dig a little deeper for it this is windows task manager and I’m looking at I like clients like this I mean I like all my clients as long as I pay the bills I don’t like the ones who don’t pay the bills but so I’ve got 80 cores here and one terabyte of RAM yes but it’s not always that pretty right a lot of us are kind of budget constrained and we don’t have all the much money in the world so it looks closer to this we’ve got a four core server with about 64 gigs of ram in it and let me zoom in a little bigger so that you can see that there it’s a little bit of a problem here we are used to monitoring available memory that looks like we’ve got a lot available but what’s this free number as it turns out with 64-bit versions of Windows when we remote desktop into the server and we copy files we drag and drop a backup file we draw drag and drop a trace file send things to each other download a service pack and copy it from one location to another Windows will helpfully cache that for you using your memory that memory is not really available even though it’s included in this number it is available to the OS to do things with not sequel server the memory that’s available to sequel server is this when you remote desktop into one of your sequel servers it’s having performance problems the first thing you

should do is go look in task manager and look at the amount of free memory not the amount of available memory because available memory really is not this problem was a surface more on the exchange team first because they really need memory even more than we do and this wasn’t a problem for us back in the 32-bit days it’s a huge problem in 64-bit days I’ve got a little example of that there just so that we can remind ourselves I’ve seen it get worse so this is a cluster if I bet a lot of us in here have clusters have like an active/passive cluster where one node is sitting passive at all times waiting around to failover and I bet that’s the node that we Remote Desktop into and we run things because we think we’re not hurting anything because it’s not doing any work but we’re just ramping this number up and up and up the cache number continuously gets higher and higher and when you fail sequel server over to this particular node sad trombone all of a sudden your sequel server is not going to run well at all because it’s only going to have a few megabytes of memory to play with because we never reboot our passive node it’s really funny for the longest time windows suck so bad that we were like well let’s reboot our server every week then we got smarter we’re like Oh Windows is great we shouldn’t ever have to reboot our server everything that’s old is new again just like a lot of the clothes that I wear that I refuse to throw away cuz skinny ties came back in style yes um not that I’m really that excited about skinny ties but now I’m seeing more and more people regularly reboot their servers especially the passive nodes to fix this problem to free up memory because yes you go it’s yes there’s a great that’s a great question I’m glad you said that so there’s two ways you can fix this problem one is to use an undocumented utility from mark russinovich so the guy behind sysinternals tools at Microsoft it’s undocumented it does work I’m just not wild about running undocumented utilities on my sequel server because you have to leave it running as a service in order to stop this the other way you can do this it’s really interesting how this works I can copy a file that say 50 60 gigs and then delete it and Windows is smart enough to free up the cache memory if the file was deleted a little odd right and of course the other way that you could fix it is just don’t Remote Desktop into the server and don’t copy files oh yes go ahead does the question was does it just remote desktop or SMB so it’s anything that’s done from that server it can be a scheduled task something that runs as a command periodically as long as it’s done on that server and copies a file it’s going to be a problem there are utilities like xcopy for example has a command-line switch that can do an unbuffered file copy it’s slower but then we don’t run into this problem ideally what I do is I just remote desktop into a different server and pull the file off of my sequel server and then it’s not a problem yes to the operating system yes to sequel server no windows can crunch this out of the way but if sequel server looks at its target memory it’s not going to go ahead and say oh I’ve got this additional memory that I’ll take advantage of it stops there if you’re ok with waiting weeks yeah you’re good yeah yes eventually if there’s enough pressure from cache to where Windows is going to start paging out yes but not stuff like sequel server yes Mike I don’t but here’s where you can look for why I keep text data graph if you search for Windows too much cash there’s an excellent blog post that goes into and I think I’ve got it under if you go to Brent Ozar comm slash go slash file cache there is an insanely dry blog post from them from the windows team that’s about 20 pages long that goes into in-depth and it may have something about PowerShell remoting don’t have yeah I think I said don’t ask Brad about PowerShell so I have this religious feeling about PowerShell uh it’s it’s a lot like you know everybody thinks they’re a good driver but everybody else sucks I don’t need PowerShell but I think all

of you should should have it wonderful and I I just don’t work on lots of servers at once usually people call me in to fix one server you know they don’t bring me in to fix a lot if I was still a full-time DBA managing a lot of servers oh I would be mr. PowerShell I would be all over so here’s my half a plan I think you should learn everything all of it don’t stop learning any of it so here’s how you’re gonna fix it monitor the free memory amount not just the available most software out there will only monitor the available memory and that task manager shot that I showed you is actually out of Windows Server 2008 2003 doesn’t even show you the amount of free memory which really sucks sets equals max memory size there’s a couple of different formulas out there on what you should set that as mine is probably the simplest there are more accurate ones that you could use I just start with 10 percent free or four gigs whichever is greater that should leave memory on the table for you you should not be using this much memory at the OS level you can always crank that up higher later over time just start here especially if you go in an internet explorer from the server run YouTube and all that stuff especially cuz you’re watching my videos who could blame you and then finally don’t Remote Desktop into the sequel don’t don’t server don’t run files over don’t copy files over run us a semester none of that stuff stay out of there that’s not what it’s for I didn’t say run Windows core Edition that’s another problem altogether next thing that I’m going to talk about every but he buys these solid state drives remember the the problem that I came up with earlier we have that very simple graph well if walking walking really slowly as opposed to running the speed of light what is it with good solid state drives well it’s all over the place and it really depends on who you buy them from and how well you set them up boy is it just me or is it hot in here god that’s we’re on fire yes not exactly I’ll try to be more gentle and quiet so these are really typical deal fusion-io drives are supposed to be blazing fast you put them into your servers off you go OC z Intel all of these are exactly the same thing and my customers do these because they’re a good deal relative to typical sands but here’s how the lifecycle on that goes people throw it in turn around six months and all of a sudden everything’s back where it was before they don’t understand exactly what’s going on the best way that I can illustrate this is from Barney and How I Met Your Mother I did not come up with what I’m about to say he did so if it’s offensive it’s his fault not mine we’re explaining that so he says if a woman is this hot she’s allowed to be this crazy and women you could say the same thing about guys who although guys were always usually just down on the crazy scale were not all that hot at least Miam I’ve seen myself getting out of bathroom it’s nothing on the hot scale it’s just the crazy scale so if a woman is this hot she’s allowed to be this crazy we put up with solid state drives because they are way hot but they are also way crazy there are a lot of problems with solid state drives the first off fusion-io their drivers require a very large amount of memory and you have to set that aside and leave it not take sequel server up to huge amounts of memory this amount of memory that they need is based on the sector size and the drive size this can be 50 60 80 gigs worth of memory that you have to leave free just for the drives it is detailed beautifully in their manuals they do a really good job of explaining how you alkylate that but I know how you guys feel about manuals you learned that from books online right you go good now I’m just gonna put the CD in and go with it and then I’ll call the consultant later which is why I love all of you people yes you’re great because I read the manual some most salty drive it’s not sure I don’t read the manual either I just do after you hold me and then I read most won’t stay dry spilt slow up as they fill up slow down as they fill up what do we do we throat empty beyond there lots of files grow it out to the size it’s supposed to be and it went blazing fast when we first tested it with sequel IO then it doesn’t seem to be as quick once we put it into service mmm golly go figure then finally a lot of the solid state drives that are out there slow down over time a lot over time but thankfully we can monitor this slowdown over time is really interesting too so if you go into the technical manuals again I know you’re not going to do it so I’m just going to tell you how this works so for a lot of drives out there like the sand force controllers they will tell you that you actually have to pull it out of the server periodically so you have say four drives in a raid array you have to pull one of the drives out go put it into a Windows machine that doesn’t have any kind of raid erase it clean it off put it back in the array wait for it to

finish recovering and then pull the next drive yeah none of you were nodding like you knew it right yeah this is the fun long with solid-state drives and it works and it does make a tremendous difference in terms of performance so there’s counters that we’re going to have to measure to make sure that we’re actually getting the stuff that we paid for first off average seconds per right and seconds per read this is how fast your storage is responding there are other counters that go for the amount of load this is strictly how quick your storage responds to the load for temp TB writes which is often what I see people using these solid-state drives for if it goes higher than 20 milliseconds for writes you have a problem and you’re not getting what you paid for out of it they should usually when you put these things in they’re closer to one or two milliseconds and then over time they just drift right start back up they’re in the 20 millisecond range you’re right back where you would have been with magnetic drives you need to start doing the pullin shuffle or look at how much memory you’ve actually allocated to these drugs any questions about this one yes go ahead exactly so trim is not supported by raid controllers RAID controller sit in between the OS and the drives when the RAID controller hears the trim command he just goes ah yeah whatever give me the next command and does not pass it down to the drives yes I’ve had so I had one particular client who went and bought a whole bunch of very expensive solid-state drives and this is not the problem at the manufacturer this is the way that the company decided to deploy him with not enough memory involved dedicated to the drives their average rights were two seconds not milliseconds but two full seconds to the point where we did a quick stream of tests they would have been better off putting temp TB on the C Drive of the server then they were on the solid-state drives and for a short-term fix that’s exactly what we did put it on the C Drive pull the solid-state drives out reconfigured things dump them back in and off we went to the races which if any of you are consultants in here that’s how you can look amazing without having to have them spend any money and all of a sudden everything’s faster you don’t have to tell me heard it for me all right any other questions on solid-state yes mmm that’s a great question so it depends on your workload kendra little one of my partner’s of Raynaud’s are unlimited um on sequel cruise how many people do we have on a sequel cruise this is just Devin the back oh and Mike yeah his tube has been and Yanni where I keep forgetting you’re up front here yeah you’re all quiet down well he’s you’re working on your phone quit babe mix for paying attention making notes yeah right um yeah whatever so yeah we’re even was I oh so she see cuckoo she demonstrated that sometimes they’re better off for data files in the are for log files if for example we’re not able to keep all of our data in memory and we’re able to satisfy like blocking transactions by getting them off disk fairly quickly most of the time though I see them best off for log files if you have a lot of databases on one server the magnetic drive suck at random writes where a solid-state drives are actually pretty good with that more questions all right oh yes yes yes hmm Dan so that one fusion-io doesn’t have to trim problem although they do have other installation problems that you have to be aware of I like fusion-io but it’s hard to find their documentation sometimes so here’s what you look for if you have fusion-io look for the documentation for the hpi-o accelerator and also look for the term sequel server with it HP all they’re doing is they’re taking fusion-io stuff you’re recording this and I’m gonna get in trouble for this and they’re adding on a price I’m sure it’s completely fair and legitimate given their business whatever and then they’re rebadging it and putting the other documentation for it or whatever specific for their blades and their deal create ease and all good stuff you search for that I think their documentation is easier to read than fusion iOS I’m going to hell I know I’m going to hell this is why vendors never call me for a coffee so that’s one except read a read a can read gates adorable an an tech an anti calm has fantastic documentation about solid-state drives and they explain in detail the problems with we’re leveling and trim so guides talking we’re leveling and trim so that is a separate set of topics every time that the onion tech does SSD reviews they talk about the problems inherent with the drive whether it has issues with we’re right

leveling or trip now taking a picture of me are you I’m not that attractive yeah that’s what I’ve done originally all right so we still love you but love is a strong word yeah yeah yeah I have a face for radio right yes yes ooh that’s it depends on the sand so some sands are smart enough that they understand how this stuff works and they do their own internal right leveling and trimming if you the lower you paid for your sin if it’s not yeah so what I see people is doing buying a ten thousand dollar sand and throwing solid state drives and they’re expecting him to go fast if it wasn’t built for solid states I would do the pull and trim thing yeah so we don’t get to see individual reads and writes inside the sand so it’s hard for us to tell what I would do is I would still continue to monitor it at the server level and of course one way you could do it I would be a bad guy if I didn’t say red gate has this already built in in force equal monitor is it’s a call right now for red gate would you like to buy the product so red gate already has stuff like this built in and has nice little spark lines I’m a big fan of Edward Tufte for little spark lines for things like response time and it’ll tell you how response time is on every single database I can look at it unfortunately I have to scroll down a little this is why I give the whole complete slide deck for examples if I scroll down and hit discs I get average read and write response times and I can pick the time duration I can say over the last month in which case we’ll see our little solid state grabs on the blip and it happens universally it’s not any one vendor they all suck like this any questions on those yes yes yes absolutely and I think even if they’re only using a small part of it for like caching you’re still going to see the stuff go boo boo boo because the other problem that we have is ass and administrators will initially by solid state and they won’t buy enough of it for caching and then over time as people add more lumps and add more data our response time starts to suck that that’s a good question so there are some manufacturers manufacturers because remember solid-state drives have a limited lifetime in terms of rights the more rights that you do the drive gets burned out man you would be surprised so I’ve had a lot of clients oh goes back to the hot and crazy thing like sometimes we have really bad luck and of course it gets even worse because what do we do we buy all of the solid-state drives at exactly the same time we put them in a raid array that is all getting written to at the exact same time and when we have failures they just go I had one client who the whole IT team was on the way out to lunch about 11:30 on the way out the door they got one email saying up HP system in site manager says we have one drive just fail we’ll replace it when we get back to lunch the hot spare will take over before they could get back from lunch another drive had failed and bomb the entire raid array because they’re just not as reliable as I would like them to be now does performance eventually go over time over trimming I haven’t seen that it’s been as long as we take care of it with trimming and leaving enough memory I’ve been able to get consistent performance throughout so some do fusion-io for example claims that their drives failing to read only if you go through my blog when I first initially tested the first round of models that came out I could get him to drop offline completely I mean they would just disappear so I would treat them exactly the same way I treat a hard drive you don’t it’s not the only you know biblical copy of the truth it’s gonna die sooner or later and you’re lucky if it fails reopen more questions about solid-state how many people are using solid-state in their sequel servers well it’s a few yeah all right I’m gonna blow past a few of these oh yeah yeah 50 60 70 80 gigs of memory it has nothing to do with the percent of the host OS it is a hard-coded amount that’s based on the size of the drive and the sector size on that drive the more sectors that you have the more memory it needs or more yeah yeah yeah yeah horrific and of course Plus – what do you do you buy pairs of them to put them in merit pairs you don’t just buy one so then you have to calculate that across all of them and as long as you’re playing around with those you need to be aware of how much power they draw as

well Kendra’s been doing testing with some of the other vendors and we’ve been able to have the power supplies on the server’s brownout you know the server will go offline because we’re trying to write everything simultaneously striped across all the drives so yeah we have lots of fun the older I get it’s just the uglier my surprises get yeah nobody ever calls me for hey man we left Auto shrink on ya know nobody seems to call me for them you guys read those blog posts you learn that and then you call me for the hard stuff so the next scary surprise time I talk about is temp TB dragging a leg you know like those nasty monster movies where you need to talk a little bit about internals first whenever sequel server stores anything on disk or in memory it does it in 8k pages I’m illustrating this in one little spreadsheet cell here usually we store them in groups of eight eight eight K pages at 64 K a pop this is called one extent now back in the old days with sequel server disk space was very expensive and when you wanted to create a new table sequel server didn’t give you a whole extent it gave you one page at a time until you’d used up eight pages then sequel server goes oh I understand you’re important about this you’re actually going to load a lot of data from here on out I will give you whole extents at you get 64k at a shop so our data files look like these big old messes of groups of 8a K pages usually it’s all one per object but not for times when we’re creating a lot of tables if we’re creating and dropping a lot of little bitty tables we’re going to end up with these things called mixed extents in the database so I’ve got a few of these here that can be shared between several different tables if I go and create a new table sequel server scans across here and says ah I have a little space here in this extent this mixed extent which is shared between a lot of objects I have this one available I can land your temp table right here for the first couple pages and then four over here for the next couple pages based on how much is going on at the time whoops too far there there is only one page per four gigs that tracks where these mixed extents are at only one page so every time I need to create or drop a temp table I’m getting or expanded by a few pages I am getting involved with this one little guy right here this is why people tell you to add additional temp TB files it is not because there’s some kind of magical load balancing going on we just need more of these so if I have four files for my temp TB now I have four of these s gam pages shared global allocation map pages to help balance out the load of where do I go create these initial pages for objects but not if I do this if I don’t keep the site of the files all the same size by in one file up here that’s small and one file down here that’s big this guy is going to get a whole lot more action than this guy I’m not making jokes they’re not the big guys get more action believe you me this is why I get why am I being taped for this me and I’m going to regret this for the rest of my life red gates going to put together a highlight reel of everything I said bad here and it Chicago this is why we can’t bring him back so there are a lot of ways that temp TB can go horribly wrong only one temp TB datafile because then at that point we only have one s gam page for the first four gigs that’s going to be under heavy contention too many temp TB files can also cause you a problem out of balance temp TB files where they’re not all the same size and not all growing the same way there’s a lot of things that can cause us these problems but the cool thing again is the sequal Monitor has a lot of these checks built-in and there’s more if you search for temp TB over on the custom metrics site temp TB allocation contention this check will tell you if you’re running into problems with that s gam page and you need to add more files to the Mort MTB files to that database Mort empty because there only one database at MTB files otherwise it’s extremely difficult to catch this and you’re just going around the blog trying to find out how many files should you add and if you go to the extreme of adding one per core that actually makes performance problems worse unless you’re undergoing temp TB allocation contention how you start and configure new servers from scratch start out with just four data files but the important thing is that they should all

be equally sized and they should all Auto grow in the same amounts there’s some trace Flags we can play around with for that we have to keep watching this because every time sequel server brings a new feature out lately they’ve all been using temp TB always-on then you always on availability group stuff store statistics and versions in temp TB so just when we think that we’ve got our temp TB sized appropriately all of a sudden we need more throughput on it so it’s something that we can never stop monitoring if questions about temp TB before I go on yes in the back it’s a great question so 1118 trace flag 11:18 is related to temp TB use most of us are recommending that you go ahead and turn that on by default now and roll with it I hate doing that on slides because I hate having I really like to think a sequel server is like a modern airplane modern airplanes are so advanced they practically fly themselves there’s only two things in the cockpit there’s the pilot and there’s a dog it’s the pilots job to feed the dog it’s the dogs job to bite the pilot if he touches anything unfortunately sequel server doesn’t have a dog and we go in there and we play around with all kinds of things not understanding the ramifications if you don’t need something don’t touch it 11:18 is one of those where I think most people need it but I’m hesitant to recommend it by default because what’s going to happen is three years from now Microsoft’s going to put out an improvement to sequel server you know RT or whatever they decide to call it next we won’t need it but I’ll be on tape forever saying everyone should have it so if you’re watching this in 2012 2013 or 2014 you should probably have trace flag 11:18 turned on but it’s time limited don’t touch it and keep out for the I’m the dog is what it ends up being I am the one who ends up having to bite people when they touch things it’s not going to last one your tables are larger than they appear going back to our little spreadsheet demo illustrations here let’s say that I’ve got an 8 K page I’ve zoomed into that cell and this is all just 1 8 K page and it’s my employees table for Brent o–‘s are unlimited of course I was number one because it’s named after me that’s how that kind of thing works so I’ve got my you record in there I’m ID number 1 brent o–‘s are my home address where you send me flowers and cookies my phone number and all that good stuff well as we grow we add in more people into this table and the more that we add the more full this page becomes so we’ll add in my last tire was Bill Gates he was a kind of board and so I put took him on if I need to update something here at this point if all of a sudden say Kendra changes her address or Jess you know goes from 321 half marathon way to 321 marathon way when I change something in here there’s no space left if I do an update on this page I’m kind of screwed so what sequel server has to do is tear that page in half and put some of the people on one page and some of the people on the other page I’m up there going Bill Gates I’ll always love you but we’re further apart now this is very expensive for sequel server to perform page splits are very io intensive for sequel server to do this there’s a lot of reasons behind that but in order to fix that what a lot of people do is change fill factor fill factors the number that controls how packed in your pages get upon index rebuilds so if you set it at a much lower oh I’ve got the wrong let me go down and gone one more there so good yeah so the default level for this is a hundred percent sequel server will pack all of your data and as far as close as it can possibly get but if you do a lot of updates in place or if you have the kind of design pattern where a lot of fields start out no and then you update them like you start out with order process data is null or shift data is null and you’re constantly going back and updating those fields they’re blowing out continuously wider and wider and you’re continuously doing page splits you could fix that by turning fill factor down and you can turn it down at the server level don’t ever turn it down at the server level because you don’t know what other databases are going on in there or you can do it at the index level because if you think about it remember my clustered index that I had back earlier it started at one and continuously went up higher but I bet on that table I’ve got an index by last name and I’m constantly inserting people all over inside that index because I don’t hire an alphabetical order it’s a good thing my friend John’s egg dough would be doomed at that point um so that’s why it’s important for me to change it on an index by index basis I may not need to change it at all on the clustered index if there’s an identity field that always goes up I may need to change it very quickly on the last name

index which is constantly splitting around so that’s exactly what I would do is the database administrator this is a picture of me back from 2004 when I thought I was the bomb and though if you looked at the books on my shelf none of them had anything to do a sequel server I didn’t need it because I was on the internet and I could find all kinds of bad advice out there and that’s exactly what I would do is I would set fill factor way lower there is actually a banking application out there today that tells you your fill factor should be 10% tenders it’s in their best practices guidelines documentation the whole nine yards well that fixes page splits all right oh yes it does you have very different problems at that point your database is twice as big ten times as big and you have no way of seeing this again it doesn’t surface anywhere in sequel server management studio all of these things take longer you have the same amount of data it till still takes longer to do backups and DB CCS when your data is spread across more pages you can cache less in memory your database falls out of memory faster because sequel still caches those 8k units it doesn’t cache just what you use it caches those whole 8k pages so doing this what I want to do is I want to go find this I want to say again my buddy Sisson Dex’s show me where fill factor is not 0 or 100 both of those numbers are the same sequel server knows that if you set it to 0 you don’t actually want 0 data on your pages you thought you met zero free space so both of those are the same and you can see when you look at that fill factor is whatever I stumble across this all the time because it wasn’t you was the last guy who was playing around with how this worked and we never thought to go look for it first off leave the filth server level fill factor 100% and only play around with this again when the dog isn’t going to bite you when we’re not going to really get into trouble with our page splits last thing that we’re going to talk about here today is the call is coming from inside the house I bet a lot of us in here work in environments where there’s multiple database administrators multiple developers and I know you guys have great relationships with them and they would never hurt you and they would always tell you exactly what they’re doing and they would document their changes and leave them behind for you and tell you in advance to make sure that you were all on the same page this is just about for the rest of those people at your user group that you can go back and tell about so I was constantly working with people who were not like that who would go back behind me and change all kinds of stuff so I wrote this whole SP blitz toward proc and finds all kinds of things about your server but if your jobs anything like mine I didn’t really have the time to fix this I kind of had to leave it exactly the way it was and every week or two I could make some small improvements fix some of the wacko fill factor problems or security problems and sometimes I just knew I didn’t have a choice I had to leave them the way that they were so I’ve added something to SP blitz I’ve added a parameter called output type by default it dumps out this big old table that tells you everything that’s wrong your database isn’t wrong it just has beauty marks we’ll say it that way this just gives you the number of beauty marks that your database has and so you can now alert on this if you’re using things like red gates equal monitor to tell you exactly how many warnings you have so that when it changes you can go look instead of going oh something just happened you’re going to find out immediately instead of waiting to three days when you run across the problem because this whole thing runs in less than five seconds on some unbelievably ginormous databases so you just want to set up a metric in red gate market red gates equal monitor to see when that number changes and trended over time or because we’re getting near the end of the year I like to have ammo for raises and I like to be able to have it a little graph that shows how much I fixed in a perfect world I’m going to start adding this to my monitoring software and see the count of problems go down because you knew our boss never has any idea what we do they think that as long as the server’s not on fire we’re too busy surfing Reddit and there were overpaid already as it is which is why you guys are free training right yeah I know that works the what my other favorite trainings are like last week I was in Las Vegas and I have the other problem there everybody there has already spent all kinds of money on their hardware their Vegas people right they’ve oh let’s go buy big terabyte servers Oh our database still sucks yeah that’s cuz you don’t know how to use an index not you guys you guys have already using you know the index things you’re here for free training so the six things that we talked about today first off people who are changing and disabling indexes without you knowing or ETL processes that are disabling your indexes you won’t have any idea just looking at SSMS available memory that isn’t actually available want long and when you go back

to to work this isn’t just a sequel problem this is everything i is sharepoint exchange all servers that you have you can suddenly look like a rockstar when somebody’s having performance problems your remote desktop in look at task manager so yeah you have no free memory let me show you a blog post by the exchange team that’ll tell you why this is all broke and that’s how you become a Microsoft Certified Master you fake it yes not really there’s test storage this and they suck stories that slowly slows down over time especially the solid-state stuff it’s interesting that magnetic never had this problem and now it’s a new problem that we have with solid-state drives temp TB with a limp because one file is too big and we’re having to drag it along and it’s the only one getting all of the activity a fill factor that’s killing your database by making a larger not fitting in memory anymore and your backups are taking too long and then finally people who are just working against you and not telling you so all of these are solved in a few simple ways first off get my Blitz script it’s completely free you can assess all kinds of ugly problems in your environment that’s a Brando’s are calm slash blitz this week it passed I’m also adding a performance checking into it as well that’ll catch things like implicit conversion that’s sitting around in your plan cache leave the server’s defaults alone except for the things that I recommend on my setup checklist and in each of those things where I tell you what you should change I give you extensive documentation from Microsoft to say here’s why you need to change this particular thing when you change something measure the effects so often I see people set up a trace flag change a configuration parameter thinking it’s gonna make things better but if you don’t prove it scientifically we should want to Mike Mike actually got me started on that Mike was originally a science student and he’s like well you have to have a hypothesis and then you prove it and becomes theory I think I did get those recorders right I bought this very here C whoo normally I just take all the credit but he’s in the room so I have to get him the credit so um use a monitoring tool like red gates equal monitor I don’t care what you use but please for the love of God don’t just come in to me and say the server slow what was it like last month better yeah okay how fast do you want to be enough and then finally get custom metrics Redgate a lot of us from the community put in work to give a lot of extra custom metrics for the things we’re passionate about me it’s like disabled indexes you can download those for free it’s equal monitor metrics Redgate com any questions for anything we covered today you’re all ready to get the hell out of this hot room right whoo well thank you very much for coming in to see me and put the rest of your industry really glad I got two things a tea that’s for sure wha