Office Hours: Ask Me Anything About SQL Server

i forgot to reboot my little audio interface this morning so of course i start on mute mr huttmacher is here good morning massimo sql dev dba good to see uh all of you here this morning welcome in on a early sunday morning here in san diego another excellent warning here uh air quality is like 61. let's see oh gosh it's gone up to 67. as i'm kind of looking forward to today we're supposed to go out on a nice long walk to our favorite seaside restaurant here there's a hawaiian restaurant right on the san diego bay where normally we'd go off on our weekends and go uh do mai tais and seafood and i'm not sure we'll see how the air quality goes today and it's another like super foggy morning too so i'm not quite sure what exactly is going to go down uh today uh raptor is always funny seeing people troubleshoot their audio stuff as we come in uh so we'll do another open question and answer webcast if anything that you all want to ask technical questions uh go ahead and get your questions in i go through them first come first serve and we tend to get like an hour deep in questions like people pile up enough questions that i have like an hour stuff going so if you're timid and you think oh i don't want to be the first one to ask a question fire away and go ahead and throw your questions in there because believe me they're going to come in fairly quickly kurt you know it's very funny i was just writing up a blog post about that so i'll share my browser so you can see the blog post that i'm working on let me arrange that so that it'll work well with the obs stream here just a second so let me go pull that entry up so that i can share it here on the screen in a second because i have other posts that i probably don't want to share just quite yet all right let's see here uh so i'm working on a blog post about why we're moving to iceland how we're moving to iceland for those of you who this is new to we're moving to iceland around the end of february uh next year so around the end of february 2021 and one of the things that i was writing about inside there is how we're moving to iceland um i have no idea i don't use sql server and docker so if you don't know probably the answer is no so we looked at shipping helmet over and it was over a hundred thousand dollars in fees if we were going to ship that fella over because he has a large engine um you know most most uh european cars engines don't need to be that large so ice under iceland really socks it to you in terms of import duties if it's over a three liter engine they want to charge you like 45 percent of the cars originals uh uh purchase price freaking bananas um so would it cost over a hundred thousand bucks us that's not a picture of what i'm driving that was we took a backcountry tour it's funny how that worked um but so right now what i'm trying to decide between is uh renting a land cruiser or buying one uh because we really need uh so you don't if it's one year or less but over a year you may have to do it uh but so and it plus two it's a porsche 911 it doesn't really make sense in iceland you know that it's only usable like four five six months a year yes it's all wheel drive but you know it just doesn't you don't want to put that through all the salt roads of iceland and um the the the best roads aren't really that good for 911s anyway i'm going to record terrible potholes and all that too as well morning tanya so i think we're we've had land cruisers over there before and i love land cruisers um it's i want something big and safe of course and it needs to be an automatic too because my wife drives um and we're out in the country we're out in a country house kind of in the middle of nowhere like within an hour of accurary so a land cruiser like all-wheel drive makes sense because the roads won't always be plowed so that's what we're kind of trying to decide between because right now too we don't know how long we're going to stay there it could be like a year it could be longer than a year we'll kind of see how it goes and it kind of has to do with how the politics and coronavirus goes over here it's not necessarily the best time to be in the united states i think things are going to get a whole lot worse here before they get better uh so we'll come over here and hit a couple of questions let's see here i'm going to copy paste a couple of oh kevlar says 911s make some great off-road vehicles that's true for air powered not as much for the water-cooled ones so let's paste a couple of things over here into the questions queue gov dba says like living in whitehall that is true ron says i'll take helmet from you well so that's that's another reason i'm local to you there's another thing that we're working on is trying to figure out where we're going to store him uh and see what happens with that because i there's a great a couple of great uh collector car storage places but you also have to figure out what's going on with insurance and how it's going to be stored and all that kind of thing okay so let's copy paste a couple of things into here and start showing through the questions [Music] max asks should database administrators start switching to azure data studio or is it primarily built for developers it is totally built for developers for a while there when azure data studio first came out they started trying to shoehorn a bunch of extensions into azure data studio to help dbas do their job so like managing sql server agent or availability groups but it's really still totally a second class citizen it is not really designed for managing sql server and a lot of the stuff that you can do with management studio you simply can't do with azure data studio yet unless you know richie unless you know how to do the t-sql commands which just frankly most people don't microsoft still also seems to be very actively developing management studio fixing things with it making improvements to it so i i wouldn't see any urge to get off a management studio yet if you're a production dba if you're a developer or a development dba i'm not even sure that you still should move to azure data studio the reason why it makes sense for me is i spend about half my time in postgres and half my time in sql server and most of my time is on a mac you know like i use an apple mac as a desktop and i have since the windows vista days but unless you're developing cross-platform cross database i'm not sure that you want to go to azure data studio um yeah ron says they took debug out of ssms but it's not in azure data studio either you know that the debug functionality is kind of dead so so that's a tough one um so let's see here let me grab the next question so there was another good let me copy raptors one on to there copy that over there put that into the queue next up i'm going to ask ritchie's because it was funny um richie says why do people use sql sql constant without sql server without constant care richie joris is a course our developer here and my my thought with this is if i was a database administrator like we're building sql constant care to be the kind of second opinion service that i wish i had as a database administrator now a lot of people can't afford a full-time monitoring tool because stuff like spotlight century one idea sql dm red gate sql monitor all these things cost like a thousand bucks per server so we did sql constant care as just 500 bucks for all your servers so like as many servers as you want to monitor because we only do once a day sampling we don't give you real time like hey the cluster is down because frankly your users can tell you that next up contact administrator good to see you again says i'm implementing dynamic data masking on a 3.5 terabyte table will it take a table lock is it involves an alter table statement and generate a lot of logs or is it metadata only i haven't actually done that because dynamic data masking is so easy to work around like it's just so trivially easy to hack your way past let's see if i can find my bookmarks because i think i have a bookmark on how to hack past it so if i go to i'm going to go fire open chrome and my bookmarks are publicly available on pinboard if you go to pinboard.in slash you colon brento if you go and ah good to see you drop table employees if you go to pinboard you can actually read my bookmarks live as they come out so if you're the kind of person who likes my my monday newsletter you'll probably also like reading through my bookmarks i'm going to search for masking and yeah there you go so if you search for masking on there joe obish has a post on how to unmask data secured by dynamic data masking and there are several other posts around how you do that as well so if you look like google for sql server dynamic data masking hacking or unmask or bypass there are a bunch of posts on how you do it so so for me um the question of implementing dynamic data masking has never really made sense it's like a last resort kind of thing um i and so i've never had a client who actually wanted oh hey sirleydev welcome to uh the show here today too thank you sir i'm with richie do i miss delicious the the old social bookmarking site that yahoo acquired it used to be so good pinboard's not bad but just not nearly as good as delicious was [Music] john asks what wordpress theme do you use for your site i use retina uh impreza impreza like subaru impreza so impreza if you do a google search for impreza wordpress it's a really popular theme and it's been out for a really long time the thing is it uses a custom page builder uh it's like wp bakery or something like that so it's really popular but it's not really gutenberg blocks friendly so i'll probably be moving off of uh off of uh impreza over to something else in the next like year or so i don't want to do it until we can get a graphic designer to really put some time into it because i'm just not the kind of person who will go do graphic design the work that i turn out is really pretty ugly as as richie would probably tell you raptor says we're looking to start getting rid of our sql server 2012 server we're likely going with an in-place upgrade no no no no no no no no no no no no no don't do that so in-place upgrades don't give you any kind of back out plan and implant in place upgrades are like the one of the biggest sources of woe and sadness that i hear when people are complaining about their upgrade installations you want something with a back out plan never ever ever uh upgrade in place even when i've got clients with like 10 20 30 terabyte databases i'm still like you still probably don't want to upgrade in place because you really have no back out plan at that size so i would just always go and build a brand new sql server rather than doing an in-place upgrade now having said that you said how do i decide between 2017-2019 at this time i would recommend 2017 because the code quality on 2019 has been pretty sketchy we've seen some pretty bad bugs in the last several cumulative updates whereas 2017 is pretty bulletproof it's in pretty good shape sure they still ship cumulative updates all the time with bugs but that the bugs aren't as terrifyingly bad as they have been on 2019 so i i wouldn't go to 2019 today as of this recording october the 18th of 2020 i wouldn't go to 2019 unless you had to like there was a feature in there that the only way that you can get the performance that you need is to put that in next up let's see here copy a couple of uh to do two two and as steve had said uh ironically i always found the see the sql server management studios debugging feature to be very buggy i'm actually with you uh there as well so copy paste a couple of these in for the questions copy this in and paste and i'll go add these to the queue and go through them uh and then this guy here okay i don't know i got that one there we go all right cool so manish asks from over on youtube why are sql server statistics very important in a hypothetical scenario and what sql server looks like without statistics so imagine for a second that i asked you to go through a table and find all of the sales from this year and sort them by by what the highest values were like i asked you to tell me what were the biggest sales that our company made this year and imagine that you have no idea how many sales you made this year and you have to figure out whether you need one cpu core or multiple cpu cores and how much memory the query is going to need now you and i as human beings will say well we'll just figure it out when we go find the data that's not how databases work they have to reserve resources before the query starts so that's where it gets kind of got kind of ugly that's why it's so important to know how what the data looks like before you go in so you can allocate the right amount of resources figure out which tables you should process in order for example should you go with the sales table first or the products table or the customers table and how you're going to join those together so that's where statistics come in handy also if you if you go to my youtube channel and you look right on the home page of the youtube channel there's a whole entire video where i explain statistics on this very desk using a set of playing cards to explain how statistics work next up raja says how can i buy your merch i'm glad you want to buy it or use the shirts um so if you go to my twitch channel if you search for brent ozar twitch which sounds like it's going to be a little goofy if you search for brentos our twitch there is on my twitch page there's a link to my merchandise store so you can get stuff over there i have no idea whether the company develop delivers in the philippines or not but your best bet there is just try and place an order i know we've had people internationally who've ordered of course just the shipping takes longer next up john says from over on youtube i'm starting to use visual studio 2019 database projects for builds where they've never had builds or release cycles is this a dead technology at this point should i just use ci cd oh here comes richie's going to be making all kinds of jokes now probably let's see database continuous integration and continuous deployment still pretty much at the bleeding edge for sql server that the the companies who are building the tooling for it are still kind of in their relative infancy and it's not as easy to put in as you might want you would think that it would be just you're going to push a button and all of a sudden things are going to start working and deploying but the reality of trying to figure out how you want your builds to work how you want to merge code changes in like if somebody decides they're going to drop a column in a table they're going to add a column and convert the data from one column to another they're going to create a new set of rows how do you version control those it's way harder than it looks at first glance the only company that i really know doing it and putting an appropriate amount of work into doing it is redgate so redgate has their continuous integration and continuous deployment tools what i would do and i've had several clients of mine do this is go do an engagement with redgate they'll see they'll have a consultant work with you either one of their people or a third party that they trust like alex yates we'll work with you to look at your current deployment processes and figure out what you would have to do in order to make their products work for me for most of my clients most of my clients who have done that kind of process with redgate have gone into it and said you know what we're not ready for it yet but at least now we have a road map of what we're going to need to do in order to get to a point where we can put in ci cd the reason why i recommend redgate over visual studio is redgate seems to be putting the appropriate amount of work and focus into it whereas microsoft it kind of feels like it's an afterthought like they're checking a box we have integration and you know that's kind of the end of it that may change with uh the whole azure devops thing microsoft's been changing their focus so much on that that i kind of want to see that bake out for a while before like if i remember right they've changed the name twice in the last two years i would kind of want to wait and see that bake out a little more and get more mature before i'd want to hitch my horse to the azure devops stuff that's just me next up richie says can i interest you in aws aurora we dropped our storage costs in half by upgrading using a drop down yeah so um uh so what richie's referring to is amazon aurora just brought out a feature this past week this is going to sound funny but if you had an aurora database aurora is there kind of infinitely scalable mysql and postgres and mariadb if you use aurora and your database grew you kept getting charged for whatever the max size was even if your database had dropped down in size so we had grown to three terabytes at one point with sql constant care and then we started being more aggressive at uh deleting data and improving uh like honing down how much data we kept richie did a great job of building functions that automatically go through and batch out turns out deleting data in postgres is a lot harder than it sounds there's this whole vacuum thing and all that but anyway when your data size gets smaller aurora wasn't dropping down your capacity richie did a deployment on a friday afternoon went to our newest the newest version of aurora postgres available and presto change-o our retention our storage costs were down by like two-thirds so it's pretty impressive it's pretty nice but it's also an example of how cloud stuff is still stow in its infancy that you would think that if your database size goes down you would start paying less fairly quickly but turns out that's not a capacity everyone has yet and microsoft suffers from similar stuff with azure like sql db managed instances next up mallet good to see you again malik malik says i was recently asked is there any formula for the number of ips required and always on so for always on availability groups with standard edition this is the part that sucks with standard edition sql server standard edition you need an ip address for every database [Music] every database and an availability group needs its own ip address that's kind of ridiculous hopefully microsoft's gonna at some point see the light and change that but at least in enterprise edition you're gonna need an ip address for every group of databases plus an ip address for each sql server node plus an ip address for the cluster like the windows cluster depending on how many subnets that you're in you may need additional lists you will need additional ip addresses for the cluster and for the for each group of databases uh sql dev dba says uh the statistics of playing cards video was amazing thank you i appreciate that that was uh i'd wanted to do that for years i was so excited when i finally got all the camera gear to go and do it now it's just gonna go into a shed as we move off to iceland because i can't bring all this cool camera gear with me i'm gonna try and see if i can figure out how to creatively do a stu a remote studio on the cheap uh so like bring just the bare minimum of camera gear and try and shoot as much as i can with iphones multiple iphones uh on tripods but we're gonna see how that works i'm gonna try and set it up here in the in the house before we go off on the road oleg says uh hi brent what do you think about using row level security either this is raw level which i kind of like is funny as a freudian slip there for restricting access to one specific user row level security is also easily bypassed so the thing that i would say is it's good for reporting scenarios where you can control the queries that they write but if you can't control the queries that they write they will be able to work around it sooner or later like we've seen several cumulative updates where microsoft keeps catching bugs in it where people were able to work around it and get past it so i i don't think that i would uh use it as a real security defense mechanism it's really just for like a reporting ease of filtering mechanism next up nerdo file asks should i use sql server for or should i use docker for sql server in production the question i always ask is what's the problem that you're trying to solve anytime that you introduce something new especially if it's something that sql server just introduced anytime that you introduce something new you're introducing risk because it's not always going to work and you're going to have a hard time getting support whether it's from microsoft or the community so i'm like when you introduce something like that what's the problem that you're trying to solve and how is docker going to help you solve that problem i have never seen a situation where docker made sense in a production environment i have seen it make sense in a development environment if every developer wants to be able to spin up their own copy of their databases quickly without having to hassle with installing sql server like if i wanted to quickly spin up a database on my mac pro you would think that docker would be a decent solution for that for me personally it doesn't really work well because i need to perform i need to show performance tuning issues which typically typically require large databases eight cpu cores or more 64 gigs of cpu or 64 gigs of ram or more and docker containers just aren't a good fit for that today especially when we start talking about 300 gig databases that need to be able to restore quickly ericker says docker is okay for some software as a service solutions if you're okay with kind of disposable databases or if you don't have to worry about licensing but remember with docker you're going to need to license the host with enterprise edition that gets kind of expensive quickly next up christopher asks will clippy be hosting the twitch stream on october 31st october 31st is i want to say fundamentals of index tuning i think is that one clippy will make an appearance but probably only clippy's hands i probably won't do the full-blown uh green screen or the full-blown green suit on there just because it's kind of a pain in the rear to pull the whole thing on and off in between bio breaks i might but we'll we'll see how that goes uh next up uh or nd bt says i understand that classes aren't sold in eu due to the gdpr i was wondering though if the same applies to switzerland yes in switzerland it's called the federal data protection act so unfortunately we don't sell to switzerland either and you know what's funny when we when i first said i'm not going to sell to the eu due to the gdpr i caught a lot of flack for it and i was in like national newspapers and stuff i caught a lot of flack for it but i'm like i as a database administrator i don't want to be compliant with the letter of the law i want to be compliant with the spirit of the law too like i want to be able to stand in front of y'all i always see y'all as my friends i want to be able to stand in front of you and say yes i am fully compliant with what your government requires but the government requires me to do stuff i as a small business i can't do it it's not financially feasible for example to only host your data inside specific countries some countries have data residency laws i can't afford to spin up different environments of my store or my training classes in different areas it just doesn't make financial sense so that gets to become a little bit tricky there al getty says what do you think will be a great new sequel feature in 10 years oh so i had a predictions post at a predictions post a few years ago with what i thought um what i thought would be mainstream at that point and i think that the biggest change is likely to be serverless so with serverless applications and databases you pay by the millisecond that your queries run that you only pay when your queries are running and you don't have to worry about cpu size or memory the admins just go ahead and i say the admins the services automatically spin up more or less power based on the workloads that you're experiencing i think that's going to be the big place where we go within say 10 years there's already hints towards it azure sqldb has a serverless tier now i don't see a lot of people using that but bob that's not 10 years that's today i mean that's been that's been out for years you know all amazon sql or amazon aurora azure sql db that's that's pretty i don't want to say it's common for people to use today but the features there what's uncommon is people aren't using them widespread yet but i think the server serverless one is the thing that's going to be really big years from now next up eriker from iceland says have you looked at the performance issues with natively compiled stored procedures so the problem with natively compiled stored procedures is it is so hard to track how much power they're using you have to turn on additional overhead on your server if you want to track those so for me whenever i've worked with clients i've never found a problem where no not if it's the free well here i'll answer that thank you i've never found a problem where natively compiled stored procedures were the right answer with the clients i've had but i'll tell you what i can imagine scenarios out there where it totally makes sense it's just that clients have never been in a position where they've hired me and that's been the right answer to the problem i'll just throw one out there stack overflow if i was still the database administrator for stack and not that i was ever the full time i was just a consultant but if i was a full-time database administrator of stack overflow i can imagine that as being the kind of place where it would make sense where you're doing sustained 20 30 batch 20 30 000 batch requests a second all day long when i was working with stack overflow that was never a uh a valid solution to the problems that we were having but i can see that's the kind of environment where it makes sense gaming is another great example but next up uh kyra bell says i'm trying to get a new job and i was wondering if you know a good list of interview questions available online i happen to have one so yes so let's see here so if you go to brentozar.com if you go to brentozar.com and up at the top you click training so if you click training up there and then scroll down i actually have an entire fundamentals or a database administrator job interview q a class where i give you the questions and then you answer them and then i tell you what i was looking for during that dur in that answer so it's got dozens of questions from performance tuning high availability career stuff all kinds of things so that's my dba interview question and answer kit next up let's see what we got here next up um darman says from over on youtube darmian says have you come across any sql big data cluster production deployments or is it too early so i'm i kind of specialize as a performance tuner where people tend to bring me emergency performance tuning issues where the website can't scale fast enough the the data warehouse can't deliver reports fast enough so for me i'm not the kind of company where people would come if they were hitting the kinds of issues that big data clusters solve what big data clusters solve is if you have a whole bunch of databases oracle postgres hadoop all kinds of different data sources and you want to query them all with t sql i get the appeal of it i get that that seems like an interesting solution it just hasn't come across as a value for any of my clients yet even in some of the clients where i have long-term relationships with i've said hey what the problems that you're having in the data warehouse have you looked at big data clusters and for a lot of people people have said yes we're interested in it but we haven't even gone past the interest stage so um yeah no so for me the answer is no it is an intriguing technology i mean it's kind of like linked servers on steroids uh and lord knows everybody seems to love linked servers so maybe it's got a a uh an interesting potential i can see why people some people bet their careers on it because it's kind of an interesting bet next up raja says do you have a task in mind that you would want to automate that is not yet automated yes index management oh i would love it if the database would figure out how to store data for me oh that would be so cool i would just absolutely love to see it and we've started making some of that progress on sql constant care we didn't go very far deep down it we just basically like looked at your indexes over time and if we saw the same kinds of missing indexes pop up all the time like that you regularly needed them then we did some work on clippy's indexes kind of improving them and then recommended our tweaks to it but we we haven't gotten to the point where we're doing what i would really love to see is just push a button and manage the indexes for me and it's it's a place that microsoft has started making investments in and i so they've done done some of this in azure sql db but they're nowhere near what i would consider good enough yet next john says thanks brent about the visual studio deployments question we use managed instances can i confidently use visual studio 2017 with that so my thing is with the cloud microsoft's documentation [Music] has been sketchy at best that i i just haven't been that impressed with the level of documentation and i'll give you a great example i was trying to do performance tuning for a client and we ran into problems with ingesting data quickly and so i ended up having to do some tests on my own and found that they had undocumented performance limitations around how quickly they could ingest data and so i post a blog post about it and then microsoft goes in and documents it it's been it's felt like the documentation writers are just like one step ahead of the community but only one step and if you push the limits at all you end up hitting walls and documentation so if microsoft says that they uh that you need to use 2019 i would use 2019. next up anant says could you shed some light into how to debug stored procedures you use a print statement use a print statement and you dump out whatever debugging information you need and you think that i'm joking i am not joking um i personally as a professional i've never used the debug tool inside management studio for positive results because the problem is it actually stops sql server it pauses execution as you're stepping through things one at a time it is pausing execution inside sql server in order to hold your locks open as you're working you can't use this in any kind of production or development environment where other people are on the same server you can only use it when you're all by your lonesome and that's something that i just hardly ever get the opportunity to do most of the time when i'm working i'm either working in production or qa environments where other people are also trying to debug stuff and the the sql server just freezes and sql dev dba says how do you feel about raze roar versus print i'll usually do print because i need to dump out like the entire contents of dynamic sql uh next manny says hi brent tell us about any interesting and funny scenario which you fixed in query tuning have so many um but i'm just gonna give you one so in one you know how when you go to amazon they're they're on any product page there's a little list of people who bought this also bought so i was dealing with a company whose website was falling over all the time and i go in to take a look at it and you know they had the same kind of thing they had an e-commerce site people who bought this also bought except every time that they rendered a page they were querying the entire sales history to get a transactionally consistent view of who else had bought that product and they were updating it in real time as every page loaded and i'm like are you out of your mind you know why would you even try to do that when you're hitting you know thousands of page renders per second and so what we just did the whole thing was inside a stored procedure i said hold on watch this and i put together a table to just cache for every item here are the items that people bought with it i did an insert dumped all the data inside there and then switched over these the stored procedure to point to that cash table instead of querying the live sales history and boom immediately everything else on the sql server was fine they were able to ship millions of dollars more per weekend that was just the end of it so there's a saying in in databases the fastest query is the one you never make that's a good example of that you in a perfect world you would just cache that inside redis or some kind of caching tier there's no re need to hit a database when you want to know what uh what uh products people had bought along with another one shouldn't even be rendering that page that should be cash the entire page should be cached um ronald says over on youtube we've upgraded from 2012 to 2017 a year ago i just discovered that the developers only changed the compat mode to 2012 for production how much pain am i in don't do any don't change anything leave it exactly the way that it is if your users are happy leave it just the way it is don't what i tell people is instead of thinking of those as numbers think of them as letters compatibility mode x y o p they're just different it's not that one is consistently better than the other and in fact you will see some queries degrade in performance when you jump to the newer one you're not missing anything if the developers are fine with it leave it just the way it is next up ati says in your free training can i watch it later any grass time grass time are you oh i get you're smoking weed and so you want to be able to slowly adjust it later see here's the thing if you're going to become a professional you really can't smoke weed while you're watching training videos it's just not good for you mentally you're not going to be able to absorb things so what i need you to do is is put the doobie brothers down for a second there and just focus on the training if you want it for free you have to be fully professional and awake live if you want to watch the recorded versions you can pay for that though and it might even be a better investment for your time than than the weed and weed's not free either so might be a pretty good uh choice next up g-surgeon from the netherlands says any suggestions or tips on setting the auto growth of a log file oh yeah yeah yeah go to the largest growth size that you can handle without users complaining so if your storage can grow a file in say a one gig increment in less than a couple of seconds use that because that way you'll only tolerate you'll only have to take the hit on one growth instead of a whole bunch of small ones in a row that's the way that i personally do it and to figure out how what that size is i'll go create another database nobody's looking at i'll go create a database and i'll start growing the log file out and i'll time how long it takes to grow out one gig or four gigs or 10 gigs and so forth and cti no no not at all not at all not at all you're totally okay there next up we have corey says from over on youtube do you work with application developers on performance or optimization issues as a database administrator yes next question next question is asks now if we install antivirus on a sql box do the dlls get loaded to sql server what are the locations that you need to exclude from scanning you know what you do is you search for i'll show you how to find do the search so if you search for because i run into this problem all the time when i need to find recommendations from microsoft i can't search their site worth a damn but i can use google site exclusion what you do is you type in that kind of need you can see these are only the actually those are all the terms even that i've used on any machine takes incredible confidence to go in and open up your google autocomplete right on a live webcast there you go so what i'm going to say is sitecolonsupport.microsoft.com sql server anti-virus exclusions and then there you go so this will give you the uh how to do the exclusion for directories and file names uh for each of the paths you know what i want things that people never think of in here too are trace files so like trace files and log files for sql server so all those are loaded uh out there so that's a great trick to use sitecolonsupport.microsoft.com sql server antivirus exclusions and i'll actually i'll put that out in the chat just so that everybody has it and there we go next up oh i should give a shout out to this week's sponsor uh so this week's sponsor is the data platform broadest merc the data platform virtual summit so this is coming up in i believe december uh is a sql server conference sql azure amazon all kinds of technical database topics so you can go learn more about that over at dataplatformvirtualsummit.com right now if you register and i want to say it expires this week you can use the coupon code up at the top right corner dps sql and you'll get 50 off so that's the data platform virtual summit they have all-day classes too taught by all kinds of instructors from all over the world and the clock on this is time zone friendly it's real easy no matter where you live to pick up material live that's within your time zone plus if you can't attend live you also get access to the recordings as well so that's kind of cool next up let's go see next up we have steve asks uh is it worth creating indexes no okay sometimes it is that vastly reduced reads but the query clock time was only a few seconds in the first place so two questions one is who's running the report if it's your your boss you you probably want to add that index and two is how frequently does the query run if it runs a thousand times per second you sure you absolutely may have to add that index there so there you go uh next up camila says how do i know when my problem is bad hardware versus query performance so that's a great question the way that i'll usually answer it is is the sql server at least as big as your laptop if you have a laptop with say 8 cores and 32 gigs of ram and your production sql server is smaller than that you have a hardware problem so that's a good way just a starting line because sometimes people get really obsessed with stuff running up in like azure sql db and it's only got two cores and four gigs of ram i'm like yeah it's kind of hard to make that pig fly but if the sql server has at least as much hardware as your laptop then i would start with looking at query performance because it's usually easier to change queries to make them fast than it is to get your boss to throw hardware at it so it's a starting point and i have a whole class on that mastering server tuning that teaches you how to measure the servers measure the queries and then figure out how to prioritize those next up let's see here sudir says tool that i'm the most comfortable with or the right tool for the job at what point should we consider to switch i try to solve all our business problems and processes with t-sql but it's not always the best tool for that job um that's more philosophical than i think i want to tackle first thing in the morning the other thing that i would say is you may not be the right tool for the job just because someone comes to you to fix a problem doesn't mean you're the right one to do it as a database person i have to give that answer all the time to people where they're like hey we need you to build html and i'm like no no no you need someone to build html if you hire me to do that you are totally wasting your money you're flushing that money down the toilet so sometimes it's a matter of oh i know the perfect person to help you with that here's uh sheila she does a great job with c sharp and she can help you tackle that problem so there we go uh next up td uh tdn says what is your recommendation for reducing the size of a 300 gigabyte table truncate truncate totally works uh well there assuming that you can't do truncates and you can't archive data out and put it somewhere else the next obvious choices are compression so you can use row page row or page compression on your indexes and you can also use column store indexes column store has a phenomenal job for compressing tables sometimes you get 70 80 90 compression ratios depending on the repeatability of your data so those are my starting points there next up varun says when diagnosing performance issues on a production server what are the three things that you check first so i run and i'll show them to you in order so i run i'm gonna go pop open sql server management studio and i'll show you the exact scripts that i run in order and these come that's what i get for not starting this ahead of time come on in big fella management studios load time has never exactly been uh impressive so if i'm going to do that so during a non-emergency what i would do is exact sp blitz check server info equals one exec sp blitz first since startup equals one so those are the two queries that i'll go and run when i'm troubleshooting a performance problem that isn't an emergency and if you want to learn how i read those and what i get out of them go to www.brentozar.com click training at the top actually right now at the at the top of the site if you just go to brenozar.com right up at the very top there i have a completely free class coming up near the end of this month see how it says up there free live fundamentals classes coming up registration is open now you can click on that link and one of the free classes that i'm doing later this month is how i use the open source first responder kit so you can learn more watch me use it during live performance problems to understand how i read it so there you go next up ah let's see here manish says is high cpu a result of bad statistics or high fragmentation it can have dozens or hundreds of possible causes man wouldn't it be awesome if we only had two possible problems to choose from unfortunately it's a whole lot more than that that's why you and i make the big bucks is because it's not as easy as just pushing one button maybe it's just me that makes the big bugs next up erickson says have you ever come across the mvp multi-variable parameter problem yes next question uh surly dev reports that over on youtube powered by tones asks good morning brent could you touch on the differences in the career paths of data architect versus data engineer data engineer is handed a set of files the data engineer she gets a set of files typically from people like a data scientist or an etl architect a set of files and they go turn these files into a table and make sure that you clean them up along the way make sure to get kind of garbage data out of there and develop a process so that this can happen automatically a data engineer is a doer a data architect is sitting in their desk and they are brought a business problem hey we need to get stuff from the travel data from the country of iceland and we need to turn that into a report to tell our executives which airport we should focus on so they lay out what tools are used what processes will be used how the data will flow from one place to another and then they hand the tasks to the data engineer so data architect is very big picture thinks about which products to use data engineers just told here's some files get them into this table make it happen every 30 minutes whatever it is next up oh that's interesting farshit says good morning does the price of your consulting change based on the size of the business not at all also apart from the degree of technical complexity also not at all so i'm a huge fan of the productized service model product eye service means that i have a basically like a picture of something out on my website and i say here's the thing that i'm selling would you like to buy it and it's just one sealed box here it is just like you go to the supermarket aisle in the cereal and you say i would like frosted flakes it's just you try to make it as simple and straightforward as possible and then based on what we find in that engagement together we may go off and do other things that are completely custom but the thing you're buying in my first engagement is an assessment of your sql server today it's kind of like going to the doctor and getting a checkup it's like if you say you want to go to the doctor and you're like hey you know i want to run a marathon in two years can you give me some advice on what i should do to get there you can't ask that over the phone the doctor is going to need a whole lot more information about you what's your current weight what's the longest that you've walked recently what does your blood pressure look like are you walking into the doctor's uh office with an arm full of doughnuts you know the your current condition determines your ability to execute a future task so my two-day sql critical care at 69.95 us helps companies figure out where they are today and what they need to do to get to where they want to be so that's why i'm able to just do it in one sealed price and go here you go if you want this you can buy it if you want something that's completely handcrafted without me even looking at the server you got to hire me first next well we talked about that one so i'll uh clear that out let's see next up oleg says on youtube i remember you mentioned a while back that you can configure the first responder kits to not er for non-sys admins oh sure i'll show it to you so if you go to brentozar.com ask brent brentozar.com ask brent there is the sp blitz first documentation page on there one of the sections is talking about how you run it with low permissions for non-dbas so look for that session how to grant permissions to non-dbas and that's over at brentozar.com slash ask brent next up let's see what we've got here ah uh raja says if i use your sp blitz with powershell to pull results different domains send an email if i send if i share data on the community am i going to be sued well i'll tell you what roger the first thing you want to do is ease up on the typos you know maybe like get more clean spelling inside there because if you're going to share stuff on the community you want it to be like clear and concise and easy to read if you're putting the your own code that you wrote from scratch you have no worry about copyright if you copy paste code from someone else that's where you start to get into trouble so if you copy paste code from somewhere else you have to read the licensing terms and make sure that you obey those licensing terms so just pay attention there it is open source but you just got to read the license and make sure you abide by the terms of the license uh eric says what are your views in terms of performance of query loops how do you get a decent picture of the execution plan let's say that we're doing a while loop and we're executing the same thing a hundred times i don't need a hundred of their execution plans i'll change the code so that it runs just once and then i'll look at what that thing is doing once inside that loop and then i can tune it from there it's like the old thing how do you eat an elephant one bite at a time that's how you tune a while query just run the thing for one loop and then go dig into its performance next up condio says is it better to have a first column as a fully random text why in the hell would you do that or use a more reliable id why in the hell would you use a fully random text i don't know what your what problem you're trying to solve by that but no that that that makes no sense whatsoever uh next up yahor says what are the bad defaults about sql server any link please to learn more about it absolutely you can go to brentozar.com brentozar.com your home for sql server information if you click on the training link at the top of the page brentozar.com training you can get yourself education on and then learn all kinds of things about sql server all kinds of stuff operators are standing by if you don't have money you can use google but that works uh as well next up isn't that obnoxious it's so terrible um but even that no he said like n bear care 255 that's not even a good so that's totally different i like where you're going but no it was it was a worse idea than that manny said suddenly my stored procedure started to run slowly so i just put recompile since the execution plan is different than the previous one and boom it gets faster is that the right approach you would want to go to my fundamentals of parameter sniffing class the fundamentals of parameter sniffing class explains why that happens and when recompile is a legit answer that can absolutely be a legit answer the problem with it is when you continuously re compile queries there's a cpu hit to that it takes cpu time in order to build an execution plan so the more problems or the more times that you use that option recompile you'll see your cpu start to go up and up and up and up and next thing you know your box is 100 cpu the way that i'll usually tell people is if the query runs once a minute or less option recompile is fine great example are data warehouse nightly load processes if you load things once a night and you have a statement that runs inside that nightly load process sure option recompile the bejesus out of it but if it's on a website the option recompiles probably a bad idea next up uh underhill on youtube says can you tell me what values i should be using for mac stop i have heard a setting to 50 but what formula or event should i look at to no somebody i don't know where's uh you heard 50 50 isn't a good fit for maxtop i think you're miss ktool you're not the only person asking questions dude right you're in the line chill out so julian who was patient and waited his turn unlike catool who's a butthole you earned that um was asking about max stop let me show you where to go through it yeah g surgeon says does he mean cost threshold so what you do is go to go brentosr.com cx packet and i'll put that link up again brentos are whoops brentozar.com go slash cx packet and in here we've got a post on what that cx packet weight type is and how you reduce it that's where you can set cost threshold max stop and what my starting points are for each of those plus i even have a video on there that explains with each of those so very good brentosar.com go slash cx packet next up um whoops wrong button here let's go oh one of these is going to be over on youtube says would you use azure data studio with widgets to monitor sql server performance or a different established tool so for me what it comes down to is do you need an email when performance changes a lot of people think they do a lot of people are like i want email alerts whenever something goes wrong and then i'm like okay so about those email alerts do you do you maybe set up an outlook rule to just dump those into a folder and then you say you're going to look at them later but then you never actually look at them so with that when people do stuff like that i'm like i don't know that you really want alerting as much as you just want a dashboard where you can go in and look at performance over time i would personally never use azure data studio with widgets just because it doesn't give me a monitor of what performance has looked like over time when i'm not in the server because i don't know about you but the way that i always get questions is people will call me and say hey the server was slow last night around 6 p.m can you find out why and azure data studio widgets just won't help me at all for that so they demo well but they don't solve real world problems for me all right so let's take a five minute bio break and when we come back we'll dig through more questions in the queue so i will see y'all back in here in five minutes [Music] so [Music] so [Music] [Music] ah [Music] [Music] [Music] [Music] [Music] so [Music] [Applause] [Music] [Applause] [Music] so [Music] welcome back uh so there's a question about is that fog behind me yes it's a super you know it's been weird last a couple few days have been super foggy in the mornings and then it kind of sort of burns off which is weird usually it's y'all can see super crisp and clear behind me but it is uh totally fogged out which is kind of odd i should take actually i can take i have a camera on a gimbal and i could kind of sort of take that out there if i wanted to you know it's really funny um i have a uh one of those little gimbal mounted deals that i bought for iceland uh so i can like like a previous iceland trip so that it does the you know moving around and all that but you know what's really funny is i i haven't been traveling so much since i don't go anywhere these days and so now i i just use it as a tripod which is kind of a bummer uh so let's turn that off and then i can turn that camera off i don't need him at the moment and off he goes all right what do we got next here uh erica says on the mvp problem any suggestions for solutions yes in the uh mastering query tuning class i have a whole module just on dynamic sql that talks about that kind of thing next up we have lee asks about compatibility levels uh i thought a main point of them was to get access to new t-sql features if my database is in compatible 2012 on a 2017 feature am i not limiting what is available to me yes but remember the question was about the developers the developers left it on 2012 so if the developers are content with the 2012 features you should stay there only go to different compat levels when you genuinely need specific features out of that compat level because the queries that used to be fast for you are suddenly going to be slow now and you can't predict which ones until you actually go to that compat level so it's a chock full of nuts kind of thing and it's not that new combat levels are bad it's just risk is bad i see so many people going this number looks higher i should click on this number why is everyone screaming why are they telling me to get lost you know when half the performance problems are because of something that they did uh mike says we just compressed a 1.1 terabyte client table in jd edwards to 42 gigs compression rocks and it's there in standard since 2016 service pack one is it really i don't think it is i'm going to look real quick but i could have swore that sql server index compression was still just an enterprise edition feature compression yeah no bad news there it's not available in every edition so if we go into here so i went into books online on data compression and if i go do a search for edition compression's not every available in every edition for more information see features supported by the additions and let's see backup compression data compression oh no it is oh no two okay why is there a footnote next to it so what's the two footnote uh applies to the that's that's column store but that doesn't necessarily mean row store compression so i'd be curious if that actually means row store compression next let's see what we got here next we have circular life not a dba says could you please let me know the process of how dbas and large organizations get the sql server and do the installation usually the windows team has access to microsoft licensing sites and then they the windows team will then dispense whatever edition that you need what sql server installer you need in large companies that's typically how it happens also sometimes like senior dbas will also get access to that kind of microsoft licensing download site where they can do it for developer edition of course anybody can just go google for developer edition and that's out there free you don't have to do anything complicated to get that one next up cti geek asks a question that's near and dear to my heart can i use distributed availability groups to update to upgrade to new sql servers without downtime in theory yes in practice no in practice distributed availability groups are super complicated no gui to do it extremely unreliable so it's not something that i would recommend unless you have many times to practice it like that you're going to do it over and over again in a lab environment with the exact same sizes of databases that you use in production everybody i know who's tried this has gotten burned pretty badly it it works fine for small databases but in production stuff let's say a hundred gigs or higher that's where it starts to get a little sketchy next up oh no we actually saw that we talked about that as a camila says as a first time dba what are some of the most important processes i should learn about backup restore and corruption checking those three by far people don't get fired for slow databases people get fired for losing data that is by far and away the big one backup restore and check db learn how to restore for every production server that you have write out a list of if this server died in blue chunk in blue chunks what would i what steps would i do in order in order to bring it back from the dead and then share that checklist with your manager because management is often surprised by how bad it is like yo if the server went down at 3 p.m today we would be out for a day and a half and it would take me we would lose 16 hours worth of data when we came back so the earlier you can be transparent about that the better kind of sucks because it's not a fun part of the job users don't high-five you in the hallways when you're done with that but it is the mission-critical part of your job camila also follows up with thanks for the free fundamental classes you are welcome pierre says ssms is slow why do we never see you use azure data studio for simple demos oh that's such a good question as a teacher i want to meet people where they are not where i want them to be so as a presenter i need to show you things you already know because in say inspire in the span of an hour i can only teach you so many things should one of those things be azure data studio no if that's not the topic of the session then i shouldn't teach you that human brains can just only absorb so many things per hour so i got to be really careful about what stuff i want to teach you inside that hour next up circular life says i got a an interview question there was no change in sql server in months and your query is suddenly running slowly what would you check for me the number one thing that i would check is is the execution plan different between what it was before and what it is now the problem is that just so few of us can answer that question because we're not continuously harvesting execution plans there's a feature in sql server query store that's supposed to be able to let you do that i just don't see very many people actually using that they don't turn it on in advance and they only turn it on when it's too late when performance already gone slow to hell in a hand basket so because of that what i would do because if i don't know what the execution plan looked at looked like before all i would look at is why is that thing slow now so i would approach it no differently than if it had always been slow plus i'll tell you a secret a lot of times when people say that it used to be fast they're lying it was never really fast next up let's see here uh circular life uh s in replication how do i add a new article and do the synchronization i actually don't work with replication at all so i just want to mention that and i don't actually recommend that other people work with it either either it's absolutely terrible but next up raja says do you see yourself retiring on sql server or will you be taken out with a body bag i don't like working i'm going to keep talking i'm a step off camera here for a second because i'm going to go turn my air conditioner down a little bit i don't like working i like what i do but if i was given the choice between working and not working i would totally take not working there's a song by franz ferdinand called jacqueline and my wife and i sing it to each other all the time one of the lines in jacqueline says it's always better on holiday so much better on holiday that's why we only work when we need the money and that totally sums up our approach to life like the instant that i have enough money to stop working now for me it's it's also a gradual retirement i'm not gonna drop the microphone and walk away what i'm gonna do is i'm gonna switch to just selling recorded classes only we're probably two years away from that but the first thing that i'd segue out of was i would just stop doing live training classes and i would just do the recorded classes and sql constant care so that i could be disconnected for longer periods of time that that to me is what retirement looks like take me out uh raptor on azure uh says my thoughts on sql databases versus managed reserved instances the the thing is most of my clients have cross database queries they want to join between a couple of databases and as soon as you want to join across a couple of databases whammo you're in managed instance territory so that usually is the quick easy answer for me key tool says thanks i would like to thank you for your videos you're welcome my entire performance team learned the indexing and other basics you're a database legend the thing i like about the term legend is that it means that i'm not in reality and believe me those of you who know me will know i am very surreal but thank you yes that's very cool that's my whole goal is really to make it suck less for people on their way up then it sucked for me on my way up all i had was boring books uh to learn from and i'm like it was like reading dry biblical type stuff i'm like i just can't relate to it at all i want something that's much more fun so oh interesting yee chan says uh good morning what are some of the gotchas to look for from moving when you move to the cloud from when you move from on-premises to azure sql db assuming that you mean azure sql and you don't just mean vms in the cloud because that's totally different but assuming that you mean azure sql db the biggest thing i would warn you about is check your restore times how long does it take you to do a restore there's no sla there's no guarantee on how long a restore will take they'll guarantee you that you can do a restore but not how long it'll take go test it while you're running a workload through like a heavy workload through on your sql server pick a date and time in the middle of that workload and restore your database to that date and time and track how long the restore takes that has been the single biggest adoption barrier for a bunch of my clients when they went you know just kicked off for a store and it was an hour two hours four hours later before they got their restore and that that's where it got tough next up uh let's see here uh from etool says from over on youtube can if i drop a duplicate index which has a wrong column no so when you let me rephrase your question when you drop an index will cached plans still point to it no when you drop an index any query that goes to execute that had that plan in the cache or had that index in its cache plan we'll just get rebuilt a new plan in the cache the only thing you have to worry about is when someone had an index hint by name in their query if they ask for the index by name their query will outright fail which i think is a good thing conor says i notice that in your execution plans you have time stats is this a setting or due to a version it's due to the engine version and the ssms version and version and i just can't remember what the exact numbers are where we got it i would start by going to the latest version of sql server management studio because it's free it's totally free and it works with all supported versions of sql server so just go kitsequalserverupdates.com and that will take you to the link where you can download the latest ssms totally free and they put so many improvements into it since uh 2016. i mean really would you use a four year old phone would you use a four-year-old phone operating system no of course not you pay to upgrade your phone ssms is free go download the updates i don't understand some people are like ah i'm allergic to upgrades i'm like well ssms it's easy it's nice it's not i wish it updated itself but we're not quite to that place yet uh next up ericker from iceland says any solutions for uh checking diagnostics on stopped sql server services yeah the log so sql server two places when i say the log there are two things with the log one is the uh applicator event viewer in windows i would look in both the system events and the application events because sometimes there's something broken at the system level like a drive is down or you have corruption problems in a drive then the other place is the sql server text logs if you open up the logs folder where sql server is installed you can go through the text logs and see why the service didn't start up so those two are the the best places for me there's also clusters uh so if you have a failover cluster you would probably want to look at the clusters logs as well there are powershell commands for that but i'll be damned if i can remember what they are i just never remember those because thankfully i don't have to troubleshoot failed servers so much since i'm not on call with folks next oh and it's funny circular life asks the same kind of thing circular life says where do we look for logs in cases of a cluster issue you look at the clusters logs for that you're gonna have to the most efficient way is to bust out power shell i just don't remember those off the top of my head instead go to dba tools so dbatools.io is a powershell framework that you can use for server management it's no it's it's funny yeah it's i find it kind of funny too it's like g surgeon from the netherlands you know g-surgeon doesn't say from the netherlands but i'm always like amsterdam represent um oh ronald interesting question my data architect is asking for a data model of our databases what tool will do for me if they exist so if you just google for sql server data modeling tools there are dozens out there in all kinds of price ranges what i would probably do is look for a free one that will reverse engineer because once you get the data model the data architect should be using their own tool if someone has a title of data architect on their resume they ought to be able to generate an erd so an erd is an entity relationship diagram i would ask the data architect sure what data modeling tool would you like us to use i don't have one but you know which one would you like us to use and then that way if they're used to using one particular one they may even have it installed and if they don't then you can work with them on the pricing to get one but don't feel bad because you don't have one even the shops that do have a data model it's bs it's garbage it's it's not up to date it's incorrect it has all kinds of just outright lies inside there nobody's updated it in five years so don't feel guilty that you don't have one scott mcfadden says hi brent have you ever had issues with antivirus and sql server yes next question oh okay hold on i'll spend a little bit more time on this you know what i've also had is issues without antivirus and sql server because i see what you people do you remote desktop into windows machines and you treat it like it's your grandma's chromebook you go in there and you play fortnite and you download stuff off the web you copy paste from things you do hideous things on your production sql servers i am worried about protecting them from you you're part of the problem so i i know i'm kind of weird in this industry but i actually believe that sql server should have antivirus software because i see what you people do with it there's like chocolate all over the keyboard and i swear i see boogers inside there because you've been picking your nose while you were should kind of grow so i actually like having protection on the sql servers so adam says i only watch brent street twitch streams on my production server it's the only surfing i do want it very nice very nice um you chan says when creating a multi-column index does the order matter yes so to learn more about that go to brentozar.com click on the top of the site right now and i have a totally free set of training classes coming in late october that you can attend live and i with that exact question i teach you how to pick the order of multi-column indexes and i'll tell you a secret yi chan at least you asked most people think it has to do with the selectivity of the stuff inside the tables and they're a hundred percent wrong they're not even close so you chan at least you asked the rest of the people on this webcast they think they know how to do it they're wrong next up circular life says what are the risks if i enable database diagrams i don't think that there are any risks really he says but i want to find the model for the current database the thing is if the database diagrams aren't enabled no one's built a model before so that enabling that feature alone isn't going to be enough to really help you there next up let's see here the almighty beard the almighty beard i like that that's pretty good that's true the almighty beard says that do you believe the situation we're living in that has forced companies to uh get remote work will open the possibility for the average dba to work completely remotely here's the thing with remote work be careful with what you ask for because if you want companies to completely embrace remote work you are now in competition with everyone in the world and their cost of living which may be way lower than yours so to just be aware of when people are like yes i want my company to fully embrace from race remote work wait where did they get a dba that works for five dollars an hour just be careful with that back you're y'all are young you don't remember this but years ago there was this craze for outsourcing that companies thought that they would remote outsource everything that they had and that things would get magically cheaper what they learned over years was that outsourcing was actually more expensive not less expensive i think that remote work is going to go through that same kind of evolution that a year or so from now companies are suddenly going to go wait a minute it doesn't matter where we get people from where's the cheapest place in the universe let's go get people from outside that oil drum right next to mars and then they find out that the skills may be there or the cultures or experiences never really quite matched up to what they were expecting yeah i drive faster i had that same exact experience and yeah really it's been all over the place it's been eastern europe there's been so many different trends and crazes so i think it's going to happen again it'll just be a cycle that goes back and forth where it's great though is for those of you who put together blogs and youtube channels suddenly now you can take on clients from all over the world i used to have when i was a consultant with the option of in person or remote i used to have one price for remote and then basically twice that price if you wanted me on site is it so it was you know like x if it was remote and it was 2x if you wanted me on site plus it had a really long lead time and company still would pay to bring me on site i had some companies who are like no we have you have to be in the room with us it's the only way that we work and now all my clients have been no that's not true i have a lot of clients in the eu i just don't sell things online to the eu but i have lots of consulting clients all over the world so it's it will open up doors to you if you put out your presence on a blog it's funny i've had i've started to now have my clients who need t-sql help and i've been able to send them to other consultants who i never could have sent them before there's one just recently who needed help fixing a whole bunch of update statements and i was like you know what you could hire these people in the united states or you could hire these this guy over in europe and because now we're all remote work it doesn't really matter uh which one you pick it's just whoever's available first so it opens up some neat doors there so i'm i'm all in favor of it just it's going to be kind of weird how it shakes out with companies over the next few years or so uh tram star says what's the difference between biggest difference between sql and elastic search speed if you don't need joins oh my god elasticsearch is fast it's banana pants fast but it's not for reports you don't want to aggregate say 10 million rows with elastic search but just absolutely fantastic jaw dropping speed when you only need one row that's elastic search whereas on the flip side if you want to aggregate 10 million rows worth of financial numbers and you can't predict which columns you're going to sort on or group by that's where sql server starts to make more sense next up as she says does adding a primary key to a temp table matter it can it's fairly unusual though if you find yourself in the point where you're putting hundreds of thousands or millions of rows into a temp table then it can really start to make a big difference but if you're only putting hundreds or thousands it's less of a big deal but more more more when i see people putting indexes on temp objects usually they're just doing it because they think it's going to be faster and it doesn't actually make a difference in speed oh camila says can you tell us about how you study how many hours per week etc tips are appreciated i haven't done this for a while but what i used to do was i used to say three days a week were earning days three days a week i had to take money off the table and then two days a week were research and development so i would go build things write things learn things to further my career as you go through different stages in your life you you kind of tweak that knowledge back and forth or you tweak that balance back and forth so for me right now i just from knowing what i had on my calendar recently when the coronavirus hit i went to almost no learning at all like i went to just completely take money off the table because i wanted to put stuff in the piggy bank in case all hell broke loose so just a vision inside the the business there's me my wife who does the corporate administration legal accounting bookkeeping all kind of thing and then richie is our developer so i'm basically responsible for making sure that three people and their families and loved ones all are taken care of i took money off the table as fast as i could as coronavirus started hitting because i'm like look i got to make sure these people are well taken care of and i've told richie this when i first hired him if i have to lay him off i'm done like the day that i ever have to lay richie off i am quitting folding the business and i'm going to take a job somewhere as a full-time dba i'm just done so that that's my my paranoia right now now starting in uh it's like december mid-december i'm shifting way back over towards the learning side and it'll probably be learning like four days a week and only taking money off the table one day a week to do that you need to raise your billable rate as high as possible so i try to focus only on really high value engagements where i can help clients a lot in a very short period of time next up circular life says can we upgrade directly from 2008 to 2016. i haven't uh touched 2008 in years so i couldn't tell you unfortunately um next up uh shamville asks is there a good tool that can suggest normalization yes it's called a database administrator unfortunately there are no tools that can do that sadly next up pierre says i'm having trouble balancing between io and the number of tempdb files any good reference on that um when you say io and tempdb files i think you're chasing the wrong thing there i don't generally speaking for tempdb you want four to eight data files but adding more data files isn't about io so i would question why you shouldn't be having i o problems at just four to eight data files that's not really a lot of data files in the modern age of shared storage and if you're going above eight data files i'd start to question why so it's unfortunately that kind of deep in consulting is a little beyond what i can do on the stream he says i mean latency i hear you but that that has the number of data files has nothing to do with the latency because think about it in terms of the number of requests that sql server makes to storage the number of requests is no different no matter how many data files you have if sql server needs to drop a hundred pages into tempdb those hundred pages doesn't matter whether they're in four data files or a hundred data files so i think there's something else going on on your server and that's where i gotta go we'd have to take a look on the consulting side uh next up let's see here wick uh says for the sake of reassurance i'm migrating 2008 r2 to 2017. uh i can't restore diffs no recovery the answer rather than using standby so i just wouldn't use diffs i would use full backups and transaction logs and i know you say that you can't uh or you're doing logs to azure storage i start to get a little suspicious this just for reference this is 128 gigs whoopsie daisy i kicked the the tripod this is 128 gigs often when people say things like i'm dealing with big data and we start to drill down into the sizes it's smaller than this if we were talking about a 10 terabyte database that's different but if it's like a terabyte or less i i don't think that i would really go down the route of using differentials in order for a migration uh next up eriker says uh hints on automating recovery yes dba tools we've used dbatools.io it's a whole powershell framework that is designed to make server migrations and recovery much easier so you can automate going across dozens or hundreds of servers and and just automate the whole process and scale it out as well so dbatools.io there uh powered by tone says on youtube uh thanks for your answers you're welcome it is awesome that you'll share your knowledge and experience uh with us for the cost of waiting in line for a moment sincerely dev notes that not everyone waits patiently in line yeah i absolutely i'm all about uh giving back as much as i possibly can and also some of it is giving back on a technical level some of it is giving back on a personal level like i want y'all to to see and understand there's light at the end of the tunnel so often on technical jobs like database administration it feels like nobody understands what we do and it can sometimes feel like we're working alone and so it's it's i want to show you all that you're not alone there's a blueprint for getting where you want to go that there are other people here wanting to help move your career forward so i'm i'm all about it oh max asks an interesting one is there a wait stat that tells me that i'm running too many extended events sessions no but i love where you're going with this no but what you'd have to think about is what is the work that your extended event sessions are doing are they cpu intensive are they trying to grab execution plans you know the more work that you ask them to do the more it's going to slow down the sql server i'm going to give you a kind of flip answer try disabling them try disabling your extended event sessions for a day and watch to see how your weight stats look different from your previous day to this day that's probably your quick uh easy insight assuming that the extended event sessions are just for you the other thing i'd say is only run extended event sessions when you're actively working on the server don't be a hoarder you've seen those tv shows about hoarders where people are capturing all kinds of like newspapers in their basement someday this newspaper will be worth something spoiler alert it wasn't date is the same way don't go hoarding data because you think it's going to be useful for some day turn on your extended event sessions when there's something that you need to troubleshoot afterwards turn them off leave them off thune says nice channel seems very professional well remember looks can be deceiving but thank you tejas says i was troubleshooting on a server and i found that only 24 cores were active how do we find such issue faster run sp blitz so sp blitz will tell you when your cores or your memory are deactivated due to licensing issues plus they'll tell you about things like corruption databases not being backed up all kinds of issues that will normally catch up and surprise you in terms of performance so that's sp blitz i'm just gonna type the name over into the chat so that you can go google for that it's totally open source absolutely free and then you can go run a health check for free on your sql servers uh next up um mark says i posted this before great i can see your patient upgrading ssms is painful users have it pinned and when you upgrade those pins turn into orphans any alternative um it sounds like you're upgrading it on a shared machine normally i'd rather have people run sms on their own machines rather than doing it in a central place if you have to do uh jump boxes so if you're doing jump boxes for example then that's legit but otherwise i'm kind of curious i'm not sure what you mean about upgrading ssms is painful there that you shouldn't be upgrading it on people's boxes next up uh oh wow ernesto says which tool or query can be used to locate or roll back a specific instruction you're going to want a tool that can read the transaction log and there aren't many most companies have stopped selling them due to licensing problems um you can google for that and i've got a post on google for sql server jump boxes and i've got a whole post on that it's like a central rdp box so the problem is a lot of tools have stopped selling that because of licensing agreements with microsoft so if you search for sql server log reader you're going to find a few they don't work particularly well because what they have to do is read the entire transaction log and then turn that into a gui for you so that you can search for specific transactions sql servers transaction log file was never designed to be read by third-party applications so it's just not optimized for speed so unfortunately this isn't something that's very easy to do in sql server it's possible it's just super super slow dtoby good to see you again says nice event you gave thursday upon deadlocks thank you i appreciate it he says when the server faltered you were as cool as a cucumber i where i would be panicking but you might have been like a swan i think when demos fail when demos fail on uh or while you're presenting live in front of other people attendees love it attendees love to see you face a problem and fight your way back so once you really understand that and live it once you go oh the server failed this is my time to show them that i'm a human being and they'll come along with me for the ride it's a wonderful opportunity instead of a roadblock one of my favorite moments ever was i was presenting in miami and as i'm getting ready to present i was talking about high availability and disaster recovery the projector died because the power went down in the room that i was in the projector goes dark no slides i got my laptop and that's it i was so excited because i'm like there's a whiteboard here i got a marker we're gonna do this i'm gonna teach you the same concept just from me drawing i got so excited and the attendees could see that i was excited and just as i got started with sketching the power came back on oh i was so pissed which you would think most people would be excited but i was like damn it that was my my chance to really show off what i love to do so funny how that works um vigil freak says dedicated employees talking about back about the outsourcing thing dedicated employees will bend over backwards to keep your shop running whereas the cheap guy on the other side of the planet will slam his laptop shut when it's beer o'clock there's less of an engagement when you don't know the people personally bosses also have the thing where they want to be able to lock people in a room and buy a bunch of pizza and make them work until it's done which has pros and cons but yeah that's one of the reasons why companies seem to like database administrators to show up inside there contract administrator says if i'm learning azure sql how much of this will help me in synapse data warehouse is it worth exclusively learning synapse only learn a database if your company is using it do not learn a database especially an expensive proprietary cloud database do not learn it if you're not getting paid to learn it because stuff like azure synapse analytics is changing continuously any learning that you do is going to be outdated within 6 12 18 months and you may never get a job using it companies yes are paying a lot of money for people who know azure synapse analytics but they want real world experience they don't want somebody who played around with it on books online for like a time or two so don't waste a minute of your life if you're not getting paid to learn that database masimasimos asks what alternative do you recommend instead of replication i'm a huge fan of always-on availability groups because then it just moves everything inside the database instead of depending on complicated t sql that will break all the time hey let's not ask questions twice remember there's a cue if you ask questions twice we're just going to delete your question assume that you're done a next one shake says some of the queries were really slow in 2016 uh example row versioning on queries and temp tables and tempdb so whenever you have queries that change performance you want to look at the two execution plans and compare them to find out why they're different often it's not the database often it's things like different trace flags different numbers of cores different compatibility levels different statistics there are lots of reasons why queries can be slow on two different servers or even slow on the same server so start with just comparing the execution plans um ericker says isn't azure synapse just like any other mpp platform no the sketch is like if you ask someone hey how to sketch it out but the way that you actually work with it is going to be different so that's where it gets tricky next up uh oleg good to see you oleg says where do you start analyzing memory dumps after sql server crashes you know what's funny i have a blog post about that and i'm gonna tell you the answer just because it's real simple call microsoft i don't do any memory dump analysis whatsoever because it's such a pain in the rear and for your money opening up a 500 support case with microsoft is so much more cost effective than trying to do it yourself so it's the same thing i'll tell clients is i don't do memory dump analysis you'd be an idiot to pay me to do it go open a call to microsoft they can go way deeper way sooner than i would be able to next up oren says i'm creating a failover cluster of azure vms i'm am i required to use an azure availability set for those servers i don't think so uh what are the benefits of the availability set now i'm gonna go it's been a long time since i looked at this like a year or two since i looked at availability sets and if i remember right it would make sure that the vms were placed in different areas so that if one of them went down the other one would be unaffected that if i remember right is the sales pitch with it but like with anything in azure always hit books online because nobody else's advice is going to be kept up to date as fast as microsoft changes stuff your best resources for anything azure related is always going to be books online next up roman says i remember you shared a fancy way to change an int into a big int in large tables i couldn't find the video where you did it let's see if i can find it because i don't remember the terms off hand let's see so i'm going to see brent ozar change data type int bigent compression and there we go look at that hot dog i am so incredibly good it's almost like i've done that before a search for brentos are altering data types with almost no downtime and also make sure to read the comments because i think there were a couple of good comments inside here about gotchas and tricks that you could do inside with this so brentos are altering data types with almost no downtime next up oh let's see here uh modern says over on youtube hi your opinion on nosql versus relational databases um nosql is really good for a lot of things that don't need joins let me let me here here's how i'm going to explain it these are the signs that you want to use a relational database when you have relations between your data that's why it's called a relational database when you have relations between your data primary keys foreign keys if you need to do joins between tables at scale like you want to join millions of rows together and you want the results within like a second or two that's where relational databases still make more sense if you don't need that if you don't have relations and all you're doing is storing something and pulling it back out again key value nosql is a way better fit in terms of performance and cost the other thing that is a real compelling story for relational databases is reporting tools often managers just want to be able to run a report and they don't want to go run a mapreduce job in order to get their data out they just want to be able to run a query that's where relational databases still make a lot of sense next up richard says good to see you again richard uh availability okay there you go ah there you go there's the answer for that one so i'll leave that up for a second i'll take a sip of my tasty beverage here next up uh dj khaled says good to see you again dj khaled i says another yes another one do you recommend replacing t replication with manual t sql code for a brand new project the way that i would ask is are you going to have do you want two or more servers that are all being able to insert data at the same time if you're going to have multiple sql servers that are all required to do inserts at the same time then i probably want to roll my own replication if all i'm doing is copying data to another location for reporting services i would first ask if we also need high availability in disaster recovery if you need high availability and disaster recovery alwayson availability groups gets you that replication does not replication is a really crappy way to do h a and dr for a sql server it's replication is a is a normal way to do h a and dr for mysql postgres other database platforms just not in microsoft sql server oh scott asks one brent what is your opinion of cosmos db the person in me who loves to learn loves cosmos db because what dr namie and her team did is incredible it has a lot of really cool theoretical type advantages if you were going to design a brand new database from scratch for the next hundred years i think that cosmos db and google's cloud spanner are the two interesting databases to me they're very attractive theoretically in practice they both kind of suck today for if you're going to do things like shopping carts they're kind of expensive to go you're not going to retrofit an existing application to go use either cosmos db or cloud spanner for stuff like insert update and delete activity that's very concurrent and you also want to run reports over it but i hold hope for the future of cosmos db it's just not as much for existing applications today also it's not cheap it's kind of expensive but i can understand why it's freaking awesome that's very interesting uh next up fatima said do you have any tip about performance tuning of triggers just the exact same way that you'd uh tune regular code same way that you would tune regular code you would probably tune triggers as well i say probably i mean i tuned triggers from time to time but you just want to keep your locks as short and simple as possible keep as many statements or as few statements as possible try to avoid changing stuff you don't want to do all kinds of inserts updates and deletes you don't want to call other stored procedures functions etc just keep keep the code really tight and short now i know it's one we're at another one of those points where uh the queue isn't showing anything over on mine which is kind of weird oh it might be here let me i'll throw a couple of things into there um a friend asks which is certainly dev's really cool trick for doing stuff on youtube i should give another shout out to surly dev too as well surly dev is the heroic moderator who uh comes in here as a volunteer and uh takes care of y'all managing the questions queue this would not possibly be possible if it weren't for surly dev's help with this moderation stuff so ronald over on youtube says for crm and sharepoint sql servers can i just use ola holland grid scripts for backups sure oh totally absolutely you can use them for any third party uh database there are gotchas with biztalk if you're trying to manage microsoft biz talk then you have to do some backups that are aware of like named transactions but for almost anything other than biztalk yeah they're totally fine next up circular life says an absurd question alert what's an approximate value what is the common hardware specs you see in mid-tier servers oh oh i'll show you i have a blog post about that would say i have a blog post about that brent ozar how much hardware so i have and there we go how much memory is normal for sql servers um so if you search for brentosar how much memory is normal for sql servers uh there's a blog post there uh talking about that so it gives you a rough idea based on different database sizes how much memory people usually have out there same thing with cpu power as well next up let's see here chris says uh how much does surly dev get paid true story uh so i have amazon uh gift cards that i get every now and then from amazon associates from like selling things on my blog surly dev after the call i'm going to email you a 30 gift certificate for amazon it's amazon the uk and i can't use it it can only be used in country so i'm like oh here you go surly dev that's exactly the person uh who needs to go open windows says does cbc use service broker behind the scenes i have no idea don't you talk about two technologies i don't use very often it's not because they're not good i'm i'm ah there goes my opinions on things i don't think you should really be using either of those but whatever now let's see here let me put a couple of other things into the queue just a second here what do you think of nosql databases such as firebase i love the idea of firebase i think firebase is so cool the ability to have data that your end users can just go directly query from javascript that is amazing it's so cool uh i don't have any use for it personally but i think it's really amazing the problem with stuff like firebase is that there's no auditing there's no good security there's not good encryption around it so i tend to work in the enterprise database space where health care records is a great example you can't put health care records in firebase they're just too many legal requirements around it so i'm a huge fan where it fits it just doesn't really fit in a lot of my clients but i i totally love it like i see it all the time in in interesting use cases so speaking of which camila says what is the most common issue that your consulting clients have one far and away one one one big issue the database server's too slow the database server can't keep up with the amount of inserts updates sleeps up inserts updates deletes and selects that people are doing and they want to figure out how to make it go faster and then people will say is it a hardware problem or a query problem to me it doesn't it doesn't really matter because you could either throw hardware at it change the queries or change the way the data is stored it's just which way is the most effective for a client when they are facing problems that's okay so earlier thank you though next up compile says i'm using sql server in docker for automated end-to-end testing what's your take about running it in docker for production i actually answered that earlier on the screen so that's on me for not looking at that first m roman over on youtube says is there any way to speed up the database version upgrade process when restoring backups a recent migration took about eight hours to restore a bunch of databases oh that's a good question so for me it's all about log shipping ahead of time and getting the logs as close in sync as possible and then when it comes time to do the failover you're exactly right doing the version upgrade process can absolutely suck so what i would try to do is talk to the business about you mentioned 150 databases out of those 150 databases are there some that i can start the upgrade process on earlier like can we start cutting over some of those databases earlier than others and then identify which ones take the longest to roll forward and see if you can roll those forward earlier but is there like a trace flag or something you can use no unfortunately not that i'm aware of at least josh says what's the difference between a transaction log backup and a differential backup so in a transaction log backup you're backing up the log in a differential backup you're backing up the database pages the reason why differential backups can be much smaller is imagine that you have your whole database just consists of one row it's how much this one row tracks how much money josh has spent at lego land this year and so all day long you're out there at legoland just swiping your credit card and the transaction log is full of all these updates josh spent another thousand dollars josh spent another thousand dollars josh spent another thousand dollars the transaction log over the course of the day may be very long but if i do a differential backup and i only back up the data pages that contain your balance i only have to back you up once so it ends up being way tinier differentials are a better fit for constantly changing data and then log files are great when you need to get every single log transaction up to a specific moment in time next up get a choose says hello brent do you need to be certified to get a job and can you share how to prepare a professional resume i'm not currently certified i think microsoft certifications are total garbage too i mean just junk just worthless but they do have a worth if you're in a stack of resumes if you're in with a hundred other people trying to get a job having a certificate is sometimes the minimum bar that they use to exclude people so because of that sometimes you have to get a certification even though it's just junk and worthless i can't say enough bad things about microsoft's current certification program so that hopefully gets you started there next up uh control k control c says is there a formula to calculate required memory based on the size of the databases with some of my.com clients we're performance is a feature they'll say every query needs to perform as quickly as possible to them disk is the new tape if queries have to go to disk in order to pull data back they're like that's unacceptable it's going to add 5 10 20 milliseconds to the transaction we simply can't afford that we want them very close to up to date they're very near zero latency so in shops like that whatever the size of the data is the memory is twice that so if you have 200 gigs worth of data they'll have 512 gigs of ram i know it sounds like a lot but the thing is if you want your database to grow over time stop stop screaming if you want your database to grow over time let me mute that for a second if you want your database grow over time and you don't want to have to be constantly pouring hardware money into your sql server that's one way to accomplish that goal and sid ctrl k control c says imagine database of 30 terabytes in size you don't put them all on one server you shard out you scale out love horizontally and with lots of individual sql servers if you're gonna do like dot com type work you try to avoid putting more than one two three terabytes of data on a single sql server if you can and then we'll do it's eight o'clock so i'm going to do one last uh question before i quit for the day uh hosantia says is there an alternative for linked servers i use them for etl yes go connect to the server that has the data that you want so if you're doing etl you want to pick up a tool like ssis or azure data factory that lets you connect directly to the sql server that you want pull the data out and then put it into the correct location so check out sql server integration services or azure data factory both of those are good so all right thanks everybody for hanging out today hopefully y'all learned something and had a good time while you were watching i always have a pretty good time with these even though i lose my temper from time to time with people who keep copy pasting their questions i always kind of think that that's funny i want to uh and certainly dev will get a laugh out of this the next time i do one of these i'm going to have a little ticker across the bottom with the rules like you know the rules is you only ask your question once wait patiently in the queue and all that kind of thing in terms of timing i should tell you what my upcoming schedule is because i don't think sirleydev says i'm here to copy paste questions uh so a round of applause for surly dev there uh what is today uh next weekend i won't be doing this actually the next two weekends i won't be doing this so the next two weekends i have emergency clients uh both weekends so it's probably going to be a while before you see me again in the meantime if you want to go to brentozar.com and click training up at the top of the site i have free classes not this monday tuesday wednesday but next monday tuesday wednesday you'll be able to learn my fundamentals of index tuning fundamentals of query tuning and fundamentals or how i use the first responder kit adam says i always find it funny that you're able to schedule emergencies so it's really neat how the whole thing works so i have my two-day sql critical care and it's 69.95 u.s during the week but my first opening right now is in january so i tell clients if you want to pay my emergency rate you can over a weekend it's just that then you got to pay those emergency weekend rates and then you you you know then you can take over my weekend and i'm always amazed by the number of companies that want to do it these days it's just absolutely on fire so i think i've had weekend gigs for five out of the last eight weekends so it's pretty crazy all right so thanks every adam says weekend rates are times three it's only times two right now it's fifteen thousand bucks for uh saturday sunday so it's only times two but i've been revisiting that i think i'm gonna have to raise the prices just because i want some free weekends i'm like oh my god uh and it happens that this weekend's free because my weekend client for this weekend did an additional yeah absolutely i'm ramen you can hire my emergency weekend rates by all means all right so thanks everybody for hanging out with me and i will see you all next time [Music] adios you

Channel: Brent Ozar Unlimited

Views: 4,610

Rating: 4.9058824 out of 5

Keywords:

Id: Aa8srkcPs0A

Length: 124min 18sec (7458 seconds)

Published: Sun Oct 18 2020