How To Troubleshoot a Slow Running Query in SQL Server Extended Events & Wait Stats (by Amit Bansal)

hello there welcome to another video if you like this video you are going to follow me on Twitter a underscore pencil or subscribe to sequel mistress com before I begin let me talk about a domino systems we do a lot of stuff around sequel and Microsoft Data Platform on the community front we have data platform geeks and sequel server geeks comm where we do a lot of free events webinars free videos and we also organize Asia's largest sequel conference which happens in Bangalore every August sequel Mastro's is another brand under a domino where we offer advanced trainings like master classes accelerators hackathons we've also built a learning platform called hands-on labs which allows you to practice a sequel concept step by step if you want me or anyone from my team to come to your premises and deliver trainings for you and your team you can contact people where India so the less stuff is all free stuff for you the middle stuff is stuff that helps us own our bread and butter let's move forward my name is Amit Bunsen and I have been working with sequel since 1997 actually I did not get any job in late 90s so I started learning sequel server and since then it has been a rollercoaster ride I started my career as a developer then I became a database administrator and since then I've been working on countless sequel projects since 2005 2006 onwards I have been focusing heavily on performance tuning and optimization and I have delivered more than 500 performance tuning assignments for 200 plus customers globally apart from my day job I also speak at global conferences like ignite path summit tech ed sequel builds and of course data platform summit whenever I get an opportunity I also found a data platform Giessen sequel server geeks.com some time back so that's the URL for you sequel maestro's calm slash a myth - bundle please log on to the website to learn more about us why are you watching this video today because you want to troubleshoot a slow running query but more precisely I am going to focus on what is your query waiting on sequel server performance tuning and credit yuning has been the hottest topic when it comes to sequel server every class every forum every customer I go to there are so many discussions around sequel server performance tuning now in most simplistic terms think about it when you send a query to stay equal server either it is waiting or it is running and if you are able to find out what it is waiting for life becomes so much easier so today I am going to show you a demo where you will see how you can capture what is your query waiting on but before I start the demo there are two concepts that I want to talk to you about first one extended events now I'm sure you have worked with profiler or sequel Trace well extended events is very similar conceptually but very powerful advanced and can do a lot of deep dive stuff think about extended events as a general event handling mechanism for sequel server it has the capability to find out what's going on inside the database engine sequel team has built extended events keeping performance in mind the way in profiler you can create traces and subscribe to events and then you can capture that data you could do exactly the same stuff but much much more advanced look at the last bullet point there you create a session subscribe to the events stored the data in external file or in memory and then analyze the data but remember you can do lot of advanced stuff and this is what the demo is all about the other concept that I want to talk about is weight statistics and weight types well extended events weight statistics weight types and queues they're pretty advanced concepts to be covered in a few minutes or a few slides but I want to give you head start here if you look at the diagram there I have divided it into three parts the first portion shows you a core let's say CPU where you have a thread running speed 60 there are a couple of threads like split 70 359 56 and 55 that are waiting on something that is called as your waiter lists and there are other threads 51 64 and you look at the last one speed 93 they are in the runnable queue every core on your hardware every CPU has this cyclic mechanism wears a thread is running a few threads are in the waiter list which are waiting and there are a few threads that are ready to run they just need to get their turn on the CPU so when a threat is waiting sequel server captures it and this is what we say it assigns a wait time to that particular thread in sequel server latest versions you have probably more than 800 different wait types I certainly do not know all the wait types but I definitely have worked with many common wait types if you look at the second diagram speed 60 while it was running it encounters a way type IO underscore completion so the sequence of a scheduler system moves that thread from the CPU to the waiter list and of course 51 is in the queue first in the queue so it follows first-in first-out mechanism so it moves to the core and it starts running and then speed 56 on the second diagram which was waiting for CX packet completes its weight type and goes and queues up in the runnable queue the final diagram is on your right corner where you see speed 51 is running speed 60 is waiting for i/o completion and speed 56 moves at the end of the queue as a runnable position so think about this cyclic model for every core on your hardware this is what weight statistics weight types and queues actually mean so in my demo now I am going to show you a workflow where I am going to set up extended events and I'm going to subscribe to wait info event of extended events and capture what is your query waiting on here is a small diagrammatic representation of what I am going to do so you have your sequel server ok and which is a very vanilla plain installation that is running I am going to set up extended events and I'm going to subscribe to an event which is called as wait underscore info now remember there is also weight underscore info underscore external I will cover that sometime again but this is an important event to capture weights that are happening inside the database engine now as a client you will send a request to sequel server and let's say your query is running at some point your query encounters a weight type and then it weights that weight type but the moment it is actually waiting on a particular weight type the weight info extended event will fire and that information is going to be captured which is the event payload and you're going to store that data in a you know let's say extended events log file or in memory and then we are going to analyze it as to what exactly was it waiting on and we'll do some mathematics on top of that so let's get started with the demo for the purpose of this demo I'm using one of my VMs here sequel 2016 let me launch the VM and expand it this VM has a plain vanilla installation of sequel server 2016 the server name is a dominar and there is an instance called sequel maestro's in the object Explorer I will navigate to management and under management you can see extended events so as you might know extended events is running by default there are a few sessions that are already created we are going to create a session for ourselves where I'm going to show you how to track weight statistics or weight type for a given query or for a session now please note this demo is going to be a very simple demo the idea is to get concepts to you and later I am going to show you some time in part to a more complicated version of the same example before I create my extended event session I will create a new query here and I'm going to use let's say adventureworks and ended adventureworks there is a table called person so let me zoom a bit so that you can watch it properly let me zoom it to 175 percent that's fine and now I'm going to say update a table called person dot person and I'm going to say set first-name equals to Emmett and I'm saying I'm I'll put on a predicate let's say we're business entity ID is equal to 1 now I will encapsulate this in transaction and I will say begin Tran and then in another query I'm not going to run this right now in another window in another session I am going to select from this particular table select star from person dot person now let's say that this is our query which is a slow running query and as you know the scenario is that this particular session speed 56 is going to block speed 55 and when I run this query I want to find out what is this query or workload waiting for so speed 55 is on which I am going to filter my extended events session so let's go and create a new session using the session wizard so I create a new session wizard move next session name let's say track weight stats that's the session name I give I move next I am NOT going to use any template I will choose the events individually and I'm going to filter on weight so I will see there is something called as weight info if you remember I talked about weight info external and I'm going to address this sometime later so I'll select weight info move next and weight info just to show it to you again weight info has these columns or actions which we call as the event payload duration operation code signal duration weight resource and the weight type so let's go ahead and take weight info let's move next we are also going to capture session ID because session ID is something on which we are going to filter will also take sequel text now these are global fields that we can capture let's move next we want to put a filter and as you know our filter is on session ID 55 to confirm that again let's go on filter session ID so let's scroll down and you will see session ID here equals 255 so this extended event session is only going to track events for session 55 let's move next I am going to store this data in let's say ring buffer for the time being so I will say ring buffer and I will just go with the default values move next and finish now do you want to start the session right now yes let's start and let's watch live data on screen as it is captured so let's close this and the event is running and the live data is coming here now what we will do is this is the query 56 that we are going to fire and as I as you see this is a transaction which is neither committed not rolled back so I execute this query now the transaction is alive and then I switch to session ID 55 and I'm going to say select star from person dot person and I am going to execute this now this is going to wait and this is waiting on a particular wait type now I know what it is but let's see what extended events has to tell us now if you go to the live data window here you don't really see anything simply because for the wait information to be captured the wait has to complete right now the wait is still not complete it's still waiting on LC kms which is lock mode shared so let's go ahead and roll back so when I roll back this particular query the wait will complete for my session ID 55 and I will get the output and then if I switch over to the live data screen for extended events I can see that there were lot of different wait types that were being captured and as you keep scrolling down keep scrolling down somewhere you will see wait type as LC kms so if you see there are a lot of different way table this is difficult to really filter out because I can I can see lot of events about 12,000 different events so we got to do some mathematics on this now so what we could do is first I would actually like to stop this live feed so I can go somewhere here and stop the day the feed so I can stop the data feed now the feed is stopped and you can see there is something called is grouping and aggregation now we need to group so when we extended events data that we captured is overwhelming there's a lot of data that we get captured if you remember I said that we will do some mathematics on this data now 12000 is really nothing you can capture whole lot of events so I am going to group on weight type so I will say take weight type from from the detailed window here right click and show column in table now when I show column in table I will also take duration and right click show column in table and I'll just tell you what I'm doing now I am going to go to this window and click on grouping and I am going to group by weight type and take this on the right hand side the columns on which I want to group is weight type click on ok and let's see there you go and you can see weight type Network IO there were lot of network I weight type there was LC kms there was a social Euler yield and there was page IO latch SH because because our query had to read data from disk so you also had page I or latch SH now this is not very helpful because you might really encounter a lot of different weight types here you want to focus on weight type that had the highest duration which means my query weighted on a particular weight type for maximum duration so I will go back and do an aggregation and I our aggregation has to be on I have name duration there and I am going to take some of the eurasian and click on ok and now i can see wow LC KMS waited about 30 1000 milliseconds which is about 31 seconds so this is where the culprit is so if I expand here I can see weight info begin and end so if I select the first one you will see opcode here begin which means the beginning of the weight and then second operation code end which is the end of the weight and I can see the duration was 31 seconds approximately here is my sequel text select star from person dot person and wait type of course is LC kms which stands for lock mode shared which means this particular query running in speed 55 waited on LC kms which is it waited to acquire shared lock resource a shared lock mode on a particular resource so in this demo you have seen how we have used extended events and weight type information to track what is the query waiting on it just gives us good hints and takes us a step closer in our endeavors to troubleshoot a slow running query hope you like the demo and it was useful to you please visit sequel mistress comm and connect with us I'm personally available on Amit Munsell dotnet my twitter handle is a underscore Bunsen I also invite you to join the largest sequel group on Facebook the URL is right there in front of you facebook.com slash groups slash the sequel geeks is equal mr. Roskam has a lot to offer you in terms of advanced training video courses and hands-on lab learning platform navigate the site explore everything that we have to offer you or if you want someone from our team to talk to you just drop in email two classes at sequel mistress comm Data Platform geeks community is something that you should be a member of membership is free as a free member with DPG community you have access to all our video resources our events our free webinars magazines learning resources lot of things that we have to offer you membership registration is is the one-time activity and is absolutely free not only the current stuff that we are doing all our past event resources are available to all our members absolutely free so please make sure you join Data Platform geeks comm and continue the learning journey thank you very much for watching this video yes and don't forget to follow me on a underscore but I'll talk to you soon

Channel: SQLMaestros

Views: 110,930

Rating: 4.7832513 out of 5

Keywords: sql server, microsoft sql server, sql server performance tuning, sql server optimization, sql server troubleshooting, wait types, sql server wait types, extended events, sql server extended events, slow running queries in sql server

Id: k55KxvjVBcY

Length: 17min 47sec (1067 seconds)

Published: Wed Nov 22 2017