 Hello, welcome to SSELITIC social this site and this is continuation of SQL Server Performance training. So, today we gonna discuss about scan and reads. In reads it could be logical read or physical reads. So, before going forward if you haven't watched part 10 of this video series so I would strongly recommend to watch that video where you can understand about the measurement of absolute resources. So, go to our next slide to understand about the scans. So, what is scan count? So, it's a number of seek or scans started from the root node to reach the leaf node in any direction to retrieve the data. As we have discussed in the bit tree we have different different nodes. So, first is a root node then intermediate node then leaf level nodes. So, if you haven't watched that video then you can also prefer to watch that video before going this. So, this is a number to start from the root node to reach the leaf level nodes. That would be the scan counts. If the scan count value is 0 then if the index is used a unique index or cluster index on primary key column. So, as we can see we just want to get all the data from the person table. So, here we can see scan count is 1. Second, as we can see we want to get data from the person table but where is the business entity ID is 1 then scan count would be 0. Then our third condition if the business identity ID is greater than 1 and less than 5 if we will put this condition with greater or equal to 1 and less or equal to 1 then again scan count 1. If we want to use with the incondition so, here as we can see in 1, 2, 3 and 4 so scan count value would be 4. It would be great to use the greater than or equals to because scan count value would be less but if we will use the condition in subquery then it will use for each value with the account. So, go to our SQL Server management studio to check this output. So, as I am using SQL 217 you may have Andhra versions of SQL Server. So, first of all I just want to on the IO so let me execute set statistics IO on Now I just want to execute this select statement, select a stick from person table. So go to our messages and here we can see scan count is 1. So here scan count is 1 once we want to select all the data from the table. Now let me execute this table where business identity ID would be 1. So this time scan count should be 0. As we can see scan count is 0 that is fine looks good. Now I just want to execute this query here business identity ID is greater than 1 and business identity ID is less than 5. So it will return total 3 records 2, 3 and 4 and go to our messages to check the scan count. So this time scan count value is 1. So next time I just want to get the data from greater than or equal to 1 and less than or equal to 1 from business identity ID. So check the scan count for this. So here we can see scan count is 1 that is also good. Next is here I just want to get all the data from the person table where business identity ID would be 1, 2, 3 and 4. So let me execute this and check the scan count. So this time scan count should be 4 as we can see scan count is 4. So let me execute this set statistics IO of command executor successfully. Now this is for the scan count. So it will check the number of counts from root level to leaf level node where data is available. So next is what is logical read? So number of pages read from the data cache. So this is the logical read and in this condition if we want to get all the data from the person table and data is available on the data pages and that data pages are available in the cache. So logical read would be 3 at 3 at for this case. If we want to get from business identity ID is 1 then logical read is 3 and many more options. So go to our sequence of management studio and here let's have a look. So let me execute this query to set statistics IO on. Next I just want to select all the data from person table. So here it would return around 19,972 rows and go to on messages and here I just want to check the logical reads that is 3817. So let me correct this 3817. So this is a logical read and here we can check the physical read is read is 0. So we will discuss about these two in later in this video. So let me execute this second query. Here business identity ID is 1 so it would return only 1 row and here we can check logical read that is 3. So for getting this data it is needed to scan total 3 pages. So here let me execute our third query and here we are getting total 3 rows. So here we can check the logical read that is 3. It means total 3 pages are required to get this data. Now let me execute our fourth query. And here I just want to check the logical read. So logical read is 3. So I can replace this scan count with the logical read and it would be 3. In the next query I just want to get data from person table where business identity ID 1, 2, 3 or 4. So let me execute this and check the logical reads. So this time as you can see logical read is 12. So logical read is 12. So 12 pages are required to get the data. Now if we execute this query with the order by clause so it will take around 3817 that would be the logical read. So here we can see 3817. So this is the logical read. So here it will read the data pages from the cache. So how many number of pages are required to get the data for that query would be the logical read for that query. Next I just want to get the data from person table where first name should be equals to KN. So let me execute this. So here it is going to return total 6 rows. Now go to one messages. So logical read is 134 because it is not a primary key column. So that is why it is taking too much pages to scan the data. Now next would be physical read. So what is physical read? Number of pages read from the disk. It shows how many pages are requested by the query processor from the storage engine and engine retrieved from secondary storage that is our hard disk and pull into the primary storage that is cache. So how many number of pages are requested from the query and that pages are coming from this that would be the physical reads. So go to one sequence of magnitude studio and this time I would like to check for the physical read. So here first I just want to clean up the cache. So for that we can use the dbcc drop clean buffers with no info message. So let me execute this. So buffer is free right now. Now let me execute this query and this time it should get the physical reads. So go to one messages and here we can see logical reads would be 3817 and physical read would be 3593. So this is the physical read and this is the logical read. If data is already or label on the cache then our query will be get the data very quickly. If data is coming from the disk then it would take much more time. So as compared to physical read and logical read logical read value should be more than physical reads. Next would be read ahead read. So what is read ahead read? So when data is not in the buffer cache then physical read would be there. So all reads will be a physical read. As we seen. So requesting row by row data from the disk is very expensive. So here new mechanism called is read ahead read. It anticipates bunch of rows that might be requested by the query. So it plays pages into the buffer even before requesting by the query. It will place some pages from the disk to cache without requested by the query. It may be requested next time by the query. So go to our sequence of magnitude studio and here so here let me execute this query first. So first clean up the cache then set statistics IO on, set statistics time on then query then statistics time off and statistics IO off. Let me execute this query go to our messages. So here as we can see read ahead read that is around 39 and 34. So 39 and 34 pages are stored in the cache. So here we can see let me copy this. So this is the CPU cost and elapsed cost. So now this time I don't want to get data by read ahead read. So clean up the cache then here let me execute these two on the statistics IO and time. So next here we can see dbcc trace on with 652. So this is used to if we don't want to use this mechanism with read ahead read. We can execute this and now let me execute the query. Once I execute this query then go to our messages. So here as we can see we don't have any read ahead read. But this time I just want to check the execution time. Let me copy this and here I just want to compare between these two. So here we can see the CPU time that is 140 ms then elapsed time that is around 2614 ms. So this is only for the 19,000 records if we will have millions of records then this value very high. So here we just want to use this mechanism we don't want to avoid this mechanism because it will improve the performance. So this is our read ahead read. So I hope you have understand about the scans logical reads, physical read and read ahead read. In next video we will discuss about the log logical read, log physical read and log read ahead read. 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.