Office Hours: Ask Me Anything About SQL Server at Vestrahorn

hello party people and welcome to office hours at vestra horn in the south of iceland it's a little oceanfront uh mountain here where there's a tide there's a bay behind me that's going to fill up as we get closer to high tide i don't know that i'm going to go that long it'll probably only go 20 30 minutes here but has just a really nice reflective effect too in the sand really pretty there's also an old nato radar station out here as well down that used to warn the americans about the incoming ruskies i suppose so let's go take a look at your questions so the number one top up voted question was from scott m he says hi brent sp blitz who and sp who is active are great troubleshooting tools for for sql server when do you recommend using one over the other oh so this is interesting and and giving you an answer on this requires giving you a little bit of back story around both tools sb who is active was originally written by adam mechanic a brilliant sql server person out of the boston massachusetts area and when adam first wrote it it was almost like back during the shareware days of sequels or back during the shareware days period where there wasn't really an established path to making money with scripts and so adam had the script copyrighted and he experimented with making it for pay for businesses well as we were doing consulting we needed a tool that we could use out freely with folks and that we could put under licenses like the gpl or mit license where we could freely install it on client servers without having to deal with any gotchas so we wrote sp blitz who not because there was something broken with sp who is active but just because we needed something purely open source that we could use to make money with our careers shortly thereafter adam mechanic ended up open sourcing sp who is active he stopped doing so much work with the sql server product started going off to other big data type things in cloud so he open-sourced it if i remember right it's the mit license i can never remember if it's mit or gpl so we never really needed to do sp blitz who at this point but it's already out there so there are some things that sp blitz who does better there are some things that sp who is active does better but at this point the licensing is is effectively free for both tools so that the licensing is less of a concern i tend to use sp who is active when i'm when the server's on fire and i need a response immediately just to know if the server's okay but if i'm doing more fine-grained troubleshooting if i'm looking at memory grants and queries for example i tend to use sp blitz who because it gives more information by default of course the fact that it gives more information by default is why it takes longer to run so if every millisecond counts you use sp who is active if you need to do more in-depth troubleshooting and you don't mind waiting a second or two longer for answers then sp blitz who gives you that detail next up let's see here we have brandon brandon says i'm filtering on price and start date regardless of parameter values clippy always suggests indexing price then start date and never vice versa can i conclude that clippy doesn't consider selectivity yes if you search for brent ozar how does sql server build missing index requests i've got an answer out on stack exchange where thanks to one of my training class attendees we figured it out that what sql server is doing is it's recommending the list of columns in order that they are in the table it has nothing to do with your parameter selectivity it has everything to do with the just the order of columns that they are in the table you can prove that by running experiments and if you look for my answer over on stack exchange i give you experiments to show how to prove it but another way that you can prove it is to look at the execution plans xml when you're looking at an execution plan that includes a missing index recommendation right click on the execution plan and click show the xml and then look at the individual xml and you'll see under the missing index request group column number one column number two column number three they're ordered in the exact order that they are in the table has nothing to do with selectivity there is one additional gotcha but rather than going into that with uh inside a video where i don't have demo scripts i'll just refer you over to that stack exchange answer where i show you all the details on how clippy generates that it's so terrible once you know that then it really influences how you interpret clippy's results because you probably know what's selective about the table whereas clippy does not even bother looking at that kind of thing next up we have hugo hugo says i've got a table that's used as a temporary placeholder and data is inserted and deleted constantly under high concurrent scenarios a query that uses this table has a bad execution plan the table is constantly having update statistics have you ever had scenarios good god it's like a train leaves philadelphia at 4 pm the conductor enjoys grilled cheese and nachos he has a pet named buffy who's a goldfish that always likes to use her left fin for the love of god will you just get to the point i try to exclude like make the question box as small as possible on pole gap so that y'all can't ask questions like this it's like a stephen king novel the last sentence says have you had scenarios where disabling auto update statistics is a good fit you go just for future reference ask the question you don't need to tell your life story so if i had situations where disabling statistics was a good fit yes so classic example is a table that has all kinds of churn like what you're describing another example is a very large table where every now and then we take the effort to do an updating of statistics with full scan but if we happen to hit the auto update stats threshold i don't want to get brand new stats just done with a small amount of sampling and i don't want to take the time that it would take to do a full scan update of the statistics in the middle of the business day while people are running queries especially if the statistics just don't influence execution plans all that much on that particular query what about my goldfish uh next up cavea np says hi brent i recently joined a company as a sql developer i see lots of no lock query hints all over the place my scroll down a little here my friend suggested rcsi as an alternative for my for that is my friend correct yes next up let's see here come on over here and move this over a little so i can move these questions around next up we have move these around gleb gleb says hello do you use postgres as a developer in products that you build or do you offer help with troubleshooting postgres problems as well i use postgres as a consumer but not as a teacher i don't want to offer training classes on something until i can explain how its query optimizer works and i i haven't put in any time into postgres to try to figure out how its query optimizer works i am what they call in the business old i am in my 40s now in my late 40s and i kind of bet the farm on sql server if there was going to be something that i would learn in more depth it in order to teach y'all it would likely be azure sqldb not postgres it's not that i don't love postgres i do but i'll tell you what if you want to make a lot of money work on something that's expensive if you don't want to make a lot of money work on something that's free the training class budgets for people who work with my sql and postgres are naturally lower than companies who spend seven thousand dollars a cpu core to license their sql servers so do i like postgres as a consumer sure but am i going to make any money on teaching postgres training classes not compared to what you make doing sql server training classes because the product is so much more expensive all right next up we have fundamentals of ms docs asks i'm asking this because i don't trust myself to interpret microsoft docs correctly if you have the highest compatibility level and query optimizer fixes is set to on how does this differ from being the highest compat level and having this set to off do you get updates beyond rtm yes microsoft ships changes to behavior after release to manufacturing so like sql server 2019 uh has fixes that have changed since sql server 2019 shipped the catch is you have to know what those fixes are and i hate the term fixes because it just means what it really means what it's supposed to mean is it's supposed to mean different behavior you hear query optimizer fixes and you think oh something is broken it needs to be fixed when in reality what it's much more about is microsoft has decided to change query optimizer behavior if you're the kind of person who's able to test all of your queries with to make sure that they don't get worse execution plans then sure by all means start playing around with random switches but otherwise don't use switches like that unless you're instructed to by either microsoft or by someone who looks at multiple execution plans compares them side by side shows you the differences and shows you why that switch is better otherwise they are not fixes they are just changes in behavior next up uh ppi asks can log shipping be an option for migration between sql server 2012 and 2016 in a production environment oh yeah absolutely i'm a huge fan of this what i like to do is i like to set up log shipping between the old server and the new server set it up so that it's working now you won't be able to read the production server until you go live but once you go live then you all of a sudden you'll be able to restore the databases very quickly with a minimum amount of outage the way that i use log shipping is that i'll set up that log shipping ahead of time have the data flowing through from one to the other and then break it turn off replication between the two by not taking the last tail of the log back up in production but just simply assuming that the new production server has gone live just for testing purposes run restore with recovery and then that way people will be able to access the new production server but this is for test purposes only so that they can test that their applications work that their there's no behavior changes in performance that my maintenance plans are set up correctly things like that so then that way we'll all after they all sign off that things are working well i'll tear down that and reset up the log shipping again you know re-reinitialize the log shipping so that that way i know it's going to work well when we go to failover but log shipping database mirroring always on availability groups i kind of think of migrations in that order log shipping is my very favorite database mirroring would be my second favorite because it doesn't require windows clustering always on availability groups would be my last favorite for migrations just because it makes migrations so complex as opposed to log shipping is relatively simple next up andrew asks when a participant in your training classes isn't available isn't able to follow your instructions and your intention your attention on them is taking the class down what internal metrics do you use to know when to cut them off oh so this is interesting so you kind of think of my training classes as like the beginning is the free stuff like how to think like the engine and i rarely do those interactively if someone can't follow how to think like the engine often they just self opt out at that point they're like ah this training class doesn't work for me it's not my style and they go find something else the next level in is my fundamentals classes that are only one day long those fundamentals classes that are only one day long tend to be 45 minutes of lecture and then a 30 minute lab and during that 30 minute lab they go on and do their work to prove what they just learned as they're passing in their answers to me i'll often say things like whoop timeout you didn't finish how to think like the engine or you didn't finish one of the other prerequisites before you go further in this class you need to stop and go back and do the prerequisite material setting that tone early on in the fundamentals classes that are only one day old helps people understand that they can't skate by in my classes if you're not keeping up i'll say here's the prerequisite material that you missed i need you to stop here and go back and hit that prerequisites i have no mercy at all on that in the fundamentals classes by setting that tone i don't have the problem in the mastering classes the only time i have that problem in the mastering classes is when someone tries to skip fundamentals altogether and i'll just say hey look i'll cut right to the chase because of the nature of your question i can tell that you didn't do the fundamentals class here or it's been a really long time since you did it i need you to go ahead and watch that fundamentals class and come back to the recordings of this class when you're ready but you're not ready right now next up rojo asks we're considering multiple satellites in the cloud that phone home data to our in-house mothership one source of the truth what is the best way to forward data back and make sure that we're talking to the mother without real loss real time not required but daily updates okay so there are a couple things that set off red flags inside here when you say that the mother ship is one source of the truth but the satellites have the data and they're sending it back to the mothership i don't think that means what one source of truth means if you could put a bullet in any one of the satellites erase it and walk away and not be concerned then yes the mothership is the one source of the truth and that's a that's a correct read on that situation i'll give you an example one of the clients that i work with does have a central mothership that's like supposedly the single source of the truth then sql servers in i'm going to call them factories that's not what it is but i'm obfuscating what the client really does they have sql servers all over the world in factories and those sql servers collect real-time operational metrics and once an hour they send those metrics back up to the mother ship we can lose any factory sql server at any time and we don't give a rip because the data that's in them is just helping them monitor the factory machines to make sure that they're still healthy but there's no customer order data in there for example in a situation like that if we assume that that's really what it is that the mother ship is a single source of the truth and the satellites are disposable they can be just erased at any given time then at that point i'm a huge fan of having like a c-sharp app or a java app that pulls down all of the applications from each satellite and then dumps them in a flat file closer to the mother ship i don't want to use anything related to sql server i don't want to use replication i don't want to use log shipping i don't want to use linked servers because i want something that can continue to work regardless of what happens with active directory what happens with authentication what happens with firewalls i want the most bulletproof way of moving data around but as soon as you try to have two sql servers talk to each other you're in for a world of hurt other ways to think about this are like how data warehouse people do it that they'll do extract transform and load they will extract from one server do some transformations and then load it into the mothership for a different source of the truth if on the other hand you mean that those satellites the data really can't be lost that's another question altogether and in that case that's way beyond what you would want to rely on a quick back and forth via office hours on that's really if you want to coordinate data across multiple servers the problem that you're going to run into is conflict resolution where what happens when the data in between those two servers doesn't agree how do you resolve conflicts and that's much harder next up daniel says hey brent what do you suggest for a dba managing memory optimized tables with 200 million rows with varicare max in terms of memory estimation and maintenance i would recommend a new job memory optimized tables with 200 million rows using varicare max everything about this screams bad idea jeans like that i wouldn't touch that with a 10 foot pole in memory optimize is not for stuff like varicare max and 200 million rows and when you start talking about memory oh i wouldn't do that on i don't want to say under any circumstances but i've never seen a scenario where i would want to do that now i'm going to tweak your question what if you said it was just 200 million rows with integers just a handful of integers and nothing else how do you do memory estimation well it's easy you build a 200 million row table and you run things like an update that affects all 200 million rows and go see how much memory that uses up and it's going to be large but that helps you understand how much memory you're going to need in order to accomplish operations because after all you can't prohibit someone from doing big ugly operations like updating entire tables when you start doing that on varicare max there's no limit on no practical limit on how much people can stuff in a whole bunch of vercare max columns that's a recipe for disaster in terms of memory management next up simon says how can i predict if my sql server has enough resource capacity to handle a 50 increase in workload oh that's a great question so the challenge with that is i'm going to give a couple of crazy examples what if your 50 increase in upload means accepting 50 more rows like right now you're adding a thousand rows a day pretty soon you're going to be adding 1500 rows a day the things that i might worry about there are blocking like lock contention as people are doing these inserts and disk space transaction log space what if your 50 activity meant 50 more users that were updating one row which sounds crazy but think about a shopping scenario where i'm worried about tracking the number of available products that are number available items of a product that are available at any one time i'm there exclusively worried about locking i'm worried about a lot more people hitting exactly the same tables what if it's 50 more select what if it's a data warehouse we're looking at adding 50 more staff and i'm trying to figure out whether we'll be able to do 50 more reports in that case i'm worried about things like memory grants or cpu overhead depending on what their queries look like so it's really all about knowing what's the bottleneck that you're facing today in your workload and then if that if you had 50 more users doing the same thing how would that affect your bottleneck and of course the answer is going to change so much depending on whether your workload is inserts updates selects and of course it's a mix but what is that mix and it would be very hard to get someone to know that answer if you're just asking some stranger wearing an icelandic sweater standing on a beach in iceland thousands of miles away from your workload who's never heard of you before that's not a plug for consulting either like if you asked me to fix that via consulting that would be extremely expensive that's pretty ugly um yeah but that that's why it's there's not a google answer out there it's hard work matt says bored out of its gourd is something that you say often about servers is there a situation where the dba deserves credit for that no because it means you're over provisioned it means you provisioned way too much horsepower for your workloads if you have an 80 cpu core server and it's sitting around not doing anything that's not to the dba's credit it's to the dba's detriment that they over provisioned so much and that they're making their company spend so much on licensing so that's why i don't clap dbas on the back for them next up we have severio asks severio says in a database and full recovery model when i create a clustered index on a large and busy transactional heap what should i expect about my transaction log growth both using online on equals off i can't find comprehensive details in docs online about that yeah so when you when you ask what's going to happen when i do a and you're looking for a manual to tell you that i wonder why you don't just go do the thing just go do it just go do it you'll find out not in production do it in development this is what you have a development server for i feel like i'm seinfeld what's wrong with you people go do it it's creating an index online and off it's like 30 characters go do it go measure and don't have log backups on while you do it and you can go see how the law how big the log backup becomes do you see baby food here why are you thinking that i'm going to feed you baby food go do it it's easy come on for the love of god next up good transition the terrible dba asks my friend is a production dba but he wants to move to develop database development or database architect role in that same company who should he approach and how and what skills should he focus on okay so because i i of course made that same uh transition um i'm going to tell you what worked for me it's not for everybody but what worked for me is identify who the customers are that you want to serve for example if you want to become a database development or development dba your customers would be the developers and the stakeholders in the application who want that app to go faster so you would go approach those people and say hey what are your big performance concerns today what things can i help you with and you start building a track record amongst that of the people in your organization that the developers look to you as the resource that they can go to in order to make their application go faster and then they will effectively the you said that in your notes in there that your manager was a systems administrator or your friend's manager as a systems administrator manager what you're looking for is you want the head of development to go fight for you you want the head of development to go i need this person they're really important to my team and my team's success in terms of performance i want that person to report to me now you see the work that you would have to do is you have to make them hap make them a happy customer of yours architect is so much harder because you probably are in competition with people in the development team who already want to become architects and they're on that team so that's a much tougher road to ho rhodo if you're trying to pull it off in other companies you need a history of architecting things and that's much easier for a developer to accomplish than it is for a production dba so i'd focus on the development dba and focus on who your customers are next up mark says i'm making an ods backup and restore via ssis backup and r i have questions backup and restore via ssis from production onto a different server and then run scripts to create tables okay nope uh so i'm not a big fan of what you're doing here so i am not a big fan of having ssis take backups of production restore it somewhere else and then go start making changes of tables this design pattern that you're talking about here is called groundhog day groundhog day is where you wake up movie by bill murray where every day he wakes up and he lives the same day over and over again until he figures out how to break through past that that's what you're really asking a data warehouse to do is all right every day let's wake up get a fresh cup of coffee and rebuild this data warehouse from scratch you know of course the performance is going to be terrible it's not going to be really good what i get that it's easy i get that it's really easy to write every day we're going to start over from scratch create table hey let's load it it's going to take forever whereas if you start working on incremental changes just only loading the changes that need to be applied you're going to get down to dramatically faster load times and you won't have this every day rewrite the entire database from scratch type approach so that's what i would recommend is don't do the the rebuild from scratch every day then we'll take one more question the last question we'll take let's see jj jj says hi i've been a developer for a while now and i've always played with my sql and microsoft sql server i have a broad understanding of stuff i like how he says that my question is as a developer where do i start learning more about the database side i'm talking about indexes tuning and fault fixing okay so of course i didn't mean for that to be a last question and that's going to sound like a plug for my training classes but of course ladies and gentlemen i happen to sell training classes on it for that exact purpose if you go to brentozar.com and click training on the top um now of course that costs money so what you can also do is go to my youtube channel if you go to branozar and look at my past videos i have tutorials on all kinds of things for free the other thing that we're really lucky for in the sql server industry is that the user community gives back so much that if you look at the conferences that are out there group by data weekender i mean they're just a huge number of online sql server conferences that are all totally free and they put a lot of their material up on youtube you can use those to then help grow your knowledge of the performance tuning side the drawback of all those free resources and yeah i think you should use them the drawback of all those free resources is they're in 30 to 90 minute chunks and there's no clear table of contents to help you go from zero to hero i guess it should have gone zero to hero there's no clear table of contents to get you from a to z and that's where things like training classes come in but if you have no budget what you can do is just start going through those youtube videos for all these free sql server conferences out there build your own training plan and your own table of contents and then pick the the things that you want to learn and become an expert on from there when you do have a budget that's where you'd go tackle training classes instead and they give you the uh the here's what you do in order to become an expert on that topic all right well we got a little bit of sun here in uh vestra horn so we're gonna be uh heading out now uh driving down the south coast of iceland going down to vic and then where are we going to oh the plane the abandoned plane so there was a justin bieber video that featured this abandoned military plane crashed on the coast um really pretty scenic site we saw it during the winter but now a friend of mine peter moorefield is in town in in country we're taking him around to a few of our favorite sites uh so now we're going to go drive out there and go see it none of us are that big of fans of uh we're not beliebers as they say although the fact that i know that the term belieber exists is probably a bad sign none of us are that big a term of fans of beliebers by any means but it's just it's a glorious place to see um and here just to turn this thing around and give you a little bit different of a view for a minute uh so there's the ocean out there we got the ocean the tide is slowly coming in here there is an old uh military radar installation over there as well let's see if i can move this a little bit more and maybe even get you a view of that as well so with that i will leave y'all and i will see you all at the next office hours adios

Channel: Brent Ozar Unlimited

Views: 5,562

Rating: 4.9215684 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: TE2y8nKioG8

Length: 31min 0sec (1860 seconds)

Published: Tue Sep 14 2021