Office Hours: Ask Me Anything About Microsoft SQL Server

all right welcome everyone to office hours i'm your host here around the shop uh brent ozar i should say good morning party people that's what i usually like to say especially since i have a glass of rose champagne [Applause] champagne hi necroduck uh good to see you um uh vp says uh may i know how to do something i tell you what i'll tell you a lot how to do stuff and that is you read the screen up at the top up at the top there we tell you how to ask questions inside this session and what i do is i take the most highly voted questions the ones that your peers all decide are really cool questions and those are the ones that i answer and the rest of the time when people ask questions inside the chat i make fun of them and drink champagne because yolo so let's go take a look at what our first uh oh hi from budapest hi macko so let's take a look at the first question that was in here you know it was really funny i didn't really intend on doing a q a session today but i glanced into pole gab and there were so many good questions that i just went oh man i really gotta jump in and uh go take a look at some of these like this one from mehdi mehdi said he got 41 upvotes so i can see a lot of people uh were uh really into this antonium is like thank you i'm glad you like it um uh necroduck says can you take one more sip of champagne now that i can actually accomplish uh so uh medi asked a great question there why is it sometimes slow to connect to the sql server oh i love this because it's something that i don't think i've ever blogged about either so one of the most common things that it is is simply that your sql server is overloaded in terms of performance that there's a whole lot of partying happening on your sql server people are running really terrible queries there's a less common one which is dns if there are dns issues or availability group listener issues where the client computer is having a hard time resolving the ip address of the sql servers availability group of the listener etc another less common one is network traffic like if you have application servers that are far away away from the main sql server but generally speaking the most common one is just that the performance is overloaded that the cpus are overloaded that memory grants are too hard to get that the availability or that the worker threads have been exhausted for your sql server so that the things that i would look at are in terms of your weight stats look in your weight stats for thread pool or resource semaphore these are weight stats that we flag in sp blitz as poison weights if you run sp blitz which is my open source health check for sql server it gives you a prioritized list of of reasons why we think the sql server is in bad shape either from a health perspective or from a performance perspective it'll warn you if you're having poison weights and if you have poison weights like thread pool or resource semaphore those can be reasons why your sql server feels like it's slow to connect now in reality thread pool means it's slow to connect resource semaphore means it's hard to get the memory available in order to run the query so all right it's a good starting point there i like that quite a bit next one whack-a-wanga asks i don't even want to know where that name comes from whack-a-wanga says how do i know when i have my indexes are hurting me more than they are helping me oh that's a great question too i can see why a lot of people upvoted that so the the thing that usually catches people is blocking blocking can happen when you're trying to do inserts updates and deletes and your query has to grab locks across so many different indexes i gotta get a lock on the address index i gotta not the whole entire index but just the rows that are relevant for your query gotta get a lock on the address index on the reputation index on the score index on the ship date index if you've got a dozen two dozen indexes on a table it can take a long time to grab locks across all these indexes update them and then release those locks plus the more indexes that you have the more likely it is that you're going to have collision between different insert update and delete queries so in uh some of my classes like my fundamentals of index tuning class i talk about my five and five guideline how you wanna aim for around five indexes or less and around five columns or less for each index then in my mastering classes like mastering index tuning i start to break down why that is and i start to give you demos in there showing how when you have dozens of indexes on every table especially on columns that are very oh drop table employees good to see you very nice [Music] uh when especially when your indexes are on very hot columns that that can be problematic but locking is the the first place that i'd go in order to answer that question next up i'll just ask which one is better i'll tell you which one is better which one is better is reading the information that's cleverly hidden there at the top of the screen up todd asks i'm a terrible person i know todd asks i have a database uh that does a whole lot of and very good exactly right cyberall uh i have a database that does a lot of ad hoc queries based on a third party app where i can't tune the queries only the indexes at what point is it useful to enable the database setting for supporting ad hoc workloads that switch if people want to flip it i'm fine with it it's never been a switch that's gotten me across the finish line like it's never been a switch where i've been like okay clients listen up every all what we really need to do here is we need to turn this switch on and then as soon as we turn this on all your problems will go away and we turn the switch on and people high five themselves in the hallways and they're screaming and carrying me on their shoulders this is not that kind of switch this is a polishing the outside of the car switch this is just like making things look better but under only extreme rare edge cases does it actually make things faster because what it is is it will let sequel sql server still has to compile the execution plan for that ad hoc query but it's just that sql server won't waste the memory for caching the execution plan if you have truly a lot of ad hoc queries it's possible that you could save memory by not caching plans for queries that you won't see again how many queries do you really have when we're talking about ad hoc queries generally it's users running the queries and i don't know about you but my users aren't fast enough that they can run enough queries that would make a significant difference there are edge cases where you have a really crappily written program that doesn't parameterize its t sql and that would be the edge case where it's running thousands of unparameterized queries per second each of which bloats the plan cache that would be closer to the point where you would enable optimize for ad-hoc workloads so it's one of those switches that doesn't really get you across the finish line i'm not saying you shouldn't turn it because i do know some bloggers who are like they turn this on with every brand new server they set up they preach the magic of it and i'm like okay that's cool golf clap you can do it it's not gonna hurt anything it's just that people aren't going to hug you when it's over it's one of those things that's going to be a very small part of a much larger solution so generally speaking whenever i talk about turning a switch or flipping a switch in sql server in order to solve a problem i'll ask what's the problem what's the problem that you expect to solve by flipping this switch and that would be the thing that i would ask there what magic do you think is going to happen when you flip it and then how do you measure that that magic actually occurred and i think when you measure the magic that gets involved with that like the reduced cpu or the reduced memory usage for the plan cash i think you're going to be a little underwhelmed in most cases that it's not really that big of a deal i will also say though that i've never told a client to turn it off there's never been a time where i've said no that you're this is causing harm for your server because the only time it really causes harm is if you have a whole lot of queries that have to run exactly twice the first time it gets run sql server will just cache the stub of the plan the second time that it gets run sql server will cache the entire plan but it would have to build the execution plan both times if you didn't have the setting turned on then the second time that the query ran the plan would be available in cash but who cares that's not that big of a deal how often do we really have queries that run exactly twice and that's it now if you do have queries that run several times 5 10 20 times they're going to get compiled twice instead of once i don't see that as a real big drawback either though so if people want to flip that switch i'm completely fine with it i don't ever advise clients to turn it off it just doesn't really make a difference so i'm like yeah okay cool you tried that and let's move on and find a real fix next up bogdan asks oh i'm gonna need to i'm gonna need a big gulp of champagne for this one bogdan asks hi ren oh is that wren oh i think that's my friend ren that i follow on twitter uh uh i think i know you uh what bogdan asks and this is like you know how when you have to do something you get distracted and you're like oh i should clean my desk this is one of those moments where i'm like oh it's wren let's talk to ren and see how it's going hi jr it is ran oh very cool good to see you um so this is one of those deals where i'm like oh i should distract myself with anything rather than answering this question so what do i think about running a sql server on kubernetes one of those moments where i'm probably never going to be able to work for anyone else again this is a good sign of that i think the people who run sql server and kubernetes in the year 2021 for any kind of production purposes are suicidal it is utterly ridiculous howdy jim good to see you i think i know jim i think i know jim from uh linkedin too as well um i think if you run sql server and kubernetes in the year 2021 for production purposes i think you're suicidal i'm not going to say you're an idiot because i don't have proof of that you might have a really good reason but i'll be damned if i can figure out what the good reason is you'll see though however you'll see people saying that sql server in kubernetes is the future just like flying cars are the future i believe that that's true i believe at some point we will have flying cars and we'll have production quality sql server and kubernetes and to be fair i think that the production quality sql server in kubernetes will happen a whole lot sooner than flying cars but when people say that they're running sql server in kubernetes and i go drill down a little bit deeper every single time it's well we're using it for experimentation and for development purposes sure you know oh paresh good to see you as well howdy sir good to see you [Applause] but sql server in uh kubernetes for production just makes no sense to me whatsoever and and so you i said that it's the future right i said that there's there's a chance that it's going to be production quality in the future does that mean that you should learn that today hell no not no but hell no because sql server is changing kubernetes is changing and by the time that those things are production quality everything underneath them will have changed completely anyway and you will have flushed your learning time down the toilet look i know that you're the kind of person who is investing your saturday afternoon or morning it's afternoon here in iceland but investing your saturday learning personally about things that are going on in sql server but you only have so many hours in the day there are only so many things that you could do and if you put one two three months of your life into learning how to manage sql server on kubernetes in the year 2021 you're flushing that time down the drain if you're not a consultant who's trying to write books on sql server and kubernetes so hold off a year maybe two years and wait until microsoft brings out something that's production quality i have absolute faith that they will just as at some point i'm going to need a hanger instead of a garage because i'm going to be driving a flying car next up neil asks my friend nicely done howdy brazil good to see you claudio my friend has a requirement that if that a client wants a copy this is like a game of telephone my friend has an aunt who has a buddy who has a sister who has a you know come on who has a copy of their production database to be available on another server that's very common the database is a tera as a terabyte so backup transfer and restore is slow so that's fair but the thing that you have and i'm this isn't your question but i'm going to say by the time that you hit the one terabyte mark you should totally be checking out snapshot backups before i became a sand administrator at one point in my life my sordid career i was a sand administrator for a brief period of time before i became a sand administrator snapshot backups sounded like voodoo that they would be able to back up a volume in a matter of seconds no matter how large it was and present that volume to another server within an also matter of seconds before i started becoming a center before i became a sand administrator it sounded like black magic like it wouldn't actually work by the time that you get to one terabyte you should totally be checking out snapshot backups they are game changing for the exact requirements that you're trying to explain imagine being able to take a backup from production and pretty presented in another environment in less than 10 seconds during your busiest peak periods that's the magic of how snapshot backups work sans snapshot backups now can they be horrible yes absolutely just like uh anything can be horrible if it's not architected correctly but if you want to learn more about this go look at pure storage [Music] pure storage they do snapshot backups for sql server but of course you would need their storage lots of vendors do it emc does it uh talk like that's that's the end of my my sand knowledge these days i'm like this is the only two sams that i run into these days because so many people run into the cloud um so backup transfer and restore slow one table howdy new zealand i hear y'all are doing lockdowns again that totally sucks i feel for you there although it's a smart thing to do and i applaud you for doing it not like you did it personally but just that you're having that's why you're here on youtube because you got nothing else to do our tape one table is 80 of the space and they don't require that is there a way to exclude that table from the backup yes but you're not going to like what the answer is so right now your database has probably one file group and that file group is called primary you have to back up the primary file group and you have to restore it it has to be the first thing involved whenever you do a database restore but if you have objects in other file groups you don't have to restore those so what you could do is you could copy that 800 gig table into or really move that 800 gig table into a different file group call it uh extra garbage or hot garbage it's inside this excuse me there's champagne inside the same database just at uh i know right can you believe it well i'm in iceland so this is actually an abnormal time for me this is why it's so sunny outside um so you move that into a different file group that way when you do backups you're still gonna have to back up the entire database but when you do restores you only have to restore the primary file group and then people can start inserting updating and deleting stuff in the primary file group without you having to restore the other 800 gigs so look at file group restore and piecemeal restore these are available in all editions res the restores can only be online in enterprise but i don't think that's going to matter for you since i think you're going to refresh your development environment anyway so to recap the answer first thing is go look at snapshot backups to see if your san supports that and then the second thing to look at is a file group restore and piecemeal restore next up we have eric eric says my team manages around a thousand sql server instances i know you drank my team manages around a thousand sql server instances most of our proactive monitoring suggestions i see make sense for single server or few server environments what do you suggest for larger scale okay the thing when you get to around a thousand sqls really even by the time you get to 20 sql servers the monitoring strategies really diverge and it's going to become one of two things either in some large companies they mandate the tool you use some large companies will say we use acme monitoring for everything gives us one pane of glass we just monitor every server that we have and those those kinds of products they monitor everything but they do a really crappy job at any one thing that they monitor they're kind of like the lowest common denominator for monitoring tools so they'll give you things like is the service up and running some basic perfmon counters everything i love that question though that's great why am i here they'll give you basic things like perfmon counters uh uh in some rare cases they'll give you dmvs but they really don't give you true insight as to what any of the products are doing it's not like they're great at sql server monitoring or they're great at oracle monitoring they're great at my sql monitoring they're just kind of crappy but they monitor everything and the dba teams in those environments they get no control over the choice of monitoring tool everybody is required to use the same thing in environments like that the dbas will often cobble things together using their own scripts because they're not allowed to spend money on anything else but they'll build their own troubleshooting utilities and and pull diagnostic scripts from different places those people are forced to reinvent the wheel i said that there are really two ways that monitoring happens when you get to that scale that's one way is the corporate monitoring tool the other way is that you see some dba teams are large enough to command the monitoring product that they want they're able to say look we have five million or we're spending five million dollars a year on sql server licensing we need to be able to understand what's going on with that five million dollars a year and those people buy a third-party monitoring tool that's specific to sql server so that they get they get a lot more detail and granularity the problem in necroducks is yeah i've essentially built this century one now i'll talk about that too in a second because it's an interesting discussion so the the problem with where you're at with the 1000 you and my mom those are the only and my wife i should point out my wife loves me as well so that makes three of you you can fight over me i'll i'll leave that to y'all um so the the problem with 1000 instances is that a lot of third-party tools struggle with a single repository of that size because the monitoring database is going to be like a terabyte five terabytes worth of stuff if you want to diagnose sql server to a really good depth looking at things like the plan cache and index usage and so forth so by the time you hit that scale it's so custom if you decide to use a third-party monitoring tool the number of repositories you use how you size those repositories and every discussion is basically different so that's why you don't see a one size fits all kind of like how if you go and google for how do i run a formula one racing team you don't see a lot of really good results because you're in a different kind of league there thanks a helmet too as well looking forward to getting back to to him when i go back to san diego now necroduck had said i essentially built century one over the years by building a lot of dmv queries here's the thing i know a lot of database administrators not just necroduck this isn't packing picking on metric necroduck this is lots of lots of people that i've known over the years who homegrown their who have home built their own monitoring systems the problem with that is that you're constantly behind the eight ball let me play you for a second all right everyone i built a monitoring system and it tells us what's going on with the sql servers here we go let's deploy it we're ready to go oh my god we just had an outage oh my i i have no idea what caused the outage let me go learn oh it turns out that there's an obscure edge case with thread pool and i need to go figure out how to monitor when i run out of work or threads all right i built that into my brand new monitoring tool let's deploy and keep going wait we had an outage there's another thing that i never could have foreseen before and as a result you're continuously behind the eight ball and you look stupid because every time you have an outage it's related to metrics that you didn't know you should be monitoring because you can't stay on top of that third-party monitoring vendors can they can continually invest in building new things inside their products it's not to say that they do a great job of it for example when brand new features come out like availability groups or like we were talking about earlier sql server under kubernetes often these monitoring vendors won't put in metrics uh to monitor edge case features because not a lot of people are using so i just if i was you i would recommend not trying to build your own monitoring tool if you can avoid it next up we have ashton ashton kutcher asks we all agree that database performance tuning is an ongoing task i always get nervous when someone says we all agree because it feels like they're getting ready to set me up for something i need a drink so this is we all agree that database performance tuning uh data quality yeah and that's so totally different you're going to have to to to write something different there every time because obviously there's not going to be a good third third party tool for that there's microsoft data set that's there's not going to be a good tool for that do you have a dumbed down short story that would help me explain to my business partners that sql server performance is perpetual oh that's easy so just ask what your developers are doing are your developers doing things are they building new features are they tweaking queries are they shipping changes to the application just ask your your business to stop i need you all to stop uh load changing anything about the application just leave it exactly the way it is also i see that we're inserting updating and deleting data we're constantly changing the data itself and the queries that we run can we stop doing that let's just stop doing that for a while so if we could all just stop changing the application and stop changing the database i'll be able to stop working on performance oh we can't do that oh you want the developers to keep changing the application and you want the data itself to keep changing well then the performance work is going to continue to go on but as long as the application and the data is changing then so will the performance work next up we have f and f dba f and fdba is that friends and family oh okay he's the database admin inventor she's the database administrator for friends and family can i just stop for a second and say i hate tech support i hate the holy hell out of tech support i get i get visibly angry when one of my friends or family asks me to do tech support like any kind of can you fix my printer can you get my wi-fi to work so bad to the point where i started buying my friends and family computers rather than doing tech support my mom got i love my mom she's wonderful uh she you'll sometimes see her on facebook inside some of these chats saying hi i'm brent's mom i taught him how to read and that's absolutely true uh but the uh my mom bless her heart was doing you know computer stuff all the times all over the computer just like anyways i got so tired of tech support not her problem it's a pc problem i was like look i'm just gonna buy you an imac and we're gonna call it quits and you're gonna go to the genius bar every time that you need tech support because i love you and if you continue to ask me tech support questions i am going to kill you so anyway back to friends and family dba's question tips for writing the best resume you can tell they're european because they call resumes a cv i don't even know what cv stands for constant velocity is all i think about for cars there's this constant velocity joint that helps cars turn around rated turns for experienced database administrators i have eight years of experience okay when you have a lot of experience you recruiters will tell you to keyword stuff they'll tell you to put every task that you've ever touched you put in a help desk ticket at some point you owe curriculum vitae oh that comes in real handy why would you not just say resume nobody speaks latin why would you put anything in about latin in the year 2021 well my i don't even know a latin word that i can make fun of um but uh so the recruiters will tell you the keyword stuff they'll tell you that you're an expert on jira javascript google chrome microsoft they'll tell you to put in every single log shipping replication anything you've ever done that's true if you're applying for a job as a stranger if you have no idea who's at the other end putting in every i'm not i'm in europe but ice uh icelandic doesn't really have a whole lot to do with latin icelandic is very different takfir um so right but you're not applying for a job then that doesn't make any uh sense uh to time or the way that you do that is do you read the instructions uh up at the top of the screen there the recruiters will tell you to keyword stuff everything in what i would tell you is if you're applying for a job where you do know the person at the other end where you're applying through your network when you're applying through people that you know what you should do is you should put the things in the resume that you like to do because if you hate doing tech support why would you put tech support on your resume if you hate troubleshooting replication errors why would you put replication error troubleshooting on your resume put the keywords in for the job you want not the job you have there's a reason why you're leaving the job you have it sucks it's terrible it's a saturday you're drinking to forget the job that you have on the weekday why would you want another one of those so instead what you do is put in the keywords of the job stuff that you love to do the things that you get excited the things that you want to learn about in your spare time that's where i would focus writing my resume stuff when i've got that much experience because otherwise it's just going to be this big old jumble of keywords because you've done everything but across the time of eight years i like updating stats to increase performance is it fast now okay good i'm going back to drinking let's see what y'all highly voted next so the next highly voted question over at pull gab comes from alex alex says hello hi alex uh any alternative for sql server management studio on mac os i tried azure data studio but i can't see sql agent always on in other folders what you have to do is you have to install extensions there or plugins i think they're called on azure data studio there is a plug-in for sql agent i want to say that there's one for always-on availability groups as well but the thing is with azure data studio it's really for developers writing queries it's kind of sort of for managers like management studio but not really management studio is much more about sql server management there are a bunch of oh that's smita that's a great question i'll touch on that here in a second um you know what uh simral bulge uh sigh mr robolch sty mr olbach we'll call him c c is actually onto something i agree i think you should just use windows and i'm a person to say that i use a mac but when it comes to managing sql server you know what i do is i have a jump box i have a remote desktop vm that i can go remote desktop into it's mine nobody else has it it has all the utilities that i need it has management studio whatever else i need to manage a project for a client so that that way whenever i want to do something i just remote desktop into there and i get my job done the reason why you like that is that then if something goes wrong with your laptop who the hell cares you can run anything you want on it as long as you can run a remote desktop client you can manage off of an ipad if you want to do that and the other thing is you don't have to sacrifice and have some crappy utility you can have the best of breed because let's be honest right now management studio is the best of breed for managing sql server there are some third-party applications that you can pay for they always lag really far behind management studio in terms of their ability to manage the features that are built into sql server like availability groups so i hate giving you that answer that the best alternative is running windows but drop table employees yes exactly what happened to my there it is now a minute ago smita said my your resume is supposed to tell your experience but what if not what your work desires are say if i'm a dba but i would like to do project management well you're obviously not going to be able to get your first job in project management from a database administrator role using your resume your resume isn't the tool to get you there you are the tool to get you there you have to go talk to the people that you know in your company's project management team or people who've left and worked for other companies in their project management teams and you have to say here's what i know here's what i've learned about project management there's nothing on my resume but i will work my tail end off in order to do project management very well but that's going to rely on your ability to uh build a bond with that person who's doing the hiring or who will refer to you otherwise jumping from dba to project management or to anything really from project management is going to be extremely hard if you haven't been gotten professional training on project management it's really hard also it pays way less than database administration so i'm not saying you shouldn't do it you should follow your heart if you love project management you should follow your heart ramen is very inexpensive and i think it's really tasty you can totally live as a project that went to a dark place didn't it moving on uh next up michael oh i love this question this is really good michael says can we somehow see the resulting query after we used views on top of views on top of views so that i can see the query that in the end is being run not in t sql no because it's not like sql server turns the view query into t sql it has this tree that it builds that says you want stuff from this object and this object and here's a filter you're applying to this object and here's how you're drawing joining to this other object essentially the execution plan is what you want to be looking at rather than the query because sql server doesn't turn it into a query itself but i love where you're going with that it totally makes sense like i can see how you would get to the thought process of saying sql server takes the views and turns them into a query but that's just not exactly how it works great question though and then let's see we've been going at this how long we've been going so like half an hour we'll do one more highly voted question so the next highly voted question comes from george suppose we have some rows we want to update but different columns in each is it worth it to create a different dynamic query for every column combination or should we just stick to set column a blah blah blah george let me tell you what's going on here when you use an update query and it just sets columns regardless of whether they've changed or not that can actually cause triggers to run if someone was dumb enough to use the update statement inside and i don't mean update stuff i mean update the update command inside a trigger to check whether or not a column is updated a trigger will fire even when you set a column to itself sql server will act as if the column has changed am not saying that you should go through the incredible hassle of doing the dynamic queries but uh kiran check out if you look very carefully i've cleverly hidden something right there at the top of the screen you may have to zoom in a little depending on the quality of your eyesight you may have to zoom in a little there to the top right but i've carefully put it up there at the top top left did i say top right top left it's over there too as well uh but so the overhead work involved with building dynamic queries for every single combination of updated columns when i describe the problem of triggers seems like it would make sense it doesn't i'm just warning you about a problem where sometimes people will dynamically craft update statements trying to avoid triggers firing the better solution is to to fix your triggers that use the update uh command or the update check to see if columns have been updated the problem with that doing uh the problem with doing dynamic queries for every single check to see whether or not a column has updated is then your plan cache will explode you'll have a gazillion different statements inside your plan cache for every different combination of t sql so i love that you're going there but you are overthinking the bejesus out of it just stick with a basic update statement well folks as you can see here we've started to get the phone we've been talking for about half an hour so we're starting to get the riffraff viewers the people who aren't smart enough to read the thing up at the top left of the screen that means that it is time for me to go refill my glass of champagne and do something other than answer questions on the internet delicious so i will stop there thanks for hanging oh and let's see here so i usually like to tell you what i'm doing for the rest of the day that's over the rest of the day i think erica and i are going to catch up on making the cut it's the amazon show that uh kind of replaced project runway on bravo tv oh cool drop tables are coming in on tuesday uh so the uh so making the cut is this show on amazon where tim gunn and heidi klum judge fashion stuff i am so totally into that it is just not even funny well the series 2 i think it is dropped earlier this year and so uh marco uh if your question on batch mode isn't the next highly voted question so uh unfortunately that's a no um if you do have urgent questions for me where i'm the only person who can respond by all means go to brentosar.com and click consulting up at the top and it explains how to hire me but there's just only so much that i can do inside my free time and now it's time to go off and watch making the cut we're like halfway into the season and uh absolutely loving it it was a great cast this year really uh fun group of people really talented so i will see y'all uh oh i'm doing another one of these monday actually i'm doing one monday at 1pm utc i think the link is already out there on youtube so i will see y'all later adios [Music]

Channel: Brent Ozar Unlimited

Views: 4,654

Rating: 4.9583335 out of 5

Keywords:

Id: j7XFeXcOZgU

Length: 40min 8sec (2408 seconds)

Published: Sat Aug 14 2021