November 9, 2020 at 9:59 am
we can use sp_spaceused to know how many space reservred by a table and know how many spaces for data space and index space, but if there is a approach to know the following informations of table , thanks!
November 9, 2020 at 1:18 pm
For most of us, most of the time, that's way too much detail. It begs the question, what problem are you really trying to solve.
However, if you want crazy detail on exactly how data is stored, you need to look to DBCC PAGE and DBCC IND. This is a very old article, but it's by Paul Randal, who I trust on this stuff because he helped write a bunch of these things. Here's a more recent article discussing a lot of the same points, just so you know that the older article is still trustworthy. Using those outlined methods, you should be able to, with a lot of work, answer the questions you're posing.
I still suspect you're focusing on a particular twig on a particular branch on a particular tree when you have a problem with the forest. If you do want to focus on that twig though, DBCC PAGE is how you do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2020 at 3:04 pm
For most of us, most of the time, that's way too much detail. It begs the question, what problem are you really trying to solve.
However, if you want crazy detail on exactly how data is stored, you need to look to DBCC PAGE and DBCC IND. This is a very old article, but it's by Paul Randal, who I trust on this stuff because he helped write a bunch of these things. Here's a more recent article discussing a lot of the same points, just so you know that the older article is still trustworthy. Using those outlined methods, you should be able to, with a lot of work, answer the questions you're posing.
I still suspect you're focusing on a particular twig on a particular branch on a particular tree when you have a problem with the forest. If you do want to focus on that twig though, DBCC PAGE is how you do it.
Grant Fritchey, thank you for your great help!
the reason why I asked this question is that there is a principle to give a needed length data type while create a table, for example, there is a varchar data type, normally, the max length of this field is 50, if we set it 100 character, it wastes space, now I want to test how many numbers of data page will be change when changing the data lenght of the fields, so I asked this question, thanks!
November 9, 2020 at 3:32 pm
Slight bit of misunderstanding there. Let's say we have a VARCHAR(50). Now let's say that our data stored there is 'dog'. It doesn't use storage out to 50 characters. It only uses storage for three (3), 'd', 'o', and 'g'. If you set the VARCHAR to 1,000 and put 'dog' in, it still only uses three (3) characters worth of space. It doesn't use all 50 or all 1,000 when allocating space in a VARCHAR.
Now, change the datatype to CHAR, different story. Then, every single value is 50 or 1,000 characters worth of storage. That's the difference between a CHAR/Character column and a VARCHAR/Variable Character column. Fixed length = fixed allocation. Variable length = variable allocation. So, let's say you have a thousand rows, each value in the thousand rows is less than 50 in length, but you change it to 100, how much more storage is there? Zero.
I hope that helps. You can validate all this using DBCC PAGE, but this is how it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 9, 2020 at 4:15 pm
In the main table storage area, a row will be on one and only one page. Any other data for that row must go to overflow pages.
-1- There's not an easy. In theory, the pages could be scattered and not contiguous anyway.
-2- View sys.allocation_units can tell you this
-3- If that was the only change happening at that time, you could use sys.allocation_units. If not, you'd have to use your own code to add up the bytes used
-4- Not that I know of. As Grant mentioned, that's not something that most people really focus on.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2020 at 4:18 pm
SQL row/page compression is typically extremely helpful to reduce disk space. Before SQL 2016, I believe you must have Enterprise Edition for it to be available.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 9, 2020 at 11:45 pm
Slight bit of misunderstanding there. Let's say we have a VARCHAR(50). Now let's say that our data stored there is 'dog'. It doesn't use storage out to 50 characters. It only uses storage for three (3), 'd', 'o', and 'g'. If you set the VARCHAR to 1,000 and put 'dog' in, it still only uses three (3) characters worth of space. It doesn't use all 50 or all 1,000 when allocating space in a VARCHAR.
Now, change the datatype to CHAR, different story. Then, every single value is 50 or 1,000 characters worth of storage. That's the difference between a CHAR/Character column and a VARCHAR/Variable Character column. Fixed length = fixed allocation. Variable length = variable allocation. So, let's say you have a thousand rows, each value in the thousand rows is less than 50 in length, but you change it to 100, how much more storage is there? Zero.
I hope that helps. You can validate all this using DBCC PAGE, but this is how it works.
Dear Grant Fritchey,
What you say is quite right! yes, the data type like char or nchar will be fixed length ,but varchar and nvarchar is different with char and nvarchar, I just want to express when I change the data type or the length of data type, and how the numbers of page will be changed when inserting or updating date for the table ,thanks!
November 10, 2020 at 1:51 am
In the main table storage area, a row will be on one and only one page. Any other data for that row must go to overflow pages.
-1- There's not an easy. In theory, the pages could be scattered and not contiguous anyway.
-2- View sys.allocation_units can tell you this
-3- If that was the only change happening at that time, you could use sys.allocation_units. If not, you'd have to use your own code to add up the bytes used
-4- Not that I know of. As Grant mentioned, that's not something that most people really focus on.
Thank you Scott Pletcher for your kind help!
a. the "main table storage area" you mentioned, does it mean MDF file ? I'm not clear the "main table storage area" as you mentioned.
b. I know the table has ntext or image or varbinary(MAX) data type, this kind of data type will saved in another page, "Any other data for that row must go to overflow pages." , in this word, does it refer to the data type like ntext or image or varbinary(max) ?
thank you!
November 10, 2020 at 2:15 am
SQL row/page compression is typically extremely helpful to reduce disk space. Before SQL 2016, I believe you must have Enterprise Edition for it to be available.
Yes, I know sql row/page compression, it is helpful to reduce disk space, but does it take more time while selecting some data from this compressed table?
November 10, 2020 at 1:50 pm
ScottPletcher wrote:SQL row/page compression is typically extremely helpful to reduce disk space. Before SQL 2016, I believe you must have Enterprise Edition for it to be available.
Yes, I know sql row/page compression, it is helpful to reduce disk space, but does it take more time while selecting some data from this compressed table?
Generally, no. Compression speeds data access. Fewer pages read from disk, less space used up in memory, with a cost in CPU. All this taken together usually means enhanced performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2020 at 1:51 pm
Grant Fritchey wrote:Slight bit of misunderstanding there. Let's say we have a VARCHAR(50). Now let's say that our data stored there is 'dog'. It doesn't use storage out to 50 characters. It only uses storage for three (3), 'd', 'o', and 'g'. If you set the VARCHAR to 1,000 and put 'dog' in, it still only uses three (3) characters worth of space. It doesn't use all 50 or all 1,000 when allocating space in a VARCHAR.
Now, change the datatype to CHAR, different story. Then, every single value is 50 or 1,000 characters worth of storage. That's the difference between a CHAR/Character column and a VARCHAR/Variable Character column. Fixed length = fixed allocation. Variable length = variable allocation. So, let's say you have a thousand rows, each value in the thousand rows is less than 50 in length, but you change it to 100, how much more storage is there? Zero.
I hope that helps. You can validate all this using DBCC PAGE, but this is how it works.
Dear Grant Fritchey,
What you say is quite right! yes, the data type like char or nchar will be fixed length ,but varchar and nvarchar is different with char and nvarchar, I just want to express when I change the data type or the length of data type, and how the numbers of page will be changed when inserting or updating date for the table ,thanks!
It won't change any existing data. New data that takes up more room, will, take up more room. That's just kind of how it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 10, 2020 at 2:15 pm
892717952 wrote:ScottPletcher wrote:SQL row/page compression is typically extremely helpful to reduce disk space. Before SQL 2016, I believe you must have Enterprise Edition for it to be available.
Yes, I know sql row/page compression, it is helpful to reduce disk space, but does it take more time while selecting some data from this compressed table?
Generally, no. Compression speeds data access. Fewer pages read from disk, less space used up in memory, with a cost in CPU. All this taken together usually means enhanced performance.
I agree. And I've seen it personally, with far better read times overall after compression. As long as your system is not CPU constrained, you should have no issues with reading page-compressed data.
However, writing page-compressed data is very much slower than non-compressed data. Indeed, very noticeably so. If you do a lot of index rebuilds, you need to be very careful of that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 10, 2020 at 8:59 pm
we can use sp_spaceused to know how many space reservred by a table and know how many spaces for data space and index space, but if there is a approach to know the following informations of table , thanks!
- how can we know the start page and end page used for storing data for a table ?
- how can we know how many pages are used for storing data for a table?
- if we know the said point1 and point2 , when we insert or update a piece of record for a table, then we can know the data size change after we do this operation , is it right ?
- is there any other way to know the data size change while inserting or updating a piece of record for a table?
<li style="list-style-type: none;">
My question is, what do you intend to do with the information... especially when it comes to such things as knowing the start and end pages of a "table", which could also actually be reverse so far as the physical page numbers are concerned?
For questions 2 and 3, those seem to possibly be related to finding "ExpAnsive" updates but what will you use that information for?
Asking questions without revealing the purpose is likely to get you some fairly useless answers for whatever it is that you intend to do with the info, so please explain.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 11, 2020 at 1:40 pm
892717952 wrote:we can use sp_spaceused to know how many space reservred by a table and know how many spaces for data space and index space, but if there is a approach to know the following informations of table , thanks!
- how can we know the start page and end page used for storing data for a table ?
- how can we know how many pages are used for storing data for a table?
- if we know the said point1 and point2 , when we insert or update a piece of record for a table, then we can know the data size change after we do this operation , is it right ?
- is there any other way to know the data size change while inserting or updating a piece of record for a table?
<li style="list-style-type: none;">
My question is, what do you intend to do with the information... especially when it comes to such things as knowing the start and end pages of a "table", which could also actually be reverse so far as the physical page numbers are concerned?
For questions 2 and 3, those seem to possibly be related to finding "ExpAnsive" updates but what will you use that information for?
Asking questions without revealing the purpose is likely to get you some fairly useless answers for whatever it is that you intend to do with the info, so please explain.
Thank you Jeff Moden.
I have these questions just because I want to know the data page and index page allocation while inserting, updating and deleting data. as the table has data type and each field has each data length, I want if we can know how many pieces of data can be saved for each data page. thanks!
November 12, 2020 at 1:41 am
I want if we can know how many pieces of data can be saved for each data page. thanks!
Thank you but the question above is basically just a rewording of your original questions and doesn't answer my question. WHY do you want to know "how many pieces of data can be saved for each data page"? What benefit will you gain or want to gain by knowing that information? WHAT will you actually do with that information once you know that information?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply