Database Watcher: Your perfmon in the cloud | Data Exposed

Key Takeaways

DB Watcher is in public preview and enables you to monitor all your Azure SQL databases and Azure SQL manage instances at scale learn all about it and see a bunch of demos of how you can do it and use co-pilot this week on data [Music] exposed hi I'm Anna Hoffman and welcome to this episode of data exposed today I'm joined by Bob Ward a frequent presenter on data Expos Bob thanks so much for coming back great to be back here at dat Expos Dan awesome it's always great to have you today we're talking about something really exciting because it's something that is in public preview something I don't know a ton about something that we've been working on in various forums for years and that is DB Watcher so let's get right into it tell us Bob what is DB Watcher and why do you like it well first of all you see the screen here I call it my perf one in the cloud because I've been working Cloud Technologies for a while and we do have some solutions for you but we don't have really what I get from like perfm on Windows servers for example which I really love been using for many years so I kind of think of database Watchers maybe being able to do that well it's super exciting though is being able to do this across deployments across managed distances across databases that kind of type Technologies we've been kind of hurting for a while people have been wanting that and if you want to get started by the way on right away go to akms DB Watcher public preview you can just start it right away but what I thought I'd do is you know I want to show you a little bit about more about what this really is and what it can do for an outworks yeah awesome let's get right into it okay so first of all it's a fully managed service it's an Azure service this is interesting part you don't really pay for the service it's actually collecting data from your existing Azure SQL managed instance and database State and storing it in a custo what's called a custo database that's what you pay for although you can actually use a free one right now go do this so it is such a comprehensive way of monitoring your data Demitri Ferman who's a pmna you know what our team he's one of the most respected people I know at Microsoft that get SQL Technologies at a very detailed level like demitra and I get together sometimes we talk SQL head language a lot especially do performance monitoring and troubleshooting and Demitri has been the lead program manager as part of this project and it just shines it shows his thoroughness is knowledge of SQL very configurable too it's configurable about how much you collect you collect uh it's got dashboards built into the technology the Azure Dash in the Azure portal and it's almost like near real time like you can get really close to getting information up to the minute about what's going across your State uh one of the things that when Demitri and I first talked about this I'm like Demetri like what are we really going after he showed me this slide which I realize is daunting to look at but that's the beauty of this like try to go do all of this across across not just a database or a database or manage dist but across all of your Azure database instances wouldn't it be cool if I could take advantage of the existing Technologies in the engine like DMV Technologies catalog views things we've put even into the cloud as well take all that together extract out that data in an aggregated way and even detailed way and do all of this and do it in a very nice user interface dashboard type fashion and that's really the nut of what we're trying to give you with database Watcher now people have asked me that's pretty cool but really how does this thing work so consider this might be your estate you've got databases you got managed instances by the way also Imagine down the road and a virtual machine SQL servers those kind of type things but today in public preview we're starting with manag instance and add databases so the Watcher is a separate service and in this service you're going to have some sort of identity you're going to use to connect in preferably like a managed identity for example very secure way to connect to either Mi or a database now what's going to happen is you're going to set up a data store which is this custo database and and I've got something at very end to show you that not only can just use a native custo database and do like even kql queries you can even store this in fabric realtime analytics which is also using a custo store so we'll show at the very end how that might work so you set up your store and then now you set up your targets which are the databases or the instances you want to go collect data from and then you start Watcher and then Watcher will start running literally SQL queries for you against these different data sources collect the data and put them in the data store in a form that can be queriable or in the form of a dashboard or something of that nature you can even actually run in austo database SQL queries on top of these things so then you have private inputs by the way support which is really important you want to be able to have a private way of connecting to these versus having to public input so that's kind of like the picture what you're doing you're collecting data so when you start this thing you're going to use a secure way to connect into the databases get access data and then the Watchers is going to feed and stream these results and put them in the custo store and again you're going to be able to visualize the custo store either through a like a pornel or even through a query or a kql query or in the case of fabric through realtime analytics the most common way is just to view this information in the form of these dashboards these pre-built dashboards that we've done in the aure portal so this is the fundamental basics of the system a separate service collecting data from your SQL servers using what is already built into SQL today which what of the beauty of these kind of things right there such Rich Telemetry that the engine has already for you and then put this in a separate store that you can go query uh independent of your workloads which is another nice thing here you're not impacting your workloads at all there's no impact what's going on in a oh that's awesome I I assume there would be like some impact on your workload but that's great toar not because you're just querying these things that you would normally query while you're running your workloads like things like DMVs uh and we're using it in such a way that's not or query store for example and in that way you're not impacting what you already just used today and see awesome cool well this looks great I'd love to see it in action can we take a look well you know me this got to have a demo somewhere right yeah let me show it to you so I am here in the atro portal looking at a watcher service and I'm actually going after three separate databases so I've registered three targets so right out of the gate I have a way in the dashboard to say let me go look at data say in the last five minutes because I think I've got a performance prop that's one of my databases out of the gate we're going to show you something called a heat map you can here there are three databases one of them has got like 98% CPU so I can you see at the top here I can look at instant CPU IO log rights workers all sorts of Rich stuff out of the gate and that's way of looking at things across my servers or even pools uh across databases pools things of that nature so here I'm looking at my databases I can get information about uptime configuration information from them you can do that as well or this get ready for this one I can look now at Anna on my top queries across databases yes you can now look across query stores wow and notice there it has already said hey for that one it had that heat mat problem guess what I have an index suggestion so it's even going to give you suggestions for indexes as part of monitoring Watcher I bet you didn't know that we were going to pull that one you give you index recommendations right so pretty neat way out of the gate just to show you across some databases at a high level got possible perf problem it looks like a CPU problem and I'm give you an index suggestion and I haven't really done anything yet but the details that you get here this perf on the cloud go so far from that let's go look at so again I could drill into this database if I want to and then when I show you what's possible now it's going to just kind of blow me way so in the performance section I'm already going to just look at resource overall for this database historically across time under the thing about this this all about history here request weight request rates request by duration I a little histogram of that how about weight types cumulative average signals waiting tasks and then look at this common performance counters you would use in perf modon Deadlocks block processes logins timeouts you know all sorts of things temp table creation activity things that you look at at proof modon but it even goes further if you will dive into things like memory clerks which I know a lot of people that know SQL want to do that look at the rich information we're providing out of the gate for you historic Al again collecting all of this on your act sequel or manage distance this is pretty crazy and I know it's kind of mind blogging to see this right uh now it gets even better because people look at activity all the time like DMX requests so I'm going to show you over like the last five minutes what are not just my activity but potentially even blocked problems well I don't have any blocking problems at least right now uh but I can even look at historical activity why don't I have to look back and look at a DM exec request that happened like yesterday or two weeks ago you can't do this today I mean this is kind of stuff today that requires you to go collect all this yourself so pretty groundbreaking out all the history you can even do for things like your activities and your sessions and speaking of sessions what I love about this let me go like just look at histo things about sessions but let me look at sessions in terms of total sessions sessions grouped by logins sessions grouped by hosts or even applications so I can look and say oh application is causing my problem versus says I know I know I hear a bunch of O's in the background o and a I mean right this is so rich in information here here again we're looking at these top queries again that I showed you even on the outset here and if I want to I can actually drill into a specific query look at here I'm looking at even the weights for a given query that that may have been happening this case it was CPU bound here's the query text I'm going to look at CPU by type over against query plans it could be multiple query plans for this query uh and then I just keep going I'm going to look at storage information I'm going to detail into storage IO iops throughput latencies again right now I'm doing this against a specific database or how about at the table level let me go look and see for example how much space has been used across all my tables quickly find out which tables are taking up the most space either by index or my table which is are my clustered indexes my cluster column store indexes my heaps uh I know this information is so rich and so detailed uh it's incredible that out of the gate I just start this Watcher it starts collecting data and now I get information at a very very low granular level or I can go back at any history time and point that I want to go do as long as I'm storing in my custo database and I'm paying for that storage yeah look at this history at any point I want pretty crazy no this seems like I mean I'm sure people are just super excited about this I don't want to distract from your demos but what are customers thinking about this oh they're blown away so I've shown this at several different venues fact I showed it off at SQL bits back in March with Demitri we were there together in stage at SQL bits and and we just got just got red review some customer they just couldn't believe it they were like I can't believe you guys have finally built something that really gives me what I need and again we're starting with Azure SQL datab base and managed instance everybody said I want this with SQL Server too I don't want it just with the cloud but yeah again a lot of excitement out of the gate I know several people in the community Daniel Taylor is one of them has been showing this off a lot he's been a lot of excitement from it so yeah there's a lot of people we've got a lot of feedback customers using this a lot of a great usage from the public preview so far awesome how about another one okay demo yeah so Anna Do You Remember uh remember you had I think Joe sack on talking about co-pilot absolutely right so let's show you a scenario where I'm going to use co-pilot and combine it together with Watcher in a very interesting way so I'm going to start out with the co-pilot the new co-pilot in fure SQL database I call it Boba box and I and I'm going to ask co-pilot hey my database is slow I don't even know what the problem is so co-pilot is going to go look at some of the information that's been collected uh by default in the database and say you know what I think you might have a blocking problem you have no CPU going on and remember the whole thing you I talk about running versus waiting waiting scenario I've got no CPU but I've got waiting going on so co-pilot has already detected though hey I detected a blocking chain like hey this session is the one blocking everybody else in fact it shows me the details of The Blocking chain 55 blocked by 110 so was 97 one and 107 all by a given type of block resource so I know that session 110 is kind of leading this blocking train so I'm like copal hey this is interesting is it possible that this session is holding open a transaction which is one of the most common reasons to getting these blocking problems and copad you know what I've got the Telemetry for that I can take a look and a short answer back is like indeed it is so you can tell running out of the gate this session is one is holding up a transaction and that's the reason and even tells me what is the query that it's running wow right now holding open the transaction and that's what's really causing the blocking uh which is just incredible I just love this right so when I was building this demonstration I was thinking to myself um what if this was a problem that has happened over time and po pilot really can't tell me that yet but could watch her so I went in in this scenario had been running it for a while got I've been doing this over and over this demo right so I went into Watcher and I said hey Watcher you know what for this particular database let's go back and see if you can show me the blocking chain as exist as what co-pilot sent up so first of all you can see here it's showing the actual block scenario this is more details of what collent showed you more visually remember we had uh the 110 leading the blocking train here's that select stap it here are the three other sessions and what they're blocked on and you can see it's blocking a three sessions and if you scroll over to the right you can see indeed there is an open transaction so I kind of confirmed already in my mind that our AI assistant did tell me what the problem was and you I can kind of verify that I can even see what kind of applications are involved in this blocking problem but here's the thing I'm an administrator and I'm like you know what is this like a common problem I need to go tackle or one timee event so I'm now going to go to the historical blocking for Watcher and kind of change my interval and like I don't know let's go back four hours look at this I can now see the pattern like if I was a developer I'm like oh wait a minute look at this blocking problem it happens for a while then it goes away then it comes back again and look at this I can click on one of these charts and I have the details of The Blocking chain back from the chart four alums going wow there's no way you can do this today you can you C it's so hard to go collect all this data historically to get the Diagnostics or what could happened so not only could co-pilot help me solve a problem today but but then I can use a combination of that with Watcher to go back and look historically decide okay I've got a trend now to solve this more permanently versus just a short-term fix yeah it's it's really amazing Bob I think I learned a lot I'm sure our viewers did too but you know we're expanding the toolbox and making the the time to Insight and the time to action um really much much smaller than it would have been before absolutely and know there's no other way I can leave this show it's showing you one other demo I got time for one more okay let's do it okay let's do it quickly this is a separate Watcher I've set up remember I told you that data store can be stored somewhere else now a lot of you out there are fans of Microsoft Fabric and you're liking wouldn't it be cool if I could do a powerbi report on Watcher yeah you can this is me putting my Watcher database in an event house oh fabric real time analytics right so I've stored it over there I can go into fabric under event house this is the raw setup data being captured by Watcher in the custo database but I can see this through a vent house in terms of tables and if I want to I can even go look at some of these tables like weight stats and I can even query them through SQL inside fabric of vental I know that sounds crazy so I don't have to use kql I can just say use a select St and I can say show me the top 100 records that have been collected for weight stats over time and I can tell hey and have I know know anything about these weight types which is a common thing uh I know administrators do developers do but maybe I can go to powerbi well andna I am not a guy in a cute so I'm like I don't know how to build one of these things so let's use co-pilot to do it and I'll ask co-pilot in fabric suggest a report for me and it says hey I don't know you want to do how about a database wait time insights report I'm like that sounds interesting what does that thing look like and look you go lo and behold it's generated to Port off for me already I can then take that if I want to and I can actually go and modify and manipulate this and I kind of started playing around with it some I added different things to it I tweaked it and this is all being collected again in fabb real time analytics so I could set up this report to be refreshed now if I want to I can really drill in and not use the power of fabric along with information being stored from Watcher to do whatever I want to wow cool Bob this is insane like I thought there couldn't be any more and there was more I have more but I'm not I don't have time today should but I'm gon to quote you on this is insane I like that quote I'm gonna tell Demitri that's what you said you should you should thanks so much for for coming on the show personally I learned a lot to our viewers if you like this episode go ahead give it a like leave us a comment go to aka.ms DB Watcher we'll also put that in the description for you uh and we hope to see you next time on Dat to exposed [Music]