Ask me anything about Microsoft SQL Server

good morning party people how's it going this morning uh welcome to our q a stream the last q and a stream for the next couple of weeks i'm teaching a weekend class next weekend doing mastering parameter sniffing on friday saturday sunday and then after the weekend after that i think we're going to be in malibu california so taking the weekend off helmet is all pretty again yes jim went and got him detailed and had a wonderful little drive up and down the beach and i was gonna i was like i was gonna take a picture of him and put him on instagram and i was like i ended up getting him a little bit dirty already just driving uh pretty quickly so yes uh santa says yes look at the light show on the keyboard i had totally forgot that i could put light shows on and it seems like such an obvious thing to do when i'm like broadcasting and streaming uh that i should just leave it up and running doing some kind of light show because they have all kinds of really cool little different light effects that it'll do so i figured oh i'll just leave that up and running so at least it'll give people distraction when the the question is boring or unrelated to them it's just something neat to see um and i don't leave it on 24 7 of course because it would burn through the battery probably in no time but kind of fun to have just exactly using a gamer keyboard next thing you know i'll be having those little uh ear uh the little ear what are they called i don't know what the little ears are on the headphones and all that uh jan says about usb on-site you can replace a key to k1 i don't even know what the gear what you're saying there but okay cool jorah says my daughter loves the rainbow light show i only got the white leds i didn't get the rgb led version i don't think cat ears that's what i was going for arthur you and i arthur we have something in common there yeah probably what else on this stream is like what uh so i'll go ahead and start adding your questions to the queue so like simon i see your question and they're all lie that hey man i haven't seen you in a while uh welcome drop table says good uh good next up gaming chair i have a herman miller air on and i never broadcast with it like it always seems so much more natural to talk with standing up so that i can use my hands and stuff like that um oh you want now so the key the k one i didn't like as much uh the k1 if i remember right it didn't have hot swappable uh keys like you couldn't switch the key switches if that was what it was i'm trying to remember what the gotcha was with the k1 but there was something i didn't like about the k1 i can't quite remember what it was i should change my white balance just a little bit so that it's not quite so bright inside here uh so neil says good morning good to see you again neil from durban south africa uh all right so let's see let's go ahead and get started we've got one question in the queue feel free to type in whatever questions you want sql server related consulting whatever and we'll go through first come first serve so first up is simon aut simonnot says i found my compat level was at a hundred i set it to 130 and noticed some queries up to 500 500 times faster i found no regressions are there any other considerations so so the thing is let's say that it makes just one percent of your query slower if it makes just one percent of your query slower your server runs a lot of different queries and so one percent of queries going slower can be catastrophic for some businesses if it's especially the one percent of queries that they happen to care really passionately about so what microsoft will tell you is is that you should in a dev or staging environment you should replay your entire workload you should use tools like the database experimentation assistant the dea i'm like of all the acronyms to pick the dea and then go see whether or not you get any regressions i i don't know anybody who does that most people do the same thing that you do they just go and turn it on at 1 30 and go see what explodes so as long as nothing exploded for you you're good there's not like you're exposing yourself to any new serious sql server bugs or anything like that it's usually just a query performance problem arthur says brent what was the strangest situation with sql server that you have ever found yourself with um oh strange i found so many strange situations um i don't know that i've told the story on the stream of me uh essentially firing a client this is probably this well okay so i'll tell you the one that was probably the the strangest so i i bought a suit one time because i had to go into a client this is right when i was getting started doing consulting and the client said we'll hire you on as a consultant but you have to wear a suit everyone who comes into these offices wears a suit and i was real new at consulting and i thought okay maybe that's normal maybe maybe some companies maybe the good jobs maybe the good jobs require you to wear suits i'm like okay i go and get fitted for a suit get it tailored and all that kind of thing and i go and show up and i'm talking to them it's just one person at this company just one person big huge company there's only one person that i'm talking to it was really clear that i was only supposed to talk to this one person so i'm talking to this one person and he says okay we've got a performance problem we need to be able to process stock trades faster like okay sure let's you know see what happens we go and pop open the server and the very first thing that i go and look at and see is they don't have backups i'm new at consulting but i'm like look i'm not really comfortable being on a system with stock trades without backups you know can you just go ahead and get backups and he goes no i'm like what do you mean no what do you mean you can't do backups and he's like no we're only hiring you to fix performance issues we're not hiring you to fix anything related to backups i'm like uh okay but if something goes wrong you know i'm just human i could accidentally drop a table i could highlight the wrong thing in ssms someone else could be working and doing something that i'm not aware of is this we gotta have some kind of backups here and he said no we're only gonna pay you to improve performance don't worry about anything else and i said okay uh here's the deal i'm probably not a good fit i said i'm uh i will only work on a system if there is backups i'm really sorry you know i uh it's just me i'm sure that you'll find another consultant who's willing to do this i'm just not willing to do it i'm going to draw the line here you know i like y'all you seem to be nice people but i'm going to walk away and he's like is your last chance you know would you like to pay you to do this and i'm like that's don't that's okay don't worry about it you don't owe me a dollar for this i'm just going to go ahead and leave so i left and a couple few weeks later i i get uh posted you know stuff from an attorney the i can't remember was via email or mail basically saying does the company owe you anything does this this company that you contracted with do they owe you anything and i said nope not a dollar it's totally okay well it turned out that they'd gone under uh that there had been all kinds of accounting fraud or whatever and uh from from that moment i was like i they could have been throwing me under the bus i don't think so but they could have been setting me up to go here we'll get this guy in here have him fix performance he'll trash the server we'll actually trash the server behind the scenes we'll pin the blame on him and then we'll say that you know all this data loss was uh was on him not us and so from that moment forward i just was like oh i'm never gonna bother you know if i before i'll even sign a contract with anybody i have to have data proving that there's backups don't want anybody's money without that i don't care and you'd be surprised at how often now companies will say oh we didn't know we didn't have backups or that database isn't important i'm like okay great drop it drop that database and then we'll be uh totally okay all right so let's see here next up let's add a couple of questions over to the queue hold on a second here add that in add that in and then you know what else i need to add in i need to just a second here i need to get my twitch chat out so that i can add your youtube stuff to the there it is add your youtube because some of you are putting in chats from uh while i play zelda uh now some of you are adding in chats from youtube so let me go get those just a second here because i think that was uh santa says does changing the compat level blow out the plan cash yes uh let's see here let me grab there was one other one that came in from youtube and let me go grab that youtube whoopsie youtube and there and then let me grab another well i'll stop there actually i'll only take questions via twitch from from today just to make it easy so let's go put that in there and see what we've got next contact administrator says hi brent from the last two months i'm observing a lot of sos and page io latch weight types how can they be interconnected no other weight type is this much prominent so i talk about these in my mastering server tuning class and i spend an hour on each of those weight types so it's tough for me to give you an answer in like 60 seconds but the short answer is sos means queries are burning a lot of cpu page i o latch means that queries are reading a lot of data from disk if you have both of those my guess is that you might be hosting your sql server on something akin to a laptop that you may not have enough horsepower now the answer isn't necessarily throwing more horsepower at the problem the answer may also be tuning your queries or indexes and that's what we go into in the class is figuring out which queries are the most responsible which indexes are the most responsible and then how you go about figuring out whether you should do query or index tuning or throw more horsepower at the box just make sure you've got at least as much horsepower as my laptop for starters so my laptop's like 8 or 12 cpu cores and 64 gigs of ram generally speaking i don't troubleshoot sql servers with less than eight cores like go get yourself an ipad and you know go from there uh next up let's see here who've we got next uh add a couple of things over here to the queue next up is darius darius says first consulting job before me do you have some analysis document templates from where i can put in my analysis and send those to the clients what should it contain um yes if you search on brainozar.com for how to do a health check so let's go over and take a look so if i fire open google and i go search for brent ozar health check i have examples out there like doing your for a free sql server health check uh there's several examples out there on the web um so i've also got a performance check performance check so in here how to do a free sql server health check doing your free sql server health check all kinds of stuff like that so go google for there and you've got sample documents out there that you can reuse the whole nine yards next up you know what i'm going to do too is i'm going to add in a little blurb watch this now let's grab copy and then come back over here to this and let's paste this in let's light this up and say i'm going to change the text over here but change this to say to ask questions ask on twitch.tv brentozar there we go so that way i will make it clear to folks that they can only ask on twitch not youtube because i won't copy paste the questions back and forth i got a really cool framework to just copy paste surly dev hey welcome to the see i was only because you weren't here i was like i was even telling them hey sorry yeah you're not allowed to ask questions on youtube all right well in that case i'm going to turn that back off then okay there we go uh let's see here next up uh eamon says what's your advice for a new blog site is it still valid for a new sql server blog site or should it be on a the the blog that you write should be on the pain point that you want to solve the pain that you want to solve for your career uh so if you want to solve sql server problems you should blog about sql server if you want to solve prop cloud problems you should blog about cloud basically think of it as every time you blog you're buying advertisements you're buying advertisements to get people to talk to you about your services now there are certainly lots of other reasons to blog as well to help the community to document what you know but if you because i say this because i know ayman if you want to build up your consulting and build up your reputation think of it as buying advertisements with your time if you're spending one hour on a weekend that you're gonna go and uh build a blog post then that think of it as say it's a hundred dollars an hour you're about to buy a hundred dollars worth of advertising what do you want that hundred dollars worth of advertising to sell people so that'll that'll kind of help you there next up i drive faster says i had a friend yes uh timeout occurred waiting for a buffer latch to no disk errors other things i should have looked at so this is getting pretty detailed uh i would probably post that over at dba.stockexchange.com just because it's kind of beyond the scope of what i would want to try and tackle during just a general qa uh next up zacky says i'm not really a database administrator neither am i uh but i wanted to ask what are the differences between relational databases and hadoop great question so with stuff like hadoop it's much more about how do i scale out across all kinds of servers and just dump all kinds of data that's not necessarily structured that somebody was just cop getting all kinds of wild data like text logs from web servers like web servers are continually compiling all kinds of text logs getting all kinds of text logs in and then when you ask a question you're willing to wait some time while this army of cheap robots goes as analyzes all this unstructured stuff to give you an answer relational databases tend to be about things where you need an answer within milliseconds and in order to get that answer within milliseconds you're prepared to organize the data ahead of time like you're going to have very strict rules about what kinds of data you're going to accept what format they're going to be in and you're not going to change that wild and loose so hadoop and hdfs more about random unstructured questions that take a long time to answer relational databases tend to be about very quick questions with very structured data hopefully that explains it uh next up uh clunky says i don't understand why i'm currently i'm troubleshooting a query that has problems if i place an option recompile everything's okay if i free it up so that's parameter sniffing yeah you nailed it parameter sniffing and so if you want to learn more about that i have a class called fundamentals of parameter sniffing it's 95 and you can learn all kinds of things about how that works uh next stop zachy says or no harish says how does disk encryption work with sql is there any performance overhead okay there are a few different layers at which you can put in encryption you can have sql server do it so when sql server goes to write an 8k page to disk or whatever page depending on off off uh off-road data whatever when sql server goes and writes that data to disk also when it reads it back up there's some cpu overhead for sql server to encrypt that data or if you have the storage do it for you on disk encryption then the storage manages the overhead of that the problem with storage doing the encryption is that generally whenever any database server connects to that storage they're going to be able to see the data unencrypted so it's not really that secure it just checks a box for auditors really if you want serious encryption what you end up doing is having the application encrypted and you never give the keys to sql server you don't want sql server to even know how to decrypt that stuff like social security numbers sql server shouldn't need to unencrypt that data for things like replication or whatever let's see next up zacky says i had the same situation you said earlier i was tasked at integrating stripe with php i saw a vulnerability with another component of the php app but the client said not to mess with those components i ended up rejecting the project the great example of that a really good example of that is you really only have one reputation throughout the course of your life you only have one reputation and if you take on a piece of work that ends up getting hacked or has serious security problems or loses data uh or is ripping off personal data from people you only have one reputation and if word gets out that you were the administrator or you were the developer who was involved in that terrible project when people google for your name they'll see it like if you were one of those people who was involved in enron or arthur anderson any of those covers up years ago or what was the cambridge analytics those people's names are out there if you go and google for them you only get one reputation and so i'm kind of like it's not really worth it to me to take on a project where there could be personal data loss or things like that uh next up we'll see what seal says did you ever get a reaction from microsoft on your blog post there's a bottleneck in azure sql db storage throughput so microsoft and i have this interesting relationship and thank you sirleydev i'm going to give a couple of shout outs to you through the course of uh today surly dev is the heroic most patient uh hard-working person who's sitting here helping make sure all of these questions and answered get tac questions get uh put into the queue surly dove does that out of the love of his own heart for the community so you should all thank surly dev for his uh wonderful patience in and diligence in working with your questions um having said that i know i forgot what the question was damn it why do i keep doing that uh what the heck was that question oh man it was wet seal uh wet seal where was it i'm gonna scroll back up in the chat logs and see if i can see it uh dang it uh oh that's gonna kill me not seeing where it was uh let me scroll down and see if i see because i think it was wet seal who knows all right well wet seal ask it again whenever you think of it ask it again and oh if they reply to my blog post thank you jackie i appreciate it uh so microsoft and i have this tricky relationship uh we they don't they won't talk to me very often they seem to be very walled off they're like a screw brent we're not going to talk to them but then anytime i post something they go diligently working trying to fix whatever it was that i was complaining about so after i published this blog post nobody from microsoft talked to me but then all of a sudden they start raising the limits they start documenting and raising the limits for azure sql db throughput now they still didn't raise it higher than a usb 3 thumb drive but at least they acknowledged that there was a problem and they started working towards it i always find it mildly amusing that that microsoft refused their like don't tell him we saw it no don't try not to look at him he's scary i'm like oh come on just a blogger and i'm just i'm trying to point stuff out and i can't uh can't get that stuff out ah next up uh abused this edmond says what performance hit is there for row level security um for me generally i'll just tell people do you need real level security okay then you have to put it in and it doesn't really matter what the overhead is it's kind of like airbags in a car you never hear people going well how much slower do the airbags make me the federal law says that you have to have airbags in your car now shut up and put the airbags in your car so that's that's all it's not really negotiable it's not like i can say well we need to protect our user data but it's just unacceptably slow in order to protect it it doesn't matter you just got to do it suck it up and do it um and i'm not saying roll level security is the right answer it just depends on you working with the end users and developers and project admins to figure out what the right solution is but don't go put something on for security if you don't need it you know don't go putting something on to slow your server down if you don't need it but do put on the things that you do need there you go uh let's see next up is ann reiling who has a wonderful little bonsai tree there for his uh avatar or her avatar says how many cores and ram do you give your windows vm on the mac pro i usually give four cores and 32 gigs of ram because i have in my in my mac pro i have 16 16 cores 32 with hyper threading on and 96 gigs of ram normally i just do four cores but if i need to do parallelism demos i'll do eight cores because it four versus eight exhibits slightly different parallelism behavior uh oh mr wormhole says uh what is p t sql and p sql so when you're a vendor like microsoft or oracle or postgres or whatever you sometimes want to ship more features than the official standard supports like you're like check this out i got this cool way of doing dot net code inside of sql server so you develop your own flavor of the structured query language so that you can ship features before they're into the official standard when you use those features as a developer then that also is makes you locked into that one vendor every now and then i'll hear somebody go screw it we're just going to convert our sql server application over to postgres yeah i'm like oh yeah good luck with that because your developers have leveraged so many exclusive features of t-sql or oracle's pl sql or whatever it is so if you're just getting started as a developer try to write and see sql try to write sql that works across more platforms not because you're ever going to move your app but just because it makes you a more flexible developer makes you be able to jump from one system to another like between microsoft and postgres uh neil asked a question microsoft exams i've never taken them not never but not in the last 10 years i haven't taken microsoft exams in the last 10 years because i think they're bs they're just a joke ever since they discontinued the microsoft certified master program the the certifications are all based from microsoft learning who has to make a profit uh on every single certification that they ship and they're heavily biased towards the latest and greatest features that aren't don't necessarily have anything to do with what dbas and developers do on a daily basis so i remember taking those exams and be like what why does why are they asking me 20 ways to format xml what the hell sense does no one's ever done this you don't do this on a daily basis but they want to ask you questions about new features just because they're trying to market the new features it's just junk just garbage zacky says what's the protocol for storing payment details in a database realistically i would never do this and i just use a third-party system but i'm curious on what the protocol is you know i try to stay away from security work it's actually forbidden inside my contract so i'm not a good person to answer that but great question it's one of those where you really need to dot all your eyes and cross all your t's and get it perfect and i just don't have the time to bother with that every now and then i'll hear someone say you should know everything like generically speaking to experts y'all should know everything about sql server you should know security and performance and clustering and hybrid you know i'm like that's not how it works it's just the products way too big and too deep for example just recently they've shipped availability groups in kubernetes in the form of big data clusters and r and python and java in the database and if you think you know all of that it's you're going to be blown away by every year new stuff getting shipped so you just can't know everything your jack of all trades mastering none amon says four terabytes of data in one instance with an average of two thousand batch requests a second what's the best guess for good ram for that instance it would depend on what the batch batch requests a second are actually doing for example if they're just selecting id from table where the primary key is something you don't need jack for ram but the more data that they're selecting the less surgical their where clauses are the more challenging that starts to become if you don't do any performance tuning or if you can't afford to like i've had.com so simply couldn't afford to a good starting guess is two times the data size so if you had four terabytes worth of data you would need eight terabytes worth of ram now of course not a lot of folks are willing to do that so that's where performance tuning comes in next up uh sre devops says what do you think about running databases on kubernetes it's ridiculously stupid now scaling mysql mysql could be totally different i'm a sql server guy right now running production sql server on kubernetes is incredibly dumb just bizarrely beyond dumb is really stupid sql server doesn't have any kind of tools built in to make it easier to manage production sql servers on kubernetes failure troubleshooting is an utter nightmare if you start following people who do sql server on kubernetes on twitter they are just vomiting text all over their keyboards all day long about bashing their heads in trying to get the problems with them fixed don't use it's a great saying in development um build build uh weird things with boring tools build boring things with weird tools so like for for me if i'm building a production sql server application i'm going to use the straight normal predictable type tooling i'm not going to go off and use something weird next up sql santa says what would your strategy be for managing large tables with focus on good performance every now and then this is the first time i've discussed in this webcast but never ever ever ever ever ever never never never never never say the word large never never never never never for the rest of your career you're always going to be working with stuff that's going to feel large to you so what i'm going to say is if you said large tables as 10 terabytes if you're dealing with 10 terabyte size tables you're absolutely right partitioning is not a good performance improvement instead what you want to do is things like query tuning and index tuning partitioning is a loading feature and an archiving feature but it's not a query performance feature and microsoft is really good about uh saying that in books online yeah j cole yeah but people don't show up every day like you do j cole next up toto says is there a way to get when tables were last updated inserted or deleted if there's no true date column lots of times people use last update seek or scan but i wanted to know if there's another way theoretically you can read the transaction log so i used to work for quest software and they have this tool where it'll go through and read a transaction log and so you can look for uh particular tables it can be just a giant pain in the rear in terms of performance because it takes a really long time to read the transaction log but if you wanted bulletproof accuracy that in the past that was the way you would do it like if the action has already taken um if it's actions that you're prepared to modify in the future or prefer to prepare to watch for in the future like if you're like hey i want to know who's touching this one table that's where things like extended events auditing and triggers can come in really handy but those involve changes to the tables george no i'm not going to cover that but if you go to sql pro um if you google for it and search for hugo cornelius hugo or you just hugo sql server hugo lazy spools there's a documents out there on that next up you get going says hi brent are there code analysis tools that are useful to sql and what's the best one to use so you mean like static code analysis that's going to go read through your code base and figure out what the problems are i haven't seen a good one there was um there was one called sql cop and i'm trying to remember what the other one was but they would just flag things that were absolutely harmless in terms of performance and then they would miss things that were a really really big deal so i haven't seen one that i've been impressed with hoodlums says do you have any recommendations for learning sql especially for someone who'll be working alongside data and tech but not so much with programming i'm going to say something and you're going to think i'm joking i would go get sql for dummies you're going to think i'm joking but it was actually good last time i picked it up was like 10 15 years ago i had a friend of mine who was wanted to learn how to write his own reports and i got the book just to see if it was still any good because i remember having it like 20 25 years ago and it's surprisingly good sql for dummies because it's also cross-platform it works for mysql postgres oracle sql server just a good easy way to go and get started so fantastic there g surgeon says is there an architectural problem with having almost all business logic embedded in stored procedures other than switching architecturally no architecturally no i don't have a problem with it where i get nervous is when you say business logic because i've even seen people extend to that to writing html where like inside a stored procedure they'll go build html to paint a screen and i'm like i get kind of nervous with the expense of that uh given how expensive sql server is i get a little bit uh nervous about that to uh yeah so because it's a sql server 2000 bucks a core 7000 bucks a core for enterprise edition kind of sketchy um so architecturally that's the problem uh as a developer though i'll tell you one other thing that kind of sucks change control so versioning with stored procedures is a whole lot harder than versioning code it's not impossible it's just that if you're doing development and stuff like visual studio or whatever your favorite ide is it's usually easier to version your business logic by doing it inside the application instead uh let's see here cranky older gentleman says does anyone still care about microsoft certs hiring managers so hiring managers and recruiters still care about microsoft certs they use it as a barrier to get in the door they're just like here if you're gonna go in and get uh be eligible for getting a job here the bare minimum that we need is a microsoft a current microsoft certification and they're just trying to weed you out of the people who don't have any certs or experience whatsoever so for that sometimes you may have to go for a cert even if it's a really crappy cert um and yeah i'll stop there in terms of my cert stuff oh we should give a shout out to this week's sponsor so this week's sponsor is quest quest has this uh database days training events canal uh janus and i were doing these a few months back and quest has brought them back too for these this fall the first or the next one is coming up is i want to say wednesday it's either wednesday or thursday this week where i'm going to teach you about filtered indexes indexed views and indexed computed columns all for free you can go see that over at brentozar.com go slash training days and go sign up for that and if you can't make the live webcast you can also get the recordings when you go sign up for that they will go let you watch the recordings for free as well so next up we have jan says i know that splitting a database in many files is a bad practice especially after 2008 but what about log files okay so let's talk theory for a second in theory sql server logs your transactions in a row in the log file it starts at the beginning of the log file writes rights rights writes rights gets all the way towards the end of it and at the end of the log file it's going to circle back around to the beginning of the log file and if there's space available for reuse it's going to start writing again so in theory there's no performance advantage by breaking things out to separate log files because sql server will never access multiples of them simultaneously having said that there's some kind of storage some kinds of storage where when you're writing to them sequentially the storage runs out of bandwidth the storage isn't able to keep up and if you're really really right intensive think stock exchanges if you're really right intensive and you're trying to keep up with every single stock trade you may need to write a whole bunch to one log file then switch to another log file just so that your log file storage can keep up if i had to guess it's like one percent of one percent of one percent of all sql servers in the world need that kind of throughput but when you google for are there ever situations where multiple log files are required you're going to come across articles that talk about stuff like that so i'm just going to pull a number out of my rear end because my rear end is full of numbers i'm going to say if you're not doing at least 50 to 100 000 inserts per second sustained like for hours at a time you probably don't need to screw around with that okay ahmed says my company only loves open source databases uh even paid versions of them i like open source databases too absolutely i'm a huge fan we use postgres for our own company tooling too as well next bob the lobster asks uh what would you like to see in the next version of number twos at least that's uh that's pretty good that's very good bob says what would you like to see in the next version of sql server i'm going to say something weird i don't have a lot of complaints i think sql server is actually doing a pretty good job and as crazy as it is anything that they give me at this point i kind of feel like it's icing on the cake i could this is where microsoft's really going to get pissed off at me i could probably use sql server 2016 for the rest of my life and be pretty happy it's damn good i mean it's it's reliable uh it has a lot of features t-sql is wonderfully robust uh i love cooper dusty's um i i really like uh sql server 2016 2017 and i don't want to sound like i don't like 2019 when 2019 came out i was like this is great they invested even more stuff in the query processor i feel like a kid in a candy store it's pretty freaking awesome now if you told me like you brent you can have them work on anything that you want as crazy as this sounds i would like better diagnostics i would like an easier time of troubleshooting problems that we commonly run into and i'm going to give you cranky old gentleman says no new features i'm going to give you a something that kind of frustrates me that we even have to have the discussion on in the year 2020. in the year 2020 no one should be running out of disk space in production it shouldn't happen we should be able to tell people in advance when they're going to run out of drive space when you install sql server part of the installed wizard should have an email address put in a distribution list here for your sys admin so that we can tell you when stuff's about to break they don't seem to be going that way at all though they don't seem to be investing in easier troubleshooting at all so i'm kind of like okay but i don't have a whole lot of requests that would be the easiest the biggest one that i have there oh let's see here jojo says are incrementing integer ids enough i like more for that in life i mean i like tequila and sandwiches and pizza and long walks on the beach it's not really enough for me i guess you have pretty low standards but is using something as good as better or totally useless the point where goods are better is if you need to generate unique ids on the client side goods can make that easier for you like that the application can generate its own goods for parent child rows and then go insert those later the guides are just too damn high but that's the classic situation the other situation is when you have multi-master replication if i have one sql server in north america and another sql server in europe and i need to let clients insert data into both of those i can't have an integer id sure if i have one that starts at one and goes up and the other starts at negative one and goes down that'll work until you add the second or the third set of sql servers and the fourth set of sql servers so things like peer-to-peer replication replication with data centers around the world that's where goods start to make more sense jackie is it jackie's rebellious next up no content says we had an issue with converting a varicare to an int in an inner join the where condition we've added is an inner join being checked first map so order of operation has nothing to do with how you write your query sql server can rewrite what happens behind the scenes to choose what needs to be done first and i actually talk about that in my fundamentals of index tuning class in fundamentals of index tuning i talked to you about how to write a an index for the where clause the group by having order by joins all of that and which ones are going to be processed in which order so you can choose which ones you want to index four and as you choose wisho index four it'll actually change what sql server does first in the plan so you can rewrite how sql server processes the query just by adding indexes uh next up not the dba you're looking for says what is the basic difference between change data capture and change tracking so the basic difference is is that people who can read the manual should use one of those features people who can't read the manual shouldn't use one of those features right isn't that terrible um xperia says it's i like not the debut or dba you're looking for uh is cool that you start streams early because of that view yeah uh kudos from europe did you intend to wake up on or did i used to wake up on weekends before you streamed yeah i have all christopher walken a walker um i usually wake up around two or three in the morning and i'm usually at work by around four uh so i actually stream because i'm awake anyway like on weekends this is just the most convenient time for me to stream i would stream earlier the only reason i push it till six is every now and then i'll sleep until four and i like having uh half an hour to shower and then go eat breakfast catch up on what's going on just with things that i need to do the blog ecommerce whatever so then by then i'm usually ready to go be on camera but yeah i'm totally a morning person and then on the flip side i'm usually in a nap by noon to 1 p.m like noon to 1 p.m i'll take a one to two hour nap and then uh back up and it's almost like i get a second date and the second day is just for goofing off like in the afternoon i don't do any work work so now let's see richie says brent wakes up when i go to sleep and it's true it's absolutely true which is one of the nice things about you know remote work it doesn't really matter when we do what we do we just get to do it whenever it works for us zachy says hey brent should i store files and databases or use an external service i'm working on a relatively small app and i don't think writing an entire file management system is a good idea so so there are things called file systems file systems they they're they're included with your operating system you can just store files on it yeah and they're free now having being a little sarcastic now let's come back over to reality if you had to get backups of the files and you wanted those backups to be portable to include the files if you were talking about something that was like 10 gigs 15 gigs sure you can put files inside the database but if you think it's going to store more than 10 15 gigs put the files on the file system where they belong that's why the database is called a relational database management system rdbms file systems are called called file systems for for a reason ah let's see here neil says um which monitoring tool do you think gives more in-depth details on what went wrong with sql server oh that's such a good question people are always like which monitoring tools should you use so i don't test monitoring tools on a daily basis because i can't rely on clients having a particular one i do know some consultants who actually do that as part of their consulting they'll say before i work with you i want you to install this free 14-day trial of whichever tool that it is like quest spotlight for example because they all have pretty true because they all have free 14-day trials so the consultant will say 10 days before we work together go install this application then that way when we work together i can use the monitoring tool that i know and love i kind of like that answer that if you're going to get married to one particular tool vendor that works out well it can also backfire when the monitoring tool sucks because every now and then every brand out there puts out a bad version and they have to kind of recover from it i don't think any of them are really better or worse than others i think that there are a couple that stink really bad that clients often for a while i would even name the tool there's one that's infamous every time that a sales request comes into me every time i'm talking to a client and every time that the client says we have a monitoring tool and we still can't figure out what the problem is it's always the same tool it's always the same one tool there's just one tool that's just absolutely terrible but the rest of them they're all kind of interchangeable like i could live with any of them but what i would do is i would go and get trials because they all offer free trials i would go and get free trials of several of them and pick whichever one you seem to like the interface on best next up ian says over on youtube is there a way to see what queries are granted too much memory ones that only use a small amount yes i'll show it to you so we're going to go over to sql server management studio which i have not started yet today and that kind of says something funny about how this q a has gone that i haven't needed to bust open management studio so what i'm going to do is i'm going to run a query that gets a really large memory grant and then i'm going to show you how to use sp blitz cache to go sort queries by memory grants so let's go connect into this guy and run a new query and i'm going to say select top 101 star froms let's go into stack overflow stack overflow dbo users order by reputation descending and let's make sure that that actually doesn't have an index on it so is query is going to get a big ginormous memory grant because i don't have an index on reputation if i remember right so whenever it finishes it's going to take a little while especially since it's the first query that i went through and ran good so now whenever this query finishes i can go say s p blitz cash sort order equals memory grant what this does is it sorts your plan cash to find the top 10 queries ordered by the size of their memory grant so if i come on come in over here this will show me how much of a grant these got and how much they used we don't have a sort order on the difference between those two what you're asking for is to find ones that have large grants but don't actually use it in order to do that it would talk about some some pretty ugly it would involve some pretty ugly calculations on our side so we just do this and then it's usually pretty easy to go see oh i have queries that are getting a 5 gig memory grant and they're only using you know 1.5 gigs if the ones that you're looking for aren't in the top ten also add skip analysis equals one top equals say 50. any time you run top though you probably want so top will give you the top 50 queries instead of the top just 10. you probably want to run skip analysis too at the same time skip analysis is doesn't do these warnings analyses this is what takes so long in order to run sp blitzcash because we run all kinds of evaluations against your the query plans in the cache as people it's cash totally free it's part of our first responder kit let's see here and so now next one i'm going to jump out of order because this i just saw this question go by and i was like i know exactly i'm not the dba you're looking for says if helmet blew up tomorrow what would you buy next and it's funny because i just had i have this conversation with my wife on a fairly regular basis and there's kind of like two points to this right now in san diego we have to pay it's about 200 a month for every parking spot above our first one we had our first parking spot for free in our building but the second third fourth take like 200 a month for each car that's the one thing stopping me from buying more cars right now i would go buy more cars immediately so i can give you a prioritized list my wife's el numero uno is she has a porsche 911 turbo all specked out it's her desktop wallpaper and has been for like two years we would go buy erica's porsche 911 because the what she's always wanted she has her color picked out all her options picked out everything and i had my dream car so it would be time for her to have her dream car so that would be the number one the second one that if she couldn't have hers what i would buy is a ferrari 308 or 328 these are 1980s cars it's the red ferrari that magnum drove magnum pi and i think that they're unbelievably beautiful cars and at around 50 bucks for 50 000 bucks 50 bucks wouldn't it be uh amazing but at 50 around 50 000 bucks you can get a decent example and they're just i think they're some of the most beautiful cars that have ever driven they're just utterly uh ridiculous yes you're absolutely right happy wife equals happy life uh so let's see here next up ohani says what's the best practice to apply database filler so fill factor so i said 360 it was it's okay but the 308 328 is phenomenal and that this is as much of a mustache as i can get so i don't like fill factor i think applying fill factor is a really bad idea i always want to explain it with a piece of paper in my hand but i'm going to use my ipad just to represent the size of a piece of paper if i said leave 20 of every page empty that page is the same page that's in ram and on disk so what i really just said was i'd like to make my memory 20 smaller i'd like to make my database file 20 percent larger i'd like my table scans to take 20 longer my index rebuilds 20 longer backups 20 longer restores can you make those 20 longer please my update statistics jobs tack 20 onto those check db take 20 percent onto those my storage costs go ahead and jack those up by another 20 percent it's funny how i see consultants recommend that you set fill factor and they're driving your costs up and making your memory smaller the very things that they're going to get paid to fix me i don't want you spending money on consulting i don't want you spending money on databases i'm good i drive a porsche 911 i'm fine i'm looking at houses in mexico so i don't know that i would really set fill factor i would leave that at 100 where it belongs so all right going on to the next um smith asks what's your thought about partition indexes are those that conspiracy uh what is your thought about partition indexes do they perform better generally no a generally partitioned indexes are a wonderful feature for loading and deleting data they're a crappy feature for select query performance the only reason that they make select queries faster the idea is that if you put in the partitioning clause or the partitioning column in your where clause sql server will be able to just scan the partition that has your data just put an index on it if you put an index on whatever you're searching for that that that that reduces the search scope you you don't need to partition that's just what a regular index is oh he's a genius um sekar says brent is it good to have an index on a frequently updated column like a status field it depends on whether you query it if you need it if you if you like it put an index on it if you uh need to query it very frequently then it can be good to have but if it only just ever changes and you don't query on it then it can be bad to have the thing is usually you don't only want status you want other things inside the index too if you only query by status and nothing else it would bring back millions of rows right because you have millions of rows in the table with the same status [Laughter] now just i don't know a i don't know if you're referring to me and b i'm curious what your high school music teacher sounded like uh haresh yes absolutely sms the code quality's gotten a lot better with recent versions but also just make sure that you don't have any third-party add-ons installed the more third-party add-ons you have the worse that seems to get um cw train good to see you um so on youtube cw train said cdc says what did it change to ct says did it change primary key watch only that's a great way of summing it up i really like that a lot uh next up masimasima says do you know or could you recommend any experts in my sequel i don't personally the company that's kind of big in this field is percona p-e-r-c-o-n-a percona kind of has the big name around my sequel at tuning and i only say that just because i know what their name is but that's probably the place that i would start amit says oh we said that so i'm going to go clear that guy next up we'll go see zacky says i've seen one of my friends working on a complex compression system for sql data i told him to just get much just more disk space but he rejects it what's your take on this okay so a one terabyte drive is about four hundred dollars so what your friend is saying is that he values his time less than a hundred dollars and maybe it's true maybe your friend totally sucks at development maybe they make minimum wage i mean it could be absolutely possible that they've been working for months on a compression system but it's kind of sad now there things i'm going to stop there that's just totally true [Music] appfulclonk says is there a way to reuse code without sacrificing sargability like for queries reporting queries with different group buys for weekday week day week hour so there are a few answers in there one way that's kind of popular is computed columns so with computed columns you can save some stuff ahead of time and even index on them and i will teach you about that this week during a totally free webcast from quest quest is uh paying for my time so that y'all can just learn over at quest database training days so you can go register for that over at brentozar.com go slash training days where i'm going to teach you how to do index computed columns indexed views and filtered indexes these are three advanced tricks that you can use with indexing to pre-bake data for reporting ahead of time it's a perfect answer for your question gives me a chance to give a shout out to quest thank you quest for sponsoring this week's webcast all right next up um db augie says i'm trying to transition from oracle dba to sql server that's interesting oracle any resources for exploiting the error log to troubleshoot problems sql servers error log blows i wouldn't recommend that as a place to start for troubleshooting performance problems i would recommend it for places to start with troubleshooting uh errors but it's just literally an error log and it doesn't really have an intuitive way to rip through it quickly now most sql server deviates just don't start there it's a great question though i can understand why coming from oracle that that would seem like you know why doesn't anybody look at the air log we just don't go there um sql santa says rhetorical when was the last time microsoft added features to peer-to-peer replication i i would almost say they're on a mission these days where they don't add features to anything they build brand new features kubernetes r python java machine learning you know like that they're on this aggressive mission to go add new stuff but they don't ever go back and fix existing stuff i will say they've done that they've done some fixing existing stuff with column store column store they've done a pretty good job on going back and continuing to invest in it but everything else oh my god it's been a hot mess uh christopher walken says i'm gonna call you christopher walken what about an oracle rack equivalent for better ha for sql server so the thing is so with oracle rack you're supposed to be able to write to either a sql or either oracle server at the same time microsoft has talked to people or like okay so do you actually do that do you actually write to two oracle boxes at the same time can you show us why you do that and and how that works and people are like oh no we don't actually pay for it we just want it in a cheaper sql server okay yeah no if you truly need the ability to write two to two separate oracle boxes you should probably go and spend the money on oracle rack and let us know how that works out for you while you're begging for change outside of the shopping mall and by the way not a lot of people are going to the shopping mall anymore it's going to take you a really long time to pay off those oracle licenses these days but for the rest of us it's just not that uh important how to hack wi-fi okay yeah that there we go uh next up uh ali says how do you create an olap database what i assume you mean i'm gonna go drop my air conditioning temperature i assume that you mean analysis services so with analysis services that's a totally separate product it just happens to share the same name as sql server it's not like it actually shares the same way that we manage it the same binaries or anything like that so if you truly mean analysis services it's a totally separate tool go get a separate you know books on how to use that but if you're talking about just how to create a database in sql server just type create database and whatever there's no difference between a a regular database and an olap database i'm gonna go mute anything channel just because they're with the how to block hack wi-fi uh let's see here oh and i've logged out of restream hold on a second here let me go fix uh that by logging into restream real quick uh while i do that we'll give a shout out to this week's sponsor just so that they can go do that while i go figure out my restream restream io go log in and go get my chat back i should play music or something while i do that [Music] just so that i go get that and then where's my chat where is my chat chat app there we go open that browser and there that goes and there we go all right cool that over here and move you over here there we go so now uh let's see who just asked that uh d.b augie says is that fog outside your window or smoke from the bonfire of the vanities it's probably both we have really bad air quality uh today out in san diego i think it's like 150 parts per million or whatever the number is um anything channel again all right let me make sure that i uh block that user there we go uh mbo says is it a good idea to rebuild indexes in a log shipping environment and and he puts in parentheses forwarded records do you need to do kind of two parts to a question do you need to fix forwarded records yes is the right fix regularly rebuilding them in a log shipped environment no the right fix would be to put a clustered index on the table so that you don't have to deal with that every day but it's good on you for knowing that you're fixing forwarded records that's absolutely fantastic ahmed says as a database administrator how do i convince my developer teams about software architects and architecture but well they say x is better than y database but i know there is only architecture issue pick your battles so pick your battles what is it that uh you really want to win let's say that you're able to browbeat them into believing that you think your database is better all you're really going to convince them to do is to get you out of the company as quickly as possible because you're standing in between them and shipping features it's not on you to convince them that your database is better you want to go learn what is it that you like better about that other database and don't freaking respond don't answer don't say one word take notes and then go learn about that other database so for example when mongodb started catching fire when everybody was like oh mongodb it's amazing i'm like oh what do you like about mongodb they're like well we don't have to define our tables in advance we can just put any unstructured data in that we want to and then i'm like okay great let me go take a note on that and see what else and then you go research to see what you learn about it's full of pinto beans smells like it too as well so but you go learn it usually use that as an advantage to go learn because if you're truly an architect like you say you are then you need to hear what their complaints are about your database and then go learn about the other database and see why they're better and it may they could be completely wrong but you want to be open at least to thinking that maybe they're right their music video uh let's see bala says do you would you recommend how to do automation in sql server rather than using third-party tools uh so that's a tricky thing when you say how to do automation in sql server it's a really big world there are so many things that you could possibly automate the place that i would start is with dba tools so dba tools if you go to dbatools.io this is a powershell framework that's all about automating all kinds of things uh so this is probably the place that i would go start you don't need any third-party tools for this it's just plain old powershell so i would go ahead and start and start there all right let's go block row hits so that he doesn't show up again off he goes uh next up let's see here mossy moss says could you explain how constant care works so what i saw so far i love it the recommendations are great oh great question um so what the way the constant care works is every day you have an app that runs just once a day and pulls your sql server for all kinds of diagnostic data index usage stats weight stats sys configurations all kinds of different things which explains how or doc pulls in all kinds of stuff sends it to us up in the cloud we import it into aurora amazon aurora postgres which we basically keep the same table structures as the dmvs so like we can query sys databases it's just that we have it for like three four thousand sql servers so we query sys databases and we give you recommendations on based on what's happening now and what's happened over the last 30 days we keep up to 30 days of history about your sql server so we can give you trending for example one of my favorite things with that is hey your sql server is bored there's nothing happening on the sql server performance is fine so you can leave it alone you don't have to worry about performance on that sql server go focus on other things instead or one database has been growing grew like weeds like 10 in the last 24 hours you know go go take a look and see what's going on inside that database to go find what's going on this is kind of fun as a database administrator to have the diagnostic data for thousands of sql servers all around the world and be able to spot trends and things like that i love that kind of stuff uh ahmet says how do we handle large index maintenance with a huge transactional environment reorganize takes too long rebuild needs a disk space search for my name brent ozar and index defragmentation and i've got a whole hour-long video just on that topic so search for brent ozar index defragmentation and i got a video just on that one you can go watch that over on youtube for free too um table employee says what's your over under on when microsoft will add a functional dark theme to management studio so let's think about this for a second would it change what database you used would you change over to oracle or postgres because you didn't have a dark themed management studio imagine going to your boss and saying boss here's the deal i've been thinking about it a lot lately and we need to change our entire application we need to use a different database platform and you know why because we don't have a dark theme in management studio you would have a butthole the size of a pizza from your boss shoving his foot so far up your rear end you would your foot your breath would smell like nikes that's how bad you would get your butt kicked for trying to pull that stunt so do i think that microsoft should waste one dollar of their time working on a dark theme for management studio are you out of your mind no not no but hell no get your big nike smell and rear end out of my office that is utterly ridiculous so that's how i don't think that they're going to do it anytime soon and it almost kind of makes me nervous because if they do do it that tells me that they're working on the wrong stuff like there are things in management studio where it crashes and you lose files so don't don't do that uh contact administrator says is there any uni is there any uh way to determine data uniqueness from statistics i would probably ask what's the problem you're trying to solve like what what's the problem you're trying to solve there and because i don't i don't think that you're going down the right track um we'll do a few fast rounds what about test driven development and sql uh what about it all right surly dub says check out t sql t which if you want to really hurt for a while it works it's just really a giant pain in the rear another fast one junior dba says is create index or drop existing on safe to use in production to move tables from a primary file group to secondary file group is it safe to use yes will it completely accomplish that goal not necessarily if you have includes that are off row it may not work it totally safe to use it just may not completely accomplish your goal next up zacky says how does training a machine learning model on a database work damn defino but it is a real thing it is absolutely a real thing but that sql server has machine learning built into it i just saw the the first consulting help request for that in like three years come through on my email um cannot run distinct on the data why not what's so what's the problem you're trying to solve though what's the problem where you go you need to know uniqueness that's that's what you need to know don't don't tell me i can't put the the shopping cart inside the pizza box tell me what's the problem you're trying to solve why do you want to put a shopping cart inside a pizza box what's the business problem you're trying to solve next up what are some of the use cases for using clr in sql server if you want to drive your cpu higher if you want to make sure that you get called back for consulting when they can't figure out where the memory leak is coming from or if you only know how to use c sharp and you don't know how to use t sql then those are of course i'm being uh facetious now exactly one good use case for it is regex if you need to implement regular expressions that's not something that's built into t sql you could do that in clr if you wanted to thank you surly dev another round of applause there for surly deb i'm working tirelessly through all of y'all's questions because y'all love to ask all kinds of questions and he is keeping me sane partially through the course of this q a oh sequel santa says uh could you think of any reason to enable trace flag 2861 except if required by a monitoring tool no and microsoft has already said that in the last couple of versions at least 2861 doesn't even do anything it's just a monitoring tool i know exactly what monitoring tool it is el crapo not not so good next up cadabradera pumper says what is a good starting point to think about in memory database design can you use redis for caching or only sql server generally you want to use the cheapest thing you possibly can and sql server is not usually the cheapest thing if you're purely doing caching go use redis it's way cheaper it scales out nicely between multiple boxes they even have persistence to disk now if you want to do that kind of thing so if you're a developer and you're thinking about caching you should totally think about redis or elastic cache whatever managed redis you want to go use uh ahmed says can we use em sql server for key value or json you can if you wanna light dollar bills on fire but it's two thousand dollars a cpu core for standard editions seven thousand dollars a core for enterprise edition so it doesn't really make sense to spend that much money for key value storage or json when you can go get uh key value databases for free so that's my thought on that one i always try to keep my people's uh money use as little money as possible in order to solve their problems thomas says what do you think about graph tables have you experienced it on sql server no because same thing again there they've been graph databases out for a really long time that are way cheaper than sql server it seems like sql server just kind of checked a box with this one so if you only need graph i mean you if you have all kinds of other stuff in your database and you just have a small usage for graph then sure it would make sense to use it but if you're starting a brand new graph project in the year 2020 doesn't really make sense to to use sql server as your primary data storage oh tk says after i learn sql server first off a round of applause for being done it's fantastic that you're done learning that's amazing i would love to be like you someday because i'm still learning sql server man there's a lot in it but what should i learn next nosql or the cloud go to the business go to the person who pays your raise the person who's who's going to give you a raise at the end of the year go in there and say okay what is it that you want to be able to do that you can't do today like what's the business problem that you need to be able to solve that you can't do today and then based on that go learn the thing that will help them accomplish that i'm going to hit zaki's because it's interesting so i've seen a lot of android devs using firebase instead of sql is there a reason for that yes if you don't want to deal with the management of a database and if your clients are going to connect directly there's no app server in the middle your clients like your javascript whatever are going to go connect directly to the database on the back end firebase is really nice for that done i just happened to be looking at firebase recently because it's kind of cool i don't develop on it or anything like that i just you know read the marketing material that's kind of the end of that one um ahmet says how can we adopt the ms sequel oh you want a baby oh i want a baby too i don't really want a baby babies smell bad they poop a lot uh for multi-data center environments do we need multi-master model or what what you want to ask is do you really need to write to databases in two places if you really do it's banana pants expensive it's crazy expensive now often when people say multi-data center environments what they really mean is they want one active and then another one passive if you truly want two active data centers i probably wouldn't actually start with sql server i would start with something that's more designed to be sharded out sideways just built in uh george says what espresso machine do you use and which coffee is your favorite i should be ashamed to say this but i use a nespresso i know i use an espresso i use an espresso because it's really easy it'll make espresso in like 60 seconds and i don't have to fuzz with anything it's a little bit more expensive and it's not a great uh quality of coffee it's pretty doggone good for something that i can make in 60 seconds and not worry about it that i can just go wash up to it push a button and boom i got espresso and espresso in 60 seconds when i make coffee for my own pleasure like that i'm going to sit there and really enjoy it exactly yes i have so many jokes about that yeah he's i like him he's really cool uh but uh when i go make coffee from my own enjoying i don't actually make coffee i go downstairs and there's a coffee shop downstairs it does pour overs and i tend to just get whatever i'm like okay what's new in terms of beans what can i go try that's new and different so it's true it's true i have four coffee makers all together but the only one we even have out in the counter anymore is the nespresso i have a technivorm mochamaster a french press an aeropress i'm trying to remember the other one but they're all in my closet because we just don't even use them anymore it's kind of bizarre zacky says i thought the greatness was outside of the view was because your window is tinted no san diego really looks like this right now and it's that smoke and fog normally you can see from over there over to the right i can see mexico it's also raining i can see drops of rain on the window so it's like misting so next up oh alex alex dba says should i use temporal tables for auditing if the auditing has to be legally defensible like if you're going to face an attorney in court then temporal tables are a bad idea because people can delete data out of temporal tables so if someone wants to hide their tracks they just delete the rows and the temporal tables or change them like they can pin the blame on somebody else by editing the temporal tables so if it has to be legally defensible no if you're just doing it for your own curiosity purposes they're interesting but ah neutron star how is sql server different from oracle what you would probably expect me to say is you would probably expect me to say that sql server is better and i'm actually gonna say it's not i'm actually gonna say that every time i look at any kind of piece of documentation about oracle it's freaking amazing it's so many capabilities i'm just amazed by how good that thing is it's pretty impressive but the problem is it costs like several times more than sql server does and people tend to run it on oracle rather than on windows and it also doesn't have really deep integration on visual studio and because the microsoft stack if you buy into the microsoft stack you're typically running windows developing in visual studio deploying on windows and hosting in c you're hosting your data in sql server and then in azure too as well people tend to fall into either the microsoft stack or the java stack if they're going to go on the on the java side fix our little white balance here next up let's see here oh we got all kinds of questions we have so many that i can kind of start picking and choosing now let's see next up zacky says is using windows normal as a dba i've a lot of people i've seen who deal with servers use some distribution of linux for sql server dbas sql server dbas tend to use the microsoft stack granted there's the new trend of uh running sql server under linux it's much more the exception than the rule though like less than one percent of one percent of sql servers out there are running under linux unless you count uh if microsoft is running linux under the hood for azure sql db which they totally could be but in the sql server world you tend to see most sql server dbas running windows at least in the places where they manage sql server i run a mac and i have since windows vista came out like back when windows vista came out i was like oh my god that's absolutely terrible and uh so but it was still when i go to manage sql server i'm usually in windows let's see here i'm going to delete a couple um in flippin says we have a large number of here number of large heap tables remember never ever ever ever ever use the word large use sizes because databases always seem large to you but they're not necessarily that large rows don't get updated if often if ever no forwarded fetches there are non-clustered indexes with solid state drives the drives don't matter doesn't have anything to do with the drives do you still recommend that these tables should still have a clustered index there's a great question i want a clustered index when rows are getting updated or deleted but if your rows aren't getting updated or deleted and you do have indexes i'm okay with the heap it's not that bad uh horca says is it a good idea to set cost threshold to 50 in case of new installations or should it only be touched if there are problems that point to that direction yes and if you go get my setup checklist if you go to brentozar.com and click scripts up at the top i have a setup checklist that walks you through that and gives you links as to why a lot of us are actually pushing microsoft to change that default too as well it's a really terrible default over on youtube maravad says can you recommend a youtube channel to learn sql so i haven't i haven't looked for that because of course i learned sql you see these gray hairs these aren't just from answering youtube questions they're also because i'm old i'm in my mid to late 40s now uh and so i learned sql a long time ago back before there was youtube i know seriously there was actually a time before youtube no kidding i know it sounds hard to believe uh but because of that i just don't have a good recommendation i would look at what other people are saying in here because they may have recommendations for it but it's a great valid question i just don't have an answer for it uh zacky said are you on latest database management studio yes i use uh sql server management studio 18. jesus says apart from sql server dba what's on demand right now in terms of the number of jobs in the current market you want to be really careful looking at that because when something is trendy like artificial intelligence or machine learning let me give you a peek into what happens with executives you know listen get the get everybody in here from tech i was on an airplane this weekend and i read this magazine about artificial intelligence we need to get us some of that artificial intelligence so i want you to go hire a couple of people right now to go learn artificial intelligence and put that into place right here that's exactly what happens all over the world continuously people read executives read things in a magazine or they hear something from their friend at the golf club or whatever it is that those people do and so you'll see this all of a sudden whoosh huge russian hiring for this one technology and then it'll all disappear as people figure out that oh that's not really gonna save our business so i would just be careful on looking at volume of data volume especially around uh it's true people have seen this a lot so i would just be careful with uh judging by which demand jobs are in demand uh let's see here oh we actually answered that one already so i'll go ahead and clear that one um sequel eagle that's kind of a cool name i like that sql eagle uh it says clustered column store index tables user can be painfully slow any solution um non-clustered indexes so you can put non-clustered row store indexes on top of column store tables so from there because i'm assuming when you say to select top 1000 start there's probably a where clause you can add non-clustered indexes on top of clustered column store since i can't remember if it was sql server 2017 or 16 where they brought that out i can't quite remember but that's a great use case where non-clustered still makes sense non-clustered row store makes sense on top of clustered column store also filtered indexes too can make a difference with that as well you can't filter for top 1000 unless the thing is deterministic but you get where i'm going with that jensen says how can we reduce tempdb size shrinking isn't helping free your plan cash free the plan cash and then that's the thing that's usually stopping it from uh from shrinking people may also be using it but i'm gonna gamble that you know nobody's using it right now so try freeing the plan cash that usually doesn't uh jojo says now that more people are using.net core i think clr will be even less used is it is it possible for clr to be even less used i'm not i'm not sure if it actually is um sangaraj thang fangaraj says hi i worked on sql server for four years and i changed my job and i'm now working on my sequel which one is good the one that you're getting paid for the one that you're getting paid for is fantastic that's the one that's my favorite the one that you're not getting paid for that one sucks so there's my answer there now in a more serious basis which one's better or depends on what your business needs are mysql's free sql server is not very free there is express edition which is free and express edition is very similar to small my sequel installations for example we were on brennozar.com on my sequel it's on wordpress as a mysql backend and i would never dream of moving that over to sql server but if i wanted to i could a similar performance and power it would be a giant pain in the rear though oh um written j now asks a great question would i recommend sql server patching be automated i wish i could but the problem is if you automate patching you got to make sure that nothing critical is happening on the sql server at the time you push the patch out because rollbacks and sql server are single threaded what happens if you got like a big data warehouse job loading all kinds of stuff into sql server it's been using like 12 16 cores for an hour and then you go throw in a patch and it causes a rollback to happen your sql server could be unusable for hours or days so i get really nervous about people pushing out patches without doing some kind of activity check to make sure that there are no open transactions there's no activity running things like that next up what would be a good what data type would be a good choice to store xml data xml next up giorgio says what join should i use that is the fastest i have a real hard time joining what more than one table the query becomes uh very slow it's less about the join type that you're using and it's more about how you write the query what its execution plan looks like i have a class called fundamentals of query tuning that helps you elaborate that exact thing but i've got uh plenty of examples with joins with 20 30 40 tables and it performs fine also joins or queries that don't join any tables and they perform like a ford pinto from 1978 it's less about the number of joins it's more about how you write your queries and how you index those tables oh let's see here next up uh thangarang says can you please explain parameter sniffing yes and it's in my one day fundamentals of parameter sniffing class so if you search for brentos r parameter sniffing there's actually youtube videos out there that are totally free where i spend like an hour explaining the basics of parameter sniffing go start there because it's free and then when you want to learn deeper than that go to my parameter sniffing classes keith says i have a friend i like that that uses simple mode in production with hourly backups how do i implement full safely if they're doing simple mode in production and they're doing full backups my guess and they're saying hourly backups my guess is that every night they're doing a full backup and then every hour they're doing differential backups which only change the 8k pages that were only back up the 8k pages that were changed in the last hour that might actually be okay for the business but if it's not okay and if they want point in time recovery it's a really good question what you're asking when you flip it into full recovery model you have to do log backups pretty frequently to make sure that you keep space available in a transaction log i have a class for that called fundamentals of database administration and i know it sounds like i'm pitching classes right here right and left but you're asking questions that are they're great questions but it's way far beyond what i can explain in 30 seconds so that's where fundamentals of database administration comes in uh neil asks over on youtube what does brent do to keep updated with new tech and changes in sql i read a lot of blogs and you can read the same ones that i read i'll show you how so if you search for brent ozar oops brent ozar github i have a repo this is just my personal one this isn't the company one so if you search for brent ozar github i have a repo with a list of the file or list of the rss feeds that i subscribe to sql blogs and so here you can go in and get the full opml file which has all the blogs that i subscribe to so you can go add those to your feed reader so we're talking 234 lines of code it's probably 200 blogs out of there so you can go grab that if you like it's also available as a feedly collection for those of you who read your rss feeds via feedly i do it's wonderful i can't say enough good things about them as a feed reader so you can also click on that if you use feedly and you can subscribe to the same blogs that i do and i keep it pretty up to date like i updated it two months ago i'm sure i've added a couple of subscriptions since then but that's how i kind of get started next up let's see here next up oh remedies no every way sucks so badly just so terribly badly it's it is a giant massive pain i have totally dealt with that i work for a company that had to do it for 3 000 sql servers uh in the united states alone and it was a giant pain in the rear we built our own replication format with our own web service and api calls just to keep the data in sync because it was such a pain in the rear next up mr keaney says what is your opinion on polybase do you believe it will get more popular no i don't i don't think it will so the idea behind polybase is that you can use sql server as a data hub to run queries against all kinds of different servers against polybase they are against a hadoop against oracle against my sequel sap whatever you want to run queries against i don't think it's going to become more popular i think it's relatively kind of expensive and i don't see people wanting to bet the farm on sql server being at the center of their entire data infrastructure especially in the day and age of the cloud where people are starting to shift their focus more if they're going to put something that's the absolute total center of their data environment it's probably going to be in the cloud more than it is on premises it's just that if you're going to do that kind of architectures i don't see that becoming much more popular ah contact says i want to order an index in the fashion that contains the most distinct oh so you're back to the uniqueness thing okay so you don't actually that's not the right way that you design indexes and i teach why inside my class fundamentals of index tuning but i'm going to give you the short answer for example at stack overflow there's a creation date in stack overflow every row has a creation date column that's super distinct everything in there each of the posts the comments the users all has unique creation dates but do you think that that should go first in your indexes probably not because even though it's unique and even when you query by it you don't query for creation date equals july the 4th of 2007 at 9 17 a.m that's not how date range queries works you query for ranges of dates like show me everyone who was created yesterday show me all of the comments that were left in the last seven days the uniqueness of the data isn't selectivity the uniqueness of what's in your where clause that's what selectivity is and we go through a lot of those examples over in fundamentals of index tuning so that you can learn how to organize those but now i see where you were going and that's why it's the wrong problem to solve there next up sequel eagle says oh we already had that we answered that one so let's close that uh noob says i really am a big noob well if you're a giraffe you are at large in terms of size stuck in a situation my job where i'm supposed to alter a column uh on it's really large oh it's my sequel i don't i don't work with my sequel sorry i'm just as big of a noob as you are at my sequel the place where i would ask is dba stack at dba.stackexchange.com that's where you can go post questions about all kinds of databases lots of talented people over there who can answer that question so but you're you're right in that it isn't an easy question it's the definitely the kind of thing that you should post over there because it is a really good question uh jesus says if someone only wants integration services and not the sql server how much do you pay for that the same as sql server when microsoft wants you sell wants to sell you something expensive they put it inside the serial box that is sql server they put it inside the sql server licensing box and they say that it's free it's like a free toy that comes with your breakfast cereal and then that way when you decide that you want to buy a bunch of the free toys microsoft is like sure you're just going to have to buy 6 000 boxes of cereal so it's not really free really when they throw something in for free they're about to tell you that it's going to be extremely expensive when you go and buy it what i would do instead i mean the one nice thing is that you can buy as few cpu cores as possible and just installing ssis on there but what i would do instead is go look at azure data factory because azure data factory is really kind of like the next generation of sql server integration services and then instead of paying for sql server you can just pay by the month for oh well that doesn't work well does it i'm probably missing something obvious next up on youtube uh nasi says what's the ideal approach to monitor sql traffic or enhanced security in terms of auditing by a third-party appliance that sits in between sql server and the rest of the network imperva guardium are examples of that if you look for google for sql server auditing appliance these are pieces of hardware or you can buy them as vms that capture all network traffic coming in and out of the sql server the dba doesn't get permissions to them the developers don't get permissions to them so that way you can ensure that it's a sealed box that nobody tampered with only the security team gets access to it and they can then run whatever reports that they want that's the way that you do it in a legally defensible fashion let's see next up we'll uh go down d toby says i want to thank you for these sessions you're welcome thank you says uh for me these sessions are unique and a strange experience you know what they are for me as well [Applause] uh next up uh haresh says i like your reactions and mimicry you are very good at that works works really well for uh things like twitch streams it's not very good if i have a manager that's why you don't see me working for a company anymore you see me having my own company because that's the only way that i could make a living doing this if i had to go back and report to someone else again i would need a very very tolerant boss let's say zacky says i've seen a lot of people going into react express in node would you recommend using sql and i assume you mean sql server for this no generally if you're using stuff like react and node.js the the node.js the kinds of examples that you're going to find out there aren't going to use sql server you want to find the use the tool that there are the most examples for and the most support for so that when you run into problems you can quickly find other people who are doing it and you're not going to find a lot of people or as many people using things like react with a sql server backend let's see here uh wrtko says do you have a stylist your outfits are great my wife yes my wife would be my stylist not hair stylist i actually went and got a haircut this haircut is terrible uh but we don't have a whole lot of haircut options right now in san diego because most things are closed down so there you go uh it does sql servers had a json data type for a couple of versions now why do i think you don't read the documentation that's a difficult more challenging question robot bear robot robair robot robert uh says i'm a noob just finished a data analytics bootcamp if you were to hire an intern to help with database cleaning uh what sql database skills would you look for oh that's intriguing um the uh i for me it's less than about like specific sql or db skills it would be more about go tackle a project and show how you did it go take an open source data set and clean it to prep it for analysis and write a blog post or explaining what you did in which order and then that when people want to see what your skills are say here's an example of a project that i did so you can see my thought process so then that way they would be able to see all the skills that you use start to finish that to me would be more useful than yelling out a bunch of uh generic sql terms because with data cleansing it's just going to be like i know how to run select and update statements um richie says i wouldn't hire an intern to clean data you say that but i bet a bunch of data science companies would uh i mean you know it's interns are cheap uh next up uh ravi taja says hi brent what is the cost of replicating a read-only instance i have no idea what the user is asking i have a blog post about that i'll show it to you so if i say brent ozar reporting server cost if i search for that there's a post on here the cost of adding a reporting server hit that because it has a whole bunch of costs in there it's not just about the licensing or the hardware it's about things like the troubleshooting and performance tuning of it so go search for brentosar reporting server cost there we go and now let's come back over here oh i should give a shout out to our sponsor too so been a little while since i've done that so this week i'm teaching a totally free class on filtered indexes indexed views and indexed computed columns you can go attend that totally for free over at brentozar.com go slash training days that's quest software's database training days where they're doing a series of webcasts with several of us like me panel and janice are all doing a totally free webcast and if you can't be there on the day of the webcast they also email you the link to the recording as well so you get all that over at brentozar.com slash go slash training days i should also give a shout out to the last couple of subscribers and followers that we've had there's cesar condor human uh and angry scott from i'm guessing south africa is south africa i think i'm not sure if that's yeah i think it's zia i think south africa but if you like what you're seeing and you want to get notifications whenever i start streaming go subscribe or follow me on youtube twitch or my facebook page and you'll get alerts whenever i go and start these streams this is the last regularly scheduled one that i'm doing the rest of them will be totally ad hoc just whenever a window opens up in my schedule so you'll want to subscribe or follow so that you get notified whenever these start next weekend i won't be doing one i've got classes that i'm teaching this next weekend let's come back over here and say let's see the last we still got like 20 minutes left um let's see let's uh close that how would dashboard js how would you tackle bringing data from oracle tables into sql server when the oracle tables are wider than sql server will allow um [Music] i have no idea i don't know i have no idea i wish i had a better answer for you i don't uh ask it that's ask stack exchange go to dba.stackexchange.com and include numbers include numbers about how many columns are inside there next up sql santa says i'm using collate in the where clause a sql server is on ci being collated to cs any way to help this yes you can use persisted computed columns with indexing and i talk about that on my quest webcast that i just plugged so by all means tune in there and i'll teach you how to do that exact thing uh cecar says brent can you give your recommendation for aws ebs volume types to use for data and log storage for five terabyte transactional data in ec2 oh dang when you get over a terabyte worth of transactional data it starts to performance speed or storage performance starts to become incredibly important because if you need to do an insert update or delete if that page isn't cached in ram you actually have to wait for that page to come back from disk in order to change it so the clients that i have who are doing multi-terabyte transactional stuff in ec2 tend to be on provisioned iops provisioned iops are not cheap they are what you call expensive but if you're going to run a multi-terabyte oltp database in ec2 that's kind of just how things work general purpose solid state is not going to be enough for you you're going to need uh provisioned iops next up on youtube jitesh says uh should a non-programming person learn sql first or r or python oh that's um depending what your career objectives were if you wanted to do just general systems administration i would probably send you down the python path because it's probably going to help you more with general automation through the rest of your life if you want to be a database person like if you want to explore data and write reports and see relationships between things i would say sql first because sql is the language of data python is more the language of systems moving things around analyzing things but processing more than it is data itself it's a language of doing things with data but it's not really the language of data uh oh zacky says is there a way to prevent sql injections on the database side in addition to escaping your backend sql queries the thing with sql injection is if you try to build something to be safe you have to be right every single time the hacker only has to be right once so i want defense layers everywhere that i can get it and for me with sql injection that means never using what the user passed in to build the query use it as a parameter never use it as part of the string that you're building for a query you can't escape it enough a creative developer creative hacker will find their way around your defenses if you want to learn how freaky it is search for bert wagner sequel injection so bert wagner has a series of videos that will blow your mind on how you can do sql injection and when you watch each of these and see how much work that's involved with it you'll realize why you really don't want to be trying to defend yourself against attacks like this it's just too dangerous let's see here next uh do you know any open source graphical visualization tools for visualizing table stats in data skew um no i would want to know more about the problem that you're trying to solve like what it what's the what's the question you're trying to answer if you're just trying to see pretty pictures of data go to reddit there's a red subreddit called beautiful data i think it is and it'll show you all kinds of random graphs but if you're trying to ask a question about your data start with the question don't start with show me random graphs about my data sounds like i'm lecturing i'm lecturing uh um we'll do a speed round of a few ahmet you can't replicas with different indexes than a primary you can't no can do thanks for putting that in absolutely those are both fantastic their data is beautiful it's fan perfect um gmos dba says a friend asks could you send share a general strategy for tuning queries involving nested views no i do have entire classes on that so that's the entire mastering query tuning piece you go into that next up zacky says i've seen lots of libraries that can generate sql queries why are developers are still writing raw sql queries when you need to do something complex that runs quickly a library is the fastest way to build slow queries raw sql is the slowest way to build fast queries if you need a query that's fast sometimes you have to resort to that skyward dev says as a dba are you required to understand the data storage laws of all the regions in which your company has database servers running or most of those things handled for you that's what your security legal and compliance teams should be for you as a dba you may be responsible for understanding the kinds of data that you have and you would present that to your security and compliance teams they would be the ones to help work with you to architect a strategy to protect it carefully sometimes i know people who will say the dba needs to know that i don't think that you're going to know the laws of all the countries and you sure as hell won't understand how to apply them that's what lawyers and security teams are for zachy says i'm really confused on why these sleep and wait for commands exist when would you need to hard code a delay great example sp blitz first i take a sample of a bunch of your weight stats wait for five seconds take another sample and i tell you what the difference was between those two times other examples if you have a process that needs to run on a queue that you want it to continuously run the same query but you need to inject some delays between those to make sure that you don't overwhelm the sql server a wait for it can be good for that especially with google the term exponential back off there's a design pattern called exponential back offs where you run a query if no results are found you wait one second if no results are found you wait two seconds four seconds eight seconds and so forth wait fours are great for that bob says would you what would you like microsoft to change before releasing cus the documentation the documentation in current ceus would be unacceptable for me if i was looking at the read me for uh angry birds if angry birds came out with reedme's this crappy i would be pissed at angry bird developers this the documentation we're getting ceus is unacceptable for software that cost two thousand to seven thousand dollars a core it is bs as an example the most recent sql server 2017 cumulative update that came out put out documentation that was incorrect and the only reason we got it changed is i blogged about it and a microsoft person read my blog and said you're right that documentation is wrong if they can't get a two sentence piece of documentation right they need to hit the breaks and learn how to write basic documentation before they ship enterprise quality software where people are using it for dispensing medications tracking where ambulances are tracking where police cars are that level of documentation is utterly unacceptable i'm going on a rant on there i'm this close to publishing my next blog post on brandozar.com is going to be about why you should stop applying sql server updates until microsoft gets their act together i'm pissed next up yummy says how can we automate replicated logins being moved to different replicas dba tools i mentioned dba tools io earlier that they have a great set of scripts to help you accomplish that and i wouldn't expect it to be built into the product because you may need different uh logins on different replicas for other uses as well next up daniel says how can you get wait stats for a previously executed query the plan stored in cash is estimated so that's not the place to look one way you could kind of think of is is a query store query store will kind of sort of get you there but 2019 has a new feature for this that's kind of cool so with sql server 2019 server 2019 last actual plan uh sql server has a new trick it's probably oh yeah that grants post just probably got it in there so this is a new database scoped configuration that will let you turn on the last actual plan and see that inside the plan cache i'm going to copy paste that out and then i'm going to go run it over in sql server so i'm going to go run it i've run it on my sql server now now i'm going to go run a query select top 101 star from dbo users order by reputation ascending go execute that then when that finishes i'm going to go over to and run sp blitz cache in another window and now when i go look at the queries in the plan cache if i go click on the query plan now i get actual numbers i can see actual numbers estimated and actual numbers for everything inside the plan cache if i right click on here and go into properties i get query time stats and memory grant info that's actual i don't get weight stats yet i wouldn't be surprised if they added weight stats i actually thought it was in there i'm kind of surprised that it didn't uh let's try that one may not have had should have had at least sql yeah so it may not have weight stats yet maybe they'll add it uh but that would be one way i would also go into query store would probably be the first way though all right let's go switch over to here and we have let's see here the last couple i'll just go hit a couple random ones uh drop table employee says persisted computed columns blew my mind on one of brent's training classes glad you liked it uh ss sql jazz says ssrs that's not even a question oh ssrs on the same server or on its own we've always split it but a vendor recently recommended on the same server what they may have recommended is because srs doesn't use that many resources they might have said like oh why don't you just put it on the same sql server because you can cut your licensing expenses that of course depends on whether you're licensing the whole vm host or whether you're licensing per each individual vm uh khaled's calendar says dj khaled dj khalid's in the house thanks oh another one just love this guy uh will async network i o show up if network has pcs with different network card speeds it can it can also show up if the the computers are very far apart like i have one client where their sql servers in chicago and their app servers are in mexico and so even if they have good network cards you're still going to see async network io kelvin says that's a very nice view it would be a lot nicer if it wasn't for the rain and the fires but okay and then we'll take one more let's see here one more veteran says i am a sql server developer working i guess that's good i mean you could be not working you wouldn't want to tell your boss that i guess you're a twitch you're not really working or youtube i suppose if i'm not asked to work in azure someday will there be anything new in sql server to learn please advise there will always be something new to learn that's kind of kind of one of the parts that i love about technology is that the stuff's always changing i don't ever want to learn a new programming language like i don't want to learn actual languages either i don't i just don't like languages for some reason but yeah there's always going to be something to learn it just never ends that's kind of the fun thing with technology if you like learning technology is an incredible place to be the more you learn the more you earn i'm sure someone said that before me but that's just how it is all right one last shout out to this week's sponsor so quest is running a totally free set of webcasts this week over at brentozar.com go slash training days on wednesday you can learn from me about filtered indexes indexed computed columns and index views computed columns and filtered indexes that's the other one so it'll be about an hour long of totally free training you can go see it over there if you want to catch me the next time i do one of these question and answer sessions just uh subscribe or follow on twitch follow on twitch or subscribe on youtube and i will see y'all next time hope you had fun and learned something i will see you later adios

Channel: Brent Ozar Unlimited

Views: 3,701

Rating: 4.9578948 out of 5

Keywords:

Id: ixVOU2DRhfs

Length: 115min 0sec (6900 seconds)

Published: Sun Sep 13 2020