SQL server 100% CPU usage fix

hello everyone my name is Yogesh and today I am recording this lecture in this lecture I will be covering one of the common issue which is faced by DB s developer and even the server management team on the production server that is the sequence of us laid into 100% CPU utilization or innate about hundred and I also consume all the memory sources which are available now this journal will only help you in this when you have checked like the sequence of is the culprit like you have done some of the common chips like there is availability of physical memory and if there is no other process which is causing issue etcetera etcetera what are the common checks which you must take so once you are sure like sequence over is culprit then only this tutorial will help you now in this tutorial I have basically I have two methods of fixing this issue one is a traditional method which you must be knowing it and you must be of you must be following it our second method which maybe you don't know but maybe you are aware of it but for the sake of this tutorial I will be cover both of the method so and also both the method are not a same or like do different approach for a common solution it is two different method applied over two different cases though our goal is same to lower the CPU usage but they are applied out different scenarios so nothing in common between two these two methods okay now before starting this let me tell you one more thing like there is a myth which most of the developer and the DBS like is this jr. DV also follow like only longer running query is a problem that is not always true because in actual longer any queries can be an problem but in spite there are other files also which can cause issue like we having maxim worker time are also problem and secondly the cuy whose a which is having maximum execution count can also cause a problem like suppose you have a Quay which is executed in 1 millisecond or 10 millisecond but the you can say the number of execution hit on the server is more than 1 within a millisecond so now at that moment your server will say oh my god what is happening okay so that could also be an possible issue of a like possible issue for the CPU utilization okay for all in this tutorial I will be using this sequel load generator tool to generate some stress on cycle this will not generate the actual production stress but it will generate stress near to the production level ok when we will be using it and how we'll be using this simple task when here to show you the CPUs it now if you will see in my CPU usage is just normal like this is just fluctuating between 6 to 34 and 40% but it is normal it is not that much big which is which can cause mice like if this will lose the production server it could cause an issue so now also I have in this tutorial I will be using Northwind database ok Northwind database I am having few one table which is having around 1 plus Quatro lack of records so what I am doing is to make it execute for a long time I have done some union to it ok now let me copy these and let me ask you more Union do it let me okay so that yeah this is not the actual production simulation but this I am showing you how it affects the system now you see the CPU usage is normal and I start executing this way now if you will see the CPU usage is getting increasing and is also not getting lowered now how you can check this one first I told you the traditional method we use this SPU to exit a stored procedure to check the process having causing the issue on the server now you will see the server is keep on like increasing the usage so now when you are using the SPU - you must be aware of one thing that you only have to look for SPID which is greater than 50 because below them below than 50 have been reserved by sequel server itself now the CPU usage is totally increasing now I see a process which is on the adventure work database and having the CPU time increased like CPU time whenever I am educating SPU to again and again the CPU time is increasing so means there is a query which is called this is a process which is continously executing and which is causing any shoe so to check the definition of this process I have to use this DP CC input buffer I just have to pass the SP ID of this and I can look into the grave now if it is a select query you can kill it if it is required because it must be used by some report or something like that but if it is an transactional quarry you must not be doing this on production by killing this process you have to wait till the time it get over after the like the transaction is done you can then look for the possible solution to minimize the response time of that transaction now if I want to kill this I will just kill this process now if you mean see the process is out okay and my CPU is you say this again low now this is the simple solution which I told you like this is the traditional method most of the DBA follow this for the Select query mu so that I'm now coming to it normal soul area okay normal scenario let me show you one thing let me select top 15 records of this table now we will see this is got executed in less than like one second okay so for you and for even me this square is not there it cannot be an issue because it is just executing within one second now what if this Quay is been hit a million time okay so we come back to the scenario later let me show you one more thing before this I just want to show you about something about execution plan and also about DMVs okay DMVs our dynamic management view which are managed by a sequence over itself whenever the query hit and they it maintains some stats and you must be aware of like there was an execution plan which you generated for the Queen so all these things and you are available to you you can look into them through this dynamic management view so in this second method we will be going into one level deeper now one more thing when the query execution time is very less you cannot catch it with this spo2 because this can only show the query which is executing for a long time because if it is getting served in one a less than one second it will not you will not be able to see that way within this with this method because the time you see and then stream you refresh it must get over like a project completed so we SP have to will not help in this interview now coming toward this dynamic execution stat this is then like views provided by sequel server which provide you the execution of other goodies now let me show you one more thing like select top to star from sales dot sales order okay now if I execute this query and I go into this one and do it by order creation date descending then I will find one Quay one execution plan okay now this is the plan handle what I do is I just copy this plan I have one more DM that is execute query plan and I pass this as in parameter to the function and I execute it I get the plan if I open it this is a plan for this select op to from Scylla sales order detail now if I close this and I execute this several number of times okay now if I go here and I check it again it will not generate new execution plan instead it has used the same execution plan increase the count execution count of that way like we have executed it for 12 times and total worker work time okay now total work time is an addition to the previous overtime let's suppose I got executed in like like 10 units of worker time now next time you execute it will increase the count to 2 and next time we take 18 work at them now then it will increase that 10 plus 18 makes 28 now if the third time it got executed the count will increase to the 3 and suppose it take ten more then if we add 10 to that 28 also that it will make 38 so total worker time is increased again and again if I execute it again and again again again and if I just go again and execute this query and look into it you will see the count is increasing continuously and worker time is also including so worker time is kind of overall usage what that really has done now like useful or in in terms of worker time okay now when this statement is it is generating it is not generating new execution plan it is using the same and increasing the count and total work no time what if I change just a definition of my query the query is same I just enter one you can say a quick like line break out there and I execute this now if I go here it will not increment to worker time instead it will generate one more execution plan because definition of same query got changed so for him the previous learning query and this query are totally different these are not same see the definition of the query is same but in actual if you will go into the plan if I go into the XML view the definition of code is change because there is an break point does that make sense okay now coming toward this one again and if I undo it again and make it or the original one and I execute it again few more times and if I return back into this one and I will see the last plan is again start getting incremented okay means sequencer will keep execution plan and maintain stats for same definition of we now this scenario this methods work very less when dynamic queries are coming into the play okay suppose at runtime you are adding and removing the parameters so at that moment there is some serious issue there are few more method in the next tutorial I will covering those also but when you are using SPS and queries which are fixed coming from the production you can say software or something website like that then for the same query the same definition query these query will get these tabs will get increment instead of creation of new stats okay now you see the execution of this query is increasing if I undo it again and I execute this one and I execute this again you can see there will be increase in the count of the curvature we ask for okay now if I increase it again now this is the different definition of the quay now if I check it again the count of this execution plan is increasing now what if I enter one more enter this one and I execute it again now if I go into the execution like check for this DMV then there will be a new execution plan for the same qwave because the definition of Karim has changed there are two line break instead okay okay I think I'll just copied something different okay forget it now I think I make my point clear like whenever you change the definition of the query it will generate a new record into the DMV which we are using so you can only fix those queries which are not changing the definition at runtime okay most of the times this is a scenario like queries are not changing there are very few queries which are in the project which are dynamic most of the projects use SP so you can press down which SP has been hit and all and off so how this is how we will trace now coming toward this one okay now as I told you like this this is a clay-like since the dynamic basically these are the DM is since the dynamic execute query stats now if I execute this I get the stats now there are few things which I have to focus on first of all execution count second is total work of the time okay now as I told you my query was simple select top to star from sales okay let me change the database dot sales order do it now if I execute this this is a simple query now here I will use this load generator I will use this Quay and I will use like two hundred concurrent connection like east red will fire two hundred concurrent use a connection on to the server and my still CPU is normal and I start executing it now if I look into the server CPU usage is going far above the normal scenario you now see this coin was getting executed in few milliseconds but still it is causing this CPU usage so it is not always like bigger queries can cause the issue okay okay sorry okay my that process corporation okay so that was the only point which I want to make okay now coming towards the DM is how you will be able to check in when we are talking about this DMA I told you like we will be focusing on two things what is the a1 is the execution count and second is the worker time now if I just execute this same TM same view and order it by total worker time I get few stats about like there is a query which got executed 18 times and having the maximum worker time now I can look for that way like which way it is if I go in here I can just copy the plan here and execute it and if I want to show I can click and I can look and this one was a union all going okay now this one was a big way it's like my execution time like the word total worker time was big but its execution of the server is the execution count of the server is not so big so the execution the frequency of execution of this way is not so big so this cannot be that much issue okay could be the possible issue but not with that messaging now here is one more problem like there is a query which has been hit that much time and the total worker time is also big so now I have to look for this way like which is this gray which is getting hit so much time now if I just go into the gate for the Quai plan sorry to execution plan and go into this one and now this is select opt install from this basically I have executed this query before recording this lecture like first around more than 600 iteration for 200 users so it is sub-account so this is this is the co a which is causing like which got executed makes a minute now the production server you can find with this way like which query is getting Maxine number of it and in getting the total work on time interest and also which are the longer anyways and what is the frequency of running those query or the server now this is a query which God I which took the Mac same time and it the frequency of execution is only one so we must not be bike that means that could not be a batma tissue sometimes longer in quiz are if you like they can cause deadlock on to the servers and all now this is the same cycle here were query I just must have changed some definition and I made it executed for a long time so now this is how you can easily check like which process is causing maximum total on time or if you have one big running query which is causing issue you can kill it through this few steps now the last one one one more thing I want to tell DM is all not only available for the quiz for the procedure you can run this select DM execute procedure dot understands this is will tell you the stored procedure which are using these and the save are the parameter if I just want to order it by the worker time and I can also go for that and also I can go for the way execution plan through the same way which I used for other queries also now here you can see the execution count is this and totally organized so this is how you can trace down both the issues now coming toward my feet of X like what we covered is first of all this tutorial will only have when you are confirmed like sequencer is only the culprit now for the first method we will find the query which is causing which is issue with the traditional method then we kill the process or taking any other necessary action record because always killing is not option on the production environment if it is a transaction we cannot kill in that in between now coming to other method - that is one going one level deeper we use built-in dynamic management views that those are six dot d m dot execute which i showed you and these are the views which we are using now also we go for finding way which is having maximum total worker time or execution count then once we go to square we can go for if they are select quiz we can you know most of the time stress is caused on the server because of the select queries okay because when the massive reporting queries are run on the server they are most of the time we they are fine like they are there it is sound like they are the culprit because when update and insert codes are running they are allowed to they are supposed to do in input-output operation but when it comes to the Select query and they start doing input-output operation on the disk then they can contribute problem if they are doing it on the big or you can say big sinner like on the big scale okay now when we are having some select query like we found the total execution count and the total worker time was big for us small query which was which is most time is very less then we have to go for like optimizing it like we can go for like creating new index if suggested or required and by dropping index if required to suppose there is an update way which is how which is getting executed again and again and there are 10 index which are getting updated whenever the update is done because the column in the index are also getting updated through that of the statement now when this situation come you must be using full cover indexes it should not be updating all the indexes like there are 10 indexes when you update one column so whenever you updating this one column it is causing update to those 10 indexes also so 10 input-output operation or extra so you can go for full cover index in that case and also you can drop those 9 indexes and keep one which is required because sometime I have seen like developer create index without knowing without basically they just know like index help in fast retrieval but they don't know like they are also caused extra stress from the server also these are done by the developers here why the DBA comes into the play now also by creating the partition if you have a big table like the sales order we have table we can create the partition according to date and all because we are not always pulling all the records suppose I most in the motion on you I pull only the current or last six months record sir or I should create six month partition okay now coming to the cotton changing the cardinality when you are having a big joint story you can change the joints and check whether the result in same and though you can change the cardinality according to that so that sequel server can claim the result set which is returning from the server okay now also by splitting long-running update quiz in the smaller units log suppose I have maintained six-month partitions or one-year partition in India and I am running the update query on the complete partition now partition is suppose one partition having thirty-nine billions or twenty-nine million or 100 billions of Records then it will take a lot of time and during that time that partition will surely get lock so what you can do is you can just split the partition the update statement with thousand ten thousand on Lac updates at a time and so that certain pages should get only blocked ok so I think I've got material will help you and how you can get as a quick fix this issue now also if you want to contact me my name is Yogesh Mahalo and my website is text via dot in my email id this one now you get out my dad's email my phone number this my skype I risk in conversing in my skype is this one your Facebook is this one so if you have any feedback suggestion or any question you can just directly contact me and also the queries and this all stuff will be available on my website and all the YouTube whenever you are watching the video in the description you will find the link thank you for watching the video keep learning enjoy

Channel: techsapphire

Views: 129,469

Rating: 4.5194087 out of 5

Keywords: Microsoft SQL Server (Software), SQL (Programming Language), Central Processing Unit (Computer Peripheral), CPU, Usage, 100%, consuming, all, memory, SQL, Server, 2012, 2008, 2014

Id: 98c8spD5k5s

Length: 22min 14sec (1334 seconds)

Published: Sun Oct 04 2015