[0:01]Welcome back guys, this is Srikanth. Today we are going to start new journey with performance tuning. This journey will help us to understand the problems which are related with the performance or slowness and how to fix them and how to understand the bottle first of all. Okay, I will start with the very basic things. I will try to explain you the problem which I faced and how I fixed the issues in my real-time experience, right? So, I'm going to give you hands-on. So, some demos will give you a lot of ideas, some demo might not give you the correct idea, but the idea or the concept which I'm going to explain you that would be helpful. So, I'm requesting all of you, please continue this series until I upload all those videos. So, let's start with a journey. So today I'm going to explain you the basic problem which a lot of developer faces while working with the SQL server store procedure, right? So, one of my subscriber requested me to give some idea about the slowness in our store procedure. Our question is basically why store procedure perform slowly and if there if there is an issue in the store procedure of slowness, how do I fix that? So, I'm going to explain my experience with this question. So basically performance tuning deals with the lot of thing. We need to understand the stats, you need to understand the data, you need to understand the query, how do you have written. You need to understand the lot of thing. You need to understand the parallelism, waiting stats, then statistics, right? Also sometime we do use the wrong joints which really not required there lot of things are there which need to understand. But to start with a simple solution, I'm going to explain you sometime we write a code and that time our data is small. Because we just start the system and initial data would be very low for the system. After some days or if we are a good company or good we are having a good client, the data get in through the database, a lot of data came into the system. So, as a time passes, data into the table grows rapidly, it expands exponentially, right? So, the code which was written when the initial system was delivered was working good because the data was less. For for reading the data, for performing the operations, it was quite good, but after some days, we realized that data has gone exponentially. The query which was we given was not working properly because when we wrote the query, we never thought this company will grow this much or this data will grow this much, right? Great. So, sometime the simple solution, the thumb rule I would say, just go through all the code which you have written for the store procedure and then find out the query or the find out the block of the code which is performing a very hard or which is taking a lot of time. You need to find out that specific block then and then only you can think about the performance because if I say I have a store procedure which has 1000 line, right? So, if I say just tune the SP. So, you might have a question, what should I do, right? What should I do here? So, don't worry about that. The thing is that out of thousand line of code there are 20, 20 lines or 50 lines which are the problematic lines which are degrading the performance of your store procedure. Or query. So, you need to find out that first. How do you find it out first, right? So, the thumb rule I would say as a real-time guy, I would say, let's say if you have 1000 line of code and you feel that this code is performing when it reaches to 50%, right? So, 50% means almost 500 line, right? So, do one thing, just apply return them in the code itself. But before debugging or before troubleshooting the store procedures, realize one thing, if you are working on the production, make sure that it's not hampering any table, it's not hampering any work which people are doing. Because if you are working on a store procedure which are inserting, deleting or updating and the number of time you perform your code operation or the store procedure calling, it will insert, it will delete or it will update the data and which can corrupt your database, which can corrupt your table and obviously you may get fired. But if you are initial developer, it could happen. But if you are experienced one, just or for the fresher one, I'm also suggesting just make a copy of that and just go through all the code and see if there is an insert, update or delete block, if it is there, just make it, or just replace it with the temp tables created at top level, right? So, you can have all the exposure of the code, you can debug the code. Okay, just come back to the point. So, out of thousand line of code, you need just need to go to the 500 line and just place the return there and run the code, right? Run the SP which all proper parameters those you have and see and observe and understand that till the till my process or till my compiler comes to the 500 line, does it take a lot of time or is does it get executed very fast? Just think about that. So, when you are executing this store procedure till the 500 line, if it is taking more than time, let's say your code is taking 10 minutes, right? And out of 10 minutes till reaching to the 500 line, it is taking 6 to 7 minutes. Right? So, definitely that's a problem for you. Then just just trick it there. Okay, then do a thing. And I now we have we have a block of 500 line there. So now out of 500 line now I need to find out the specific block which has a problem. So what will you do? Then do one thing. Then do one thing. What will you do? Just go to the 250 number of lines. Okay? So, I am I reach to the line number 250 there, just place return there and just alter the code and run the SP. Now see, how much time it is taking. If it is taking in a less than second or the 10 second, 20 second, then it's fine, so if it is not taking or if it is taking 5 minute out of 7 minute, okay? Then think it think that there is an issue. There is a problem in between the 250 line. Again. Now we we need to find out the main block. Now it is taking a 4 minute out of 7 minute. It means that still 4 minute is not a good number. So what do you need to do? You will simply go to the again half of that that's a 125 line and just place a return there and do one thing. Just alter the code, run the code and see the output, right? Now, when you will run the code, you will see the output and you will realize this code is is is executing in second. It is very fast. So it means that there is no problem till 250 number of lines, right? So, line number 255 is okay. So, there is no performance problem for that specific block. So now your main focus would be from line number 250 to line number 500, right? So, there is a performance problem in between these two in between in between this block basically. So, again do one thing. Now 250 and 500 just go to 300 or 400 line and apply the same procedure. Apply the return and focus which part of the queries is taking time. So after sometime or after spending 5 or 10 minutes, you will get a specific block where the code is taking time. So don't worry. It will take some time for first time but but when you will be expert, it would not take more than 5 or 10 minute, but when you are beginner you need to start from the. So these are few steps. I am telling you as an experienced guy. So now you have a specific block of code and you know there is a problem and after after performing some iteration, which I suggested, you will find out the block. So you will just note down or you will now go to that block and see why this block of code or why this query is taking time. Just think about that. Okay? If there is a looping, looping means if there is someone has used the cursor or someone has used while loop for processing some business logic. Just think about that. And you know for if if code is written in the loop or if code is written in the cursor, it takes a lot of time because it needs to perform the iteration. It needs to perform the operation one by one, one by one, row by row, row by row, right? So, just try to think if I can replace this entire script by set basic. So, I can save a lot of time, right? Right? So, just try to replace that entire looping part to the set based logic. So, I can say procedure logic to the set based logic. You will get a performance. What happened, you know, let's say in that block, let's say you have 1000 entries or 1000 1000 rows, and for performing 1000 operations, it calls the specific block in a 1000 time, right? So, 1000 time the code get called. So, when you design the system that time there were only 25 rows, so it was not that problem. Now, it has a lot of data, right? And performing that thousand operation, it puts a lot of data and it do a lot of calculations for performing the values, so it takes time. So, just replace and try to replace if possible the the row based logic to the set based logic. That is the one simple solution, right? If you do that, you will get a solution. That is a one performance solution. So, the what I will suggest, revisit the code and find the performance bottleneck.
[11:03]Perfect. Then what? Then think about code, why it is written like that.
[11:23]If it is a complex code. If complex, then try to rewrite the code. If row based calculation is there, replace with the set calculation.
[12:08]Okay? This is basically if this is basically for the looping. And sometime, you know, what happened? You just simply see a simple code there, simple joint sometime. Uh, recently I was working for one of the performance problem. There I realized one very good a case. The case was there was the scenario where relation was happening there in the relation code, there was the left outer join, yeah. And the in the left outer join, they have used some non-null values in all or conditions. So what was happening there? Unnecessary lot of data was pulling and some relation happened. So, pulling that data it was taking a lot of time after joining mismatches was happening. So what I did, I I made that logic into two steps. Uh, because because you know, as a developer changes the logic also changes, but the output should come same. So I changed logic as per my my requirement. Uh, I realized this code is working is working correctly, but the way it is written is not correct. So, I rewrote the code and it was working good and it was then giving me output in a 5 second, right? So, 5 minute to 5 second, just by rewriting the code, I got the output. So, sometime you need to think that the code which is written, it is correct, that's fine. It is correct, it is right. Okay, but is there any way I can try or is there any way I can do it better? Where else? If you do better if you rewrite it, just try to give it a try. Sometime giving it a try to give you the best result. Okay. So give it a try for complex code. Okay, now after doing all these things, if something is not happened.
[14:16]You have you have revisited the code, you found the bottleneck, and then you replace the everything. You change the complex logic to the simple logic. You tried a lot of things but nothing is happening. Then you need to do what? Then you need to select that code, then go to the execution plan and then run this query here. We have the object for example, I'm writing select star from in table. Let's say select one for example, I'm writing. And if I want to start the execution plan, here we have an execution plan, equal execution plan, right? Uh, the moment I run the code, I can see a new tab here.
[15:10]And I on the execution plan, now let's see what happened.
[15:19]The query is for me here. Let this execution plan. So, this is called as the execution. I will talk the execution plan in incoming videos, but just to just to give you a brief idea. So, execution plan is very important while dealing with the performance tuning problem, if the query rewrite or the query query operation query simple query is not working, right? Then you need to go to the complex part. You don't have any option, but I suggested that option, you know, for why I suggested? Because sometime sometime performance problem are very simple. If we rethink the simple way, it can fix all your issues. So if it is a performance, it doesn't mean that you always need to think very in a in a typical way, right? Great. Now if just want to recap, don't worry about this entire execution plan. Just want to show you some idea about the execution plan, how to read it. Execution plan always be from the right side, right?
[16:36]So here you can see the node ID also. It will come here. This is node ID 4. This is node ID 3 to 66, right? But you need to you need to read it from the reverse manner. So now the first thing what you'll read, you will see here this is the cluster index scan. So scan is happening on which table? Sis jobs, right? On this table the scan is happened. It has taken the 73% of the one. So this is total 100% always be shown here, right? This is just a simple plane table. Now, this is filter, one of the execution plan operator, this is called as a operator. This is another operator. The scanning happened, operator then the clustered index scan, then nested loop joins happened there. Then the compute CLR and the final one is the select. So, this is called as a execution plan. Don't worry about the reading. I will tell you each and everything, step by step, but just as to be familiar with to tell you the execution plan is a kind of a plan, which is a graphical plan basically which tell you how the query is generating, how the query is pulling the data, right? Which index it is using, which uh which operator it is using, how many using how many cost, how much cost it's taking to retrieve the data, right? So, uh, and which logical operation are performing inside the database engine. All these things are captured and all these things are present on the screen and all these thing can be understood with the help of what? With the help of execution plan, right? So, execution plan is very simple. Reading the execution plan, when if you are a beginner or if you are just starting one is quite difficult, but if you are reading it again and again, it is one of the easiest way, right? Okay. So, the next solution what I'll suggest for fixing the slowness problem is is to create is to create some indexes. So indexes always be created based on what? Based on the condition. Let's say I have two table and there is a condition of the 5 or the 6 column. So, indexes should be created on that 5 column only and the column which are taking inside the select, right?
[19:12]So, we create a new index that index we call it as a covering index. You can go through my previous video. You will search you will search you will see the covering index. You can get a lot of idea about the covering index part. So what I am suggesting, then create try to create index if it is a temp table. If it is a permanent table, then you need to be very precise because creating a more indexes on a table will hamper the database insertion operation and the deletion, right? Because indexes will be created, new new table will be created basically for the indexes entire structure will be different for the indexes. So, you need to create for indexes. First of all, you need to find is there any missing indexes is suggestion in the database if not, then you need to think about you are creating your own indexes. Okay, find out. Then after finding out the indexes, you need to run the query and then you need to understand the execution plan. Is there any kind of waiting problem are there? Is there any parameter sleeping problem, right? Uh, then you need to see that you have just created index is that index is used in the query and then you need to think if you are just pulling a 10 or 15 records that then what is happening? Table scan is happening or the seek is happening. You need to think about that, and also if you are using the heap table, then is it good to use heap table or not? You need to think all those things while while reading the execution plan, right? So, all these thing can be understood. We'll try to cover each and everything in simply sprint. If any point, if I miss, just message me or just comment me on this video, I will definitely try to incorporate that point in my coming video or I will I will first try to make a video on that topic, then I will move to my second topic. That will again be helpful. Because I'm explaining because I know everything. I don't know how many guys sorry, I don't know which thing you guys don't know, right? So that would be helpful for me and that would be helpful for the people who are listening my video. I want to create this video for the help of people who are trying to learn performance tuning. And I want to share my experience with you guys so that it will be helpful for you, right? This is just a, uh, you might be thinking that I am speaking a lot in this video, but don't worry, this is just intro video. So intro video always be boring, so bear with bear with me there. Uh, yeah this, then you need to read the execution plan back to it. Uh, okay. Sometime, you know, sometime we need to after fixing it, we realize that it is again not fixed. Then we need to think about the statistics. Statistics, right? Statistics are something like a history data for the query when execution plan get created. Execution plan always be created based on the statistics. Statistics is something that it's kind of history that for example, we have a table in the table. We have a two column and in the column how much data distinct data we have in the column? How frequently data is get access and the and the different different parameters we have. So all those information historical thing are present in the statistic part. So, this statistic information are very useful for taking the decision for for creating the execution plan for the buffer engine, right? Great. Uh this is one. Then we need to go for index refactoring, index rebuilding, right? And sometime we need to update the statistics for updating the pages. If the statistics is not updated, it will show you the wrong number of rows which can hamper the execution plan. Then after fixing this again we will see the one new parameter for the execution, that's a waiting stats or waiting type. I can say, So you can see here all those information, just go to the properties. Here is a one section called as a wait stat. Let's see it is have there or not. So for this for for this specific query, we don't have, but there is a specific clause which deals with the waiting stat, so for example, if I'm running the code, right? And that four is running on the number of different different core procedure core cores, right? Let's say I have four cores, so that query will be parallelly get executed on the four cores and one of the core get completed first, first, and then it needs to wait for other three. Then second completed, then it needs to wait for the other two complete. So this kind of waiting happened into the database system and all those kind of the waiting things will be captured and stored right in the waiting stats. So we need to find out is really does it require that query need to be executed on the multiple core or the single core, that part come into the waiting state. So C packet you might have guys see, right? So we need to understand that. Then we then there are a lot of concept like the dirty read, lazy right, all those thing will be covered. And my intention is to start this kind of a video. This video might be a small small because I'm going to explain a lot of things and I will not make next video this much fun. Uh, okay. So, hope you guys try understood that this is just a intro video. We'll we'll take an next example with a one store procedure and there I will show you how to pick the performance problem with real-time experience. I am finding out the best example for you guys because the company data or the company code, I can't share with you because it's a public and if they guys see you, they will definitely fire me. So I can't do that. It's a breach of contract, right? You can guys can understand. So I'm finding out the best procedure over the internet which can I use and which can I explain you guys to demonstrate the performance bottleneck or how to fix it. And one more idea I am going to tell you that I've a lot of my friend also asked me to to to question that how to debug a store procedure or for the troubleshooting, if they have any issue, how to fix it that. That video also I'm going to upload very soon and thanks for watching. If you guys like my content or my video, please don't don't forget to share and subscribe with your community. Sharing the video or sharing the channel will help us to grow tremendously. It will help us to increase my subscriber base. So more the subscriber, more the comment and the more the idea will be shared among us, right? So please try to share and subscribe this channel. But it's not force, right? It's your choice. Thanks for watching. Have a great day.
