Introducing Copilot in Azure SQL Database (Private Preview) | Data Exposed

Key Takeaways

introducing co-pilot in Azure SQL database need I say more watch the episode [Music] now hi I'm Anna Hoffman and welcome to this episode of day day spills today we have a super exciting episode around Ai and for that I'm bringing on Joe sack uh many of you might know Joe from the community but for those who don't know Joe can tell us a little bit about who you are and what you do thank you Anna it's good to see you it's been a few years since the query store hints um so yes uh my name is Joe sack uh I am a product manager on the Azure SQL database team most recently I've been working with uh data science team members and also Engineers on uh SQL co-pilot and specifically we're talking about co-pilot in Azure SQL database cool awesome and we're excited to have you on uh query hint was really exciting but co-pilot is exciting too uh so you know this is something brand new like I'm not sure when we're going to be able to release this episode but hopefully it's right around the release so folks maybe just heard about co-pilot and Azure SQL database what is it yeah so it's really two experiences that we're starting with so let's start with that Framing and you know we always have lots of different names but just think about this is a co-pilot in Azure SQL database and we're surfacing it in two areas in the portal and you you'll see on the slide first area that we're releasing this in is built into Azure co-pilot uh last November uh you may have seen the announcement Azure co-pilot was released in the portal you have this uh sidecar experience where you can ask questions about your Azure estate all across Azure and what we've done is we've built in Azure SQL skills and think of them as like DBA skills and I'll show you a few examples of it so that's the first experience and then the second experience is natural language to SQL and we've built this into the portal query editor and so I'll be showing you a demo of being able to ask natural language questions and then the co-pilot generating tsql so uh I can dive in uh let's let's go ahead and look at the first experience um the first thing we're going to show is uh launching Azure co-pilot integration so you see I'm in the Azure database blade I click the co-pilot button and I'm just going to say my database is slow so let's just keep it uh nice and simple I have a slow database co-pilot looks behind the scenes says yes there were actually 41 minutes where CPU was higher than 80% and it actually sees that there's a specific query that seems to be driving that CPU so you see the query here and then we look at query store in the background so before it was DMVs now it's query store and we see for the last 2 hours this thing has been executed almost $25,000 times and based on these observations co-pilot recommends we try to troubleshoot the high CPU query now following this you'll see a couple of different suggestions so uh looking at what's driving CPU maybe missing indexes any changes into workload but I can also just ask so I could say hey how what can I do to improve that query and notice I use that query I didn't have to restate what the query was so keep the context and co-pilot says yes for that hash we see an index would be helpful on list price for the property's table and here's the index that we recommend and then the estimated percentage we think that we could help with along with some helpful links like if you're a new DBA some links to learn about missing indexes and about index tuning in general we also looked at some anti- patterns for example does that query have any association with a a large in list for example and we didn't find any and then you can also click here at the end of any chat Exchange look at the queries we might have run in the background co-pilot ran to answer this question so I just click the link we see a missing index CTE definition I can run it in the query editor I can copy it out I can modify it uh but we just show those queries along the way at any moment in time uh in your interaction and discussion wow that is super cool Bob or sorry Joe I got it is like a bob In The Box by the way that's I was thinking I was like man I feel like I'm just working with an expert but right in the Azure portal seems super cool it seems like especially if you I mean for folks who are experts but even folks who aren't experts like a great way to quickly debug and figure out what is the issue happening in my Azure SQL database right now um of course like one question I have to ask before we get into these skills is like what's happening with my data when I'm asking co-pilot these things like I feel like maybe as a user I might be a little bit concerned like is my cut company data going to Azure co-pilot and being stored and training or what's happening yeah super important question so first of all we do not train Azure open AI models with your data with your prompts with the responses so your data is your data so that's the first thing second thing is that co-pilot operates under your permissions so if you don't have permissions to see things nor will co-pilot um now in terms of the actual feedback um we look at interactions we'll store did you give it a thumbs up thumbs down if you choose to share your prompts we may use that to improve the product but you can also choose not to do that and so we'll just look at what your overall response was to the various co-pilot questions this is also why it's so important in the early days when we do this early dated preview because we want to have pretty tight interactions with customers as we collect this this feedback yeah absolutely makes a ton of sense and I also really appreciate the transparency at the end of the result that kind of says like hey if you want to know what query we were using here's what we were using so definitely yeah yeah so um with that said just a little bit more on on Azure co-pilot integration you'll see on the slide there's a a lot of different skills this isn't the full list I think I I counted on the spreadsheet we're using to to check the various skills there's more than 40 skills that we're going to gated preview with um and some of these are are like act active user connections for example you might think well I have a query that I could use to run that but you'll find with a lot of the responses from the co-pilot it enriches it so it may give more context and sometimes it even asks questions that you should maybe be asking but you didn't and it adds it and so it's pretty magical in a lot of different ways so okay natural language to SQL this is the second experience I was telling you about in the portal query editor and um for those who don't use the portal query editor it's really just for light browsing light querying a lot of people still use SS SMS and ads and uh but we do get a lot of traffic to the portal query editor as well we added a place to ask a natural language question and then what we'll do is translate it to tsql and we'll use the context again based on your own permissions of table name view name column names primary key and foreign key relationships now um how does it uh actually operate behind the scenes it similar to co-pilot we're using large language uh uh models behind the scenes so uh another demo here uh you're seeing the query editor you're going to see a new launch inline co-pilot button and you're going to see a new box where you can put in your prompt and so I'm asking a question show me all agents who have listed more than two properties for sale and then we have this option to filter down the actual tables of views for consideration for answering your question I'll leave them all checked but you could do that if there's any ambiguity or overlap and then you have this generate query button and this will generate three different outputs this will take about 3 seconds the first output is the prompt in comments to just remind you what you originally asked the second is an explanation of the query that was constructed by the co-pilot so walking through that query and then the third is the query itself which at this point you can accept you can run it as we're doing right now um or you can edit it or change the prompt another example for you is a little bit more complicated it's a pivot summary table that displays the number of properties sold in each year from 2020 to 2023 I could not do this by hand I've been working with SQL since 1997 can't do it by hand this generates it in a few seconds again we'll show The Prompt we'll show the explanation um within the select you'll see this select year showing a count of property sold by year and then you'll see the pivot section which determines what are the different column names 2020 through 2023 at this point I can modify it or decide to run it and sure enough we have a account of properties by uh by year so that's the natural language to SQL experience awesome wow that is so cool you know it reminds me of some other kind of autocomplete natural language type of uh experience I've had however like this actually has the context like it seems like it knows the names of the columns and it knows the value so makes it a lot better right that's right yep so it'll use the the metadata it won't use actual data values but it'll use the column names table names uh view names uh and then also primary key and foreign key and that's just the start we think that there may be more that we'll want to add for context to help with more complicated schemas uh but we wanted to get this out here soon so people could start testing cool awesome I see some FAQs here actually these were all of the things on my mind so great I'll go through them yeah so so you you alluded to this a little bit earlier just whether we're training models with this which the answer is no but also we talked a little bit about the permissions already so um How does the to get used uh and we ground the responses in your environment but it's only those resources that you have permissions to in the context and actions you would be able to perform uh how much will it cost during preview there'll be no cost and then for this initial offering it's across the Azure SQL database so that means general purpose business critical hyperscale vcor DTU uh serverless elastic pools and then the next question which is very popular which is okay what about everything else and of course this will lend itself to so many different areas of SQL so just watch this space we know co-pilots will be useful across the full portfolio so we're just starting here awesome cool we're really excited about this um yeah absolutely so uh as it comes to tips and tricks maybe the right tip and trick is how to sign up since this is gated yes yes so this is a very uh similar to to when we had the squl server 2022 EAP we're starting slow we're going to do a limited number of and then we'll on board on a rolling basis you see the short link here SQL co-pilot signup um we're going to be just focusing making sure we have customers across a diversity of different workloads and use cases to make sure we get that feedback while there's that ability to a lot of these folks I'm going to be calling connecting with and just trying to understand um how we can focus on the three main attributes which are the quality of the answer the depth because getting that depth just right is is a fine artc and then speed of course you might be willing to wait 3 minutes for high CPU because it might have taken you a few hours but maybe you're not willing to wait 10 minutes so we're just trying to calibrate the different speeds across each of the skills awesome cool well Joe thanks so much for coming on the show I learned a lot I'm really excited about the new co-pilot capabilities uh I'm sure viewers are too so viewers will put a link in the description if you learn more as any as well as any other resources you might need uh if you like this episode and you like these capabilities go ahead and sign up for the private preview make sure you share all the info we need to make sure you have a good scenario that fits with the things we're trying to test leave us a comment and let us know what you think about this in general and we hope to see you next time on data [Music] exposed