 Hello, welcome to SSUnitech, so see this site and this is part 3 of SQL Server Performance training. So today we are going to discuss about execution plans. So here we will understand about the execution plans, what is execution plan and what type of information it will contain and types of execution plan. And then what are the different ways to save the execution plan. So let's start with execution plan. So it will be generated by query optimizer with the help of stats of the tables and indexes. So we can say that it will be generated with the help of query optimizer and it will use the stats of the table and indexes. Then types of execution plans. So in SQL Server we have three types of execution plans. First is estimated execution plan, then actual execution plan, then live query stats. So live query stats is available 2k16 onwards. So before 2k16 it is not available. So if you are using 2k16 or ever version then you can check the live query stats. Now let's have a look of estimated execution plan and what type of information it contains. So go to our SQL Server Management Studio. As I am using SQL 2017 you may have another version of SQL Server. So I am having Adventure Works 2k16. So here this is our simple query. We gonna fetch the values from address table and schema is person and this is our database. So let me execute this query. So before going to execute this query so we have three type of execution plans. So first is estimated execution plan. So before going to run this query we can check the execution plan and that would be the forecast of this query. So here we can see this is the estimated execution plan or we can press control l. So once we click then we can check the execution plan. So here it will scan with a cluster index scan. So here we can check it will contain the information like physical operation, logical operations, estimated execution mode, then the storage that is row store. So if you will have the column store index then it will be changed with column store. Then estimated IO cost. So what will be the IO cost? It may be used 0.26831 then operator cost, estimated CPU cost and subtree cost. Then estimated number of executions that would be 1. Then estimated number of rows that would be around 19614. So these are the information it will contain. So we are not going to execute this query. Before going to execute if we want to estimate then we can estimate by using the estimated execution plan and we can simply select the query and press control l or we can click on this icon that is for estimated execution plan. So this is the first. So it will not going to contain any information that would be actual. It would contain all the information with the for cost basis and it is generated by query optimizer with the help of stats. Here as we can see in our second method that is actual execution plan. That is also very important if we want to execute our query and after that we want to check the execution plan that would be the actual execution plan. So here we can click on this icon with include actual execution plan or we can press control m. Then we want to execute our query. So let me execute this query. So query executed successfully and we are getting 19614 rows. So here we can see a spectacle results. So it will discussed in our upcoming videos then messages then go to one execution plans. So in execution plan once we go with this cluster indexes scan then we can check the information that it is contained. So here we can say that physical operations logical operations actual execution mode that is row then estimated that is also a row. Then storage row store as I have told you if we will create any column store index then it would be replaced row by column store. Then number of row reads that is 19614 and actual number of rows that is again 19614. So it will contain the information with the estimated as well as actual. So this is type 2 for actual execution plan. Our next is we can see over this icon that is include live query stats. Let me unselect this actual execution plan and select this live execution plan. So let me execute this query and we can check at the execution of time. So this execution plan is going to generated at the same time once our query is going to be executed and we can check the execution plan at the same time. So once we check in this cluster indexes scan then it would contain the information which we have seen in our actual execution plan that was physical operation logical operations estimated execution mode storage actual number of rows estimated I of course. So all the information was available once we have used actual execution plan. So this is for execution plans. So we have three type of execution plans and we have discussed about the estimated execution plan actual execution plan and live query stats. So in estimated execution plan if we don't want to execute the query and we just want to check the execution plan then we can estimate our execution plan by this estimated execution plan then actual execution plan in actual execution plan it will be generated once our query will be executed then it will generate the actual execution plan and it will contain the information that is actually happened with the query. Next is live query stats as we have seen this is our label only in 2 case 16 and above version and here we can check the execution plan at the same time once our query is going to be executed next is execution plan can be saved into below formats. So here we have three type of formats where execution plan can be saved first is graphical then text then XML. So let's have a look how we can save it. So go to on SSMS then replace this live with the actual and let me execute the query. So this is our execution plan simply we can right click on this and save execution plan as then I want to save in this folder and we can rename with execution plan graphical go to on the folder and this is the execution plan now double click on this and we can check it will open as a graphical mode as we have seen. So here as we can see this is the execution plan now we can close it and go to on SSMS and this is for graphical now right click on this and here so execution plan in XML we can open it so this is the execution plan which can be saved in XML now we can control s and here we can save this execution plan with XML format now we can save we can close it go to on the folder then this is execution plan in XML so we can right click and we can open with notepad so this is for XML. So if we want to send this query to other users or other developers then we can use this XML format and here we don't want to provide the right to the other users to check the execution plan and sort out the queries then we can use it now we can close it go to on SSMS and third option is TXT so right click save execution plan as here let me try execution plan dot txt now we can save it go to on the folder and we can double click and this is for flat file so we can save the execution plan in flat files as well but that is saved as XML as well so as we have discussed we can save the execution plan is graphical then text then XML and XML is used once we gonna send the execution plan to other team to check and validate the query is working fine or not if our query is going to take a lot of time to be executed then that team can be sort out the issue by checking the execution plan which we will send in XML so here let's have a look of operators so go to on SSMS so here as we can see we are having this operator that is called as clustered indexes scan with the cluster so this is the one operator in execution plan we will get a lot of operators and that is a label and if you want to learn about those operators then go to on Microsoft website and here as you can see this is the operators so all the operators is a label over here if you want to learn about these operators we can directly click on this and it will be moved to next page and in next page you can learn about this operator so this link will be available on the description of this video so if you need you can copy from there so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much.