Office Hours: Ask Me Anything About SQL Server

all right hello good morning party people and it is a wonderful chillaxed morning here in san diego i had a nice uh goof-off day from the bed okay that's interesting aries and i are uh in bed together uh yeah i had a nice goof-off day yesterday my my big project for this five-day weekend is to uh have everything finished for my black friday sale every year i do a big black friday sale and do like 70 80 percent off a bunch of stuff but i do a lot of marketing kind of things a lot of emails leading into it free classes all kinds of things two hours one way holy smokes well wow richie dank that is you know and florida's a really long state so it's like it's easy to burn two hours going in one direction richie's starting out about as far south as you can get in the continental united states without having to be in a jimmy buffett song if he goes any further south wow geez uh i laid mrs clippy uh good morning good morning so uh today we'll do totally open question and answer any questions y'all want to ask about sql server i am always amazed that there uh continue to be questions you know we've done office hours webcasts from back when we had teams when uh we had you went over like three or four of us and down to where there was just me and i was always amazed at just uh how many questions there just continue to be and that they're different that uh richie you know it was funny i was gonna actually show the black friday marketing calendar like everything that i have to do in order to set up all these staged emails and different webcasts and stuff so it was i was like debating should i go through that this morning but i think i'll do a separate uh uh separate webcast yo adam saxton yo uh yes i love that that keyboard i don't usually leave it on for lights 24 7. i just only like turning it on for the webcast and of course it has all kinds of different lighting patterns that it'll go through and do and so i kind of like how some of them are separate like weird sideways good morning massimo so i kind of like how they it shows different stuff when it's on coming from the side which is kind of cute akil says the car is back i rotate different cars in and out that one's back to my my porsche uh because i feel guilty i was like i was i was this close to buying another car my wife and i were talking about there's one car that she would give me permission to buy and i was like right teetering on the edge and i'm like i have no business owning another car i work from home we're our apartment leases up in like february i i don't know that well technically it's up in december we're going to try and do month to month for a couple of months and then we're aiming to shoot off for iceland for three months uh to depending on if i get a work visa will stay even longer a delorean i do like deloreans but i would get a different engine i've always wanted to do like an ill not always but i would do an lt1 swap into a delorean but just a regular delorean i like my fire because they're slow as hell so it that makes it uh kind of tricky uh so superior serbia asks we'll do that as the first question what else is important in an actual execution plan other than to find out parameter sniffing so for me it boils down to what's the server's biggest problem so depending on for example if the server's biggest problem is blocking then i want to know what kind of locks that query is taking if the server's biggest problem is memory grants then i want to look at the memory grants in the plan if it's cpu i want to look at the cpu and the plan because even if it's not parameter sniffing the query could just suck every time right it's not like you just suck sometimes it sucks every single time so uh let's see here i'll uh add a couple of queries over to the uh over to the queue there so there we go got those in justin says the delorean only needs to hit 88. yes but it's how quickly you get to 88 if you've ever been in a volkswagen van for example you know that they'll hit 60 miles an hour but you could read a book in the time that it takes it to get to 60 miles an hour i'm not saying that you need to get a vw bus any faster to 60 miles an hour because in a vw bus you are the crumple zone you know it's not like you really want to get in an accident with that thing but for me it's like i'm not going to really go that fast top end but it's how quickly can i get there and after all of course if you're driving in delorean you have to escape the terrorists in the mall parking lot right you know you got to get there faster so aries asks besides sequel uh what else does clip besides i think he's saying besides sequel and clippy what else does uh brent like brent likes vacations brent does not like working at all brent really likes not working brent likes sitting at the side of the beach i don't really like getting in the water that much i mean i will get in the water and i do like body and surfing and stuff like that i can swim but not like professionally by any means like i would lose anybody a race with anybody might swim okay um and uh so i i like uh being near the ocean and seeing the animals you know dolphins whales and all that hearing the crashing noises having my ipad and drinking a lot you know eating mexican food drinking margaritas that kind of thing so that's that's my idea of a good time iceland dude the reason that we're going over to iceland iceland sounds like the opposite of that right but they actually have the ocean there it's uh you get a lot of beautiful beaches stunning beaches in iceland and it's not really that bad because it's not like i'm getting in the water and it's going to be cold anyway so it's i kind of don't really miss anything the scenery is unbelievable all right let me copy a couple of the questions over into the chat we'll see from youtube we'll ask that and then we'll copy this over and from youtube richie says richie says richie likes working richie doesn't drive four hours for one soccer game that's true all right let's see the next question here uh let's ask that put that up in there so greg says any tips for a dba forced to do a lot of power bi work and the reason why i put that on was adam saxton is here because adam saxton is guy in a cube of the epically beautifully produced guy in a cube youtube channel with him and patrick leblanc so if anybody would give somebody tips for a dba force to do a lot of power bi work that's the place where i would start so one one tip that i would give you is go follow guy in a cube's youtube channel and i'm not saying that just because he's here that's how i learn when i'm having i don't actually go out and proactively watch all their videos because they put out a ton of content but when i want to find a solution to a problem that i'm facing in power bi i tend to search for that problem plus guy in a cube because i know that the videos that i'm going to get from them are going to do a good job of walking me through the problem in a fun way that for me is was the biggest starting point i don't know that i have another learning resource that i really liked a lot in terms of how to get started with power bi that that's true you have to make a decision right from the get go do you want to load the entire data set into power bi or do you want to have power bi make the queries every single time and architecturally that can also have to do with the size of your data set like when i first got started working with power bi my data could fit entirely in power bi and i would just pull it all down in one fire hose and not have to worry about doing any processing later but then over time i got to the point where the data is just too large to fit in power bi and i can't do direct query because i'm working with amazon aurora which has to be accessed via the damn gateway i like power bi i love power bi but it's still there just it's so close with a couple things that i need to do and not just quite there yet not that it stops me from using it it's still the only gui data front end that i use for amazon aurora so that's pretty cool uh next up from youtube youtube viewer asks is my sequel faster than sql server i'm looking for any authority who can answer me this question it's kind of like saying what's faster chevys or fords it all depends on the specific hardware that you use and where you're trying to drive them for example are you taking it off-road or are you running it around the nurburgring so there really is no answer about which one is faster overall it's for specific scenarios specific access patterns what it is that you're trying to do and i'll give you an example data warehousing with data warehousing if you look up the benchmarks for tpc the transaction processing something or other for benchmarks for tpc you're not going to see my sequel up anywhere in the charts it's exclusively oracle sql server i can't remember if db2 is still up in the charts or not but those kinds of systems are very highly optimized by expensive database systems that mysql just can't even hold a candle to it however if you're going to do for example sharding out across thousands of servers and split the data across all of them to deliver your results you can end up with better performance from mysql as long as your application knows how to handle the sharding which sql which my sequel server it needs to connect to so that's why you're not finding a single answer from an expert it's like saying what's faster chevy or ford from youtube a stored procedure uses a number of multiple temp tables to speed this up is this okay i smell something weird when you say a number of multiple temp tables 16 in order to speed things up what i'm guessing is that they're pulling down a large amount of data and repeatedly processing over it like step one then step two then step three and they're kind of treating sql server more as an application than they are a database server i'm just guessing though so does it pass the smell test no i'd probably want to know a little bit more about that because something about that smells a little fishy now do temp dbs are tempting are temp tables useful for performance tuning absolutely and i talk about it a lot in my mastering query tuning class in my three-day mastering query tuning class we cover how you use temp tables when ctes aren't efficient or when somebody's trying to join like 40 tables together sometimes you need to do some pre-processing with the first couple of tables into temp tables so sql server can understand how the rest of the joins are going to work let's see let's add a couple more into here let's see here uh all right there we go uh just pk says uh why are there no future of temporary functions in t sql well because most of us were not employed for only a day or two we're employed for longer periods of time i understand why you want temporary functions because you go and put your code into production and then they fire you within 48 hours you want to make sure your functions don't stick around but i got great news eventually you're not going to suck so bad at your job eventually things will be stable enough and they'll let you put your code into production permanently now that joking aside there actually are temporary functions and temporary stored procedures in sql server you just got to know how to create them let's do one so to do one normally we think about stored procedures as create or alter proc dbo usp and i'm just going to make a stupid one get date as select get date so normally this is the way that we think of permanent stored procedures well just like you create temp tables you can create temp stored procedures so here i'll go execute that and then when i want to exec it i can just say exec usp get date and it works for me and my session and only my session so that's how you do temporary stored procedures you do the same thing with global temp stored procedures by putting two pound signs in front of it the trick is these disappear whenever the last session that referenced them closes so you they won't just stick around permanently there are hacky workarounds for that but generally i when i need to create a temporary one i just create one temporary to my own session it's useful for uh those of us who are consultants when folks say you're not allowed to create any thorough procedures in production but you have to fix it right away and so i'll use this trick in order to create temporary ones for performance tuning testing all right back over here see drop table employee says is there a schedule of your live classes somewhere on your site yes if you click training up at the top for each of the classes every one of the classes has its own schedule and that's normally how i would recommend going through the classes is find the class that you want not the next upcoming date however those of you who have a live class season pass you can look at my site and have a list of uh links inside there and you see the upcoming classes um john i'm not sure uh oh so they're both databases so see sql server is a database just like mysql is just from a different company and it's really really expensive so you make more money next up uh let's see oh this is tricky um scuderia says if you had the priority that's the italian like racing word uh if you had the power to change or fix one thing in sql server what would you change this is so tricky and i've thought about this a few times because people assume because i write some posts that kind of poop on microsoft uh i write it from time to time be like this feature sucks or this is a really bad idea or why would you do it this way but you know what for the most part i don't really need anything desperately from sql server i'm really pretty happy you know what i wish we had less bugs less bugs i would really go for less bugs because after all if we think about uh the number of cumulative updates that we come out like cu7 for sql server 2019 just came out like 30 days ago and they had to pull it out because they didn't do enough quality control testing then they ended up making things worse instead of better so i'm just always sketchy with that i i think if i had to choose uh one thing to improve uh it would have to have a better way of fixing parameter stiffing like story yeah adam you're absolutely right a better way of fixing parameter sniffing by storing multiple execution plans for each query and use varying out use varying execution plans based on the size of the data now oracle does stuff like that sql server not so much and of course my sequel and postgres because we have my sql people inside here you're not even close but that would be the one thing that i would probably wish for and aim for other than that i don't really query plans are great t sql is great i don't think i ever uh find myself going dang it i can't do x and sql server um i love where they're going with azure sql db and hyper scale and managed instances all of those things are great okay okay i got one other thing i kind of sort of wish is that standard edition wasn't limited to 128 gigs of ram now i wish that you could go to say 256 gigs of ram adam you're you're right but the thing is see for example with my consulting business i am mostly of my clients are on 2016 and 2017 if microsoft fix parameter sniffing in the next version i'd still be having a great time doing consulting for five or ten years you know it would just take a long time before people go through and adopt the new versions drop tables has 3d query plans that's true i'll give you uh credit for that as well uh all right let's add a couple into here so there we go let's go put the next one in is pro hiller says any sneak peek regarding your black friday training pricing if a lot of you say please i'll let you go see it ariel says what's the optimal definition of minimum memory usage be more specific about the problem you're trying to solve because i i don't know what you mean by are you talking about the query level the server level like resource pools be more specific about them just pk is a streaming service for sql server when querying huge amounts of data tell me more about what the problem that you're trying to solve because your definition of huge is probably different than other people's like for me my definition of huge is 10 terabytes in one table and above several of you are asking all right cool so here let's go take a look so if you go to brentozar.com and you're gonna have to put a url in because it's not like out anywhere on the menus yet but if you go slash black friday so if you go brynnozar.com black friday here's the pricing that's in effect and it's out there already but i just haven't shown it to anybody yet because people who are recurring subscribers they get to get in before most of the public does so level one fundamentals this is a bundle that includes my recorded class season pass sql constant care the consultant toolkit if you bought all three of these individually it would normally be about fifteen hundred bucks if you buy it during november you get eighty percent off so it's 295 to watch all my recorded fundamentals classes the senior dba class the dba interview q a fundamentals a column store fundamentals of parameter sniffing like all seven to 30 40 hours worth of videos so that one is 295 then level two includes all of that plus a live class season pass so you can attend all of my live classes for one year straight that one's 9.95 you save 82 percent like five grand yeah don't touch that that settings five just leave it to zero don't touch that one then finally a level two for two years straight because what i had was i had a lot of people come to me and go hey brent it's just as easy for me to go to the company just once and ask for two or three thousand bucks just let me go once and i'll take care of my career for a while plus two that way if i quit and i go to another company i can change the email address on my account and i can keep learning if i go to a cheap company as well no i don't accept trades for weed cocaine or anything else like that just plain old what we call money it's kind of an old concept but it works really well so that one level two for two years sixteen hundred bucks basically you get a whole another year for just six hundred bucks bringing the savings up to like eighty six percent so there you go uh ariel don't don't don't don't do that don't do that don't do that what don't don't change a setting until like why'd you set min equals to max that's a bad idea don't touch that put that back where it was microsoft had min at zero i understand if you want to set max don't set min that's a bad idea um mr robot all of my attendees are students because because i'm a teacher these are these are classes so there you go all right so there's that one let's come back over here to your general q a and then the emails for that will start going out around uh first of november exposure bucks i think you mean query bug exposure books that's pretty funny um uh yahoo says on youtube and there's some on twitch and i'll get to those in a second but yahoo says are the defaults for azure sql db and managed instances the same is bad in standard instances yes uh absolutely next up let's see here next up our air journal says we're using a sharding pattern roughly how much data or how many rows would you recommend per database so for me what it comes down to is they're striking a balance between cheap hardware and pain in the rear management because the more boxes that you shard across sideways more vms whatever the more that you shard across sideways the more management that you have to do the more high availability and disaster recovery that you have to deal with the larger you go in size the more you have a tough time in terms of hardware so the clients that i have doing sharding tend to shard so that to the point where they have twice as much memory as they have data so if you're at the point where you can buy like a terabyte of memory per pizza box you can put 500 gigs of data per server the reason why they do that is that the data is going to grow and they're not going to change memory immediately so they want to be at the point when they they want to be the point where they can still continue to grow caching most of their data and ram and then still deal with that same sharding setup and then a year or two later they go back and revisit the number of boxes uh hannie says oh good to see you hannah says uh no questions just wanted to say thank you brent for all the knowledge you're sharing in the community you're welcome i appreciate it i always uh have a good time uh sharing this kind of stuff so it's a lot of fun uh next up life extinguisher says i'm a production dba who wants to switch over to dev dba my resume never gets shortlisted any tips as soon as you're facing a list if your resume is in a stack if your resume is in a stack with other people you're screwed you're not going to get the job rather than going through the stack of strangers what you want to do is network with everyone you've worked with and over the past and currently because all of them are going to go out and get a different job away from that poop hole where you work today when they go off to another company you want them to remember what you're good at or what it is that you love to do so for that you go and just send emails to catch up hey you know we used to work together at acme you know fire extinguishers just wanted to let you know if you need any help with query performance tuning or indexing just let me know i always enjoyed working with you so i'd be glad to help you out any way that i can so that way you don't say that your work asking for a job if somebody gets the email the rumors don't go out that you're looking for a job you're just genuinely offering to help them because you did like working with them and you want to work with them again so that if you if you stop and think about it and you go oh my god none of the people i've ever worked with are ever going to want to work with me again that probably says something too because i know i was at that point in my career when i first got that advice i was like i don't want to work with any of those buttholes turns out that everywhere you go everyone's a butthole it's not just the one place it's all buttholes everywhere buttholes buttholes everywhere brentosars uh h human resources advice uh so you you just gotta get good at sucking it up and being good to other people that you work with and that you because eventually they're all going to be your future co-workers or clients uh let's see next up grab a couple of those um oh not the dba you're looking for says i assume that you're still not uh doing selling to the eu yeah i really wanted to this year but when the eu struck down the privacy shield law that means i can't really store data about eu customers inside an american's database server so i'm not really going to bother with that then because at that point i would really have to forklift up all my data and shove it over into the eu like my american customers would be over there the website lag would suck too bad and i don't want to deal with any of that kind of privacy rule type stuff the e was only like five percent of my revenue so i'm like okay i'm holding it off for them yeah yeah yeah yeah people try that too all the time people are like i'm gonna try from you know bob in florida but we match your credit card billing address your ip address all kinds of other stuff and so then people like next up now john says what would you recommend for a sql database for a website is mysql and php admin fine so it depends on what your skills are if you're skilled with my sequel then just use my sequel what you generally find is that in enterprises if you go to work for uh i'm just going to name big companies general motors uh amc theaters you know just like big giant chains of companies they're often more comfortable with enterprisey software products because they tend to offer things like auditing tighter security access control 24 7 support from like a big global company but if you're just building something yourself my sequel's totally fine postgres is totally fine i would probably lean push people more towards postgres than i would my sequel postgres seems to be a lot more vibrant these days we use postgres ourselves for our own stuff i use mysql for my wordpress installation obviously but we use postgres for our software as a service stuff and postgres and dynamodb which is also wonderful nothing against azure azure just didn't have the database stuff and nosql stuff that i wanted at the time that we started the software as a service products but yeah brent brent does use mysql and cpanel and php my admin too as well not good at it i just use it abused this admin says would a temp stored procedure have any use in dynamic sql noc is effectively when you write dynamic sql it is a temp stored procedure it's kind of the same thing but i love where your mind is going like that you you have a dirty mind and i like it i love where your mind is going that you could theoretically create a temp stored procedure in dynamics equal i just don't think it would really serve us any purpose because if you're going to type out all the queries in dynamics equal you just run them you don't need a stored procedure for it but neat neat question that's uh kind of cool neil on youtube asked what are your tips for dev dbas any do's or don'ts well i highly recommend my training classes if you go over to brentozar.com and you click on training up at the top i have whole classes just full of my tips and do's and don'ts for professional database administrators and development dbas and developers enjoy so could i sum it all up inside one question and answer uh yeah obviously not that's why i sell all these cool things next up over here let's see here let's add a couple of things into the queue um andy leonard says that's funny uh andy leonard says my i'm self-employed my boss is a jerk sometimes what's the great uh line that's out there it's like why work 40 hours a week for someone else when you could work 80 hours a week for yourself and it's truly true like those of us andy's a freelancer or like i am or you know company founder or whatever you want to call it uh and the the if you want to be your own boss you really end up working way more than you probably would for someone else i know a lot of you work for someone else and you think that you work hard well if you looked at your checkbook and the amount in your checkbook was exactly tied to the number of hours that you worked the temptation is there to work continuously and burn yourself out so that can be kind of stressful elgetti says i want to learn azure any recommendations or resources please search for microsoft azure training and microsoft has tons of free training material the rest of us who are like professional trainers we can't keep up with that because azure keeps changing so if i were to go write a course on azure it would be outdated by the time that i finished building the course and so i would lose money on building it so it's kind of tricky uh yeah hor that's a great question no it's not because those don't have plan caches they have to build a brand new execution plan every time so they have high cpu usage for other reasons that's why it's really tough to get them to scale to say a hundred thousand two hundred thousand queries per second because they have to build an execution plan every time uh bluetooth says what kind of features do sql server offer for geographical datas and data and queries so micro sql server offers uh spatial data so you can store data basically in latitude and longitude it feels like one of those check box feet and box features where they wanted to be competitive with all the other cool kids who are doing it so you can do it it's just that very few people are doing it in microsoft sql server just not a lot of people are using that particular feature so the support doesn't tend to be robust the documentation isn't all that in-depth it's hard to find other people you can hire off the street who are doing it as opposed to postgres postgres is a much more popular tool for spatial data so if you were going to pick a platform i'd probably pick postgres if you were using sql server for everything else that you do then that would be different then you would just use spatial inside sql server all right let me copy a couple of queries from over from a youtube queries uh let me copy these over here so from youtube paste that in there and then i think that uh oh one other one hold on a second here copy this from youtube paste that over there and there we go now let me add the other ones to the queue add add and add okay good so uh geocerm says a friend of mine got mad googling this i like how you said that there that's pretty smart you're a regular viewer you're paying attention when uh joining tables from two different databases on the same sql server instance are stats from both databases used as if the tables were on a single database yes as far as query execution goes you won't really see that much of a difference and i'll clarify that much here in a second you won't really see that much of a difference if the tables are in two separate databases doesn't matter for performance in any way shape or form the place where it can catch you is depending on which database you run your query in you'll inherit database scoped configurations like mac stop which can be set at the database level since like sql server 2016. so but otherwise it doesn't matter as long as they're on the same server if they're on different servers that's a totally different ball game then just generally speaking i would tell people don't do that but as long as they're on two different databases on the same server you're fine next up one of the viewers from youtube says my first question your thoughts about using graph databases in sql server you know how just a second ago i was describing uh spatial data features in sql server and i said it kind of feels like one of those check box features like microsoft needed to check the box that they were compatible with space we have spatial the cool kids have json data we have json data um that's how it kind of feels with graph databases other people have graph we have graph and they check the box like they're just trying to be competitive in some gartner quadrant thing if you're starting from scratch and you're thinking about putting a graph data inside sql server i generally tell people if you're just getting started use the cheapest tool for the job and sql server isn't the cheapest job cheapest tool for graph databases nor is it anywhere near the most mature so my my thoughts on that are usually don't do that if you're just starting a graph database from scratch go pick a graph database rather than putting it in sql server if you have a big established database say a hundred gigs or above and you just need to store a little bit of graph stuff well then sure you could put that inside sql server and just extend your application a little without having to introduce some other database that you don't really know how to manage db augie says the client will not tolerate off-premises database yet it seems like azure sql is rapidly replacing sql server must azure sql always be oriented to support off-premises implementations you have a few different questions inside there so number one the client will not tolerate off-premises databases this is going to be like a uh a tipping point where all of a sudden a whole lot of people who weren't satisfied with off-premises are going to be suddenly satisfied with them because really at the end of the day microsoft amazon and google suck way less at managing backups than we do same thing with security you can do a bad job of security on premises you can do a bad job of security off-premises it's kind of the same thing but this is yet that seems that azure is rapidly replacing sql server that's just microsoft's marketing because if you go up to azure they make money on you every single month whereas if you buy sql server they only make money if you renew your licensing software assurance so it's kind of a little tricky um so he said dude like do i need to worry about knowing azure sql db or whatever i i love i think it's a really interesting product but the thing is anytime you spend learning it today the stuff that you learned today may be out phased or outmoded by the time that you go to use it so if you're not using it it doesn't really hurt that bad to hold off for another six months or a year until your company's starting to consider it every now and then you'll hear a blogger go if you're not learning azure sql db you're a dinosaur that's a load of hooey i know a lot of dinosaurs who are still making great money on good old sql server and i know a lot of people who thought they knew azure sql db and don't know jack and can't find work using it so it's it's kind of all over the map there uh let's see here next up uh and to adam says on-premises isn't dead far from it yeah absolutely they mentioned between azure and on-prem sql in the latest ignite what microsoft's trying to do is this arc data services thing where you're going to be able to run azure sql db on-premises and manage both your on-premises sql servers and in the cloud sql servers and your azure sql db all from the same ui they are writing this thing as fast as they possibly can because they want to get your money regardless of where your data is stored so yes azure sql db will be available on premises yes you'll be able to manage your at your sql servers in both ways exactly seven lies nails that they'll only stop selling it when they're not making money from it and if you go and learn at arc today i wouldn't spend five minutes of my life learning arc because nobody's using it and it's gonna change so much in the next one two three years you'll have flushed that learning time down the toilet next up seven lies asked from a dba consulting perspective what does a great sis admin look like and what does a terrible sys admin look like a great sys admin has the books open as in if a dba comes in and says i'm having a problem with vmware performance uh sand performance my the number of cores that i have a great sysadmin goes we'll sit down next to me well not not in the day and age of corona but you know virtually okay so what do you want to know let me let's go in and look at the management tools i'll show you anything that you want to know i don't have anything to hide if i'm doing something wrong i want to learn about it you know i want to improve my skills sharpen my knives so let's look through together and see what we find out a terrible sad man when the dba is having a problem is like i can't show you anything no no i'll get back to you in a few months and you know what they're doing is they're trying to google to cover their rear dude like us as database administrators we suck too we suck at database administration we don't know what we're doing and if a dba tells you that they do they're lying i don't know what i'm doing i'll be the first one to tell you that half the time when y'all are asking me questions i would be like okay let's go google for that together okay here's the answer now go with god next up adam asked isn't everybody working off-premises these days why can't data be stored off-premises i'll give you a great one from a client that i was recently working with they had a manufacturing floor so they had a big plant and they had all these machines on the factory floor that were pushing data directly into sql server and then their other manufacturing machines would pull the data out and use that to make decisions for example if you're assembling keyboards every time you get a shipment a new pallet of parts every time a keyboard moves from one place to another in the assembly line who it's going to be going off to and so forth so the client that i was working with they couldn't go to the cloud because of the amount of data they were sending and receiving from their on-premises manufacturing machines the latency wasn't as big of a deal it was the costs when you push and pull data back and forth out of the cloud you pay for that by the amount of data that you push and pull back so that's a great classic example someone who simply couldn't afford to go to the cloud today so much more convenient to have it all on premises farshid asks brent do you support the idea of turning hyper threading off um i mean for highly concurrent applications that open many connections no no generally speaking hyper threading is pretty good there's one exception which is if you need the cpu cache if your application is highly dependent on how much cpu cache you have just because you turn on hyper threading doesn't give you twice as much cpu cache your cache is effectively cut in half per core so it can backfire under very specific workloads but these days i'll turn it on by default and just rock and roll especially since you don't pay twice the licensing as long as you're licensing at the host level so go for it the number of what people say for many connections if you're talking like 10 20 000 sure but before that not quite so much uh next up uh cultivar says mongodb is the future guys okay so a lot this always provokes angry reactions from database administrators who are like call me when you can join two tables together call me when i can run power bi against it or whatever it is the future for value is going to sound like i'm being sarcastic but i'm not it's the future for data that you don't care that passionately about when you don't really care about consistency levels relational keys triggers things like that it's fine if all you're doing is dumping date keys keys and values in and then you're pushing in a key and pulling back a value mongodb is totally fine it's totally okay there's nothing wrong with that i am a huge fan of other data platforms to store data that really shouldn't have ever been in sql server in the first place sql server is not expensive it it's true it's not like investment banks are known for their good decisions anyway right let's see what's up uh let's see here go through there were a couple of other questions oh i think i've hit them all um oh um so atrionx says since we're on twitch don't have a schedule just try to be live as much as possible and this isn't a question but i just wanted to point it out because it's kind of fun to share so the whole reason that i stream isn't that i'm trying to build up and become the next ninja you know it's not like i'm trying to go uh be uh you know massively famous on twitch because i don't make any money on twitch you know i make money on sponsorships which i'll talk about here in a second but there's a stand-up comedy yeah kind of but it uh i just do it really to give back to y'all to help answer questions and answers because my time between 6 a.m and 8 a.m pacific on the weekend is kind of open anyway so but if i wanted to be popular i would do it during more mainstream weekday hours i work i work then i have a job you probably know what that's like because you have a job too um and then also in here uh bosco says i have devs constantly asking me why we can't look at nosql options for our databases i've given the talk about transactional performance etc what's your take what's your take on this i love nosql options for databases and we use them i'll give you a great example so if you go to paste the plan pastetheplan.com or if you just search for paste the plan this is where you can pass uh execution plans into here so let's go do one real quick i'll just say select star from sis databases i'll go get an execution plan and then i'm going to right click in here i'm going to say show me the execution plan if i wanted to save this with a friend i'd copy it go over to paste the plan paste it and hit submit now i get a url that i can share with watch it not work which would be really funny i get a url that i can share with other people and then i get a visualization of my query plan right here inside the browser so what this is useful for is if you need to share an execution plan for tuning queries with someone else i don't this doesn't need to be in a relational database there's no need for that all we have is a key which is that key right there off of the url and then a value which is the xml contents of the execution plan so i'm totally okay with not using a relational database for that it just doesn't make sense so i'm a huge fan if your developers are asking to use nosql and be like sure what kind of thing do you want to use it for and recognize that if they're just storing a key and a value and then fetching back the key no sql it's totally legit i'm never going to query across this data i'm not going to use it for reporting i don't have a centralized dashboard that lists out all of the query plans and lets people pick if you don't have that key you ain't getting the value back it's just that simple i don't really care if we lose data you heard me i don't care if we lose your execution plans life goes on i mean i've got backups set up richie's got backups in there uh set up for uh our dynamodb and whatnot but it's not like i need every minute to consistency on that so it's kind of neat uh so shout out to this week's sponsor so this week's sponsor is column score if you've got a database and tables where you're thinking about implementing column store indexes you can go over to columnscore.com and take a seven question quiz to figure out whether or not columnstore indexes and sql server make sense for you so that's columnscore.com will give you your column score on what percentage likely it is that your table will do well inside sql server so let's see here what else we got inside the queue let's go pull some of those onto come copy some of this stuff into here uh copy that and do all right there we go now let's get the youtube stuff off from db wizardry looks like he has one or she let's see from youtube copy that over and then we'll copy this over copy from youtube paste and then come out over there and copy and paste from youtube paste there we go all right we got a few in there and now let's go back to the next one so let's see here for the next one let's pick uh what keys says is the xp command shell disabled by default for it's always been disabled uh you have to enable it if you want it says that command is a huge problem in the context of sql injections no not really because anybody who gets cis admin permissions they can turn it on anyway so if somebody gets to the point where they can run xp command shell they've probably also gotten to the point where they can turn xp command shell off and on so jesus says can you please tell when we compress a table when when do we compress a table so if you want to do compression i would look at column store indexes instead because sql service page and row compression just isn't really all that good compared to column store indexes column store indexes are much better that cranky older gentleman is absolutely correct there next up k the blade runner says nice selection for an avatar picture i currently work on a windows app with sql server as a database do you recommend using stored procedures or queries in code i have a blog post about that i'll show you so i'm going to search for stored procedures uh in app code sitebrandozar.com because i can't remember what the exact title is that's it right there should we use stored procedures or queries built into the app and what it generally comes down to is are you better at version control in the database or are you better at version control in the app and when you have a performance emergency would you rather just have a database expert tweak a stored procedure and go live or would you rather recompile your app and ship a new version of the app i'll give you an example worked with a website for a football team very popular football team in the united states and as they started winning more games they started running into problems where the database server would fall over because every time they'd win a game everyone wants to buy a jersey they can't buy jerseys if the websites don't now thankfully all of their database code was in stored procedures so i was able to identify the one stored procedure make a quick change to it hit execute and immediately their website stayed up they could sell millions of dollars of jerseys in a weekend if we had to had had that exact same data logic inside the app we would have had to change the app redeploy it push it out across all their web servers and if we would have even been able to do that which we weren't because they were in the midst of building a new version of their app even if we were able to do that would have been hours worth of work in order to get there i was able to get there in like 10 minutes tops next up let's go see what we got next uh nicole let's copy paste that out put that into the log from youtube put that over over there the says does it make sense to store and bearcare max in a separate table uh that's more of a data modeling question and i don't usually get involved in data modeling just because people have already broken their applications by the time they get me involved go get a book on data modeling instead eric says best approach to feed raw data into sql server generally whatever app your team is the most comfortable with c sharp java ssis azure data factory whatever you've got the most people to support because once you start feeding raw data into sql server you're going to be working a lot on that business logic you want to choose a tool that you're very comfortable with if you're not comfortable with any tools at all then that's where you start masking a bigger architectural type decision uh let's see here copy paste a couple of things into there um that goes in that goes in that goes in and that goes in all right so let's see here geos asks a bi colleague of mine loads fact tables as heaps and instead of bothering to understand just transforms the heap into clustered column store how bad is that kind of approach that's actually microsoft's fast track data warehouse reference architecture design they actually want you to use clustered column store inside data warehouses it's not that bad at all it's actually very common so what you might be you might not have gone to any data warehouse training in the last i'm going to say six years because this technique has become the de facto standard across the last six years as column store became better adam nails it calm stores are great if you don't update your data there next up from youtube a friend of mine asks why clippy decide to put the inequality column um and so that i cover that in my fundamentals of index tuning class watch my fundamentals of index tuning class and i go there a middle says and i'm going to put that up there because we're talking about compression a middle says i use page compression in a lot of places and it's a godsend really helps my performance and speed up any reads from disc we have tables from 100 million to 1.5 billion roads rose and i swear by it it's worth the cpu hit so often though when i run into folks you they're still not happy with query performance ask yourself the honest question of are users happy or are they tolerating it like performance used to really suck and then you put in page compression and it hardly sucks at all and your users are like golf clap okay great well you and they're trying to make you feel better by saying it hardly sucks at all but it's still nowhere near the level of performance that you get with real compression which is like 70 80 90 percent that's what column store index does next up uh from youtube any advice on choosing data warehouse gear between azure sql db and column store analysis services there are so many choices it changes continuously it changes all the time based on what different vendors are shipping what uh what your executives are choosing to do in terms of which cloud platform you go on so what i'll usually say is before we choose a tool let's talk to executives and which cloud are we going to be in for the next five to ten years are we going to be in microsoft's amazons google's or someone else's you know you tell me which cloud we're standardizing on and then based on that that influences which choice you end up using so that that just is your first part in ruling out those then the second thing that i would ask is for the the cloud options on the for the data warehouse options on the cloud vendor you went with look for a road map that's like two three four years long for the products that are out there for example if you decide on the azure cloud you go okay so let's look at the road map for the next several years for each of the data warehouse products that they have out there for example if you look at microsoft you may also want to look in the rearview mirror because i swear to god they've changed the product names every six weeks it's like today it's name is hermes okay tomorrow his name is sheila you know it's just insane how they're just squirrel continuously so i want to pick something that's kind of stable that i know i'm going to be able to bet my business on for the next several years and i'm talking crap about azure's uh synapse analytics pamela or whatever it is that they're calling it these days the technology is really awesome i just wish that they would pick a plan and kind of stick with it geoserum says tabular uses vertipack engine that compresses data per column correct that's column store indexes yes absolutely let's copy paste a couple more questions over there from youtube and keep going there from youtube the same thing that boss goes the same thing with the documentation too as well gets it a little tricky all right copy these into here and do here and here and anything else i think that goes there we go all right so next up from youtube i work in a managed service but i'm limited as to what i can learn because i limited to what i can learn as i always require customer approval and cost would you say that working for an actual company is better every so i'm going to give you the instead of that i'm going to answer a different question because it'll help you understand what your question is what's better working for a small company or a big company in a big company you have to go through five layers of approval to get anything that you want you got to get approval from phyllis and finance and bob and sales and your three middle level managers you got to get all this approval the company moves really slow but as a result when you adopt technology the technology's been out for like 40 years nothing changes you see everything coming a mile in advance it's really stable sure you might get laid off at any given moment in time you know that's always true anywhere but it tends to be a lot more stable and you see things coming down the pike you also have a lot of co-workers to learn from you're not on call 24 7. you're in a rotation with other people you have senior people and junior people on the flip side on a small company you may be the only data person and you may wear other hats as well you may be able to walk into the company owner's office and tell them you need five thousand dollars for whatever and you might be able to get it just based on your own personal credibility there's not a lot of red tape in small companies so it's the same kind of thing with whatever industry you pick it's more about what you like to do personally i know that i can never work for another big company again i hate big companies i hate bureaucracy if i go to work as a as a database administrator again it would be for a very small startup where i can kind of rule with an iron fist where i can know everybody inside the company cti geek says worked at a fortune 500 company worked for a startup now work worked for a sweet spot and it works for you for me a 5 000 company employee sucks or a 5000 employee company sucks too but that's just me it's all all of us are different in what we like i'm the kind of person who likes to now these days work for myself and i pity the person who tries to hire me after them next up from youtube can we use t-sql programming for string matching and pattern matching you can but sql server at two thousand dollars a core for standard edition is a really crappy way to do that and absolutely you can use reg regex with a clr but if you're going to do dot net code why would you spend two thousand dollars a core to that's like saying well i would like to use dot net code but i'm gonna run it on my phone that way because it's sql server licensing i'm gonna pay eight thousand dollars worth of licensing for my phone it just doesn't it doesn't make sense to pay that kind of licensing to run clr code so if you have application needs do that in an application if you just need to store data that's a database the base for your data but if you want to do things with the data like shred strings or build xml or html or css or ajax or whatever that's that's the application server next up malek says any good resources for high availability and disaster recovery so the problem is that h a and dr options keep changing constantly every new version of sql server they're bringing out new options they're changing the way the old options work and what i always tell clients is every version that you go to you should assume that all the ha and dr stuff is deprecated start again with every version that you go to because of that there's no real roi in writing training material for it so you're not going to see a lot of good resources for high availability and disaster recovery that are on current versions it's easy to find stuff that's like three four five versions back because they paid off long enough but these did modern ones it's not outside of books online that you really want to start by i know this is to sound weird but reading the documentation oh my god i know uh let's see here next up we have not the baby here looking for says we use microsoft division uh so no code changes are possible and it keeps getting deadlocks between two tables is there anything that can be done without code alterations yes index tuning and changing the uh solid state drives for your tempdb tempdb needs to be extremely fast with navision because if i remember right it uses rcsi or snapshot isolation which keeps all of the changes from the versions inside tempdb i've seen situations where people were trying to run nivision's tempdb on like hard drives still and they're so slow that the version store was causing deadlocks so between the tempdb and the indexing on the tables those are the two big shots for nav sp help dba says how can we correlate queries or statements from a batch or stored procedures easily from the sql cache plan sp blitzcash so check out sp blitzcash that is a stored procedure it's totally open source a part of our first responder kit and i teach you how to use it in our how i use the first responder kit training classes too ernesto asks what's the best approach to deal with date and time data when you say deal with be more specific like i need to know what it is that you're trying to do because i don't know if you're filtering on stuff what what specifically is the problem that you're trying to solve when you're saying deal with i don't know if you just mean you're looking for liquor recommendations or if you want a therapist or what it is that you want but tell me what you're trying to accomplish and we'll go from there cti geek says does your keyboard blink and flash like that all the time no i actually only turn it on for the webcast there's a button you can just turn the animations on and off with but some people aren't that bright who watch the webcast they need something flashing to distract them like a cat with a laser pointer like you i suppose because you're the one who noticed that it's interesting isn't it uh next up cti geek labs uh let's see here far sheed says when do you conclude that there is no room for database performance tuning for example the issue is hardware limits in terms of memory io etc do tpc benchmarks help with us in that regard no because tbc benchmarks are somebody else's code so they don't really have anything to do with the code that your users are trying to run those things are pretty useless um when i conclude that there's no room for a database performance tuning i'm not sure what you mean so the issue is hardware limits i'll give you a great example so one of my clients does one by one inserts inserts rows one at a time and they need to ingest millions of rows per minute like they're trying to pull in say two million rows per minute and they're inserting them one at a time and they insert one row wait to get the confirmation back from sql server before they insert the next row we'll in order to prove that hardware was the problem we tried for example taking the same amount of data and doing a batch insert in and i said look if we changed your code we could insert two million rows in 10 seconds it's not really that hard but because of the way that your code works putting in one row at a time we're having to deal with the latency on the log file and we showed how turning off delayed durability made that go away made their application go faster so it comes down to the specific bottleneck that we're trying to face and then trying to change the code in a way that it wouldn't hit that bottleneck and giving the client the choice between would you rather fix your code or would you rather go get faster hardware and i don't care what the answer is i'm fine either way because some companies love to throw people at a problem some companies love to throw hardware at a problem like you'll see one exhausted database administrator sweat pouring off her face but she's got 60 sql servers that all have two terabytes of ram you know so it's just on which way people like to spend money thiago you could you could you could maybe search for that there are sites called google or bing that you might use i support alternative lifestyles thiago you just got yourself a band so you are now blocked so you're out here all right next up let's copy paste a couple of the questions over from i know i rule with an iron fist from youtube zacky says i love your uh your uh sarcastic comments um let's see i it and stuff asked are there any sql server tool suggestions for everyday use i just use management studio that's uh usually fine for me bj says how is snowflake i haven't actually used it so you got me there so let's copy paste a couple others into here and youtube and paste that cultivar says did you really just ban thiago i banned his comments from showing up inside here which is i don't mind if he stays around in the channel but i just don't want all that crap coming up all the time on y'all's question feed um so let's go copy all i i have no tolerance for crap like that copy paste those in copy paste copy paste copy paste copy drop table employee says come for the database q a stay for the zings i agree there we go all right so copy all of these all right now let's start putting them in uh ganesh says have you ever done consultation for microsoft yes i was part of the team that built the last microsoft certified master exam for example after i passed it and then went in and did consultation for microsoft but you know what the funny thing was is that the billable rate wasn't that good that microsoft's billable rate wasn't as good as what i could get just working directly with the public so i stopped working for microsoft just because really at the end of the day i work to live i don't live to work i i want to work as crazy as it sounds i want to work as as little as possible i love what i do but i'd rather get drunk and read wikipedia and watch big brother i have really bad taste in television so uh so yeah so i don't do any consultant consultation for microsoft anymore just because the billable rates aren't that good honestly it's true for most large companies if you want to work for a big ginormous company generally the consulting rates aren't that good compared to what you can get working with startups you know who are facing an urgent problem so is the guy streaming at 6am absolutely yeah yeah because i'm going to be up anyway so i'm kind of like yeah uh from youtube where do i learn and get used to monitoring solutions this is going to sound like it makes sarcastic but seriously the vendor's documentation the vendors are the only ones who are going to write documentation for that no third party person is going to write a tutorial on how to use a ry in our spotlight it just wouldn't make sense all right so and zacky the leap says get drunk and we will read wikipedia uh are two phrases that i thought i'd never hear uh together um zack he says where's surly dev he's not in he's totally a volunteer so i'm like whenever he shows up i'm always wonderfully blessed all right so we'll stop here and take a five minute bio break we have a bunch of questions in the queue and when we come back i'll keep digging through the queue so five minute bio break go refill your espresso and i will see y'all back here shortly [Music] [Music] [Applause] [Music] [Applause] [Music] [Applause] so [Music] [Applause] [Music] [Applause] [Applause] [Music] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] [Applause] [Music] but [Music] bye [Applause] [Music] [Music] [Applause] [Music] all right welcome back the sun is starting to shine here in downtown san diego see what we got next here in terms of our question queue uh so next up geocerm says then what's the better approach to uh preparing a fact table with column store should i first load the data into heap and transform it into a column store or directly write the table as a column store it's actually neither if you want to get segment elimination what you need to do is load it put a clustered index based on the column that you want segment elimination then load the column store index from there actually teach you why and how to do it in my new class fundamentals of column store indexes so that's free and included with anyone who has a live class season pass and for those of you who have a recorded classes well excuse me recorded class season pass you can watch the recordings of it of the first class after i want to say it's like next tuesday or wednesday it's the very first one but it's such a good question and the answer it actually takes me as we go through the class it's like the fifth or sixth module by the time i get to that and then all of a sudden it understands you're like oh now i get what segment elimination is now i understand why partitioning is important and things like that uh raymond welcome to the club it's like rain man but different also any of you who want notifications whenever my streams start you can subscribe to me on the streaming platform you're looking at whether you're watching twitch youtube whatever next up geo server says sadly i'm from the eu yep yeah unfortunately i don't know of a good class over there ernesto says we need to have a report that orders by date time we use get date to read from the server but we show it in local time wherever the user is located what's your advice do the date conversion like converting it to local time on the web server or reporting server don't use sql server's expensive cpu cycles to do things like change date column formats just doesn't really make sense next up from youtube we have too many sql server users dumping data into excel with a sql server connection will it impact performance doing it that way or as opposed to pulling it from management studio what i would worry about is how much data are they really pulling exactly adam yes how much date are they how much data are they really pulling back is it millions of rows if it's millions of rows both excel and management studio are going to suck at pulling down that much data but if it's not millions of rows i don't really care it's not really that big of a deal next up ferner asks is tempty b a big bottleneck on sql server in your opinion you know it used to be it used to be a really big problem before people really widely understood that you need to create multiple tempdb files so that solved it for a while then the next bottleneck became everybody started loving using tempty b again the next big bottleneck hit and was fixed when sql server decided to keep stuff intended being ram rather than write it out quickly in one of the 2012 cumulative updates sql server 2012 microsoft realized that they could just keep stuff in memory as long as possible and only put it to push it to disk when we were running into memory problems so these days it's fairly unusual for me to see as the biggest bottleneck i'm just i'm just going to dump out the off the top of my head the top weight stats that we see from constant care usually bad parallelism cpu using queries and being unable to cache data from disk those are the three big issues that i usually see out there on uh uh column store all right on not on column store i'm still thinking com store from the other one or on sql servers out there now several of you are asking back and forth are doing troubleshooting on uh a two column date problem and i love that you're trying to hash it out here unfortunately what you're pursuing is not the right answer now the tough thing is for me to get you the right answer i'm going to post that question because i think i have it yeah right here hey um any tips on how to to more efficiently locate records that are between two different dates where the dates are in two different columns so like a start date and an end date sucks and we talk about it a little in fundamentals of query tuning the fundamentals of query tuning class i dig into that but it is genuinely hard work uh abuse this admin let's not uh give folks as to how tips on how to get out of uh gdpr issues i appreciate your understanding on that one i'd really like to keep my business and lot not lose my business to some buttholes from the eu and i don't mean that by y'all i mean the government zacky says what do you think about using databases like dynamodb to cache data dynamo is a really crappy caching tool because it has to be persisted to disk if you want to use as a cache check out redis redis redis is a much better caching solution than dynamodb dynamo dynamo is better if you need to persist the data to disk permanently redis is better if you don't mind walking away from the data if the redis cluster crashes you can configure redis in a way that it'll persist the stuff to disk just most people don't most people just use it so purely in memory caching solution atek says please share your thoughts on polybase and containers for a transactional environment that's just not the the problem that polybasin containers were designed to solve they're much more better suited for big data solutions where you have data scattered across all kinds of sources and you need to query them all into one place from time to time but transactional stuff is small inserts updates and deletes store my shopping cart pull my shopping cart out place an order see what the status of my order is these are very narrow short queries poly basin containers just get in the way they're not really related which is always kind of frustrating when i deal with microsoft marketing you know you see them up at uh on stage and they're like this feature is good for everyone try it today and i'm like uh no no no no i i get that you're just trying to get adoption for that feature but it just doesn't make any sense jan asks an interesting question jan says are there any tools like paste the plan for blocking deadlock graphs no i'm not aware of one where you can upload a deadlock graph and then render it and that isn't something that we would go and pursue because it's so much smaller of the population so i i would probably just paste it to like paste bin if you search for paste bin that's a place where you can just copy paste stuff in it's not going to render visually but next up from youtube what's the best way to design api endpoint calls to a stored procedure that does searching i don't do any development work at all like i don't do c-sharp java i got i'm kind of old the last development i did was around 2003 four five where i was playing around with classic vbscript makes it sound like it was ever any good was not uh c sharp java and i just haven't touched it since then because i realized i really hate debugging i have so much respect for developers developers are just unbelievable heroes in my eyes because they have to put up with so much debugging stuff all of a sudden my watch vibrated it's time to wash no it's not just because some of your questions are dirty that doesn't mean i need to wash my hands um so much respect for developers who have to deal with debugging things across platforms up in the cloud serverless type stuff it's just amazing how hard development work is and how easy database work is in comparison databases haven't really changed that much in the last 20 years sure they keep adding features and you can choose to whether you use those features or not but really at the end of the day it's the same language which makes your life pretty easy uh frisconi says in terms of licensing is sql server express viable for managing a company data mart known for a couple of reasons one is that express is limited to like 10 gigs or 50 gigs i can never remember 10 gigs or 50 gigs per database which usually isn't large enough for a data mart and two sql express is limited to just one cpu core and one gig of ram frisconi says 10. yeah so one cpu core and one gig of ram i don't know a lot of data mark queries that could run on less power than a phone has so i i'm not i'm not a big fan of sql express for that uh let's see here next up we had a couple of them coming from youtube let me copy paste those over into this from youtube and there we go and was that other oh there was another good one from leaving so copy that over and go there and let's go put those up in the chat so next up is oh i got to put them over here add this here and this and then here so from youtube uh do you advise customers to upgrade as soon as the new sql server version comes out not no but hell no when you go to a new version of something go because you have to go not because you want to go let other people find the bugs first the bugs in the first couple few cumulative updates are usually horrifying so out of the first adam says it's like buying a new car except i can't usually hold back because the first year is just utterly amazing but that's true um the the first couple of cumulative updates if i remember right out of the first three cumulative updates for sql server 2019 two of the three fixed corruption bugs so at this point i i i've been struggling about how i felt about recommending when people to move to sql server 2019 and as the cumulative updates have been coming out i have been getting less and less interested in going to sql server 2019 and for me the straw that broke the camel's back was cumulative update 7. i was complaining yeah it was more than last week was a few weeks ago but like i was complaining like crazy about the quality of cumulative updates microsoft is now writing like one line here's what the fix does and they're not documenting in depth and we can't uh yahoo let's not ask again let's not uh post second questions if you post this a third time i'm going to ban you from the channel thank you for understanding um i'll get to it just not there yet uh so uh when cu7 came out i had been complaining for a long time about stop just putting out one sentence sentence descriptions of what fixes are tell us what's broken so we can make decisions about what how quickly we need to patch this cu are the fixes in there relevant to what we're doing and i had been ranting and raving about it on sql serverupdates.com which is another site that i manage lists out all the updates and tells you which ones to apply and after cu 7 cu7 they actually pull back and they said attention everyone who's using it we need you to uninstall that right away and a bunch of us are like why what's it do what's the bug that we need to watch out for and it's not that we don't want to uninstall it but we got to go to management and say excuse me everyone we need to take production down in order to i i don't know why we need to do this but microsoft won't tell me they just said that i should take a production outage immediately i got people who run hospitals i got people who run all kinds of things that are uh absolutely mission critical and they have to plan for outages like 21 30 days in advance so when they wouldn't even tell us what was broken inside of there but was so important that microsoft a global company would tell you to yank it from production immediately and uninstall it i'm like that's bs and then cumulative update 8 came out and they didn't tell us what was fixed they didn't tell us in the kb article that the stuff that was broken in seven that demanded an uninstall they didn't tell us that that was actually fixed or not and i had to post a blog post going excuse me microsoft can you at least say that what you had broken in cu7 is now fixed and they updated the cu 8 release notes so here's the deal i'm at the point where i'm trying to decide whether i post a blog post over on brentozar.com because there's like 100 000 subscribers on brandozar.com and i know when i post something up there the poop's going to hit the fan but i'm trying to decide when the point is where i post up there i'm sorry folks i'm done recommending sql server 2019 until microsoft gets gets its poop together if they can't tell us what they're breaking and fixing i don't recommend that you go to it because the risk just isn't worth it now i can say that here on the screen because there are 108 of y'all on twitch there are however many other else there are over in youtube and it's not like i'm doing it to 100 000 people y'all are kind of in the insider's club i kind of think of y'alls like my friends and neighbors and all that my co-workers really is the way i think about it um so i can kind of tell you this kind of jokingly in confidentiality i know right i get pissed i get pissed at like something that costs seven thousand dollars a cpu core and the note and the notes for a patch are like bug fixes and improvements oh sorry we need you to take down production to uninstall bug fixes and improvements what that's bs that's ridiculous i get fired if i try to stunt like that in any kind of modern company so i get really pissed off about that and i i'm getting to the point where i need to post a blog post on renozar.com but at the same time that i know when i do i'm going to start a firestorm because people think of me as really yeah adam says that's why i like light work in the bi space just reload the data if it gets corrupted and the other thing when the bi space is nobody really believes the numbers and the reports anyway the numbers could all be wrong the executives are making bad decisions regardless of what numbers you put up in front of them on the report it's not like somebody's going to die nobody's using power bi to dispense patient medications you know it's not like they're using radiological therapy based on what comes out of power bi i love power bi it's fantastic but it's not like people are using it for that purpose sql server they actually are and we're having incorrect results bugs that's why where i start to lose my mind uh so there's that question i answered the holy heck out of that one bob says never install cu 2. i agree with that as well so let's throw a couple of the other questions in there let's copy paste that in and there was one other oh no there were a couple other here we go josue had one from youtube tube paste that in and yeah asked a second time he's been wonderfully patient so i should probably he hasn't really been wonderfully patient but i should probably answer his one first uh so that good that caught up everything there on twit on copied it over to twitch and let's put these into the queue uh so we got all that all right perfect so where was your horse yeah was ch there we go um manny i teach that one in my classes if you go to fundamentals of index tuning fundamentals index tuning and mastering index tuning i teach that over in those classes um so yahoo said how do you think postgres stacks up to sql server relative to oltp tran uh scenarios i think it's really good i think most of what most people need is in postgres for transactional stuff now where it gets a little tricky is what's your compliance needs like do you need to audit i'll give you a great example the clients of mine who are hospitals if you want to be hipaa compliant what you need to do is you need to track everyone in your company and what they see like if someone runs a select query to look at george clooney's health care records you want to track which nurses and doctors saw george clooney's health records so that when someone sells them to tmz you know who did it and you can go track that down to fix your hipaa violations that kind of thing is more challenging with postgres because sql server has been around longer they have more enterprise features more compliance features more security features so it's easier to do those kinds of things with sql server than it is with postgres but if you're just talking about running a website that just sells stuff postgres does a pretty good job of that i'm pretty happy with that uh next up from youtube share your thoughts on indexing inline table valued functions you don't have to there's no difference between those in a regular t sql query it's the same exact indexing approach the best approach for partition switching for larger fact tables there's only one approach to partition switching it's the sliding window load scenario so that one is the one that you want uh next up uh from youtube what's a good performance tuning book you would recommend oh i have a whole article with my list of favorite links i'll give them to you so let's go to brentozar.com go slash books if i go to brentozar.com go slash books b-o-o-k-s it's got my recommended list of sql server books and i update this usually like once a year to give you a rough idea of what my favorite books are uh there and then i've got links to amazon other places where you can go buy them so that's brentozar.com go slash books let's see here uh let's see oh al getty has an interesting question let me copy paste a couple of those out there from youtube there's that uh and then copy and from youtube boom ask put those over into the queue and then we'll answer a couple more here put those in a middle says can you speak to those that hear what i say about sql server 2019 and point to the compatibility level as a workaround the problem is the compatibility level doesn't fix bugs in the engine remember how i said that two out of the three first cumulative updates fixed corruption bugs didn't matter what your cumulative update was you get corruption you're getting corruption has nothing to do with compat level some not interesting in there oh funny surly dev oh welcome to the club a geocacher undo a chinese puzzle i think geocaching is so interesting i don't do it it's i don't like going outside but i think it's really interesting in terms of uh uh that whole as a hobby uh thiago says oh then that's early devo nailed that thank you sirleydev also i should say a warm round of applause for what most viewers didn't catch there is that surly dev just showed up who's our question moderator here at uh the office hours [Applause] thiago says what do you think about secret clearances for dbas if you're going to work in government field it helps you get more jobs if you're going to work in as a contractor for in the washington dc area in the virginia area then it can help you get a lot more jobs faster if you just as a as a general personal advice if you ever get the chance to get secret uh or if you should get any kind of uh consulting or any kind of government secrecy clearance you should probably get it go for it because it will open up additional options to you for your career that you didn't have before people have lower standards for taking people into secret clearance jobs if you have the secret clearance you're more likely to get a job that you're not even qualified for which is kind of cool and then from youtube this is kind of interesting what advice would you say to brent of the past to brent of the present about learning sql i always have a tough time changing uh my career like i love where i'm at today i'm ridiculously lucky i work less than anybody i know feels like i know it looks like to y'all i work a lot because i show up here at odd hours but i do not work that hard um the so i would be and i'm successful i'm happy i'm fulfilled i love what i do i'm having a killer time i don't feel like i would go through a midlife crisis or anything like that because i'm just having way too good of a time i feel like i'm a child again already half the time real drunk and gray-haired child um but what would i tell my past self one thing that i would tell myself is i wouldn't waste time going deep on a vmware aws backups check db the things that you do to keep sql servers lights on the things to keep it up and running because being on call and being good at being on call i don't find fulfilling i would rather not be on call i would rather not have my phone ring i like being drunk we've talked about that before i'm not an alcoholic by any means but i do come from a family of alcohol i really come from two families of alcoholics on both sides of my tree but i feel like i have it under control i'm not i joke a lot about quarantine alcoholics or whatever but i don't want to be on call i want to be able to disconnect and not have to look at my phone so i think i wish i would have had that realization earlier in my career don't get good at troubleshooting up time get good at something else that pays you a lot it i didn't go down this route but another route if you were thinking about it business intelligence architecture uh uh application architecture uh performance tuning of course is the route that i chose going back that that's adam's absolutely true i've always he says i've found in my work that business understanding has helped me gain more in giving users what they want than in the technical knowledge i often tell people that don't worry about learning more tech if you think that's holding you back you have google for that you can get google you can go to youtube you can learn almost anything one of my favorite examples is grimes the musical artist claire boucher i think her last name is but she's taught herself everything that she knows about production and musical instruments and playing and all that just through youtube through through free videos you want to learn more about your audience what it is that your business users want that's the thing that helps you succeed rather than the technical stuff the technical stuff you can do just in time learning from all over the place when you need to learn it so there you go uh tg says you will also want to comp tia security plus to get dod jobs and what i would say is if you're in virginia and the dc area those are the kinds of things that matter yes elon musk's wife just very disappointed when that happened because i'm like no he's not good for you he's it doesn't make any sense he's just uh it's a terrible pairing but and then it's later i'm like actually they're actually not a bad pairing they're actually kind of crazy in the right kind of ways so i kind of like it uh from youtube we're on sql server 2016 with production does it make sense to start upgrading to 2017 or is 2016 fine for now change equals risk whenever you change something you are risking you're risking that things are actually going to go right and you're also taking your time to do it so the only time that i would go is when you're having a problem when you're having a problem in the solution it involves sql server 2017. otherwise 2017 or 2016 is still under mainstream support works just fine it's totally okay uh my friend asks from over on youtube uh samani says what's the difference between predicates and seek predicates oh i forgot i we uh we covered that one in i said go hit my fundamentals of uh index tuning class for that one there we go uh zacky says i have a friend who works in lockheed martin and he had to go through a ton of background checks for working with the air force i've been offered contracts like that too and you know as soon as they start um uh going through and saying hey brent we're gonna need you to do all the security type clearance kind of work i'm like okay and so what's the hourly rate again the hourly rate sucks yeah i'm not really interested in that but for those of you who who where that does make sense then sure by all means go for it but it is really hard my wife went through that same kind of thing to become an air traffic controller and all kinds of stuff in the background i have a dirty past but whatever let's see here uh juwon on youtube says what are some of the cool integrations you see with using python and sql server oh i'm going to get myself in trouble here i'm gonna get myself in very big trouble i think it doesn't make sense to spend two to seven thousand dollars a core to run python in sql server so the people i see that are doing cool things with python in sql server every time i've seen them do it i've been like have you scaled that up to production levels and how much did that cost and i get one of two answers no we didn't scale it up it's only a proof of concept or two my licensing was free meaning they didn't pay for it they're either using developer edition or they're kind of skating by rules so i haven't seen cool integrations that have scaled up and paid the actual price for sql server licensing i believe they're out there i believe somebody's doing something where the the company just was like screw it have a forklift full of money but otherwise i'm like if you're gonna do python work it's open source just go run it somewhere that that doesn't charge a ton of money uh uh sebastian try pricing that out go run go the pricing on it and go see how that works uh next um feiker says a friend found a suspect page on one of my user databases from msdb after running checkdb no found or open a support call any time that you get a corruption event immediately open a support call don't waste one minute of your life get on the phone to immediately open a support call and i have a blog post about that actually too if you go to brentozar.com go slash corrupt so brentozar.com go slash corrupt i have a post that tells you what to do when checkdb reports corruption and it walks you through the exact same checklist that i do with my own clients just so that you can see next up let's come back over here and see what we've got next nazar says what are your thoughts on running sql server on linux is it good enough for production let me show you something let's go see so let's go over and check what's the latest update for sql server the latest update for sql server 2019 is cumulative update 8. so cumulative update 8 let's go search for linux on here and it says in here there are no sql server updates in here for twist cu8 how to obtain this thing for linux get cumulative update eight i wonder why that is uh i am not sure that i would really invest a lot when it's not even production equal yet it doesn't have the same features it's not getting the same level of attention the day that cumulative update 8 even came out it was like cu 8 for linux will be out at a future time like i don't really want to be a second class citizen here adam says the linux thing always seemed like a marketing gimmick for me it feels like it today it's more of a just telling oracle people hey if you don't like windows and you want to migrate to sql server you could run it on linux too you'd kind of have to be an idiot to do that though the troubleshooting resources aren't there the documentation sucks that's kind of a tough call there uh ibex says can you please advise web resources where we can practice our t sql and performance skills with real life scenarios absolutely my mastering classes so in my mastering classes i give you a live running workload with live queries running against the stack overflow database and it's your responsibility to figure out the root cause of the bottleneck and tune it then you get to watch me do the same exact thing so that's my mastering classes mastering index tuning mastering query tuning mastering parameter sniffing and mastering server tuning next up ajit says please suggest software for which checks database objects are correct syntactically i don't even know what you mean maybe rephrase that question i'm not sure what you mean correct syntactically um let's see simon says [Music] simon says simon you probably get that all the time and i just got it just by accident i didn't think it was funny until i said it out loud and then i was like oh jeez i just said simon says [Music] uh says sorry for asking can i see the weight stats occurring during a specific query execution rather than a session level sure extended events at uh with extended events you can set up a session that will finish when a session completes and tell you like request by request which weight stats it had another thing that you could do is you could look at the queries actual execution plan on recent versions of sql server and the actual execution plan will show you weight stats the problem is i don't really believe that they're accurate they're kind of close to accurate but not perfectly accurate gets you kind of close though bob the lobster says from over in the uk bob says you could run linux oracle on windows but why two wrongs don't make a right that's a terrible thing and i shouldn't say that uh adam says i'm moving to 2019 but only for the analysis services stuff that's where moving up in terms of versions makes a big difference i think there are lots of features in sql server 2019 that i love it's just that the quality control appears to have been done by a thousand monkeys bearing hammers that they were just randomly bashing around on accept buttons not really paying attention to where the bugs were now i'm a huge fan of where it's going i'm just not really that big of a fan of all the bugs that we're seeing uh in there but like i was when 2019 dropped i was more excited about 2019 that i had been for a version probably since 2012 20 2012 was awesome in terms of huge jumps up 2016. awesome i don't think quite as awesome as 2012 was but it was awesome as well 2014. give a rip 2017. i don't really care that much either 2019 i was like they're all in again but then just like oh my god the quality control is just terrible um [Music] tank says what's your baseline for sql server versions with respect to only upgrading when you need to um mainstream support so for me if you can't get support for microsoft and you're running production things that like matter to people's lives you know you're dispensing medication you're dealing with someone's uh stock portfolio you need to be in a situation where you can call microsoft for support me as a consultant i won't take calls for things that aren't that microsoft won't take calls for so my policy is if microsoft can't support it i don't really feel good trying to support it either so and i'll tell clients that and they'll be like well will you help us get to a supported version i'm like no i'll refer you to another consultant and have you go through that work with them i just won't risk my personal time on something that's unsupported just that's about personal preference uh next up zachy says zac he's just asking the question for me from stack exchange very funny i like that so um the only one that the only resource that i found was the one that's actually in my question about trace flag 2549 but i love that you asked that same question that was kind of funny uh avashek says we're on sql server 2012 and my client is not to open sas cubes on an excel spreadsheet what could it be the reason um i've no idea i actually don't use sas i do use excel but i don't use analysis services at all your best bet there would be to ask it on a q a site like dba.stackexchange.com it's not that i have anything against analysis services i just pick my battles on which tools that i want to use there also just as a side note just in terms of professional advice never ever ever ever ever ever ever ask someone for help and say my client if you can avoid it like sometimes i will just because everybody knows that i i'm a consultant and i only have clients but if you just say that i am unable to do that and then that way people who are consultants may be more likely to give you help because sometimes consultants are like i'm not going to solve your problem for free which i wouldn't blame people for not answering my questions either also i tend to ask really wacko hard questions just because i know how to google first that's all it's not like i'm smart dennis says what's a normal cpu on sql server and when is overloaded i'll give you a great exam example from stack overflow so if you follow taran pivots on twitter taren pivots is a sql server's dba our sql server is stackoverflow's dba and she's brilliant i have so much respect for her so taryn will says time to wash again it's not that's not that bad of a question um taran is stack overflows dbh she'll post graphs from time to time on stack overflow sql servers stack overflow tends to run around five percent five to ten percent cpu utilization because if their caching tier falls over the sql server has to be able to survive their workload even when the caching tier falls over so for them on day-to-day needs if it goes beyond like five ten percent it's probably time to upgrade the sql server now that's an extreme case but i'll give you another one i get another one that only has one busy day per year just one busy day per year and that's it and for a few reasons they can't run in the cloud so for them they run one percent cpu all year long but then on that one day we run you know 60 70 80 cpu so we don't mind going to a 64 80 core sql server sits around all year idle and then on that one day a year it actually gets used so that's why you don't usually see hard and fast numbers out there tde is actually available on standard edition these days so you want to get to 2019 so you get in there and then next up we have hernan from over on youtube says what's the most efficient way to update a number of rows with a stored procedure do i call a stored procedure for each row updated or pass all the values to be updated in via json or xml so what i'll do is i'm going to zoom this out a little bit and i'm going to say how do i know which query runs faster if you because what you'll want to do is build two versions of it so that you can see and learn for yourself you build both versions of it and then you compare the execution plans if you want to learn about that process i've got several free videos on it so if you google for watch brent tune queries if you google for watch brent tune queries i've got several videos where i walk you through the process of comparing two execution plans to figure out which one is better and how you want to go about tuning them so go google for watch brent tune queries and you'll learn about doing that process so why am i sending you there because there are times where one process is better there are times where the other approach is better it's going to depend on your workloads your tables the amounts of indexes on your tables all kinds of stuff like that so i'd rather you find out the process for doing it rather than you getting a bad answer from some yoyo who happens to just have a live stream next up let's see here um not the dba you're looking for says isn't there a stack overflow page where you get queries that need tuning for the stack overflow database yes it's called stack exchange data explorer stack exchange data explorer next up let's see here let's copy a couple of things over into the queue uh let's see here farshide says some businesses are based on windows or biased on windows security and forcing dbas to move to sql on linux as one case i had okay cool then next cti says my friend touched on this or you touched on this topic yesterday we have a ton of jobs running from agent i'm thinking about changing it all to run off a separate server via task scheduler and powershell where i kept keep database backups on sql server agent jobs this comes back to my change equals risk so what i when someone wants to go do something what i'd say is let's sit with your business go run sp blitz and go look at the health issues on your sql server today to prioritize what you need to fix because otherwise i'm worried that we might be just changing something for the sake of changing it you only get so many hours a day like i use this a lot during my mastering classes i'll give myself 30 minutes in order to go work on something and i'm like at the end of this 30 minutes people have to have a noticeable difference in whatever it is that i did i don't want to be just like shining my keyboard or getting tuning my keyboard lighting or something you know it's got to be something that people notice the difference when i'm done so just before you go down that rabbit hole probably go ask the business what's your biggest problem today ah let's see here now le ignacio says what do you think is the difference between a basic sql user with an intermediate one um i don't really have a good answer to that um i don't really have a good answer to that uh and i i don't know that that i'd really care either it sounds kind of goofy but um if if someone let me rephrase your question ask how do i get better at sequel like how do i know that i'm getting better at sequel read a book by itsic bengal i'll show you so if you go to uh amazon oh it's actually on my books page too if you go to brentozar.com go slash books if you go to brentozar.com go slash books i've got my recommended list of sql server books and these are a few books that you could read from its benghan that will help your t sql level up these are absolutely phenomenal these are not easy these are very hard books and they'll help you understand whether you're really getting better at sql or not all right coming down the home stretch got a couple of questions left before we call it a day sebastian says your thoughts on the future of sql um my thoughts on the future of sql it's great it's fantastic um of course i say that because i'm making a lot of money on it you know it's if i okay so here that's probably a good way to say it if i thought it was dead if i thought that sql server was dead you would see me moving off of it but i don't i have a lot of work doing it from the stage when you hear folks like microsoft talking it's all azure all the time because that's where they're going to make money at if they can get you to azure where they get you to pay by the cpu cycle and they get you to pay by the month and you're locked into it you can't go anywhere else you if you listen to their presentations but everyone's moving to azure it's incredible it helps you do all your things we're not going to talk about our monthly bills here we're just going to talk about how empowered we all are so it would sound like everything's happening over in azure that sql server stuff that's predictable in cost and cheap don't pay attention to that the real action's over here please hand your wallet in at the door you know so i i get like you know it's not sql server isn't as dead as you would think it to be uh and then last up amen says any idea on how to use gpus for a sql server workload so no the postgres community has done some work on that in trying to get postgres to use gpus but the thing is sql server is licensed by the cpu so because they're licensed by the cpu they put all their work into cpu they haven't really put any work into gpu even though there are some workloads that you would think would be better off in terms of gpus if they did start putting that in they would have to do all kinds of crazy work and testing and most sql servers just don't have gpus and won't you know most cloud vms have exactly yeah imagine licensing a graphics core with a graphics card with a couple thousand cores just the licensing doesn't start to make any sense so you won't see sql server going down that route anytime soon if you want to do that if you want to use the same hardware that you play fortnite on and also run a database server on it go check out postgres and i don't say that as bad but it's wonderful uh gb just last question do you work with eric no he used to be my employee but then i decided to stop having consulting employees because i don't really like having consulting employees it's a whole lot of work um so i sent him off and he goes and has his own consulting company now eric darling data all right well thanks a lot everyone for hanging out with me today thanks to surly dev2 as well for doing the moderation there on the questions uh thanks everyone for hanging out and i will see y'all next week i'm off downstairs to go get myself some locks and bagel adios [Music] you

Channel: Brent Ozar Unlimited

Views: 2,596

Rating: 4.8356166 out of 5

Keywords:

Id: tk-dRz7sJ-o

Length: 116min 50sec (7010 seconds)

Published: Sun Oct 11 2020