Query Tuning – sys.dm_exec_query_stats and sys.dm_exec_procedure_stats (by Amit Bansal)

hi welcome to another video from sequel mistress and today we are going to talk about query tuning and specifically I will focus on two DMVs query stats and procedure stats now it's a no-brainer that DMVs and bi DMV I mean dynamic management views and dynamic management functions without these two sets of objects it's very difficult to really even think about query tuning or performance tuning inside sequel server so this particular video that I am recording now is of course not to really get into the DMV's per se but it this video is inspired by one of the one recent interaction that I had with one of the DBS when he was a bit confused between these two DMVs so the query stats and procedure stats as the name says query stats DMV has lot of information about every single query that has run inside sequel server of course unless you have cleared the cache by dbcc free proc cache or you've restarted sequel server otherwise that information is going to live inside this DMV and procedures stats as the name says has lot of information about stored procedures now here is the key thing when you have a procedure a stored procedure there are a lot of statements inside it right and they run as a as a batch as a single stored procedure object so how will sequel server record that information let's say you have multiple select statements inside the stored procedure so the stored procedure as an object itself has a start time and an end time and the total amount of CPU the stored procedure has taken all that information will go into the into procedure stats and the every individual query that has run inside the stored procedure that information will go into query stats so this is this is the key thing that I want to explain and then when you have a slow running stored procedure the procedure itself is not really running slow there are probably statements inside the stored procedures that are causing problem and then you may want to figure out those individual statements so you will have to go to query stats so that's the kind of correlation between the two DMVs the query stats and the procedure stats I want to show that to you and of course only if your query or a workload or a stored procedure is running slow will you really have the business of trying to understand these dynamic management views and again query tuning is very very vast it's a very vast topic the lot of DMVs to work with a lot of information to collect so of course all that is not really covered in this video in this video I'm just showing you the the difference between these two procedures and a few important things on how to extract the right statement from inside the stored procedure so without any further delay let's get to the demo and understand this with with code so first things first I will turn on statistics time and IO on done and we are going to use adventureworks 2014 for the demo executes and before I run free proc cache let me show you what is the kind of output you get from query stats and procedures stats so if I do a simple select star from query stats you are going to see lot of data the sequel handle which is for each query and then the plan handle and as we move forward you have the creation time all that is okay let's look at some important columns here you can see the execution count how many times each of these statements have run and yes of course the total worker time this is the amount of time their workload the query has spent on CPU so of course this seems to be kind of high because the number of executions are more so if you divide total worker time by the total execution count you can get the average worker time and so on and so forth there's a lot of information about reads and writes etc well understanding all that is quite a bit of work and probably requires the full-day training but yes let's understand that each query here the sequel handle that you are seeing has something called a statement start offset and statement end offset and this is what I want to explain now let's go back to the code and see what does procedure stats give us so if I execute this one you will see that there are just two entries here and again you have the sequel handle and the plan handle etc for the procedure itself and relatively very less columns so you of course have the execution count and the total worker time but yeah less information about the procedure itself now what we are going to do is let me free the proc cache and when you free Pro cache there is a small myth here people think that when you free proc cache you are actually freeing the procedure cache yes that's correct you are removing all the cached plans from the system that's not a good thing to do but you have done that and what also happens is all the records the information that was stored in this DMV the query stats and the procedure stats they also go away so when I execute this and now if you look at query stats you will see absolutely nothing in the DMV and likewise for the procedure stats if I execute you're just going to see that one entry which is probably something run by the system itself so why is this important for you to know that every record in the query stats is actually a representation of a cached plan in the plan cache so if you take out the plan from the plan cache its corresponding entry in the query stats or the procedure stats will also go away so please keep that in mind so now that I have freed the proc cache I am going to create a stored procedure called sales details and look at this simple stored procedure it's just running to select statements the sales order header and getting the data from sales order detail as simple as that let's go ahead and create the stored procedure there's already an object named fair enough I already have the object is good so it does exist or demos should be perfect so let's simply alter this and you're done so this is the stored procedure now and let's free the pro cache again execute and now let's execute the stored procedure so this is a manual user stored procedure that I am executing and now after executing the stored procedure and it's done in a few seconds and you get the output from both the Select statements let's go back to the editor and now when I look in to the query stats here you have couple of entries and of course I want to figure out my select statement from the stored procedure and then you have the procedure stats also and one of one of the entries here is our stored procedure now where do you get all this information from so first I want to show you the query stats here and I am going to cross apply with a DMF called DM exec sequel text and I'm going to pass on the handle to this DMF so remember this is a dynamic management function which takes one input which is the handle so I am using this cross apply function to apply every handle to this DMF and when I execute this I am going to get my statement so now what you should be seeing here is this is interesting so look at this well this is being run for query stats okay so I am going to do this again for procedure stats but now if you look at this one here for our two statements you can see you can you can actually see that complete the proc code there so you can see create proc something and then select from sales order header and then select from sales order detail and you see two entries actually in query stats the first one is for sales order header and the second one is for sales order detail so how do you know that when you as I said this is for these two individual queries that were part of the stored procedure that we created so you got to look at the statement start offset so stay quint start offset here is the 60th the offset here is of course in bytes so you have like two bytes per character and so you are starting from the 60th position going all the way to one thirtieth position and and then you again if you can see you are ending at 1:30 and then the next select statement starts at the position of 136 ending at 2:06 so if we apply these offsets correctly in this DMV we are going to get the exact statement and this is exactly this particular query that we can run on query stats and then you can see I am doing I am trying to get this statement start offset by two because it's one two bytes per character and I do the same for start offset as well as for the end offset and then of course there is some check on minus 1 well minus 1 here means sometimes statement end offset can also have a value minus 1 which denotes the end of batch so we've got to give that filter now if I execute this we will get a more meaningful data and now you can clearly see the statement text which is I've got this extra text now select star from sales order header and select star from sales order detail so I'm extracting the correct texts out of my stored procedure well now coming back to looking at this information from stored procedure stacks so if you join stored procedure starts with sequel text and you do a cross apply what kind of information you get we're getting the complete stored procedure text here so this is the full code of the stored procedure and look at this DMV output the stood the procedure stats DMV does not have anything to do with start offset or end offset etc because that is only applicable to these queries the individual statements inside the stored proc so I hope this thing is a little clearer on start offset and end offset so this is how you need to correlate so when you are troubleshooting queries or query performance if it's a head hoc query you really don't need to bother with procedure stats but if it is and so you need to only focus on query stat but if it is a stored procedure with multiple statements like running as a batch then you need to focus on procedure stats as well as query stats now let's look at a few more things I ran the stored procedure let me just run the statement alone so if I just select this one and you see I'm just starting from s and ending at header look at the cursor positions and if I execute this now this is an ad hoc statement I've just executed it in isolation and now if I look into the start and offset and all of that stuff and execute this on query stats now you are definitely going to get these two from the previous execution of the stored procedure and then somewhere you are also going to see select star from sales order header this record again the eleventh one as as an individual query that I've had and look at its look at the start offset is zero because that was the starting position ending at 70 so this is what I mean by the individual statement and this is from the stored procedure now let's look at some of the metrics and just for the purpose of demo let me show you the metrics related to the execution of the stored procedure and the individual queries and because I have turned on statistics time and IO on let's go and look into the procedure stats first so when I look into the procedure stats you can see the total execution so total worker time for my store the first one here is about is five five one two six one so remember this is in microseconds five five one two six one and and let's so five five one two six let me just note it down somewhere so we have this is for the stored procedure so I will take this as five five five five one two six one and which means in milliseconds what do we really get about five five one that's is the accuracy up to milliseconds so that's five five one and when you look at the individual performance of the queries so if I execute sales order header let's go and look into the messages tab you get 141 milliseconds there and that's 141 so I can just easily capture this information from here and just for the purpose of demo I'll put it up here and let me just quickly comment it and sales order detail if I execute this again let's take how much time sick three one two and let's capture this information and put it here and then we will I will comment this as well and let's do some mathematics so we have these two numbers that we have which is 1 4 1 and 3 1 2 from the individual execution so if I select both of them I am getting 4 5 3 so up to the nearest second 4 5 3 and then when you look at their actual exit total worker time in the query itself so let's go and look at both these one sales order header and sales order idea so that is a record number 11 and 16 so let's go and take the 11th record here so my total worker time if I scroll down a bit is 2 3 5 8 0 2 so let me just put it down 2 3 5 8 0 2 so that's 1 2 3 5 8 0 2 and of course the the next one is a record number 16 and the total worker time for this one is 3 3 8 4 2 4 and I'm going to record at 3 3 8 4 2 4 so that that it goes 3 3 8 4 2 4 so what is our number total is 574 right there you go 574 now now you can see the the of course these were two separate executions but you can see how it matches up so you can see this is the total worker time for the stored procedure itself which is five five one and the individual queries come to again close to 500 something milliseconds so this is all like point five milliseconds is the amount of time they are spending on CPU so all these numbers do match up and what you also what is also evident from here is that we are calculating the amount of time the code is spending on CPU we are not considering the elapsed time so elapsed time is the amount of time it takes for the entire workload to be rendered in sequel server management studio so we are not you know computing that or we're not taking that into consideration we are only calculating the amount of time the workloads are spending on CPU so a quick recap to many things that I've shown here so I'll just quickly summarize I was trying to show you the query and procedure stats and of course the difference between them procedure stats captures procedure level information query stats captures individual query level information and of course all the statements that go into procedure stats are captured here in query stats and if you sum up all of them in let's say trying to compute a metric they should come closer to what you see in procedure stats overall and you by executing these statements individually in computing them I was just trying to prove that point most importantly statement start offset and end offset is important for you to find out every individual statement that is running from inside the stored procedure hope this demo was useful hope the devil was useful and you have learned something new here are a few bullet points about me in short I have been working with sequel server since 7.0 it has been more than 20 years and I still love working with the product there is a URL there sequel Miss Rose / Emmett - bun cell click on that and you can read and learn more about Who I am and what my work is here is a quick snapshot of my work and some credentials I am a Microsoft Certified Master of sequel server also Microsoft MVP for sequel server and Microsoft has also honored me with Regional Director status I do spend quite a bit of time with sequel maestro's in fact this is my day job and we have a very popular performance tuning video course a wonderful product hands-on lab learning kits and of course a popular sequel server he'll check service just visit sequel maestro's com to learn more about these offerings apart from my day job I also spend time with the community I spend my time with sequel server geeks and data platform geeks we do lot of webinars and in-person events and one of our flagship initiative is data platform summit this is an annual conference which happens in Bangalore in the month of August and you can visit DPS 10.com to learn more about this a quick word about performance tuning video course so what I did was we had a popular master class on sequel server internals troubleshooting and performance streaming which I converted into a master series with more than hundred hours of deep typed content expert level modules both in terms of breadth and the depth and it is constantly updated you can subscribe to it and watch anytime anywhere as many times and the labs you can access them via sequin wastrels hands-on lab that bitly link between /a b video course is where you can go and learn more about the video course or just go to sequel my screws comm and you can explore the video course all the links are available in the YouTube video description a quick word about the annual conference as well if you will see this about Microsoft Data Platform as your artificial intelligence Big Data cloud and of course including sequel server do not miss this conference this is one of its kind world's best Microsoft product engineers MVPs and MCM s come from more than 20 countries and they assemble in Bangalore in the month of August and deliver more than hundred sessions across three days and we also have full bake classes called Data Platform summit pre cons so this is a place where you should be visit DPS 10.com to learn more about DPS here is a quicker organizational structure of a dominar sequel mistress is something that I spoke about this is on the community front and there are other units expand SM ERP which is an award-winning ERP solution from a dominar and our corporate training unit people were India call-to-action you can subscribe to this YouTube channel of course you should do that you can follow sequel maestro's on facebook twitter linkedin it's just sequel maestro's and you can follow me on twitter a underscore bundle and you can follow me on Facebook and LinkedIn as well the URLs are up there on the slide with this thank you very much hope this video was worth your time see you soon in another video

Channel: SQLMaestros

Views: 2,759

Rating: 4.9365077 out of 5

Keywords: sql server, microsoft sql server, SQL Server Query Tuning, DMV, DMF, SQL Server Internals, Monitoring, Performance Tuning, Performance Monitor, SQL Server Administration

Id: Gz2akWY2Wzg

Length: 20min 56sec (1256 seconds)

Published: Tue May 14 2019