Watch Brent Query AWS Aurora PostgreSQL with Azure Data Studio

[Music] good morning party people how's it going it is another beautiful I say beautiful but I mean you know we got some clouds back here in San Diego I haven't looked to see what the temperature anything is supposed to be like today I was at a minor air-conditioning emergency in my house I couldn't figure out how to make the air-conditioning turn off we've got one of those digital thermostat type deals and I was in there clicking like crazy I'm like it's freezing in here I want a broadcast for once without having like three layers of clothes on all kinds of sweatshirts and stuff so this morning I'm going to be talking about querying Aurora Postgres so we have this monitoring application called sequel constant care that goes in and pulls data from your sequel servers once a day got around about 4,000 folks or 4,000 servers in I want to say it was like 450 500 users the last time I looked at it and they send in their data once a day will it all comes into our central Aurora server Aurora Postgres so today I'm gonna be like totally shifting gears I'm gonna be using Azure data studio from Microsoft to query Postgres a database my way up in the cloud being hosted in a Amazon and probably showing you Jupiter notebooks and some other cool stuff that I find really intriguing not necessarily useful for sequel server DBAs in their day to day lives or data professionals but it might be interesting to for you to see how some of the other tools work plus if you're into sequel server the data that I'm gonna be querying is all basically the DMV's the dynamic management views from sequel server we just collect it all into our server and then chill you'll see so how some of the DMV's work techno viking good to see you this morning as well so let's go get started I'm gonna start by introducing the thing that I'm trying to build so once a quarter I've been putting out this population report over on the blog hey there's me I've been putting oh that's kind of funny I could kind of stand right over it then I've been putting together this population report over on the blog and I've been saying what what's the adoption rates look like what are large sequel servers what are small sequel servers to help people get a feeling for what the market overall is like this isn't a perfect picture good morning Copenhagen or afternoon I suppose it is over there this isn't a perfect picture across all sequel servers obviously it's only the ones who are using sequel constant care good morning bossy so it's your you I don't want people to think that this represents a true overall average of all of the sequel servers out there it's just a picture of the people who subscribe to sequel constant carriage she does have a good point yes richy's correct the database is absolutely perfect so the last time I did this three months ago I tried to do it every three months we had about thirty five hundred and seventy servers of folks who had sequel def DBA thank you I appreciate that y'all so subscriptions are what keeps me motivated to continue doing the free streaming and the content thing especially while we're stuck here in quarantine a little behind the scenes for those of you don't know if you have like Amazon Prime you can set up for twitch Prime at no extra cost and then you can subscribe to specific channels and then the broadcaster's get a little kick back there doesn't cost you anything extra so in order to produce this I'm gonna scroll down and give you an idea of the blog posts that we're gonna be working on so here I said last time what versions are the most popular what editions are the most popular so here it looked like last time we had something like 1 or 2 percent of the sequel servers were 2019 this is really the one of the big reasons why do it is I know that if you watch conferences if you watch Orlando very nice and Richie's down in South Florida too as well if you watch conferences or online marketing videos it's gonna sound like everybody's gone to kubernetes on Linux and you're being left behind cuz you're not in as your sequel whatever but the reality for that for quite truly the same there's still the reality is that most of us are still on sequel 2014 2016 2017 so this helps to kind of reset those perspectives then if we go further down we talk about what versions people are using in development how much data different sequel servers have like what's normal the majority of databases out there have less than a couple of database servers have less than a couple hundred gigs worth of files altogether so where am I getting this data from morning folks several folks from over in the EU joining us this morning not the Morocco's in the EU for Foxy recite my geography sucks as an American but I'm not quite that bad so where am I getting this data from so Ritchie and I work together on this thing called sequel constant care where y'all send your data in and Ritchie's basically laid out the database schema now it's time to shift gears and go over into the database itself in order to query it I'm gonna use Azure data studio now I'm curious how many of you have or have not seen as your data studio before leave a comment over in the chat and say yes I've you've seen as your data studio before or no I have not seen as your data studio before and if you use it I find that even more intriguing if you actually use this thing all right spare deLee says no I'll give you all 10 seconds to say whether you guys seen it before yeah so it's a mix all over the place Microsoft is kind of marketing as your data studio as the new tool for people who write queries Oh Daniel Hutmacher good to see you sir yeah it's the new tool for people who write queries and it's compatible on the backend with both Postgres and sequel server and all kinds of other stuff so I love it because it's it feels like custom made just for me because I spend both my time split back and forth between sequel server and Postgres and this works with both now the way that you usually use it in terms of writing queries is like what you see here on the screen of a big long CTE plus a query that I've written inside here if I just go through and execute it I'm gonna highlight the whole thing and I'm gonna go hit run it'll go ask me what database I want to connect to got a bunch of sequel servers up here I'm gonna be using a read-only replica up in the cloud for our constant care service and then it's got results in here just like that techno viking I think it works mice with my sequel Maria DB don't quote me though I'm not a hundred percent sure on that just cuz I don't use my sequel that much nothing against it I just don't use it so here you have your results pane and then one of the things that I absolutely love about it is over on the far right hand side there you get these icons to export to a table into Excel if you want all kinds of stuff water world fan there has to be a story there Cheers water royal fan thank you so this right here the bulk of the data that you're seeing here this is how I usually build that blog post and I'm gonna make my own connections list over on the side go away for a second so here you can start to see the kind of stuff that I use in order to build that report now I'm gonna do way back now and I'm gonna show you where this data comes from kind of how the data structured inside our database but this will be the thing that I'm basically replacing this I could just execute this query copy paste this into Excel or use the export to excel feature and then I could start go building a pivot table I want to do something this time around I'm gonna try to get it to work in a notebook and that's where things are gonna look a little cool inside here I'm gonna try to do my reporting just inside of Azure data studio without having to jump back and forth in Excel I don't know if it's gonna work I have no idea I haven't tried it yet but that's where I figured I would stream it with y'all so this the CTE I always got to remember the command to hide the result sets cuz ever all the keyboard shortcuts are just a little bit different than sequel server management studio so big long CTE plus all kinds of other stuff jumping around to other tables let's zoom out for a second and talk about how we're hosting the data the thing over on the left hand side is a lot like object Explorer over in sequel server management studio if I go over here here's the replica that I'm querying constant care reader I can go expand databases or in the constant care database and then tables and here I can see the tables that we've built for sequel constant care some of them are unique to us like Richie designed for example collection file collection rule collection run collection server these are all tables where every time one of you wonderful customers now sends in your data it goes into these collection tables like here's a collection for sequel ABC over in you know Florida then for every one of the servers we have all these tables down here and you're gonna recognize a lot of these tables down here because these are the same things that you have on premises for example if I go in here to say I'm just gonna start picking a couple at random I'm say server property or if I look at this databases I'm not gonna expand or I'm not going to show you the contents of it of course because it'll have things that are personally identifiable when we start to talk about people's database names theoretically your database name could be an industry secret you can see in heart in here that we basically collect the same stuff that's in the DMV's we don't collect all of it because a lot of the columns are useless to us we just don't run any kind of analysis on it however if I go down and look at some of these for example performance counters sis DM OS schedulers that's a good one this is where your CPU cores are at so if I want to go get some of this data I can go copy it and say select star from sis DM OS schedulers limit 100 then I can go run that query and it'll show me the contents of sis DMOS schedulers it's broken out across different users and different connections each user has a whole bunch of connections every time that they send in data to us for a server that's called a connection so what I'm going to do is I'm going to assemble all of this data together up into that into reports in different graphs and this is where things are gonna start becoming a little weird as you get to watch me write different queries how much data are we dealing with here in each of these tables little Postgres you can go in and see how large your different table sizes are this query will go show me how big each of the tables is so here you can see that insists index columns we've got a hundred and four gigs of data index operational stats we've got 97 gigs of data partition stats backup set and so forth y'all happen to do a lot of backups which is kind of cool how many rows have we got in each of those just to give you a feeling for how fast some of these queries respond or how slow some of these queries respond how many rows have we got across say a hundred gigs well you can also go in query in Postgres roughly how many rows a table has this is kind of like looking at statistics and sequel server 1 billion fries so and the reason that that table is so large is that every index has a row in here for every column and then we're tracking that across all of the databases across all the servers that we have and every server has sent in data multiple times across the last say 30 days we keep about 30 days worth of metrics inside here doesn't do us any good to keep older metrics I try to be a database administrator who believes in purging I'm not actually the administrator Richie does all the work then I try to purge as much as I can I don't want extra old data I'm really only concerned with what's been happening lately you could make an argument that there would be a value towards trending data over longer periods of time for example seeing how sequel server 20 19 first performed with the original release to manufacturing build versus what it does with the latest cumulative updates do realities there's just only so many hours in the day to run analysis queries missed and if you're nasty so we're talking about fairly decently large tables here up on the high and on the smaller ones if we start to get into say this database query stork is not a lot of people are using that now it's this database files things get a whole lot smaller as we start to get in here towards more small DMVs so now I want to go through and run that big monster query again that analyzes the customers who have opted into sharing their data so what this query does is it lays out all the attributes it says things like what version they're running what Edition they're running that's cool but now I want to start aggregating this stuff together inside Aurora and as your data studio instead of copy pasting it out to excel so how am I gonna do this there's a thing called a notebook now let me stop for a second you know in the old days when you would want to send someone data you would copy paste stuff from sequel server management studio you would copy paste it into Excel and the query that built the results was separate from the results themselves and then when you paste it into Excel you also had to send in some kind of explanation as to what you were doing you'd have to type in and say like what's down below represents blah blah blah blah well notebooks originally started out in the data I think the data science community it wasn't something that was Microsoft focused at all the data science community needed to be able to type and tell a story and then show a piece of code and then show the results from that code and then keep typing and to give more explanations for stuff so a notebook is one file that you could almost think of it as like a notebook that you pass around between people that it has your scribbled notes and some drawn out examples of things I adore notebooks they're amazing except there's one gotcha they don't include execution plans if I could get execution plan support in there you would see me switch all of my training over to notebook tomorrow that's how big of a believer I am but of course since I'm a performance tuner I need execution plans if I don't have that it isn't very useful to me I still think it's gonna be useful to you though because I bet a lot of you have to pass data around back and forth to people or you have to build run books you have to build run books for your juniors like here's how you troubleshoot a cluster here's how you check to see if the sails ETL process is working here's how you check to build a monthly report for the data warehouse team whatever so and notebooks are something that are built into Azure data studio there in other tools too it's just that Azure data studio focuses on notebooks that are either about sequel or or Python or also PowerShell and sequel dev DBA points out that it's also great that Azure data studio has Mac OS support yes I agree wholeheartedly that's one of the reasons that I feel like it was almost a custom bill for me because I use a Mac and my daily driving type work so now when I'm doing stuff that as your data studio supports I don't have to bother opening up a VM with sequel server management studio so let's go see how how a notebooks work in Azure data studio what I'm gonna do is I'm gonna go back over to my server list and then over on constant care Reader I'm gonna right click on here and say new notebook now notebooks I can have a combination of code and text and I can jump back and forth between the two so the first thing that I'm gonna do is I'm gonna put together a text because I in a perfect world if this works right I'm going to distribute the population report in notebook format so that y'all can go open it and play with it as well so I'm gonna say add a text cell now when you type in notebooks it uses markdown I'm a huge believer in markdown formatting it's like a what-you-see-is-what-you-get word processor that only requires text it's a beautiful format if I put in one pound sign or one hash or one stroopwafel or something like that if I put in one hash and I say sequel constant kick their population report spring 2020 June 1 will say May 30th Brent owes our every quarter I put together a list of so you can put all kinds of stuff in here blah blah blah charlie whoops Charlie Brown's parents you can also do things like bulleted lists you see the little format things up there you don't have to reach for those you can really just do them in text so like if I say this is a big deal this is also a big deal this this is not so it's really cool make keeping track on me making sure that I'm using the readable secondary because of course we have a primary two that's actively taking uploads from folks and processing their data I tried I especially because I try to write pretty decent queries not great queries so I try to use the reader as much as I can so I'm gonna come back after the webcast and I'm gonna type in my explanations that'll go up at the top in the workbook I'm less concerned about that then I am the queries themselves let's hop back over to the blog post and let's see what's the first question we need to answer what versions are the most popular so let's go write that so we have say what versions are the most popular now you notice that I put in two hash signs here and then that made it a little smaller of a title so now I got my text cell up here and that got a nice just kind of a nice what you see is what you get now let's add a query so I'm gonna click on add code and now I'm gonna be able to copy paste or just totally flat out write queries inside here like I can say select star from collection rule or if I want select star from sis DM OS memory nodes limit 100 see how I'm getting autocomplete inside here you get the same autocomplete you get over in the normal query editor spare devices that is cool I totally agree I think this is really slick now here's where the really cool stuff comes in I can click run sell and it runs the query that's just inside the cell and the data is inside here in the notebook so when I save this when I save this notebook and I upload it to like our website if I want to distribute it y'all can open this file up and you can go down and see all right here's the query that he wrote and you can click in there and copy/paste it now you won't be able to hit play since this data lives up on my server but if I was writing for example DMV queries stuff on how to use SP Blitz I agree Craig I think this is brilliant then or for your own fact you know how many times if you had to write a report for someone and you had to kind of explain what you were doing is you worked through it well now you can and then they can even open this up and they can dump it out to excel they can put it into a line chart if they want their different charting type options I'm actually not going to do charting in here just yet I'm going to go ahead and get start a semblance are the most popular so let's go nail that down I'm going to go over to the other query that I've written before that has the addition it has product version product version my major and minor I think I need product version major and minor in order to determine what builds people are on for those of you who don't do a whole lot of DMV querying it's like version 11 and sequel Server 2012 version ah Dan McNamara thank you I appreciate it very cool version 14 in here is sequel server 2017 14 2017 15 is 2019 and so forth so these two are the columns that I really need product version major and minor let's go see what we need to do in order to pull that out let's see here I think I've got that just in this CTE here this should be absolutely beautiful I'm gonna copy that out go over to my notebook and then I'm gonna paste this into here and I'm gonna clean up the formatting just a little bit there is a brand new sequel prompt Redgate makes his product called sequel prompt that has things like intellisense for as your data studio i didn't install it whoa woods and goods that's kind of cool name thank you woods and goods I appreciate it I didn't install red gates equal prompt only because I was just getting ready to do the webcast and I didn't want to change the environment at all in a way that would break it sometimes I mean you know it's open source when it breaks you get to keep both pieces so in here I have here I have let's go see what we have before I tell you what I have why don't I just go go go do it so let's run this I'm gonna take a quick glance through just to make sure that there's nothing personally identifiable that I'm worried about with happening and then let's go run the query and then go see what happens okay so we've got thirty-eight hundred and eighty rows this has a lot of stuff plus stuff I don't need aha Eamonn says it's very exciting to see you're using abs was waiting for that to use it all so I'm good to see you this afternoon afternoon for you so I've got um product version product version major and minor I really just want major and minor for this so let's go get just those two columns and then I'm gonna say some one not some one as servers so let's run that query again and it sure helps if you put in a group by their order broker group-by and then copy this out copy boom paste paste - and that should do it let's see play whoo awesome we're getting really close so now we have the product version and product version minor so the de gotcha with minor is sequel Server 2008 r2 was when they called it version 10.5 so I got to include that one as well now when you're querying in here there's another gotcha not sure so azure reports its version as like version 12 if I remember right so let's go back over and see when I first ran this original query let's go paste him into a new window just so that I've got him just by himself I'm anal retentive about with uh-huh angel and angel linear angel linear there's an I want to say something along the lines of there's an angel in here except that's gonna be absolutely terrible that's not going to go up so I think I also need addition because addition sometimes says sequel a sure let's go see where I'm gonna put in and addition equal or addition is like sure just to see if there how many different things that we have that are sequel Azure and because I'm looking to see if they put anything in here about like managed instances engine edition in server properties designates different kinds of Azure this is where whenever you're querying the DMV's some knowledge of sequel server and the contents of those DMV starts to come in helpful so I'm gonna go show you we're gonna go look at engine Edition just so that you can see there's something out there called the documentation and I know you're going to have a hard time believing that this exists sequel server server property so if I go look at server property this is where we're getting this data from and I scroll down here engine Edition there it is that's the part that I want right there so engine Edition this tells me what kind of sequel server it is so if it's edition number five that's as your sequel tb6 is as your synapse analytics I swear to god I know somebody from Microsoft is watching this but will you just pick one name for that product date Allegro parallel data warehouse as your data warehouse massively parallel as your synapse analytics the thing that's holding it back isn't the name the name is fine leave it stop touching the name just when people start to get used to a product and you change the name how about if we change your name instead if you want to start changing names go down to the DMV go get a change of pissed about that one that's just utterly ridiculous that's marketing amateur hour hi folks today I'm Fred benzoyl I know you were used to Brenna's are but I'm somebody totally different so coming back out of there why can't have nice things so I'm gonna have to do something with server props engine addition to as well so let's go back over to our notebook and so case when boom equals what was the number it was five then as your sequel DB when props engine Edition equal six then what the hell did they call this thing this week Azure synapse analytics or a load of garbage as your synapse analytics when props engine edition equals eight ah thank you so you an anonymous cure an anonymous cure agrees with that evidently then this is a sure sequel DB managed instance and then as your sequel edge I guarantee you we don't have any of those and when props engine Edition equals nine then what was that thing called as your sequel edge okay cool else sequel server and I think that's how you do a case I'm not a hundred percent exactly sure let's go ahead execute what's the worst that could happen it's not included in the group by okay all right I don't want to group necessarily by that hany says good that they stopped at nine you they stopped at eight a little while ago and then they added edge and they're like oh it should be told something totally different let's see if I can group by that particular string let's try that J worked yeah okay so now I need to order my inside here so we'll say order by and I'm gonna put that case this first thing up at the front because I think that's just a little bit more classy so let's move that up to the top paste and then that's perfect so order by I'm gonna do something I know you're not gonna be happy with and I don't really frankly care order by one two three Richie said I didn't know about engine edition nine I don't think anyone in the world knows about engine edition nine so I think it's a really interesting idea as your sequel edge meaning that you can run sequel server on a Raspberry Pi when I when they first said this I'm like you from a licensing perspective that is utterly stupid like that they're gonna have to do something different in terms of Licensing thankfully they did they said right now the way the licensing looks last time I looked at it it's you can use it for things on like a Raspberry Pi if it's bundled with an application but they're not saying like standalone prices on it yet which totally makes sense okay so now you see why I had to break out the azure stuff because it would quartz itself is version 12 because I guess when sequel server design - sure they thought it was about as good as sequel server 2014 but what that's about it so then we have the rest of these you know what I should really do have it a bit cooler for mine Norway I can always forget if it's Norway Norway over benevolence I could have put the column name after the case and skip repeating its name on each condition I thought about that but see here's the thing that I really want to do watch this I'm actually thinking ahead I know it's hard to believe but what I want is I want one column that has all the stuff I need in it so that it's gonna end up graphing and looking a little better so I know it's unbelievable check this out when split part this equals Iceland it is Iceland which I'm so bummed about I can't we read all the Iceland newspapers every single day and I saw the Blue Lagoon doing layoffs like oh I want to be over there as soon as possible now my wife and I even just like looked at what it would take to just go fly over there for like a month and just go over there for a month now I'm waiting this we're kind of watching and waiting to see how the whole testing goes as we as new foreigners come over like how that testing piece works you come your country just has it in the bag it's amazing so when this equals 10 and this equals zero then sequel Server 2008 for example then and I forgot that equals zero I'm missing something there zero so this will be 50 then sequel Server 2008 r2 so let's see if this works the way I expect it to work and then we'll keep building it out if it does work actually that way tactical one of the things I'll just stop and say for a second one of the things that I really miss when I switch back and forth between sequel server and Postgres is the sequel server does such a long job of implicit conversion sequel server can figure out how to automatically convert strings to numbers and numbers to strings with minimal amounts of work on your part Postgres needs a little bit of a helping hand to say hey buddy this is the side i want you to compare so i'm gonna have to go do all du jour is's trick here of casting one of them to an integer I'm not happy about it but I'm gonna do it so here I'm gonna say I want to say that this is how it works bang bang integer yeah so then I'm gonna say this then this thank you for following experior welcome to the club paste this over here and let's see if this works props engine Edition must be included in the group bye that's not entirely true it's that I did something fancier inside here so I'm gonna have to do this then let's see if that works run and total whoo well kind of sort of yes oh no beautifully oh that works just fantastically so here I get these numbers down at the bottom diego's is grouped by one two three Oh Diego Oh Diego if that works I never thought about doing a group by in here with that if that works Thank You Diego that's absolutely awesome that's so much whoops so much more elegant there so that's pretty cool yeah thanks em Khan says cool t-shirt thank you I appreciate it so I'm a really big travel guy in Boeing I fly a lot so this is a play on jet fans so fanboy of travel that kind of thing okay nice job Diego so now let's finish it out with the other versions so we'll go I don't need split part for the second part of the version so now I can just say when it's eleven then twenty twelve and we'll do this for the other ones boom boom boom boom seventeen nineteen and then this is twelve thirteen fourteen fifteen L sequel server Futurama because we haven't quite figured out what the other ones are yep and let's go see tah-dah well you shouldn't be laughing that was I know my query is absolutely terrible but look the idea is that we get the rows of the results across so now I have something that I could actually put out to folks and customers hell I don't need product version and product version minor anymore I can totally get rid of those to just kind of clean up my query results go put that in and now I only need to group by one order by one and that'll make it just a little bit tighter in there just make sure that that works absolutely beautiful now I can finally do a graph pager obsess group by is supposed to be evaluated first for that go hit somebody's thing on anybody's post on logical query order of operations it's beyond what I'm gonna go tackle inside here so let's save this thing huh where the hell do you think that there would be a disk icon up at the top and I'm gonna go click file save as and I am going to go put it in my sequel constant care folder and I'm gonna call this a sequel constant care population George and whippersnapper spring 2020 spring and save that so now the cool part is that this has the query built right into it and I can give y'all this and let you play with it the other thing I'm gonna do is I'm gonna go switch it into a graph and here's my goal let me go back over to the blog post so the blog posts that I did I the only reason I did this is I wanted to work really fast I wanted to work really quickly I set myself a two hour limit on building this blog post and I talked about it inside the the blog post I'm like look I don't want to spend a whole lot of money on this muffler I don't want to spend a whole lot of time on this blog post so I use just quick and easy Excel formatting and it looks like a dogs behind I mean it doesn't look good at all so let's see if I can do this this is my big hope is if I do this in Azure data studio and I use the charting inside there is it gonna look a little bit better so drum roll now I will go click on the little show chart button and let's see what we get [Music] okay look well wait okay hold on now this this does look also like Dogbot but what if see how it says down here case and servers maybe this is my fault for when I built the data set when I built the data set I didn't name this column so let's name this as sequel server version and then as long as we know this is going on the chart we'll just put this as an upper case here right side note I I really wish that we didn't have like obviously this is an Intel isense type problem because the query works just fine but you know okay whatever so let's go give that a shot again let's go run it and so this is another kind of goofy thing when you save stuff inside as your data studio it seems to be like what what server am I on now you saved me I I completely forgot where I am so now I got to go back and go look buddy I got a little bit of amnesia here you're on the constant care reader replica now and every time you save the file you have the same problem kind of jumps back and forgets where he is okay so now now it's a lower case instead of upper case all right whatever let's go hit the bar graph and go see and okay there's a configure there's a configure chart so let's go configure the chart and let's see bar type horizontal use first column as row label oh yes oh yes yes yes not really it's kind of crappy because it doesn't really put the labels right underneath okay hold on let's configure the chart again Legend position [Music] it doesn't really help me that much either how about the other how about the other chart types let's try the other chart types don't Vaughn tool 3 Chocula that's terrible that looks even worse okay that's not good let's let's try the chart again and configure it let's see here donut I like donuts oh come on now Oh use the first column as a lip oh okay all right so there we go so at least that got but without without the without the labels right there this isn't very useful either so let me go configure the chart again let's try pie charts all my data analysis friends make fun of me whenever I do that so I'm not I'm not gonna go near that we tried bar let's try a horizontal bar and use the first column as a row label I wish it would show I wish you would just put the damn labels right next to the I don't think I can ship this I don't it's really close it's really close I don't think I can quite ship this though this isn't something that I would want to have on the blog well okay let me put it it's not much worse than the Excel version it's worse it's not much worse though I might rock and roll with this just in order you know what I am gonna rock and roll with this let me tell you why so not only am i tracking the status of the sequel server population over time so I'm basically showing what adoption rates look like and I'm gonna start comparing once we get to the one year mark I'm gonna start comparing adoption today versus one year ago so people can see how quickly or slowly new ver sequel server versions get adopted you know what if I post a blog post and it looks like this if it looks like a cat but as Richie would say maybe it'll encourage Microsoft to make better looking charts inside here cuz it'll other people will see this a go yeah you're right this isn't quite ready for primetime so screw it I'm a ship it off they go mad Mac Mac Jeanne says can't you set the y-axis label let's go see so it says up here y-axis label let's say Mac Jeanne 1 and say ok and great Mac Jeanne Oh 1 there you go it's right there it's not really that good Wilfried says trife and dance ok cool oh wait wait I know how to say here that that means when I have my hourglass right I'm Chuck a Chuck and sugar sugar sugar dah dah dah is that any better oh yeah it is better Oh evidently it's a subscription dance Blane Trimble just scribed whoo huh keep dancing [Applause] no I'm being humorous obvious obviously so sand dances this extension that you're supposed to be able to install in here so what I'm gonna do I've never installed it before but I was reading about it this morning and I was comfy well let's go see so let's see how you install sand dance now normally there are plugins extensions over here let's go say sand sand dance Frasier data studio install cuz you know it's out on the internet I'll take anything that anybody gives me installed there's a little button here to preview okay whoo it looks like it's set to go now I'm watching this animated gif idea what the hell's going on anonymous cheer cheers again with the with the sand dance I should probably keep doing that I have no idea what the hell how do I get there inside of a notebook so I've installed it if I look over here there's no let me try rerunning the query again and we'll see if anything about sand dance shows up nothing about sand dance shows up there let's try to configure chart button does it say sand dance it does not so now how do I get to sand dance from here how do I get to run a sand dance feature contributions change log usage right click on a CSV or TSV file in a query results plan click the visualizer icon what is the visualizer icon so maybe it's this thing over here all right so let's see if we have that so let's see in a results pane let's go back to the results click the visualizer icon I don't see a visualizer icon I'm guessing that maybe they mean it's not in notebooks or maybe I have to exit and restart let's do that so let's close all our stuff save that close you let's close you acquiesce just subscribed oh that's cool it's kind of like call us I like for less I don't save that one do save that one that's quit it and then let's go back in and give her another shot let's try a sure data studio again and then we'll open our recent notebook notebooks aren't in the top okay notebooks aren't in file recent okay I'll click on that guy okay so now let's try it again let's try running the query again and I'll go connect to my reader and then let's see nope I still don't have anything in here about a visualizer icon okay so what I'm probably gonna have to do let's try copy pasting it into a new query window and then if I copy paste it into a new query window and go run it and tell it I want it in the reader now where the hell it's over there visual icon so let's go clear it click on it okay so this is probably a good time but maybe I need it let's try clicking anything and then let's click on it no okay this has been my experience with a lot of stuff inside azure data studio I want to believe like Fox Mulder I'm really want to believe really bad but damn it I have a bad user experience with this I hate doing stuff like this in front of y'all and having it not work so like this isn't Wilfred's fault wolfertz like trice and dance and then I'm like okay but this was more effective than that hot mess that is really frustrating as an end user and I can see why people aren't really adopting this tool like crazy yet now having said that I can't use these visualizations inside sand dance I will use like a name Craig's as like an ashtray on a motorbike that's pretty good so I can't use the visualizations inside saying that's yeah maybe they're cool maybe it's a problem with a Mac who know their problem with an extension whatever but so what I will do is I will I will ship the blog post with the crappy graphs that are built into Azure data studio just as a kind of hey here it is I'm gonna ship this and keep right on moving and keep going but what we'll do we've been going for about 45 minutes now I'm gonna stop here take a bio break and I'm gonna go beat a pillow I'm gonna go is take a bio break and then go refuel my coffee and then when we come back we'll start writing the rest of the queries to go hit the population report this thing was only originally scheduled to go for an hour so it's up to y'all if y'all want to bail I will continue keep going in here though through you've got me until 8 o'clock you've got me for one more hour one more hour my coffee shop opens downstairs and y'all are debt to me so I will go over grab some more coffee and I will Wilfred says I got also problems but I was relying on extensive knowledge you were hoping that I had extensive knowledge right sadly we were both disappointed alright so I will stop here for a five minute bio break 5 minute bio break and then when we come back I will keep writing queries against this thing and we'll live with the graphs that we have see y'all back in 5 minutes [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] [Music] you [Music] [Music] so Amon says now that it's now that I see you using as your data studio I don't want to use it in which of course wasn't my goal but it's one of those to see you can really see how it really feels in real life just watching someone else use it when you watch the demos of it of course people have constructed carefully their demos to step around the landmines I just use it I will say I use it a good if I had to lay out the top applications that I use probably number one for all of us is a web browser in my case it's Chrome or Safari I've been trying to switch to Safari I just still use Chrome a lot because they use a whole lot of Google services so Chrome and then to some extent sequel server management studio or Azure data studio I flip back and forth I like as your data studio I believe it has a great future it has a lot of interest like just being able to query Postgres is a showstopper for me that's phenomenal so wonderful to just use one tool all the time welcome to the club you an stirrer damn I don't know why my stream labels aren't working so the thing that's down at the bottom next to the star is supposed to show y'all who my latest follower and subscriber is not know why those aren't working I just don't know what the deal is with those oh well I said everyday I'm shufflin and then another question somebody said Oh Mac Jeanne says what kind of espresso machine do you have an espresso when I only have like two three minutes in between jumping in between sessions I usually prefer to just use mine espresso I have four other coffee makers the techni for mocha is probably my favorite Technovore mocha I have one of the big ones so it makes like 12 cups of coffee thing is I hardly ever drink 12 cups of coffee anymore I drink like one small thing of espresso in between takes or whatever but that's about right a French roast pots Aeropress all that kind of stuff I really love good coffee it's just that I just don't usually want to take the heel I see only as a Keurig oh I saw the most wonderful video on tick-tock of someone putting you know those little garlic butter things that little who is it Papa John's gives you Papa John's Pizza a lot of you don't know this your pizza snaps that's okay I'm I'm not but Papa John's gives you those little garlic butter things I put the garlic butter thing inside a Keurig and gave it you know made a cup of coffee quote-unquote and gave it to somebody it was hilarious to see their facial reaction that was so phenomenal a catalyst says it feels like they haven't got the basics right yet I would argue that they've got the basics right but this is like icing on the cake you're like graphing we don't have graphing in management studio you know we don't have export to excel in management studio and it's been 20 years you know so they've made a huge amount of progress for an analyst focus tool like the notebook thing I adore it's amazing cuz I will give you all the notebook as part of the blog post the blog post for this will drop on Monday along with this video and my friends at Microsoft will be like uh Brent you got to stop using our software but they're like Brant why don't you go take a look at this thing over here called Oracle just to get me out of the out of the fold alright so let's go back in here and let's continue working with the notebook and see if we can put something together that's vaguely you so I can close the query window now cuz I'm not gonna need that that did turn now sand danced and sand dances like my dancing evidently so then here's the map that was just so terrible so we're gonna do with a bar we're gonna use the first column as label we're gonna put the legend on the bottom and say okay this is roughly as close as we get to a usable with the kind of thing that's in the blog post and of course I can just change the aspect ratio of as your data studio I can just change the aspect of as your data studio and it's gonna resize this graph right let's try to play the query again no let's try and go into the chart again okay cool ha you son of a I got a click configure chart again I got to change this over to use the first column as label I got to put the thing on the bottom and I got to click close again whatever ship it god doesn't even look full I have fine words who was it that said catalyst catalyst you said it feels like they haven't gotten the basics right yet catalyst catalyst type I might be coming back around to your side I think you might actually be a little bit more correct but okay so at least this gets me a starting point though doubt so that was the first blog post or the first thing that was inside the post was what versions are the most popular now let's go add the next piece so I'm gonna add another piece of text and we'll put a comment in for the next query that we're gonna go and run so the next query will go and figure out is let's go back over to the original post Oh what versions are people using in development because I wanted to see if people might be developing in newer versions to prep for future adoption like it are they already moving towards 2019 for their development okay so to figure this out I can't tell for sure you're because a sure there's there's not really such a thing as development versus production Renee sends me a video that explains and sand dance Renee I've been Rick Rolled before you're not gonna get me that easily but no you you might absolutely be right in terms of explaining sand dance but I'm not gonna waste the viewers time trying to go watch a video when the buttons supposed to just work I'm supposed to be able to push the button it says push the visualizer button and it's supposed to I I swear a lot I have the most potty my Renee says it just works on my machine oh great great it works on your machine Renee oh that's fantastic Renee do you work for Microsoft I bet you do it works on your machine so everybody else is gonna be fine aren't they Renee Renee you know better Renee says you can't do it in notebooks Renee you weren't paying attention you must be broke as hell if you can't even pay attention cuz I copied the whole query out I clicked file new query glue your eyes to the screen Renee click new paste it in here this is not a notebook Renee I'm gonna go click run I'm gonna go put it in the reader then go so here it is now I got this little visualizer icon here Renee this isn't in a notebook I click it I click it I click it Renee Renee you see over there in the corner go have a seat go have a seat over in the corner no I and I I'm having fun with Renee of course hey I appreciate you trying to help out that's cool but works on your machine is not OK don't ever let those words escape your mouth again unless you're a developer in which case is totally ok to do that right Richie right huh all right so let's come back out of here spare to life just keep clicking maybe the 300 time it'll work I want to believe ok so an Angus the swearing helps it helps me at least I find it healthful eat healthy too event those kinds of things and it really might be one of those things where it only works on windows or it only works on Linux or it only works if you're within a 15-mile radius of Redmond and you got a company on laptop whatever so the next the next question that we were gonna solve was what versions are people using in development what I was saying was is there I can't really tell for production versus development in Azure because it's all just one price so what I'm gonna do is I'm gonna rip out the azure portions of this query and I'm only gonna focus on the on-premises sequel server versions but then I'm gonna be looking at addition so addition if someone's using developer edition is the mix different for production versus you know only works if you turn off your ad blocker yeah exactly you must be you must be willing to accept these Facebook cookies and then it starts working all right so let's go get the query the original query cuz I want to say we had addition inside there and we do so let's grab Edition and then new edition let's copy paste this I wonder if I can duplicate code blocks that would be kind of cool No okay that's all right I don't I can live without that if I got to pick something to live without that's alright so now down here it will say what versions are people using in development because Renee says it works on no that's totally I'm not gonna say them so now we've got that let's go add a code break or go out a code block so here's my code block Renee says Brent drink more coffee that's probably the solution then let's go get so props engine Edition is the other thing that I need inside here so I will say props engine edition and then I'm gonna group by that also this is just for me to first check and see what Edition I need to filter for so let's go run the query that's engine Edition that's useless for me I didn't want engine Edition I want I don't want product version where am I getting development from where am I getting development from is it pro which product version have more on product version come back over here and it's product version now let's hit execute that's not it either okay all right so we tried oh it's edition its Edition it's it's just edition yeah yeah [Applause] so we're gonna say in development we're gonna say and props addition like developer so now let's try that again so that we're only getting the developer edition stuff and there it is so now we go wow I don't want to group by that dope because now I have both some let's all take a moment and raise a glass to some poor soul in this list who's very the top in the list who is still running sequel server 2008 32-bit developer edition oh so now I can rip out all the I'm gonna leave the azure stuff in there just in case having if they ever added a developer edition up in Azure I don't know if they ever would but we'll give it a shot now I don't need to group by Edition anymore because now I have everything that I need down inside here so let's take out the group buy and then go run the query again you thought your job sucked so here are the people who are running developer edition now let's let's look at these numbers here for a second let's go see so it well in terms of your honor I really want is I want a percentage I want a percentage to so that I can see what percent are skewing towards newer versions hmm yeah that would be kind of cool and I'll add that in after the broadcast but if I look at the mix so you get a feeling here that the majority up in all versions not just development but in all editions I mean in all editions up top the majority of people are on 2016 and then they're moving towards 2017 2019 we have a hundred and two out there if I look down at development um now you see a slightly different mix here and you also see different color coding I don't really know why they do this whatever Oh okay it's called hard-coded in order that might actually be interesting okay so 2016 is still the highest one have to hold my finger here because it's sort of can't manage to answer another studio can't manage to get this stuff together 2016 is a highest one and then the next highest one is 2017 and then 2019 isn't that far off from older versions that's different from what we see up here well 2017 is also the highest up here but look how far behind 2019 is up in production that mimics what you would think is going to happen out in the real world people are more likely to adopt 2019 and newer versions in development before they are over in production so that's also an interesting thing if I look at the top graph the top graph is everything including development what if I exclude development up top because you can see that a big chunk of our 20 of our servers coming down further a big chunk of our 2019 servers are actually in development not yet in production so let's go copy that out just to see and then come up here in the production query not a big fan of that in the production query and go run and go look at the chart and now if I look at production versions so now I have to say not like and now when I hit execute you know what I'm gonna have to do I'm gonna have to go fix the chart again go change it to chart go change to configure the chart use the first column as the label put the labels on the bottom click close I seriously hope my friends for Microsoft are using it like sit around record a video of people using a product how people actually have to use a product and it will be eye-opening it will be jaw-dropping stop working on features like trying to query Oracle and things like that get them as you can see here so now we can see the adoption rate on sequel server 2019 once we filter out development take a look at this as opposed to all the way over there sequel Server 2008 r2 take a look at 2008 so 2008 sixty-six instances out there in the wild 2019 Hey 2019 the adoption rate is beating sequel server 2008 that's fantastic that actually wasn't the case last time we I ran this thing I don't think so that's kind of cool Peter says how many people use non-development Edition for development and backwards how many people run production with development Edition licensing there are some questions Peter that you don't really want the answer to certainly I don't in terms of legal reasons I'm so now when I change the query one thing that you'll notice so what I just did was I deleted that line in the query when you change the query you also need to refresh the results down at the bottom just make file this away for when you need to do corporate fraud when you need to do corporate fraud you write the query that produces the Rosi results that you want hit play you get the nice execution down the results down here at the bottom that makes it look like your company's making money then you go up here and you fix this so that the query is correct that it doesn't reflect your fraud click Save and it looks like the query results actually reflect your desired reality not necessarily the sad truth that your company is worth a dollar and forty five so let's hit play on here so that we have the accurate results all right so now we have that it's back to normal back to the way that it was we have what versions are people using in development okay that's cool I'm gonna click file save so that I can save this thing that means that I'm gonna have to reset my connection again the next query that I run that's fine I'm fine with that I'm not really fine with that what's the next one how much data do servers have how much data do servers have and this is kind of it so the way that I group this out I grouped it out by what I tend to talk customers through like roughly hundred gig chunks and these chunks were the only easy way that I could get it into Excel to show that the negative two are the from zero to 25 gigs is a huge chunk of the population I'm gonna have to do this same kind of grouping now manually in my query instead of having Excel do it and I'm totally okay with that I should have been doing that anyway Eamonn says good lesson for faking reports my pleasure you come for one thing and you get something totally different so how much data do servers have here and notice that it says in production only so I need to add that filter inside there so let's go figure out how we're gonna run that over on my original report so over on the original report the size of the files is here now I should stop for a second and say that I'm only looking at the sizes of the file so I'm not looking at the contents in them I'm not looking at how much data people have inside their files only because it would take longer this at least gets me a fast sketch out of what the sir what the population looks like if some poor fool has a tenth era by file and no data in it that's gonna be interesting as well goodmorning crazy tech good to see you so let's copy that out we'll take this out and paste it well actually I need to copy paste the code from my code block instead so let's copy this and what did we call the original title we called it how much data do servers have so how much data do servers have and this put a note only reflects data file sizes not contents and I'll type more stuff out there in terms of the note I'm basically gonna take the things that were in the blog post and go copy paste them over into here instead then I'm gonna go add my code block and I will paste in the earlier code block that we had here now I don't need the version stuff I can rip the version pieces completely out cuz I don't need the sequel server version at all I don't need to someone as servers and good to see you as well Nora Nora I'm gonna say not like developer because we're specifically talking about production here then let's go back over to get our size query copy because it matters and then paste it in here so how am I gonna make this work so this used to be a sub query and I would kind of rather not make it a sub query how do I do that well one way I like doing that is are lots of ways to do this we'll say oh I want it rounded oh this is gonna be tricky Oh d-dave says good cap where can i buy it's a good company cap actually you can buy them from good company barbecue in Houston Texas so what am I gonna have to do here I think I'm really gonna have to rewrite this query from scratch so I'm getting oh it's a it's a sum though this is interesting all right so this might take a minute in order to rewrite I'm gonna leave this one and go on to another one only to show y'all a different example just because I'm watching that's good I have to do it anyway let's go let's go for it so what I have to do here guitar ultimate oh very cool so what do I have to do here is I have to get the list of servers I don't think I need user at all I don't need server property I don't need so that's good and now I'm gonna join sis DMOS performance counters and now you start to see how knowledge about sequel server and the DMV's starts to help you here so now I here are my filters copy on so we'll say on VCS connection I do love the the intellisense that's even just built into here just inside Azure data studio without having to install anything else you get pieces he says I don't think they ship to Brazil I don't think I ship period it's a restaurant Oscar thank you welcome to the club appreciate it so so here I love the autocomplete that they have built into here this is good this is nice it shows I'm gonna join based on these two columns here my connection IDs that's the thing that makes things join well so then I'm also joining on all these columns because I specifically only want the user database data sizes so I am going to need crazy tech well crazy tech that's not really related to today's topic so I'm gonna hold off on that and just focus on the stuff that I'm doing today sometimes I do open question answers I'm not doing that today though so let's get the song so we get our some Philip thank you I appreciate it very cool so let's go get our some here and this will give us we'll call this as total data file size to do and then that gets me oh and GB I'll call it GB just to make the measurement really clear then the I might also get the collection ID BCS collection ID connection ID because that is the unique row for one specific server so what I'm basically doing here is grouping together which servers have how much data good morning Boca ratone probably a beautiful day down there Robin now group by one let's be good let's name this out VCS connection ID and I'm not gonna order by I am gonna say okay so here's here's a query that I want I think this gives me everything that I need now what I'm gonna need to do is I'm gonna need to put them into buckets based on size but what I'll do is I'll just go ahead and run this I'll just go ahead execute on this to go see how this goes so execute missing from clause okay great yep that would be a problem oh it turns out that I do need the email because I want to exclude my own servers because I upload my own servers as well so let's go get that join that we had in our earlier there's that little fella copy and cuz I don't think collection server has let's go see if it has email it might actually have email it does not have email okay oh thanks krazee technocracy ate it so we'll also do a join up here to registered user I try to be good and I know Ritchie isn't happy with this I try to be good and always put inner join on my joins I know he isn't happy with that but I just like to be explicit when I'm doing demos for y'all all right so let's try it now missing from table for collection props ah that's fair I don't have the properties okay that's cool let's go get that and properties I didn't think that I needed the server properties but it turns out that I do and now let's run it again run there we go okay so now the query is often running I actually expect that one to take some amount of time because here's the deal there are hundreds of millions of rows over in that perfmon counter table and I didn't tune this query you saw me run the other one and it runs in less than three seconds when I run this whole entire query but I tuned the bejesus out of this one so because of that I expect this this to take a little bit longer and sure enough it does alright so now we have thirty five hundred and fifty one rows now let's stop for a second here's 3551 rows now that's different than the 3800 rows that we started with but the reason behind that is we're excluding the development Edition boxes now now we're just only talking about the user databases on production servers so the numbers going to end up being lower here believe it or not some people have servers with no data files on them they just have sequel server sitting around there that they either haven't implemented yet they're sitting in a staging type setup etc okay so now I have their total get data file size and some of you are probably gonna be interested to know who's the largest so I'm going to go order by two descending just so that y'all can see it I'm not going to have that as part of the the final finished product but now you can see just in terms of what's out there there's 25 terabytes 18 terabytes 17 terabytes sequel server can go pretty large I've seen sequel servers much larger than that too as well but it gives you a feel for what's out there in constant care so this gives me the server's individually now I want to lump them into groups now I want to categorize them so I could use a case statement again for this it's probably not the most elegant is somebody who's really smart like a nit sick been gone or an eric darling would probably come up with a derived table with like min and Max ranges I'm gonna be that fancy I'm just gonna do a case statement because I'm not that bright and also my coffee shop opens in 45 minutes case do do to do to do oh you know instead of doing the round so so I might well let's try it case round some case round some when less than equal to 25 then 0 to 25 and let's see we'll call this ho this is gonna present a problem else other as sighs category so let's see how this works execute syntax error less than or in direct case this when that so evidently I don't understand how case syntax works over in state in Postgres case when let's do this when this less than or equal to 25 then this now you try that in tax syntax near near as end there we go so that works there and then hit execute yeah it's just you Mac Genie syntax near at what the oh as end you Maura as execute 14th tries to charm and there we go so now I have my size categories inside there and of course because I'm ordering by 5 descending now I need to go get the list of categories that I have so let's go back over to my blog post I'm going to put that on the right hand side and then I'm gonna put I did not want to do that I'm gonna go put as your data studio over on the left and my size picture over on the right just so that we can go see it a little bit more easily there I'll move it up so that you can see it above my head now let's go write our case statement over here so I really don't want to copy/paste this like 40 times I really want just a simpler case statement with let's try one other thing case cut when let's see if this works and then executes it sequel dev DBA says is in tile available I'll let you go ahead and hit the documentation for Postgres if you want I'm gonna keep the exact same categories that I had from last last quarter just so that I'm have things nice and exactly identical to the last quarters blog because what I'm gonna do is I'm going to show how things change over time once we hit one year of these blog posts directly well let's go hit the documentation for Postgres on the case statement so Postgres case statement let's go see how that works documentation and then over here yeah crazy techy up exactly and i rather than reintroduce the same topic if you want to watch the beginning of the stream go back and catch up on the beginning of the stream just cuz we're like an hour and a half in okay so we have let's go so there's case maybe case maybe they don't have a case vent at case went out when oh that's what i want the following is a specialized variant case when this else this then okay so that's what i'm doing here case this round whens 25 that looks like exactly what i want but it's giving me case a when one that looks like exactly what I'm doing here but it's giving me that oh maybe it doesn't let me do less than or equal to maybe it only does specific values oh that sucks that's miserable oh yeah sequel dev DBA says can't use greater than a lesson Postgres you suck um so alright kind of hell comical database is this I love sequel Thurber less than or equal to 25 alright so in that case we'll just now let's go back over to our original sized pieces and we'll say looks like I said less than 25 so we'll do that when less than equal to 125 then 25 to 125 we'll call this less than well actually I'll come back to why I why I'm gonna do that a little weird you're gonna see that the sorting on this is going to be a little weird when I go to sort it 125 to 225 let's just paste this down a few times so 225 325 for 25 5 6 7 & 8 25 nine twenty five ten twenty five and then else will be enormous greater than 10 25 so now I got to come back over here and do this piece 1 2 3 4 5 6 7 8 9 and then and maloi says case when yep remember I said we tried you tried that and it didn't seem to work but if not go watch the recordings and oh you're nailing this oh you're getting me on this piece there it's always tough to understand what people mean when they just put in two words they're like elephant zebra I'm like what is there is there one behind me no elephant zebra I'm like Jammu so we got that now let's go to 25 3 4 5 six seven eight nine ten okay so what's gonna suck about this when I go to execute this let's make this big so that we can see it again better so what's gonna suck about this when I go to sort it is that it's not gonna sort correctly it's gonna it's gonna you're and if I group these together they're not gonna store correctly especially if I want to chart them but let's go give it a shot and see what happens now I no longer need VCS connection ID I no longer need data file size gigabytes dead DBA says when oh okay okay yep good catch good catch and this equal def DBA thank you for typing out exactly what you're trying to say there that's magical and I wonder if all shift works in here it does not no it does not well it works in terribly the opposite way that I wanted it to started duplicating lines thank you welcome to the club wow I appreciate it so let's give that a shot let's save this file save and then let's execute it and let's go see what happens I forgot to do a group buy so let's stop there and then I'm gonna do a group buy inside there and then say someone as servers so group buy oh oh oh I can group by VCS connection ID okay no okay I can do this alright this is the first piece is that we're gonna say this will give me the number of rows group by one I don't need an order by anymore so and I might as well get the connection ID let's go grab that the bcs connection ID all right so let's guess so that i think oh that looks pretty good let's go execute that so now what this is gonna give me is it gives me all of the connections and what size tier they're in now i got a group them together for ease of use for me i'm just gonna use a CTE for that I'm going to say with individual servers as so here's my CTE I should really tab that in because that'll come in handy later I'll reuse that for other stuff select size what I call it science category size category someone as servers from individual servers group by size category now order by order by size category and this is going to be the part that sucks so when I go to run this the ordering by this I don't expect to look right yeah look at that order by there oh so this is gonna suck because I really want this organized by the size that it is like zero to 25 125 and so forth so they're probably a lot of smart ways to do that I am NOT a very smart man especially when I look at the time and I'm trying to get out of here in half an hour and have this whole blog post completely done or the data at least for the blog post so what I'm gonna do is something that's gonna uh turley horrify you and I just don't care cuz though I'm gonna overwrite all my work I'm gonna copy this out I'm gonna give myself a little bit of space so that y'all understand what I'm doing and I'm gonna say 101 of you a sequel dev DBA said alt hold down alt shift and the click and it might work yes now I can go back and say 3 4 5 6 7 8 9 10 11 nan size category ID and we'll call this one 12 so now we're gonna order by group by size category size category ID order by size category ID yes sequel devs has all kinds of other ideas I'm remember I said I'm just ripping through as fast as I can in order to do this I still got like half a dozen more queries to go I won't be able to do it fast enough in order to show you all this stuff so here we have it so now I can go through and turn this into a chart click configure chart use the first column as a label put the label on the bottom put the lotion on its skin so now I have just a rough distribution here and this will be good enough I suppose also when I ship the blog post I will ship it in both length of column Matt mark I'll let you experiment with that I'm not gonna go into that No thank you for the suggestion No so here this gives me everything that I need for that one ship it on we go so let's go see what the next query is set file save and then let's go look at the next query that we have to go right so let's see we looked at data size for production only is Enterprise Edition normal for larger databases I'm not going to run that query again here I'm gonna leave that I'm not it's not a piece of analysis that I was interested in after the first couple the numbers were the same in terms of this analysis versus other analysis so I'm gonna skip that this one's interesting to me though how much Hardware do production servers get let's see if this is changing in overtime so the number of CPU core for production servers let's go see so the place that you get cores out of well there are there a few ways to get them but the place I'm getting it from assist DMOS schedulers sysd MOS schedulers is the number of cpu cores that sequel server has lit up you might be on a box it has 48 cores but due to affinity masking maybe you've set it to 20 or you have a sequel server licensing problem so here I'm counting up the cores that have a status of visible online or visible offline like something broke in terms of the licensing or your affinity masking so let's go copy/paste out the last query that we did we'll say in terms of add code oh I didn't want to add code I want to add text so how much Hardware do production servers get and then let's go copy out that last query and go put it in down here now the last query we were joining out to system OS performance counters here we're going to join out to cysts the MLS schedulers welcome to the club shaaka pick Chaka Chaka Khan and hi Brock tar as well so sis DMOS schedulers let's go grab that sis DMOS schedulers and I'll call this CPU on VCS connection ID equals CPU connection ID and then there's a filter that I need on there to say that CPU status is online or offline either of those two mean that the core is in the the CPUs are in the box so there goes that now I have the CPUs the reason why I want to copy the original the query just before this is because this is also a grouping kind of query that they're gonna be lots of rows in sis DMOS schedulers for every single one sequel server so now let's go see the grouping on this guy what's the grouping on this particular post let's go see so this one was this time I'll just move this around just to make this a little easier move Hugh over here so there we go there's our CPU cores now let's move you over here so let's see here for this one I'm gonna say case when some one because some what there's just gonna be one row for each individual CPU scheduler case when someone is less than or equal to four then one to four copy uh-oh copy this time let's timeless murder a case when someone is less than or equal to eight and then five to eight and now I'm gonna copy paste that a bunch of times so we have was just eight dumped a bunch of those inside there so it looks like I went 12 16 12 16 20 24 28 32 and then else is greater than 33 greater than should be actually that's greater than or equal to 33 then this is 1 2 4 5 to 8 9 to 12 13 to 16 News the loneliest number than chubu 21 to 24 my grandpa always used to sing just whenever he was puttering around the house and I always thought it was so endearing and then when he passed away I realized that I was always doing the same thing then and which also as a result means that when I walk around I hum things that he did which I've never even heard the original of and then I also hum things that just don't even make sense like I hung I am Christmas songs all the time when I'm just walking around the house it's beginning to look a lot like Christmas everywhere you go which is idiotic because I'm in San Diego never looks like Christmas here I'll get rid of that so then three well I hate it when that happens then three four five six seven eight and then nine as sighs category ID then I don't even think I have to change anything else about that sir about that query that's kind of baller let's go hit execute and then let's watch and see Oh cuz it's not PC data where the hell'd at peace oh you left a whole range of queries inside there got that okay wonder why the query was so long and now I don't need a trailing comma okay that's good all right will it compile all right beautiful so now we can see how much server how many CPU cores these things get now for in the sense of graphing I should probably instead of calling a sighs category I should probably call it as number or CPU cores okay so there's that I love that of course I got to rerun this in order to get the accurate results down inside there then I can click on the graph I can click on configure chart I can hire a subcontractor to click these same things for me every time Richie this is why Richie's like probably cringes every time he hears me start coding okay beautiful so now I have this this is Oh cuz there's a watch what happens when I try to move around there it's like the world there's one there's one server Colin says your old graphs were showing a percent yes what I'm gonna have to do is I want to give people two versions of this I want to give them the percent and I want to give them the raw number so what I'm gonna do is I'm gonna come back through these queries and I'm gonna go give the table version of it they'll also I'm gonna add a percentage column in for each of these because realistically it's going to take me longer than two hours in order to pull this off all right stop over here for a second Oh oh man it's getting light over into here I'm gonna have to close out some of the background here so that y'all can actually see me so we are coming in to the closer to the end of our broadcast so I'm gonna take a quick bio break here and go refuel my water in before I go take another shot inside here bill Ramos says I'm feeling pretty inspired the compatibility of Postgres and teas equals pretty good I just did something like this with VMware yeah I'm amazed by how much data base knowledge carries over from one platform to another that they're really highly similar in the places where they're different it's not different although when you think about things like stored procedures and Postgres functions and Postgres those are dramatically different but this is why they're so close in the beginning and they have so many edge cases where they're different that that's why people think oh I'm just gonna forklift my application and move it over to this other platform and you might as well just crumple it up into a ball and rewrite the whole thing because it's they're just not that compatible where you could forklift upgrade and move across pin all says does the stream deck help in situations like formatting the charts when you're changing the formatting yeah I could put together a set of hotkeys if the if Postgres accepted hotkeys for each of those but I can almost guarantee you that they don't that I would have to have some kind of app that would move the mouse around to move and re highlight things and just recording a macro like that it's just powerful or just painful what else bill says I cheated with power bi I know I like power bi a lot it's and I wanted to show people like a different user interface like I've done Excel a lot in the past for these power bi would work fine here it's just that I wouldn't be showing people how to query you're not really querying and power bi you're just like a manager that just drags and drops things around okay so I'm gonna stop here for a brief bio break before I do I want to say thank you to this week's sponsor so this week's sponsor is sequel grease sequel grease is a totally free monitoring tool they have paid versions too but you can get started with the free version right now they have a there repository' up in the clouds so you don't have to install any big thick repository down on premises you just install their collector and it sends data up continuously to them does things like extended events monitoring they even have machine learning these days looking for unusual query workloads and they'll send you emails the free version you can install at any time and it collects data for seven days so it's really good for when you're trying to troubleshoot some performance issue then you just need to go back to their site when you want to re-up it again for a period of time so you can go get that over at sequel grease com totally free thanks to them for sponsoring this week's web cast I'm gonna now go hop out go grab a refuel water and we'll do the last sprint to 15 minutes go see if we can tackle one more query so I'll see you back here in 15 minutes [Music] and I think I might have said I'll see you back here in 15 minutes I'll see you back here in five minutes and then we'll work the last 15 [Music] [Music] [Music] [Music] [Music] you [Music] [Music] all right so over the break Mac Jeannie goes hey could you do this query completely differently sure but I didn't stream lab says I okay at school spare d'lai says it looks like as your data studio is behind oh I'm such a jerk spare delay says it looks like as your data Studios built on are running in Visual Studio code you are correct very good eyes it's an electron application that shares a lot with Visual Studio code and they inherit a lot of the goodness from Visual Studio code as vs code does updates but um I'm curious to see how they spin that going forward because with Azure data studio has a lot of overlap with Visual Studio code so I don't I'm kind of surprised and yeah Joris Ritchie says that visual date the Visual Studio code is greater than Azure data studio Visual Studio code has more stuff and it's generally newer for that Azure data studio is more around just database developers people who are just doing T sequel Postgres my sequel whatever I agree that Azure data studio is a horrible name is like Microsoft just the marketing people or morons are just idiots you're just slathering the Azure name on everything so it results in this mass confusion with people are like oh my only supposed to use Azure data studio if I'm querying in Azure and like no it has nothing sip zilch zero to do with Azure nothing I mean they might as well call a SharePoint data studio or dynamics data studio or cat but whole data studio it has nothing to do with a or whatsoever this is also how I get in trouble Rodman says Brent I have sequel server 2014 and my main sub software's provider only supports 2016 do you recommend upgrade to 2019 and play with the compatibility level no only go with what the version that your vendor supports because if you go with anything other than what the versions that their vendor supports they will pin the blame on you look I don't have a lot of viewers I need all the viewers that you can get and if you get blamed for that you're gonna lose your job and then you're gonna have a lot of time to watch twitch you should go ahead and just upgrade to sequel server 2019 whatever you feel like playing with him you don't bother doing backups or check to be you can't find another job node it's in all seriousness I would just only go with the versions that your vendor supports which they usually just don't have enough time to test on newer versions you don't want to catch the blame if something goes wrong okay so we'll do one more query hmm I love the writing on this you have the same amount of hours in the day as Beyonce so I only do have so much hours in the day I only got like 10 minutes before my coffee shop opens downstairs so and I love you all but not as much as I like my coffee shop downstairs so now let's look at the next query that was in our blog post so let's say oh that's it is that oh no no there were more okay oh we got more Oh memory okay how much memory do servers get um so memory it's gonna be the same exact thing that I did just a second ago with CPU cores except for memory I don't have to sum it up there's just one row in I want to say assist DMOS memory nodes let's go see what and where we're getting the memory from and then we will go and put the groups in together and play here in a second we'll move this around just so I can have it up there and there's memory okay cool so now where was I getting memory from before target server memory oh this is tricky okay so what did I say I said server OS memory oh so I only did server OS memory I didn't do target interesting okay so let's have a quick conversation here so there are a couple of different counters used for memory for sequel server target memory is how much sequel server is willing to use like sequel services well I'm willing to use up to 64 gigs normally under most situations total memory matches that total memory is how much memory sequel server is actually using so sequel I'd like to use 64 gigs all right let's use 64 gigs but there are cases let's say that you go and buy a big ginormous by a big beefy 1 terabyte server and you've only got like 50 gigs worth of data sequel server I'll say I'm willing to use up to a terabyte worth of RAM but I'm only using like a hundred 50 gigs so target can potentially be much higher target is how much memory sequel server is willing to use not necessarily how much the OS has because the OS may have more than that but you might have capped Mac's memory at a lower number this number target shouldn't be higher than your Mac server memory setting permanently it will be briefly like if all of a sudden you set max memory down way lower there may be very brief periods where target is still high before target readjusts but otherwise you can think of target memory as how much memory sequel server is willing to use I may have other things inside here yeah looks like sis DMOS memory I also have and that is how much memory the box has you might have multiple instances installed on the same box so I did in the chart I did how much memory the server OS has which is this number right here OS total physical memory I don't need any grouping inside here because there's only one row in sis DMOS memory so that makes this query a little bit easier let's go back over to our notebook now if we don't need all this crazy well we do need actually the grouping we do need the grouping cuz we're gonna over in the blog post we're gonna group things together by how much memory these guys have so let's go ahead and copy this thing out and we'll say for next text how much memory does the server OS have and then we'll add another piece of code and paste that in here now for this one instead of going from system or schedulers now I'm going to sis DM OS memory nodes was it it's not nodes that I'm getting it from is it no it's this DM OS memory I don't know I always make that noise whenever I copy/paste would you OS on VCS connection ID equals OS that's the wrong one LS connection ID there's that one yep now we don't want developer servers that's absolutely perfect now instead of someone now I'm gonna be getting what's the column that I'm using for memory it is this guy right here Oh sequel def DBA just bought t-shirts and whatnot I have t-shirts caps hoodies all stickers all kinds of stuff available on my twitch channel so if you search for brento zart which you'll see that's where you can buy all my stuff out and I don't I don't make any money on it at all it's just cuz I always got the questions of where where can I go get the cap that you have for my usually I wear my company cap on stuff like this but so I let you all go buy it and I don't make any money on it now I eat bogeys I'll give you that answer after I finish this query I'll go through and write this query and then we'll go I answer that one so copy and then so now where am I getting this that case when do to do to do paste so here is this gives me the gigabytes I don't really need to divide it that much yeah I'm gonna though why not screw it so now 0 is 0 to 15 so 15 then v 0 to 15 now I need a bunch of these wins and let's set this one up to be a little bit easier on the copy pasting so then we're gonna say just delete all this and then Kyle actually will delete this too just to make it easier for copy pasting and copy so we got a bunch of categories so the next ones are 31:47 63 who the hell came up with these category 95 111 and some of you asked why I didn't do like end tile or just break them up into percentages the reason why is I want to stick with the exact same categories that I've been using so far for about the last six months nine months so that I can be consistent when I start trending stuff flying over it you're a year over a year and as greater than 192 khalid says will multi instance installations be counted multiple times yes so each instance will have its own collection server row whoopsy its own collection server row so if you have ten servers one server that has ten instances on it it'll report whatever the total number is for each of those and it's okay if people do instant stacking I'm totally I mean I hate it from a personal level but people do do it out there or they'll have multi instance clusters that failover from place to place another metric to look at would be the target and total memory counters that would be the per instance number okay so this one is 16 to 31 this one is 32 to 47 this one is 64 to 79 this one is 80 to 95 does it man you know to when I publish this blog post people are gonna go they're gonna be giving me so much poop in the comments about the code that I have an azure data studio they're gonna be like your queries are terrible that's what it is that's what it's like whenever you ship open source stuff is that you you think people are gonna really criticize you in the comments they think that that you think that they're not going to give you props for giving back to the community even the reality I screwed up one of these columns 32 to 47 65 you dirty little okay no no that's cool I'm gonna change this it's easier to change one of these it's easier to change this to 48 to 63 and then change this guy to 7995 111 127 4359 75 90 to delete you so you think that people are gonna criticize you for the quality of your work and in reality people are just thankful to get stuff like this for free they really appreciate you giving back they would never criticize you in public canta lied they always criticize you in public zero and now I can use my fancy alt shift type thing I seriously think that the less people pay for things the more the worse of a customer they are and I bet like Microsoft probably feels the same way about me complaining about Azure data studio they're like Brent's never paid us a damn penny that's not true I've actually paid Microsoft tens of thousands of dollars personally because of the Microsoft Certified Master program don't even get me started on their twelve all right so there's that I think that gives me everything that I want there in terms of grouping so now for size category this is going to be memory OS gigabytes and let's see how she goes let's hit play Diego and B says thanks I appreciate what you do thank you I appreciate that that's very cool and now I have an incorrect [Applause] Oh correction tax Ernie rise okay so what did I do here where is in correct syntax near near as sure would help if I had a line number what did I screw up so I have end one not oh no that's right there else twelve and as Alice twelve end as that's right I think that's right else greater than one ninety two there's an as down here syntax error near as Toronto oh thanks you're glad thanks Jeanne Owen I just hit plague Oh let's look no thought it maybe you had to do with me highlighting something case went round oh you what the hell you hold down shift and alt get into here delete then this shift alt delete you delete and then execute must appear in the group by I'm not grouping anything Oh in here I am okay I don't know I no longer need that I don't need a group by inside there execute so now I can chart this and of course I won't even make a joke about how I have to go click these same things every single time in order to get the right rows out in here you know now it's starting to look a little janky with the number of rows that I have it might actually make more sense just because of azure data Studios craptastic um no no that's terrible I wanted to run that doesn't even make sense date a Direction horizontal okay so that the the bars are laid out horizontally now I want vertical instead so I click configure chart data Direction vertical oh you know what I bet it's gonna be a different yeah yeah okay so it's now called horizontal bar up there alright and then use so at least now the the stuffs vaguely nearby and close why there's a line in the middle I have no idea that doesn't make any sense whatsoever okay that's fine whatever ship it so file and save and ship it and we are out of here so now hopefully at least switch back over here to okay so now at least y'all get an idea of what it's like to work with Azure data studio in the past when I built this exact same blog post I have used Excel rather than Azure data studio just cuz then Excel with pivot tables it's relatively straightforward to build something although I would say that the looks of the blog post aren't really that fantastic when I use Azure or when I use Excel so I thought well this time around I'll use Azure data studio I'll put the stuff in a notebook you can get it and I'll owe you ba thank you I appreciate it very cool so I'll distribute the blog post with this video and with the notebook along with my thoughts in there about the numbers and how the population looks different this quarter next quarter who knows we'll see if as your data studio has its pants on then one of you said where did you say one of yousa asked a question I eat bogeys ie bogey said when would you not use a sequel or relational schema for a database I'm key-value stores if you're passing in a key and you just want a value back so great example is paste the plan com it pastes the plan com we have this tool where you can paste your execution plans we don't run any relational queries against them so all you do is pass in your execution plan we give you a key back that you then paste in as a URL every time whenever you want to get your plan back you go to that URL and we give you the plan we don't do any query inside there so a key value store makes perfect sense you may not store the execution plan in the database itself maybe you just store for example the location of a file where the execution plan lips like in Amazon s3 for example but that's a great use for a non sequel type database let's everything that I wanted to teach y'all this week thanks again to securities thanks again to sequel Greece for sponsoring this week totally free monitoring tool to go get started with you can go download it start analyzing your sequel servers until people never just use your own monitoring scripts always get some kind of second opinion you want a second opinion before it's too late before you find out that you don't have backups that you have corruption all those kinds of problems so go download sequel Greece it is completely free and you can go get started and get analysis on your database server in a matter of minutes that's over its sequel Greece com so thanks everybody for hanging out with me this week and I will see you all next week adios everybody [Music] you

Channel: Brent Ozar Unlimited

Views: 1,862

Rating: 4.8367348 out of 5

Keywords:

Id: vnrsmSZiDQg

Length: 124min 14sec (7454 seconds)

Published: Sat May 30 2020