Office Hours: Ask Me Anything About SQL Server in the Reykjavik Harbor

good morning party people i am coming to you from the reykjavik harbor which is near my home base in reykjavik that we've used for the last couple of months a lot of whale watching tours go out of here commercial fishing ships so it's just a nice fun place to go and see the sun rise and just hang out so uh this morning let's go tackle some of your questions let's see number one is mehdi the most highest voted question is medi says hi brent what's the first step when sql server is overloaded we can still connect to the sql server by ssms okay so what overloaded means can be different to different people for me overloaded means it's no longer responsive like that you can't even connect to it but i think you might be if you're able to connect via ssms and queries are working my guess is you just mean it slow rather than it's overloaded so i teach you how to do this in my how i use the first responder kit class the first thing that i'll go and do is run sp who is active just to see which other queries are running right now i do that because sp who is active is extremely fast to respond second thing that i'll go and do is run sp blitz first and i show you what parameters i use with it during how i use the first responder kit class i know it sounds kind of funny to have a query called sp blitz first that isn't the first query that i go and run but the reason why is sp uh sp who is active just runs so quickly and tells me if we have the server not responding at all sp blitz first takes a five second sample of things like your weight statistics perfmon counter plan cash and during those five seconds it checks all kinds of things is a log file growing is there a long-running query that's blocking others are you experiencing any poison weights is there a backup running or a check db running checks all kinds of things that you and i just don't have the time to go and check inside of the span of five seconds and then at the end of those five seconds it gives you a prioritized list of reasons why we think the sql server is slow right now with urls for each of the warnings that's the place that i would start sp who is active then sp blitz first and it'll give you a quick rundown of what to look for and then for more details attend my how i use the first responder kit class next question comes from alan allen says do you think learning ssis is still worth it or should everyone hop onto aws i think you might have mixed up a couple of terms here ssis and aws don't really have anything to do with each other ssis is a tool that you use to move data aws is a cloud provider what i think you might have meant was should i learn ssis or azure data factory azure data factory is kind of microsoft's replacement for ssis it's not that ssis is going away i mean as far as i know but as your data factory makes sense if you're in azure if your company's decided to move to azure because etl you know extract transform load type stuff that load is inherently bursty it runs in very short bursts so you need it a whole bunch of it but only for a short period of time well it doesn't make sense to pay a lot of licensing for that and have it just sit idle most of the time that's where things like azure data factory come in because you just pay for what you use when you use it if your company's in azure i would check out azure data factory if your company's not in azure like if you went to amazon and you're looking for building etl tools i would look at whatever that cloud provider's native etl tool is that scales practically infinitely and just charges you for what you use in aws's case that would probably be aws glue there are lots of other ways you could roll your own etl though next up uh igor says hi brent in a recent office hours you had a question about changing the care data type column into varicare in your answer you mentioned that she correctly spotted that the correct that the care data type should not be used and that should be avoided in the future development why is that okay so a long time ago in a galaxy far far away people like mainframe developers would use character data types and they were a fixed length no matter how little or large you put in them today it's extremely rare to see people doing new development with fixed length strings it's much more often that they will have variable length strings like names or part numbers that will always change over time so it doesn't make sense to incur the overhead of a fixed width data type in most cases when the data isn't fixed width if your data truly is fixed width then sure use the care data type but it's probably not really fixed with they just think it will be and then tomorrow they're gonna tell you something different it's like man i've had so many situations where the businesses said oh yeah i swear on my honor that this column will always be unique and then of course what does it turn out 15 people share the same social security number i have the burn marks for that one next up 1440 by 1080. very funny asks hi brent is there a way to remove a completion time of just a specific query something like no count and not use the global settings in ssms not as far as i'm aware i would wonder what it is that you're trying to solve there why you want to do that if you're looking to track the completion time of a lot of queries don't rely on the messages output in ssms try logging things to a table there instead so then that way you can more easily rapidly compare between multiple passes if you're doing a lot of diagnostic tuning i'll often go create a table not a temp table because i want to make sure that i can use it across multiple sessions and i'll log diagnostic info into there steve asks what is the funniest mistake that you've ever seen a developer make that took down the whole database i was once this is steve still talking i was once writing a stored procedure to delete data from multiple tables and i put in a begin trans and i don't know how but i took down the database for a few hours for recovery instead of saying like what somebody else did i'm going to tell you the things that i did like my funniest memories from outages that i caused along the way one of the first outages that i can never remember was i was working as a systems administrator pretty much kind of fresh out of college and i don't want you to think that that means that i completed college i did not i dropped out three times but shortly after one of the times that i dropped out of college i was working for a photo studio and this photo studio did high school graduation photos every morning the photographers would come into the office and they would get printed out labels of all the shoots that they had to do that day and where they had to go and like stickers that they put on the film canisters i'm not going to explain what a film canister is well it was my job as a systems administrator to come in first thing in the morning and print those i did the same thing that all of you have either done eventually in your career before or you're going to do at some point in the future i ran a query without including the where clause and i managed to hose all of the students and appointments not just for that day but for all of them so i had to fix that obviously so i immediately panicked and at least because i was the systems administrator i knew that where the backup tapes were because i was involved with switching those out so i knew what i'd done that morning since the backup i just dropped everything that i was doing i restored everything from scratch and as the people started filtering into the office i said look i'm going to give it to you straight i hose to the database i'm restoring unfortunately you're going to have to wait around for your labels and and what you're going to be doing today because it's going to take a while to restore this stuff from tape i'm not going to explain what tape is take a while to restore all this stuff from tape now the owner of the company had a legendarily bad temper he would throw things he would fire people on the spot for stuff and so i was kind of rightfully nervous that he was going to freak out but when he came in and i'm talking to him about this i said look this is entirely my fault i'm the one who screwed this up i'll own it and you can fire me i would understand but don't fire me until the restores finish and i print out these labels because you all don't know how to do this and you're probably going to need to find my replacement i understand if you're going to fire me it's a fireable offense i get it but just wait well of course he knew that i i kind of he was kind of backed into a corner at that point goes and leaves and he never did fire me he's like later he's like people were like why didn't you fire brent and he's like well that's an example of taking ownership and accountability and i i still to this day think that if the restore had already finished when he came in i think the story probably would have been a little different so that's one of my favorites we'll uh stop with that one uh next oh there's one other one that i really loved uh i was uh sooner or later everybody in their career who works with sql server is going to try to use database mail and you're going to try to use it inappropriately so i was using it to email our customers about something that they had to go and do this wasn't at the photo studio this was much later in my life the i was emailing customers about something and i had a bug in the code and it just continuously kept sending emails overnight we crashed our exchange server because we filled up the drives without going email which was kind of good because it limited the blast radius of how many customers got repeated emails could have been so much worse if our mail server hadn't run out of drive space so it's kind of funny because everybody in the office is like why isn't email working like oh once we started getting to uh root cause analysis and the exchange admin was able to tell me that there were tens of thousands of outgoing mails from my sql server then i was like oh emails don't really belong in sql server next up alexi asks hi brent how it's funny how everybody says hybrid alexis says how hard would it be for microsoft to rename the master database in sql server to something else just as they did on github they renamed the master branch to main oh that's such a good question so here's the thing let's take the sa login sa is the hard-coded built-in systems administrator login it's included when you install sql server assuming that you use sql authentication over the years there have been a few best practices guides that would tell you to rename the essay account to something else because what this would do was it would head off problems of people who were trying to use password guessing scripts scripts that would just continuously rotate through passwords in an attempt to log in as sa eventually they would crack it and then they would get in and wreak all kinds of havoc that was the theory well in order to head that off microsoft decided to try or people decided to rename their sa accounts it was a best practice for a while going around some checklists still have that out there i don't bother doing that but uh what do you know it broke upgrade scripts sql server shipped or microsoft shipped several versions or cumulative updates and patches to sql server that would break if the sa account was renamed to something else and that's just a login let alone a database name you change the name of something like master and they're going to be all kinds of effects going through not just sql server but community tools out there third-party applications there are so many things that expect a database name to master okay now having said that if you go under the hood in azure sql db managed instances and in sql server 2019 there are hints that microsoft is working on replicating the master database to other places and as part of that work they have columns in there in sys databases that talk about another database name because if you replicate a master database to several other sql servers you can't do that because they can't share they can't have their database over their master database overwritten in flight so it's named under different names it is theoretically possible that at the same time that microsoft is working on copying the system databases they might also be working on a way to rename the system databases but even if they do i would still worry about any kind of third-party application or script and the ramifications would be so big out there i understand why folks want to do it and it's the same reason that i renamed our github repo the first responder kit we renamed our master branch over to maine i believe it was so i understand why they won it's just incredibly difficult for sql server next up uncompressed dba says hi brent if i use compression for some tables where the keys are ever increasing does that increase the odds to encounter page latch weights not page i o latch but page latch weights so before you go in and think about that the thing that i always ask is what's the problem that you're trying to solve generally speaking high insert tables don't feel like good candidates for compression to me it's possible that yours might be i would just want to know a lot more about the problem that you're trying to solve now could you run into problems with page latch weights due to high insert rates yes whether you have compression or not but generally i think about that in like the one to ten thousand inserts per second sustained range if you're doing one to ten thousand inserts per second sustained for hours at a time that's where i start to worry about things like latch weights but if you're talking about an insert per second or 100 inserts per second it's just not that big of a deal on modern hardware even laptop great hardware it's just really not that big of a deal next highest voted question a guy named jim asks hi brent do you feel that the option with encryption is the best remedy in an enterprise software context where leadership argues against stored procedures and functions because it means that our code is on the customer servers he says that's what we did before before the incident i guess and it was painful from a support perspective anytime somebody wants to use the with encryption option what i do is i go download i have them watch my screen and i go download there's a tool out there called sql decrypter and you can download a totally free test version you download it and it decrypts all of the code in seconds for those of you who prefer powershell i want to say dba tools has a command lit that also decrypts encrypted stored procedures and functions doesn't require knowing the password that the with encryption stuff is wildly insecure so if your goal is to hide the code from customers it's simply not going to work and i've used that knowledge to my advantage when a software vendor has said well sure our code is slow but you're not allowed to see it i'm like oh yeah watch this i've restored the database to another server pointed sql decrypter at it immediately showed them their code and said here's the deal it's your call if you want to keep encryption on i'm going to keep pulling your pants down in front of your client and showing them the code or if you'd like to work together instead of against each other go decrypt all these things so that monitoring tools will work at your customer and you'll know what's slow and that shock and surprise of how easy it is to decrypt this stuff has won them over every single time they're like you must be some kind of lead hacker no i know how to use google which apparently they do not next up let's see here torben asks schema lock select star into new table from old table will that lock the schema while the select is running if the okay so uh anytime that you have a question like that like will this lock something try it just do a begin tran and go see go create a fake table where you're going to do your work don't do this in production obviously but create a fake table with millions of rows in it and go see don't go to the internet and post your question in the vain hope that some stranger is going to take time out of their day to teach you something that you could figure out with a 10 second experiment go get up off your lazy rear and run the experiment doesn't take that long come on neil ah let's see here now now i got to go back my thumb accidentally scrolled so let's see here asian dba says that that's the name that they used asian dba says love your work brent thank you when would you choose read scale clusterless always on over traditional always-on availability groups so i wouldn't um if your goal is to have lots of readable replicas for reporting the problem with always-on availability groups is it's enterprise edition only so it ends up getting really really expensive for every replica that you query in that case i would use regular plain old availability groups just because if i'm going to have that many sql servers up and running i am going to try to have the ability to use some of them for failover at some point when all hell breaks loose the other thing that i would think about in there is that if you need to scale out to that many servers for reporting purposes you should probably check out replication too or log shipping one of the other solutions things that work with standard edition and can scale out to more replicas with less overhead whereas always on availability groups always adds overhead on the primary neck or to put it another way i don't know anybody who has chosen read only reed scale avail always on availability groups i'm sure there's like a dinosaur i say a dinosaur you know like loch ness monster out there somewhere in the harbor that's using it i've just never seen it personally uh zach says i have a follow up to my vericare max and veracare 2500 question as an aside the max usage in that one column is only a thousand characters is there a performance impact for including a varicare max as an included column in a covering index i'm basically trying to optimize for reports okay here's the thing the overhead is only based on the size of the data if you define a big old honking column and you don't put much data into it it doesn't have that much overhead now there are special rules around what you can do with the varicare max and envercaremax column things that change the way that they're handled as keys but since you said you're only doing it as includes i don't know what you're trying to optimize there it's not going to help you at all in any way shape or form to prove it to yourself go create another copy of the table with the design that you want like changing the size from varicare max to varicare 2500 or 1000 and run your reporting queries back to back with set statistics io on measure the differences you will be sadly disappointed doesn't that suck next up zach oh zach again zack asks is there a file size for a data file like an mdf file where you would consider adding more data files if so what's the best way to approach such attacks or a task and what's the benefit of doing so is an 18 gigabyte mdf file a bad thing 18 gigabyte zac my phone handles more data than that 18 gigabytes if someone gave you a 32 gigabyte usb drive you'd probably be like golf clap but i can buy one of those for 10 18 gigabytes is not big data at a terabyte of data i might start to look at those kinds of things the reason why you might want multiple data files is if you want to separate the data files across different storage devices or across different storage adapters if you're running under things like vmware if you're running under amazon ec2 where you can put different data files on different ebs volumes but generally speaking those kinds of benefits start to kick in at around a terabyte is where that starts to make more sense now let me let me rephrase that somebody out there in the audience is going to be like well i have this 18 gigabyte data file it actually makes a big difference if i put it on four four gigabyte data files now because i get more throughput from vmware that's because you're not caching jack if you're caching anything then it's not going to have that kind of a benefit and when you're only talking about 18 gigs worth of data you could cache that here that's a waterfall and it's kind of like a garden of eden area over in iceland it's absolutely gorgeous really pretty and then the last one that we'll take is from still a clarion programmer wow i used to program in clarion too says i'm a slob and i'm never consistent with indenting column stacked or on one line capitalizations of keywords etc do you ever use a code pritifier and if so which one or should i just try to develop some self-discipline in my old age so the only reason that i don't use a code prettifier is that i'm constantly jumping from client to client and from machine to machine so i have to deal with their coding standards and with whatever pritifier they have installed so when i'm writing stuff i'll just generally give it to the client be like all right now you go put it in whatever code style that you like works best for you if i was going to be a full-time employee at the same company working on the same code base for long periods of time i would just use whatever code pritifier everybody else uses i'm just not into the religious war of uh what things should look like however if you don't capitalize your select in select statements you're dead to me that's about all i have to say about that it's just a hard you know it's a rule that i'm never able to break i still always type select an uppercase all right well there goes doesn't look like we're going to get much more of the sunrise here today we got some rain clouds moving in here oh it's just a beautiful windless morning though here in the harbor absolutely pretty uh so today i'm teaching my mastering parameter sniffing class so i got to go back home and get some breakfast and then it's real weird how my time schedule works here my u.s time zone classes for me locally in reykjavik that's 1 pm to 9 pm so the way that i work i just i'm always getting up at 3 a.m i can't 3 4 a.m i can't seem to break that habit uh so i got up at like 3 4 a.m this morning i uh had uh oatmeal we had oatmeal and now i'm going to go probably go off and get just a little something and then i'm going to go to bed as crazy as that is it's like 7 30 a.m go take a short nap and then that way i'll be able to have lunch and teach from 1 p.m to 9 00 p.m well it really screws with your sleep schedule especially now that the days are getting shorter here in iceland the uh sunset and sunrise because you know we get like we iceland gets like 24-hour sunlight during the summer and almost 24-hour night during the winter especially as you go north in iceland up towards the arctic circle see what today's sunrise and sunset so today's sunrise was at 6 13 a.m and sunset is at 8 46 p.m but it's amazing how they change every day the sunset gets like five minutes earlier every single day so it's you really see the changes we uh fly back to uh california moved back to san diego in about a month from today so it'll be perfect timing right as the days are getting short and gloomy here in iceland i'll be uh heading back to california and getting my bright sunny days again and seeing my porsche and my jaguar i just can't wait so i will see you all again at another office hours adios everybody

Channel: Brent Ozar Unlimited

Views: 4,094

Rating: 4.9534883 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: gRwLZERyORk

Length: 26min 58sec (1618 seconds)

Published: Tue Sep 07 2021