That is a perfect Explanation and on point. Bro I want you to create few Query optimization-related videos. If possible please create videos to understand the Execution plan and other query performance tweaks. Will wait for your new videos as you are on top of my list for MSSQL :). So informative.. Keep it up !
Very well explained 👏 .. Can you do a video on using sp_blitzcache and sp_blitzindex...It would be helpful for suggestions...or any kind of tools which will make our day so easy.
Hello, Thank you and greetings from Bulgaria. Great tutorial. I would like to ask you - you say we can request bigger sample. Yet it is said that statistics is restricted to one 8K page. So it seems that we can not extend indefinitely. Or am I wrong in some way ?? Also - you mentioned that statistics can be created on a column without an index. Very few people mention that. So I am now exploring - if we can have just statistics to help us build a query plan, why do we need index/es ?? They are bulky etc. And most important they rely underneath on ... statistics. So seem like that they are like middle man. Would you like to help me (and the others) on that ?? Thank you.
I do not think statistics is restricted to one 8K page as they are BLOBs learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#statistics-1 Regarding difference, I highly recommend watching my video from 6:56 specifically as I explained how statistics help to choose best way to run the query. Let me explain in this way: You have following indexes: Index A Index B Index C You are starting to run a query to retrieve RowsA. In this case, what SQL Server does firstly is, to find the best( less-costly) way of getting these rows. For this, it should evaluate several options like below: 1. Run query with Index A (e.g. in this case SQL Server has to go through only 100 rows to retrieve RowsA) 2. Run query with Index B (e.g. in this case SQL Server has to go through only 120 rows to retrieve RowsA) 3. Run query with Index C (e.g. in this case SQL Server has to go through only 160 rows to retrieve RowsA) 4. Run query without any index (e.g. in this case SQL Server has to go through all 1000000 rows to retrieve RowsA) As can be seen, SQL Server chooses first option with Index A as it is less costly (it should work with less rows in this case). In this case, statistics help SQL Server to predict how many rows are expected to go through beforehand to choose the best option which is 100 rows in our first case. As you can see, if we take index as a route to reach destination, statistics is "prediction of costs" if we choose this specific route in terms of number of rows.
@@arslanov1886 Thank you for taking so much time to explain. After some thinking and hitting the wall with my head :) may I bring my words in addition ? Please tell me if I am wrong. So statistics helps to find / allocate the data and predict the number of rows that would come. Then the SQL Server makes the decision whether it can use an index or not to collect the data. Are you Japanese ? I mean you could be just working and living there. Big Thank you.
@@arslanov1886 All right. Thank you. For a moment I thought I asked something inappropriate. I love learning about Japan. It is so unique and exotic in many ways compared to the western world. I have many things to sort out now but some day I will look for you in LinkedIn and ask you if you would have time share some of your insights.
That is a perfect Explanation and on point. Bro I want you to create few Query optimization-related videos. If possible please create videos to understand the Execution plan and other query performance tweaks. Will wait for your new videos as you are on top of my list for MSSQL :). So informative.. Keep it up !
Sure, will do. I have a plan on this and started working on it :)
@@arslanov1886 :: Appreciate your hard work bro :) Thank you !
Very well explained 👏 .. Can you do a video on using sp_blitzcache and sp_blitzindex...It would be helpful for suggestions...or any kind of tools which will make our day so easy.
Sure. I will work on this. This is interesting for me! Thank you.
Thank you for your content. I'm exploring mssql internal to preparing some interview about database dev. Blessed to view this
Glad it was helpful!
Can you please make a video on execution plans and operators.
Sure. I am doing that. It will be released soon
Hello, Thank you and greetings from Bulgaria. Great tutorial.
I would like to ask you - you say we can request bigger sample. Yet it is said that statistics is restricted to one 8K page. So it seems that we can not extend indefinitely. Or am I wrong in some way ??
Also - you mentioned that statistics can be created on a column without an index. Very few people mention that.
So I am now exploring - if we can have just statistics to help us build a query plan, why do we need index/es ?? They are bulky etc. And most important they rely underneath on ... statistics. So seem like that they are like middle man.
Would you like to help me (and the others) on that ?? Thank you.
I do not think statistics is restricted to one 8K page as they are BLOBs
learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver16#statistics-1
Regarding difference, I highly recommend watching my video from 6:56 specifically as I explained how statistics help to choose best way to run the query.
Let me explain in this way:
You have following indexes:
Index A
Index B
Index C
You are starting to run a query to retrieve RowsA. In this case, what SQL Server does firstly is, to find the best( less-costly) way of getting these rows. For this, it should evaluate several options like below:
1. Run query with Index A (e.g. in this case SQL Server has to go through only 100 rows to retrieve RowsA)
2. Run query with Index B (e.g. in this case SQL Server has to go through only 120 rows to retrieve RowsA)
3. Run query with Index C (e.g. in this case SQL Server has to go through only 160 rows to retrieve RowsA)
4. Run query without any index (e.g. in this case SQL Server has to go through all 1000000 rows to retrieve RowsA)
As can be seen, SQL Server chooses first option with Index A as it is less costly (it should work with less rows in this case). In this case, statistics help SQL Server to predict how many rows are expected to go through beforehand to choose the best option which is 100 rows in our first case.
As you can see, if we take index as a route to reach destination, statistics is "prediction of costs" if we choose this specific route in terms of number of rows.
Btw, Greetings from Japan!
@@arslanov1886 Thank you for taking so much time to explain.
After some thinking and hitting the wall with my head :) may I bring my words in addition ? Please tell me if I am wrong.
So statistics helps to find / allocate the data and predict the number of rows that would come. Then the SQL Server makes the decision whether it can use an index or not to collect the data.
Are you Japanese ? I mean you could be just working and living there.
Big Thank you.
Correct.
Yes, I am just living and working here in Japan :)
@@arslanov1886 All right. Thank you. For a moment I thought I asked something inappropriate.
I love learning about Japan. It is so unique and exotic in many ways compared to the western world.
I have many things to sort out now but some day I will look for you in LinkedIn and ask you if you would have time share some of your insights.