Office Hours: Ask Me Anything About Microsoft SQL Server

all right welcome pack party people hello and welcome to office hours with me brent ozar um back in my home studio in reykjavik i've been out on the road we're in iceland until early october and erica and i have been alternating between working for one to two weeks and then being out on the road for one to two weeks and we just got back into reykjavik after driving all over the central highlands and the north of iceland hussevik and you may have seen if you've been watching my youtube channel you may have seen office hour sessions happening all over iceland because what i did is i shuttled my cameras around or one of my cameras around and did uh office hour sessions over all throughout the country those were disconnected because of course i didn't have any high-speed internet when i'm out running around throughout the entire country but now i'm back in reykjavik so i actually have high-speed internet oh i absolutely love it uh jared says any favorite spots one of my favorites that we saw this time around was the thieves waterfall it has something to do with a bunch of thieves being chased down here or there in a thousand years ago but an utterly epic beautiful spot and pretty far off the radar there was another one now i'm going to have to go back and think of what it was there was another oh the lava church the lava church in the central highlands there's a beautiful church basically made out of lava formation uh by by nature not by man but just utterly beautiful and if you follow me on instagram or go look at my instagram feeds i'm brento over there either brento or bruno's are i think of breno on instagram i have uh tons of photos over there mossy mossy moss good to see you welcome back yeah i haven't been streaming live in a long time um so let's go get to the questions let's go hit what y'all's top voted question is and it looks like it is from steve steve lipson steve says how can i figure out if my sequel server has too much or not enough memory also for those of y'all who are posting in questions make sure to read the url up at the top that gives you where you can post questions i won't be taking any question questions from like the live chat they all have to go up from there hi david sanchez um or david i should say i believe it is uh so how can i figure out if my sql server has enough memory this is such a good question and i'm going to twist around and ask the opposite how do you know if your sql server doesn't have enough memory well you'll usually see page i o latch as your top weight type page i o latch means that sql server is waiting to read data pages from the data file in that case you don't have enough ram to cache all of those data pages i'm not saying that the only fix is to add ram but ram hides a lot of sins and that's one of the sins that it hides if you don't have very good indexes if your question or your queries aren't sergible memory helps fix page i o latch weight types another weight type that you'll see is resource semaphore resource semaphore means that queries are waiting on memory before they can even get started sql server's got the execution plan all built things are ready to go but they need enough memory in order to run so sql server won't let them start that's the resource semaphore weight type memory fixes that but it's not really the best fix for that there are other fixes that are good for that so now now that you know a couple of things there now let's turn it around and ask it the other way how do you know if you have too much memory well you don't have any page i o latch weights at all you don't have any resource semaphore weights and users are excessively happy or i guess i'm in iceland i should use the bjork song right violently happy violently happy i love that song if it has if your users are violently happy and you're not facing any page i o latch weights or resource semaphore weights you probably have more memory than you need now what should you do about that well if you start backing down the amount of memory i like playing with mac server memory because it's one of those things that you could tweak while sql server is even up and running you don't have to shut down the server to do it you can drop mac server memory i'm not saying it's a good idea but if you're the kind of person who thinks you have too much memory you could start by gradually backing down max server memory until your users weren't violently happy or until you saw page i o latch weights to an acceptable page i o latch weights of an unacceptable level and resource semaphore or resource semaphore query compile weights but that's what you could go around playing around with all right next up we have let's see here next up is muchacho muchacho says do you notice big performance impacts on databases that are very normalized as compared to tables with minimum normalization does heavy normalization help with indexes because it uses more numbers to reference foreign keys you don't really want to normalize or denormalize to fix a performance problem unless it's a last resort because when you change table structures you also have to change the queries i would suck as a g surgeon good to see you uh amsterdam in the house um so i would suck as a performance tuner consultant if i walked in the door if i came in and i was like oh everyone your table designs are all wrong we should normalize this we should denormalize that it'll take me about three years to fix all the queries and the applications users would be furious users would be so pissed off so i wouldn't go to normalization or denormalization as here's what's causing the problem what i would do is just say okay here are the table structures that were given now given those table structures how can i change indexes change queries change the server hardware in order to get the best bang for the buck i know a lot of database administrators out there like to come in and say this is all wrong you should redo everything but you'll find that you get a whole lot more uh further in your career if you try to make the tiniest the least invasive change possible and look like a an amazing performance ninja that'll be a bigger bang for the buck for you brman says are you actually solving the problem then or working around it you're working around it so right like i've seen how unorganized your office is that office is a hot mess your stuff is all over everywhere your files aren't alphabetized you're not folding your socks correctly your clothes drawers just disgusting you're not washing your sheets the right way have you seen on tick tock there are people who will teach you how to fold your cl you're not doing any of that correctly in life we're all about trying to make the fewest changes possible like nothing else matters the fewest changes possible in order to have the biggest bang for the buck possible don't try to say i want to fix everything flawlessly because then they're going to look at how your own stuff is and they're going to be like oh you're pot calling the kettle black there no yeah exactly adam nails it right there next up victor asks what metrics could we show to business people just as a side note try not to use the term guys because it's i don't want to say sexist but it's not inclusive and i know you meant everyone say folks instead f o f-o-l-k-s that's much more inclusive so where what metrics could we show to the business people when they ask you how's the database performance oh that's such a good question i'm glad that you all uploaded this they're not technical people it seems that they want to see that we're improving the database performance consistently oh yes oh that's such a good question oh i just absolutely i love that question it's fantastic so they they hired you kind of for a reason and they want to measure you somehow business people are really obsessed with you get what you measure they measure their revenues their costs their profit their taxes every year they measure all kinds of expenses and numbers so what you want to ask them is all right what's the most important thing for you do you want the server to go faster is there some kind of query that you want me to measure i've worked with some clients where we've said the checkout process needs to happen in less than a certain number of milliseconds for example and they're happy when checkouts take less than let's say 400 milliseconds and i'm not talking about the human process of clicking through things to check out i'm talking about the uh the process of submitting a url and seeing how quickly the results come back other companies they're less concerned about performance and they're much more about how do we cut costs this often happens when they're internally facing systems and people don't really care about performance as much they're much more about how do we reduce the cost of servicing our employees of bringing rendering web pages and in that case they may be more obsessed around your annual licensing spend so ask them what's most important to them generally when i'm working with clients it's about either reducing their expenditures which means looking at how many applications can we cram into a sql server at a really low cost or they're all about performance like i don't want to say money isn't an object but they have specific goals for specific web pages or business processes that they want to happen in a certain amount of time but they're the ones who drive that um one other thing i would say too if you report into a big it organization another pair of numbers that general it managers like to look at is rpo and rto rpo and rto's recovery point objective and recovery time objective how much data you would lose during an outage and how long you would be down for and there are all kinds of cool ways to measure that if you search for my name and rpo and rto i've got lots of blog posts about how that kind of thing works next up chico chico asks what's the best criteria for creating indexes do the ssms index recommendations do any good you know there are some questions that i can ask i answer in a matter of like 60 to 90 seconds and we're out the door some of them require entire training courses and that's actually what that one is so i have if you go to brentozar.com and click training up at the top i have a one day class on fundamentals of index tuning yes where i bring out mr clippy as dal beer says i have a one day class on fundamentals of index tuning take that but then wait a few days or weeks then i have a three-day class on mastering index tuning that goes into much more details but you want the basics from fundamentals to sync in first before you go in and do the mastering class in that class i explain how sql server management how sql server builds the missing index recommendations the kinds of situations where they're wrong and how you can use those to build better ones great questions oh heckatron says those index classes are so good thank you woohoo appreciate it uh oh david says uh david from tegucigalpa uh says uh thank you for all you do for the community in answering these questions muchos gracias de nada david i or david i should say i appreciate uh that it's funny i bet you're wearing something a lot different than i'm wearing uh today this would probably i would probably burst into flames aside if i was uh down in mexico today today it is i don't have celsius set up i want to say it's like 17 celsius in reykjavik and to be honest the only reason i'm wearing this sweater is i bought a whole bunch of icelandic sweaters and i really love them and i'm like if i'm in iceland i should totally have this on a webcast but i am burning alive here even in iceland the things that i do for fashion and for y'all my pleasure george my pleasure next up we have simon simon asks whoops let me uh do a couple of things there so simon asks in your top 10 mistakes that developers make that don't scale you say that you're not a fan of goods are they a better choice for primary keys than an identity column in a high concurrency oltp database because they enable faster inserts of new records okay so let me ask you a different question simon do you have indexes on your tables sounds like a stupid question but bear with me let's say you got a customers table i bet on your customers table you have an index on name email address you have another index on address maybe an index on phone number those indexes are slowing down your inserts because you think just based on what your question is you think that guides are going to insert faster because they randomly spray data everywhere okay great but you realize that you also have impacts from all of your indexes too where we have to maintain all that overhead i find that people get so obsessed over the order of their primary key and then they totally forget about the all the overhead of all the non-clustered indexes they have i've walked into shops that have 10 20 30 indexes on tables and they're so obsessed over the primary key i'm like screw that go look at your non-clustered indexes we could get rid of half of these things right here and make your inserts go 10 times faster or more and we show you that during the fundamentals of index tuning classes and mastering index tuning classes today i i i go against the premise of your question i don't agree with that at all until you hit a thousand inserts per second sustained on the same table if you have one table where you're doing a thousand inserts per second sustained i'm not talking about peak workloads i'm talking about around the clock a thousand per second is your minimum number then you can start playing around with things like clustered index changes but until then hold that thought in your better bang for the buck is non-clustered index changes next up let's see what chris asks let's see here have a sip of my tasty beverage here hi uh abanov that's an interesting name there abhinav daily logs is logs like a reference to how you poop are you like showing off the fact that you're regular i'll give you a hand that's good that you're regular fiber is important um it's crabactocris so chris asks i don't know why anyone continues to watch this show chris asks given a physical machine a vm is not an option in multiple databases would it be preferable to split databases into smaller groups on multiple instances or keep all the databases on a single instance i will take a single instance all day every day because it's so much easier to deal with performance peaks and lo and valleys like sometimes one database will burst and it'll need a lot of horsepower temporarily it's easier troubleshooting there are so many things that are much easier when you're dealing with just one instance i hate instant stacking i won't even take a tomorrow funny um i won't even take a client doing instant stacking i get that there are economic reasons to do it but that's where i'm like virtual machines makes so much more sense i would never say that instant stacking is an option where virtual machines are not that's just backwards i would back up and question why virtual machines aren't an option david there we go but but chris it's a great question like i i totally understand where you're coming from i would just go a single instance myself um let's see here next up arriba arriba asks do integer primary keys and foreign keys help with performance i'm going to set us up i'm going to assume that you mean primary you've already got clustered indexes but now you're wondering whether or not you should put in primary keys and foreign keys there are super rare edge cases where foreign keys can help but they are super rare and i'm going to say something controversial that's just going to get me lambasted by my peers but it is what it is not here to make friends i'm here to tell you what i've learned over the course of the years i have had to pull out foreign keys more often than i've had to put them in the reason why is that remember how a few minutes ago i was talking about a thousand inserts per second sustained well when you get over a thousand batch requests a second sustained five thousand ten thousand batch requests a second there's overhead on all these foreign keys because every time you go to insert a row update a row delete a row sql server has to go check all the relevant foreign keys and there are overheads inside of that uh george just that's an interesting thing so that uh that is an interesting it's not the way that i interpreted the question but i love that question too and i'll i'll answer that too afterwards um so they are super rare edge cases where foreign keys can help in my mastering index tuning class i kind of debunked the bejesus out of that but if you search for foreign keys on brentozar.com both me and eric darling have written a bunch of blog posts showing how they don't usually make real world queries all that faster now every now and then i'll see them being useful here's my general takeaway rule when you're building a brand new database from scratch you should use foreign keys and i don't really give a damn whether you use integers or whether you use guides or even all kinds of other combinations of data types i talk about how you choose clustering keys in the mastering index tuning class but when you're just beginning an application for the first time here's what i would tell clients use an identity column use that as your primary key on every table just as a starting point and put in foreign keys to help sql server understand how your data relates to each other because in the beginning when you first get started building an application that's when the application is the most likely to accidentally insert bogus data or update bogus data and having foreign keys helps prevent that but if you find yourself hitting the point where you're at a thousand batch requests a second or higher don't be surprised if you have to remove some of the foreign keys due to their overhead and if you have an existing database don't be the kind of dba who walks in and says we need foreign keys everywhere i'm going to put them all in because what you're going to find is that there's a bunch of garbage data in the application already and they're going to look at you to figure out how to solve it and you're just adding more busy work on yourself than you originally had and nobody's going to thank you for it when that's over next up oh y'all are just asking all kinds of indexing questions today matt says is there a benefit to having an identity column on a table if it's not used in any queries or joins in the mastering index tuning class i go into what the benefits of clustered indexes are how they work and how it helps sql server understand how to organize and sort your data if you don't have an identity column starts at one and goes up to a bajillion that you want to use as a clustering key that's okay pick something pick something that's static unique narrow and ideally ever increasing s-u-n-e static unique narrow and ever increasing and in the mastering index tuning class i talk about where those guidelines come from and i show you how each of them improves performance you don't have to do all of them for example goods aren't ever increasing but the more of the sunny principles sunny static unique narrow and ever increasing the more of them that you follow generally the easier of a time performance management you're going to have with indexes next up we have f and f dba fnfdba says i have a select query running on multiple sessions one of them is runnable and the other nine are suspended with the same spid well hold on that that doesn't make sense because you're saying multiple sessions with the same spid that's not how sql server works here's what i think is probably happening whenever you see queries running from one spid if it's the same query and it's running multiple times if you run tools like sp who is active or sp blitz who and they show one line plus several others for the same speed what's happening is the queries going parallel and with parallelism what's likely happening it's tough to see without the actual execution plan but based on what you're asking in the question this is what it feels like is happening one of the threads is still working while the rest are finished in that case the query is not really blocking itself it's just that sql server didn't choose to balance the work evenly across all of the threads if you want to kind of prove that out what you could do is you could run the query with option max dot one and if you run it with mac stop one it's going to go single threaded there are cases where lower max stop numbers actually run faster for specific queries you may have an issue or sql servers doing unbalanced parallelism try running the query with option max stop one and see if it's faster am i saying that option max stop equals one is always the right answer no no no no no of course not in a perfect world we tune the queries or tune the indexes and i teach you how to do that in the mastering classes but some people can't afford my mastering classes i totally get it they're expensive because i'm not cheap i'm not crappy generally the good things cost you money and i cost a lot of money just kidding uh but we go into the details on that on the parallelism classes if option max stop one or in the mastering query tuning classes if option mac stop one gets you across the finish line just use option mac stop one and then if you want to learn more about it go hit my mastering query tuning classes all right so folks for your questions make sure to go read the instructions up at the top of the screen i hopefully hid them right there at the very top of the screen to make them easy for you to find i understand that some of us have very short attention spans but hopefully it's it's just two lines you'll be able to pull that off oh thank you matthew i just got a haircut today oh yes i just got a haircut today as a matter of fact so shout out to i can't remember my reiki vic barber's name but just going to a new barber today for the first time next up let's see here cockered kakara kakaradu that's such a cool name cockradoo cockrara do that's it oh that's so cool cockraridoo asks uh we have many applications and currently they're sharing the same databases in the same tables should we look at breaking up this database i'm worried about data duplication oh that's a good question so i kind of hinted at this with one of the earlier answers you want to make as few changes as practical in order to get the biggest bang for the buck possible if you were talking about over a terabyte worth of data then i might be interested or if you were worried that it was going to hit a terabyte of data but if it's under a terabyte of data it's not really that big of data anymore i i keep a backup database or a backup drive here just as a kind of reminder for clients this is one terabyte you can buy these on amazon for like 200 so just keep that in perspective when you think about worrying about data duplication i'm not saying that all storage is cheap but if you're dealing with less than a terabyte it's just not usually worth that much i'll say that you are dealing with a terabyte or longer or terabyte or more what should you start thinking about what you think about is you think about where the data would need to be restored to in terms of a point in time do you have some data that could be restored to a different point in time or it would be okay if you lost some of that data i'll give you a great example logging tables and reporting tables logging tables and reporting tables tend to be large and they also tend to have a kind of low value because you could recreate that data or it's really low business value start as you're building new tables and i'm just talking about new tables when you're building new tables look at their or interview with the developers creating them and say hey could we put that in a separate lower value database call it our reporting database or our logging database but just know that when you restore multiple databases together with sql server it's really hard to get them at the exact same point in time when you're dealing with cross database transactions next up we have uh let's see here brennozar jr says what's the best data type for primary keys as i kind of suggested with the sunny principles integers integers driven by an identity uh default will be static unique narrow and ever increasing and we discussed that in much more detail over in the mastering index tuning class next up we have ray ray says should i be setting read committed snapshot on in my local environment since that's the default for azure sql databases i assume that you mean that your production database is in azure sql db and in that case yes if your production database is in azure sql db you want to make your local development environment behave as similarly as possible so rcsi on will help mimic what azure sql db is doing i just want to give you a gotcha warning there though the sql server doesn't necessarily behave like azure sql db because your azure sql db might have a totally different core count a different amount of memory and there are different behaviors up in azure sql db than you get in the on-premises box product so just do you can't make execution plans match exactly i'll give you an example scalar function inlining i don't think that sql server that azure sql db has still turned on uh sql function inline or scalar function inlining yet even in azure sql db today so whereas it's on by default in sql server 2019 with compatibility level 150 or higher next up we have wee wee dar asks we have a third party oops let me clean up a couple of things here there we go we have a third-party software and we host the database on our own vm performance is garbage database size is gigabytes i don't know what that what that means uh did i vendor blames on network activity i don't see much activity on the server what can i do oh that's a great question so the the first place that you start whenever you're troubleshooting sql server performance is weight statistics weight statistics sql server is constantly tracking what it's waiting on and i'll show you how to query it so if we go over here if i go over into i've got a sql server here the version doesn't matter all versions behave the same way if i go into sql server and i type oopsy daisy let me get this dag dab i'm hitting every possible different keyboard combination when all i really wanted there it goes all i really wanted is acting all slow now sp blitz first since startup equals one if you run sp blitz first since startup equals one splits first is part of our open source first responder kit if you google for sp blitz first there are instructions on how to download it how to install it not totally safe to run in production environments i do it with emergency troubleshooting all over the world and then look at what your top weight types are now in my server tuning the zoom in and out is completely freaking out today on remote desktop in my mastering server tuning class i teach you how to interpret those but the one that you're looking for is async network io if async network io is your top weight type then it does mean that networking is an issue with your sql server it does that that's not the only possible answer sometimes it can be that applications are dragging too much data across the network sometimes they're doing select star when they don't really need to they're bringing back data that they don't need but just to verify that's the first starting place that i would go sp blitz first since startup equals one will show you your weight set stats since your server started up and if your number one weight type is async network io then the vendor might be on to something if your weight type is something else then the vendor may be on something as in crack or something to that effect all right let's see what the next uh most highest voted question is here uh so we will uh oh we need wee weed ours followed up with the database size is one gigabyte it's entirely possible that uh that uh networks is the deal so do what i just said there in terms of the answer next up lars lars says we need to move away from documents stored as xml in the database um to something external any thoughts on how to achieve this so you start by just working with your developers whenever the developers want to store things inside the database you put them somewhere else looks like they they can't all be long questions right i don't have a magic wand i mean i got like an apple pencil but i can wave it around it doesn't really do anything if you want to store your data your data somewhere else you just start storing your data somewhere else i can't believe six people upvoted that question what on earth i'm not saying it's a bad question it's a bad question next up we have uh melina sharma asks i've been watching a lot of sql performance related videos all the videos talk about is adding an index or tuning a query but i have to tune a whole database which has more than a thousand stored procedures each of which have its own logic any recommendations on where i should begin oh absolutely so uh where you begin is number one find your server's top bottleneck number two find the queries causing that bottleneck oh man i i wish that this was really easy like i could teach you in the span of 60 seconds i'm gonna get you started but really this is the core of where my mastering server tuning class comes in so you have just a second i said for somebody else's answer i said go run sp blitz first since startup equals one and i said this will tell you what your top weight type is then based on what your top weight type is you can run sp blitzcash sort order equals and then you can't put in the top you can't put in the weight type name you have to know if it's cpu related storage related memory related or so forth what sp blitzcache will do is it'll give you your top 10 most resource intensive queries sorted by this sort order like we'll sort by cpu by which queries read the most data which queries took the longest all kinds of stuff like that so hopefully that at least gets you started if all else fails you could just start with sp blitz cache sort order equal cpu and that'll give you your queries that are burning the most cpu power but i just get worried about people working really hard on working through these top quick 10 queries because your bottleneck may not actually be cpu your server cpus might be sitting around not doing anything that's where my mastering server tuning classes teach you how to understand what your top bottleneck is and then how to focus the on the queries that are causing that bottleneck there you go yeah all right it is it does seem much easier said than done and we go into all the intricacies over in mastering server tuning all right next up now andrea says will sql server 29 utf-8 support be a game changer in terms of storage and performance no when i hear the term game changer what that means for me is everyone needs to immediately report to training because this is going to change the way that you do databases this is going to change the way that you do performance tuning throw the old rules away and they're all new rules in town and it's not it's a tiny feature i get that somebody in microsoft worked really hard on it and they're they're really proud oh that last note was from michael j swart i just now happened to see it would go welcome to the class sir not class but session whatever i i get that somebody at microsoft work really hard on utf-8 support and i get that there are dozens maybe hundreds of environments in the world where it helps but it's not like even the places where it helps people are going stop the presses change everything everyone report into training at once this changes the game it doesn't it's just a feature it's just something it's a it's a tool at your disposal that you can use i'll give you an example of changing the game azure sqldb azure sqldb changes the game because now depending on how much you spend there are hard-coded limits on a lot of resources like transaction log io or temp db space and you have to change the first things that you look at when you go and approach it your azure sql db can restart at any time and you don't know when it did that changes the way that you do monitoring you have to be much more vigilant about collecting statistics the right time because they just simply disappear your index usage data may not be available or useful to you at the time that you go to tune indexes for example when you move from sql server to azure sql db that is what i would call a game changer and i didn't say it made the game better it just changes the game and i'm not saying azure sql db is bad it's wonderful it's amazing i'm really excited for it i'm legally obligated to say that i'm not really legally obligated to say that all right next up we have joshua joshua asks what's the best way to find out which query has filled up tempdb data files if this cannot be done retrospectively then what about the best way to detect or prevent future issues so unless you put something in ahead of time there's no logging in sql server to say what query used tempdb space 15 minutes ago once that query finishes that's the end of that while the query is running live you can run sp who is active if you google for sp who is active i've got a training video out there showing to you totally free out on youtube but a brilliant open source free stored procedure from adam mechanic and when you run it one of the columns in the output is temp db allocations which if i remember right shows you the number of 8k pages that the query has used up in tempdb now the thing is though so one one thing that i could say is if you want to troubleshoot it after the fact what you could do is log uh sp who is active to table every five minutes or however often you want to do it and if you search for brentos our log sp who is active to a table there are instructions out there written by tara kaiser who explains how to do that as well so you could go back and look over time and see which session was gradually using up more and more attemptdb allocations until the query disappeared so that the thing is though queries aren't the only uh thing that can fill up tempdb diversion store is another classic example triggers are another classic example and i show you how to troubleshoot those in my fundamentals of tempdb class but start with logging sp who is active to table first because you can probably find it let's be honest most of the time that when it's happening it's because some bozo is building a temp table uh that's trying to process the entire data warehouse over in temptb because they've read a bad blog post once arun the answer to your question is cleverly hidden at the top of the page they're right up there at the top and believe it or not that's that's actually the answer right there that's where you go next up make it rain asks will there be a sql server 2021 or 2022 anyone who knows can't say which also tells you that anyone who says can't know i am not saying that i know because i don't but let me tell you what i think is going to happen for several years microsoft sql server team was talking about how they were going to ship updates faster and faster that they were going to have this fast train of updates and they were going to ship a cumulative update every month and then every 12th month they were going to drop a brand new version and i'm like uh uh no timeout that vendors can't support things that quickly you can't have like if we're gonna support sql server for five years we can't have five different major versions of sql server out there they're like shut up get out of the way the fast train fast train coming through you're gonna be a caboose sucka and i'm like okay good luck with that at first it looked like they were gonna try to pull it off they got sql server 2016 then they ship sql server 2017 and then they sh this train started slowing down a little bit because they couldn't get 2019 out the door and fast enough and if you watch when they were bringing 2019 out parts were falling off the train they were like we have availability groups in kubernetes oh wait we can't make that happen in time we're running out of time so they started shoveling features out out the door off the train throw uh throw kubernetes from the train oh there are so many funny jokes inside there uh so they ended up dropping support for stuff on the way out the door and they still couldn't get it in within a year it's now august of 2021 traditionally when microsoft shipped sql server in the past they would do three to six months worth of uh worth of previews before they actually shipped the final version it would be really hard for them to get a sql server 2021 out the door it's doable it's possible but they would have to start announcing it today like they got to start getting their hustle on what i think is going to happen is that they're going to consider sql server to be the box product to be more stable and that they're going to release it less often and that they're going to focus the fast train up on azure sql db which is really frankly smart with azure sql db they don't have to worry about walking you through installing it or having problems with an installer or teaching you how to use availability groups or teaching you how to use clr support because knowing that stuff is up in azure sql tv they can just iterate really quickly up in azure sql db learn their lessons and the parts that don't work then they don't ship those out to the box product and that's kind of the end of that next up richard asks wow that's an interesting question what's the best way to implement version control on a database schema i don't do any version control work at all but i'll tell you who does alex yates alex yates is out of the united kingdom and he does a lot of database life cycle management work so if you search for alex yates dlm as in database lifecycle management he's the person to ask about that he's on twitter he's super approachable a really nice guy complete uh sharp uh person but he's the one of the few people in the world who can tell you the pros and cons of lots of the different solutions out there next up caboose asks funny that it's uh caboose um caboose asks what's a single sql server upgrader tuning thing that will yield the best bang for the money indexes indexes because indexes will fix so many terrible problems you don't have to change the code you don't have to get approval from management to go and buy new hardware you don't have to worry about long upgrade uh times when you add in things like memory you often have to add it into the production box plus your high availability boxes plus your disaster recovery gets really expensive especially when people are renting their boxes either in colo or from the cloud adams has missed an uh opportunity you should have said higher brand ozar no because i'll tell you what so index tuning would be the number one and the way that i would go about doing it is attend one of my training classes see what i did there because it's much cheaper to attend one of my training classes than it is to actually hire me you can go watch my mastering index tuning class watch fundamentals first i know so many people would go try to jump into the mastering when they're like whoa i didn't realize i didn't even know the fundamentals um so go watch the fundamentals one first it's like 89 bucks it's really cheap then go watch the mastering index tuning class you can do both of those in less than it costs to hire me that's the most cost effective one next up let's see here wannabe a dba says i've had five uh let's see here i've had five or different primary key trends like what on earth i run a query any solution i don't even understand what you're asking logical reads of more than a thousand a thousand logical reads is nothing remember a page i usually keep a printed page here a one logical read equals one 8k page so you're talking about a thousand pages that's eight megabytes eight megabytes eight megabytes nobody gives a damn about reading eight megabytes i mean like you get to some edge case if you're running amazon shopping cart then sure you care about things like that but a thousand logical reads who gives a damn just ship that thing go that's that's totally fine don't sweat it next up drew says we have too many agent jobs doing far too much and everything everybody thinks they need to be able to manage them until that now it's been give accomplished by giving people uh sysadmin access i've tried all the built-in roles and none of them will suffice do you have any suggestions oh man this rings a bell post this on stack overflow or dba dot stack exchange because there's an answer to it and i don't know what it is because i don't do security work but i am vaguely remembering that around 2019 something dropped in the way of giving you more granular permissions for sql server agent and i don't remember what it was you might even find it by looking at built-in sql server roles built-in sql server roles in the sql server 2019 books online because i want to say it showed up there but if not go to dba.stack exchange and the way that i would phrase it is always try to boil down your question to the simplest part of it the way that i would phrase it is what's the minimum amount of permissions required under sql server 2019 to manage sql server agent jobs and that'll get you the answer very quickly next up uh brewski says what do you think about pinal dave dave's approach i don't know can you be any less specific if you could be just more broad please don't give me any information the best questions just ask things like how you you're almost that vague you're not quite there you got a little bit more work than you can do but if you could just get a little bit more vague i'll certainly be able to ignore your question even faster and then we're coming up near the top of the hour we'll only get a couple more questions in philip says we have an application that is connecting to a database through this essay account i don't want this to continue and i wonder how best it is to determine what permissions are actually required for the application to work the vendor no longer supports the application oh that's such a tricky question so what you're really asking is is there a way to audit everything that queries do and i don't know of an easy way to do that because what would happen if the application has a function built in that only runs once a month or it only runs when the users actually go and run that particular function it might do things like truncate tables it might change mac stop i have seriously seen an application that goes and changes max dot to one temporarily while it goes and accomplishes certain tasks and then pops it up to unlimited so i don't know of an easy way to audit all of the permissions that a specific application needs but if you wanted to try if you wanted to go and change uh just to give it a shot to see what would happen i would start by saying give that build them a brand new login give that login permissions to be the database owner of the application of the database where it's working on give them full absolute permissions on that one specific database but nothing else and then wait and see if people come back and complain and say oh we're having problems with this application because we can't do whatever if you wanted to get ambitious you could try to uh you could try to log every time there's an error from that login i just i'd be really hesitant to do that because of course you're going to run into all kinds of other application errors from it and then we'll take one more so let's see here uh ralph says and i'll go ahead and stop taking questions from now let's assume we have two different networks and a three node cluster we have one node in our primary site and two nodes in the dr site my question is will the cluster survive if network splits occurs and i don't have any witnesses in other words what is a witness a must for a multi-subnet cluster oh man this is so big that you don't want to take somebody's advice just over a live stream you really want to sit down and sketch out what's going to happen when the network drops what's going to happen when our dr site disappears what's going to happen when the primary site disappears what failures do we want to be automatic what failures do we want to be manual then based on that you can change the number of votes that each member of the cluster gets i get really nervous in your configuration where you have one in the primary data center and two ndr because by default if you have a network blip the primary is coming down so you either want to take away the votes from dr or you should just expect that the primary is going down whenever the network split happens so i'd just be really nervous with that kind of thing all right there is about an hour's worth of questions and answers there hopefully y'all had fun and learned something i am now going to go out and go have some dinner it's about five o'clock p.m here in reykjavik and i heard my wife get home a little bit while ago so off we go thanks y'all for hanging out with me and i will see y'all at another office hours adios [Music] you

Channel: Brent Ozar Unlimited

Views: 6,735

Rating: 4.9398499 out of 5

Keywords:

Id: MBPSFuVOFKk

Length: 52min 45sec (3165 seconds)

Published: Mon Aug 09 2021