Office Hours: Ask Me Anything About SQL Server In San Diego

hello party people and welcome to san diego this is my new home base of course i'm back from reykjavik iceland having spent a lot of 2021 over in iceland and this will be the new home base obviously you can see that there isn't any furniture in here in the moment i'm waiting for the movers to bring all the furniture back in we put it all in storage when we went off to iceland but it gives you a rough idea of what the rough empty space will look like great windows looking out at the san diego skyline and then over here we have the bay as well we can see out on san diego bay and right now there's a music festival up and running so there's a dance music festival happening out there was out on the balcony just listening in on all that so i figured uh since it's going to be a couple of days before the furniture gets in here and i can do one from my studio and a lot of questions had piled up while i was traveling back from iceland to the united states i figured i'd go through and do a round of questions so here we go i should look at the time just to make sure i don't go too far in first off we have yoni yoni says we have 60 to 100 gigabyte databases on sql server on windows will it be better performance if we change windows to linux just for the sql server part and if my applications stay on windows so no generally i wouldn't move to linux for the performance gains the reason why you would think about moving to linux would be if your staff mostly has management experience with sit with us linux but not windows like if you're replacing oracle with sql server to as the back end of your applications but i wouldn't go just in the expectation that you're going to get some kind of performance benefit sometimes every now and then i'll see people reinstall the operating system on their server and at the time when they do that they make other changes that fix bad practices that they just didn't know about 10 15 years ago obviously that's a stretch in terms of a number now but they didn't know about a long time ago when they were building the sql server so if anything i would say maybe just start learning about the best practices for sql server installations and then go do new fresh installations and that's generally going to be more likely to get you the performance bang for the buck as opposed to switching to linux let's see here next up we have brandon asks are you able to see sp all night log without hearing lionel richie so sp all night log is one of the stored procedures in our open source first responder kit there was a client in downtown chicago that had us write a log shipping solution where the servers didn't need to be able to see each other it was going to be very cloud friendly and me eric darling and a couple of members of the client staff all tried to come up with catchy names for it and i'm proud to say that my name sp all night log was the one that won because i wanted a name for a script that would make me happy when i heard it and you can't see sp all night long without going all night long all night uh so that that still brings me joy i i don't like that stored procedure anymore only because i don't use it that often we stopped working with that client because they hired a big mass of dbas and then they ended up building on more and more complicated solutions so i don't have to maintain sp all night long that much anymore but it's like you see me laughing reflexively as soon as i say sp all night long because you just can't help but oh the video is so good if you've never seen the video for all night long by lionel richie you should totally go and watch it because it's just this happy 1980s moment in time super cheesy the costumes are terrible but it's just amazing to go back and see all the dance moves and the 80s hair and all that next up gustavo asks if i have a table with 10 foreign keys does sql server create an index for each foreign key gustavo why don't you just type that out why don't you just go create a foreign key and then go see if an index gets created it'll take you 30 seconds go create the foreign key and then use sp blitz index or your tool of choice to go see if there's an automatic index being created now the one thing that i'll warn you about is make sure that you try it with primary keys and foreign keys and see what the differences are between those two gustavo i can tell you're not the kind of person who likes to read the documentation i don't blame you the documentation is usually so dry that you could start a fire with it but you could go experiment and learn that's not that hard to try right go create two tables and create a key relationship between them and as you can see here the movers haven't brought in my spoons yet so i can't spoon feed you those answers just yet did you see what i did there i was proud of that i don't write these jokes ahead of time and they certainly don't write themselves uh willem asked is switching from sql server to postgres a big step i find articles regarding differences but i was curious about your experience i actually talked about this a couple of office hours ago so if you go back and look at our past office hours you can see the table of contents for the questions on each one i'm going to give you the really short answer management i don't care because amazon aurora manages it for us uh and for t sequel you're moving from t sequel to pg sql uh the the differences were close enough it wasn't that big of a deal performance tuning it's a whole other ball of wax though things like vacuum are a totally different concern on postgres that aren't a concern at all on sql server next up sean asks what are some sql server undocumented secrets that you've found hidden system functions or undocumented clauses or keywords so my thing is i don't bother wasting time i say wasting and as if that's a bad idea if you want to waste time i'm all for it i waste time all the time i enjoy doing it doing all kinds of things that i have no technical need for but the thing with looking for undocumented or unsupported things is that they may not be around the very next cumulative update or the very next service pack and i'll give you an example spms4hdb spms4hdb was a barely supported you know kind of undocumented system stored procedure that would loop through all of the databases and run a script in each one of them and a lot of us who were doing professional database administration over the years built scripts that relied on that even though it wasn't technically supported uh and had known problems with it the problem with that then is if anything breaks or if it doesn't work the way you expect it to microsoft just isn't going to fix it they're not going to bother oh i have to apologize my smoke alarm has a dead battery and as you can see i i don't have a chair in here to go up there and go hit it so we'll see if it keeps beeping i'll uh i'll pull the plug on the webcast but when we ran into problems with sp for ms for each db microsoft wouldn't fix them because it's an undocumented unsupported system stored procedure so i can't leave things at my clients that rely on undocumented stuff geez there it goes undocumented or hidden system stored procedures so it doesn't make sense for me to rely on that all right well because that thing is going to pop up and keep going i will i'm going to guess it's going to pop up and keep going i will stop here on this round of questions uh and then i will get well it stopped playing chicken with the smoke alarm that's always a oh nope see that's it all right so i will wrap it up here and i'll do another one after i at least get the movers to bring in a chair or something or go buy a stepladder so that i can go turn off that smoke alarm adios y'all and i will see you on the next office hours

Channel: Brent Ozar Unlimited

Views: 6,451

Rating: 4.8736844 out of 5

Keywords: sqlserver, brentozarunlimited, brent ozar, mssql

Id: o7CYItqFiXY

Length: 8min 59sec (539 seconds)

Published: Mon Sep 27 2021