Capture SQL Server Query Execution using Extended Events

[Music] hi everyone welcome to another video from SQL mos in this tutorial I will show you how you can capture a specific query using extended events now let's say you have lot of problematic queries in your SQL Server environment but one of them is a throwaway query it's consuming a lot of resources and you want to track it you want to capture it whenever it executes so when you send a query to SQL Server apart from doing a lot of different things It prepares a signature for the query that signature is in hexadecimal format and it is called as query hash and I'm sure all of you know what query hash is and how you can extract query hash from DMVs like CIS DM exit query stats so using this query hash we will set up a extended event session and capture the specific query the problematic query now I am going to use an a example of this query which is there on the screen now this query is expensive it does a very expensive sort over a table that has millions of rows and unfortunately this expensive sort spills down into tempdb so look at this query and if you jump over to the execution plan I've already executed it because it takes quite a bit of time to execute and look at this there is an expensive sort operator going on so what I want to do is whenever this query comes to SQL Server I want to capture it and all the associated metrics with this query now if you're wondering that how can you capture a query that is spilling down to 10db I mean the sort spill to 10db if you want to capture this well I have another tutorial which I just recorded a few days ago and that tutorial is available on in the members only section in our YouTube channel so what you're watching right now is the free video the free tutorial which is available uh publicly but we also have a membersonly channel where there are lot of advanced tutorials available so switch over to the members only Channel and explore the videos or you can go to SQL mos.com and take up a premium membership so that you can watch all our Advanced tutorials you can also take a free membership and watch all the free tutorials and free webinars as well anyway you have a lot of content there uh on YouTube as well as on SQL mol.com feel free to explore so let's get back to the demo this is the problematic query there is an expensive sort which is spilling down into tempdb and you want to capture it now the query is already executed you can see the execution plan in front of you let's jump over to the next query window here where I've written a very simple select statement to join the the CDM exit query stats and the CDM exec SQL text DMF to extract the query hash now this is a DMV query stats and CDM exec SQL text is a DMF Dynamic management function which takes one parameter which is the SQL handle so when I say we we joining these two Dynamic management objects we joining them by the virtue of using cross apply which is the apply operator which means the function is going to be applied to every record coming out of query stats and how is it going to be applied using the SQL handle we also put a filter where the text the query text is something like this let's run this and let's go and execute so if you run this and execute you'll get a bunch of uh result sets there you may want to ignore all of this that you see in the beginning your query is right here this is the one that we ran select product ID actual cost row number Etc and here is the query hash so if you take this query hash copy it and put it here let's paste it here done and get the big int value from this query hash this is the B number now the query hash is stored in heximal format inside SQL server but now when you want to search on this number you got to convert this hexad decimal to B Because big in is this number this big number is what we are going to punch in into our extended event session okay so let's just copy it and put it here let me save the file so that it is available to you on SQL mos.com in the resources section right lot of our scripts are available in the resources section okay time to set up the event let's expand management let's expand extended events let's expand sessions right click let's create a new session now this is a small wizard with a few pages so let's put up the session name let's call this as capture uncore query you may give better names I'm sure so let's call it as capture query let's go over to events and you have hundreds of events here now I want to take up something related to my SQL statement so let's call this as statement just filter on statement and let's take SP statement completed and SQL statement completed right let's take these two because these two will give you lot of these metrics that you need about duration CPU time and whatnot let's take both of these on the right side and then click on configure well you can also take query plan as well but then let's avoid that for this demo because uh you got to be a bit careful when you want to capture the actual execution plan bya extended events it causes lot of overhead on the server so be careful with that now once you have taken the events let's take one of the um uh actions a couple of actions like you know these are uh Global fields that you may capture with uh uh events so we are going to scroll down let's take client host name let's take Client app name couple of important things you may want to take database name and ID or just take database name that's good enough let's keep scrolling down and well you can also take Plan handle there and there you have something called is query hash let's select query hash because you got to filter on this you may want to take session ID if that interests you and uh ENT username login name Etc lot of these fields feel free to explore them whatever interests you we have taken what we wanted and then let's jump over to the filter predicate in theil filter predicate here we are going to specify the query hash so let's choose query SQL OS query hash they you go this is our query hash equal to the value so we have taken the bant number let's paste that there okay and we are done so we are trying to put in a filter now where query hash is equal to the big number value there that we had extracted from the plan cache now let's click on okay there you go okay your session is created okay did I choose the target let's go and go to the properties again we took the events as we know okay this is done let's just go and verify the configuration the filter for our okay we took this for uh SP statement completed and SQL statement completed the predicate applies to both the events which is all good let's go and jump over to the data storage page and let's take a Target let's take something like ring buffer memory area which will keep a th000 events or so good enough for the demo let's click on okay once this is done let's right click and start the session we have started the session and let's watch live data click on live data here and now when you run the query it should show up here that's what is expected out of this demo let's go and run the demo now now run the query this is the query let's set select this and let's turn on actual execution plan fair enough let's go and execute now the square is running let's jump over to the live data let's wait we have taken statement completed so we have to wait for the execution to complete because most of the metrics would come along with the completion of the uh statement be it SP statement completed or SQL statement completed long running query let's wait for a few more seconds and hopefully this should be done and as expected this is spilling down into tempdb and that's why the query is of course taking time to execute let's wait for a few more seconds okay execution is done jump over to the execution plan and you can see yes indeed it is spilling down into tempdb the sort operation well that is not what we troubleshooting right now let's jump over to the live data and there you go the event get captur so we have SQL statement completed because this was is a ad hoc SQL query and we get all the metrics that we had asked for so we get the default payload which comes with the event itself and all the additional actions the additional Global fields that we had chosen okay so here is your statement uh The Spill details fair enough the session information that um that we had chosen this is the query hash that be filtered on and of course the CPU time the duration and everything all the all the important metrics that might matter to you all right friends good enough hopefully you like this demo and extended events is very very powerful you can do lot of different things with extended events you just need to learn how to capture the right information and of course please cut down on the noise which is apply the right filter the right predicate hope you like the demo hope you have learned something new do put a comment share the video with your friends and colleagues see you soon in another video Happy SQL if you like the content give it a thumbs up subscribe to the channel and click on the Bell icon so that you're notified on new videos most importantly visit SQL mos.com there's a lot of SQL learning resources out there video courses master classes lab kits ebooks blogs Hands-On labs and a lot more more follow us on Twitter at theate SQL MOS and myself aore bunel last but not the least do subscribe to our newsletters see you soon in another video [Music] goodbye

Channel: SQLMaestros

Views: 2,402

Keywords: sql server, microsoft sql server

Id: C8d_6Zh5-x4

Length: 11min 27sec (687 seconds)

Published: Thu Dec 21 2023