Hi,
The Books Online article "Estimating the Size of a Table" gives you the
method to estimate the size of a table. Using this method I estimated the
size for one table. However after populating that table with data, I used
sp_spaceused to find out the space used by this particular table, but to my
surprise I found big difference between the value given by method "Estimating
the Size of a Table" and sp_spaceused stored procedure. I also passed
updateusage parameter as true. Can anybody throw some light on why such
difference is found between these values?
Regards,
VaibhavVaibhav
Try UPDATE STATISTICS and then run sp_spaceused.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> Hi,
> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
> Regards,
> Vaibhav|||> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
Have you counted the indexes? Do you have variable length data types?
What are actal numbers?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
IMAGE)? How many of them are populated to the maximum capacity, how many
are NULL (or empty string), how many have valid data? What kind of indexes
do you have? What is the "big difference", 10%, 50%?
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> Hi,
> The Books Online article "Estimating the Size of a Table" gives you the
> method to estimate the size of a table. Using this method I estimated the
> size for one table. However after populating that table with data, I used
> sp_spaceused to find out the space used by this particular table, but to
my
> surprise I found big difference between the value given by method
"Estimating
> the Size of a Table" and sp_spaceused stored procedure. I also passed
> updateusage parameter as true. Can anybody throw some light on why such
> difference is found between these values?
> Regards,
> Vaibhav|||Thanks for the response.
I don't have any variable lenght fields. And yes I have also calculated
estimated size for clustered index separately. And this table does not have
any non-clustered index. To tell you something more about this comparision,
if you add the values for all the tables from field 'data' (in the recordset
returned by sp_spaceused) you get a figure which is approx. equals an MDF
file size. So what about the size occupied by other database objects. I would
want to know what this 'data' field value comrpises of...
"Aaron [SQL Server MVP]" wrote:
> Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
> IMAGE)? How many of them are populated to the maximum capacity, how many
> are NULL (or empty string), how many have valid data? What kind of indexes
> do you have? What is the "big difference", 10%, 50%?
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> > Hi,
> >
> > The Books Online article "Estimating the Size of a Table" gives you the
> > method to estimate the size of a table. Using this method I estimated the
> > size for one table. However after populating that table with data, I used
> > sp_spaceused to find out the space used by this particular table, but to
> my
> > surprise I found big difference between the value given by method
> "Estimating
> > the Size of a Table" and sp_spaceused stored procedure. I also passed
> > updateusage parameter as true. Can anybody throw some light on why such
> > difference is found between these values?
> >
> > Regards,
> > Vaibhav
>
>|||The difference is of 53 MB or 37%
"Aaron [SQL Server MVP]" wrote:
> Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
> IMAGE)? How many of them are populated to the maximum capacity, how many
> are NULL (or empty string), how many have valid data? What kind of indexes
> do you have? What is the "big difference", 10%, 50%?
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
> "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> > Hi,
> >
> > The Books Online article "Estimating the Size of a Table" gives you the
> > method to estimate the size of a table. Using this method I estimated the
> > size for one table. However after populating that table with data, I used
> > sp_spaceused to find out the space used by this particular table, but to
> my
> > surprise I found big difference between the value given by method
> "Estimating
> > the Size of a Table" and sp_spaceused stored procedure. I also passed
> > updateusage parameter as true. Can anybody throw some light on why such
> > difference is found between these values?
> >
> > Regards,
> > Vaibhav
>
>|||I have no idea what you're talking about here. But if you want an accurate
judgement of a table size you can do something like:
SELECT SUM(DATALENGTH(col1)) + SUM(DATALENGTH(col2)) + ... FROM table
Then add the overhead associated with nullable columns, and the overhead
associated with the clustered index.
The *estimate* in Books Online is just that, an estimate. It is meant to
give a rough guideline for how much space your table might occupy, it is not
meant to serve as a means to determine the exact number of bytes in an
existing table. So use it for what it is...
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:98CF4A5C-5C3B-44DE-BD73-9E97D7F53A3D@.microsoft.com...
> Thanks for the response.
> I don't have any variable lenght fields. And yes I have also calculated
> estimated size for clustered index separately. And this table does not
have
> any non-clustered index. To tell you something more about this
comparision,
> if you add the values for all the tables from field 'data' (in the
recordset
> returned by sp_spaceused) you get a figure which is approx. equals an MDF
> file size. So what about the size occupied by other database objects. I
would
> want to know what this 'data' field value comrpises of...
> "Aaron [SQL Server MVP]" wrote:
> > Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
> > IMAGE)? How many of them are populated to the maximum capacity, how
many
> > are NULL (or empty string), how many have valid data? What kind of
indexes
> > do you have? What is the "big difference", 10%, 50%?
> >
> > --
> > This is my signature. It is a general reminder.
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> >
> >
> > "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> > news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> > > Hi,
> > >
> > > The Books Online article "Estimating the Size of a Table" gives you
the
> > > method to estimate the size of a table. Using this method I estimated
the
> > > size for one table. However after populating that table with data, I
used
> > > sp_spaceused to find out the space used by this particular table, but
to
> > my
> > > surprise I found big difference between the value given by method
> > "Estimating
> > > the Size of a Table" and sp_spaceused stored procedure. I also passed
> > > updateusage parameter as true. Can anybody throw some light on why
such
> > > difference is found between these values?
> > >
> > > Regards,
> > > Vaibhav
> >
> >
> >|||Please find the numbers used in the estimation process -
Number of rows 2245536
Number of columns 11
Sum of bytes in all fixed-length columns 33
Number of variable-length columns 0
Maximum size of all variable-length columns 0
Null Bitmap 4
Total size of variable-length columns 0
Total row size 41
Number of rows per page 189
Fill Factor 100
Number of free rows per page 0
Number of pages 11882
Table size (MB) 92.83
And that given by sp_spaceused -
rows 2245536
reserved 150152 KB
data 149200 KB
index_size 896 KB
unused 56 KB
"Dejan Sarka" wrote:
> > The Books Online article "Estimating the Size of a Table" gives you the
> > method to estimate the size of a table. Using this method I estimated the
> > size for one table. However after populating that table with data, I used
> > sp_spaceused to find out the space used by this particular table, but to
> my
> > surprise I found big difference between the value given by method
> "Estimating
> > the Size of a Table" and sp_spaceused stored procedure. I also passed
> > updateusage parameter as true. Can anybody throw some light on why such
> > difference is found between these values?
> Have you counted the indexes? Do you have variable length data types?
> What are actal numbers?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>|||Hi,
After following method given in the Books Online article, I got following
results for a table -
Number of rows 2245536
Number of columns 11
Sum of bytes in all fixed-length columns 33
Number of variable-length columns 0
Maximum size of all variable-length columns 0
Null Bitmap 4
Total size of variable-length columns 0
Total row size 41
Number of rows per page 189
Fill Factor 100
Number of free rows per page 0
Number of pages 11882
Table size (MB) 92.83
And the result given by sp_spaceused is as follows -
rows 2245536
reserved 150152 KB
data 149200 KB
index_size 896 KB
unused 56 KB
After comparing these two resulrs one can see the difference of 57 MB. Could
you please tell me why this much difference is observed. Do I need to
consider any other factors while calculating estimated size for a table.
Again the estimated size of clustered index on this tables is 0.45 MB. Also
can you please tell me how to estimate the size of a database. As the same
knd of difference would be shown in the estimated and actual figures if one
calculates estimated size for a database using Books Online metod?
"Aaron [SQL Server MVP]" wrote:
> I have no idea what you're talking about here. But if you want an accurate
> judgement of a table size you can do something like:
> SELECT SUM(DATALENGTH(col1)) + SUM(DATALENGTH(col2)) + ... FROM table
> Then add the overhead associated with nullable columns, and the overhead
> associated with the clustered index.
> The *estimate* in Books Online is just that, an estimate. It is meant to
> give a rough guideline for how much space your table might occupy, it is not
> meant to serve as a means to determine the exact number of bytes in an
> existing table. So use it for what it is...
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> news:98CF4A5C-5C3B-44DE-BD73-9E97D7F53A3D@.microsoft.com...
> > Thanks for the response.
> >
> > I don't have any variable lenght fields. And yes I have also calculated
> > estimated size for clustered index separately. And this table does not
> have
> > any non-clustered index. To tell you something more about this
> comparision,
> > if you add the values for all the tables from field 'data' (in the
> recordset
> > returned by sp_spaceused) you get a figure which is approx. equals an MDF
> > file size. So what about the size occupied by other database objects. I
> would
> > want to know what this 'data' field value comrpises of...
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > Well, do you have any variable-length data (VARCHAR, VARBINARY, TEXT,
> > > IMAGE)? How many of them are populated to the maximum capacity, how
> many
> > > are NULL (or empty string), how many have valid data? What kind of
> indexes
> > > do you have? What is the "big difference", 10%, 50%?
> > >
> > > --
> > > This is my signature. It is a general reminder.
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> > > news:310A97C9-498D-4C12-B92A-F8C53814F4FF@.microsoft.com...
> > > > Hi,
> > > >
> > > > The Books Online article "Estimating the Size of a Table" gives you
> the
> > > > method to estimate the size of a table. Using this method I estimated
> the
> > > > size for one table. However after populating that table with data, I
> used
> > > > sp_spaceused to find out the space used by this particular table, but
> to
> > > my
> > > > surprise I found big difference between the value given by method
> > > "Estimating
> > > > the Size of a Table" and sp_spaceused stored procedure. I also passed
> > > > updateusage parameter as true. Can anybody throw some light on why
> such
> > > > difference is found between these values?
> > > >
> > > > Regards,
> > > > Vaibhav
> > >
> > >
> > >
>
>|||> After comparing these two resulrs one can see the difference of 57 MB.
Could
> you please tell me why this much difference is observed.
All those starts are fabulous but I still have no idea what kind of table we
are talking about here. Can you please generate the CREATE TABLE statement
(see my signature for details) and make sure to include all keys, indexes
and constraints.
And please, please, please remember (once again) that the method in books
online is a *rough estimate*... 57 MB over more than 2 million rows is
really not that alarming to me, and I'm amazed this is turning into a
two-day discussion.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Hi,
Thanks for the quick response and I apologize to test your patience. I
assumed that the estimated figure that I would get would be the closest one.
Are there any other methods with wich one can get more closer estimates? If
yes than can you give pointers to those. If not then can I have some pointers
to resources which would expain as to why such difference is seen between the
estimated figures and actual figures?
PS: I would be able to send you the DDL and sample data tomorrow as I would
need some more time to generate it.
Thanks once again...
Regards,
Vaibhav
"Aaron [SQL Server MVP]" wrote:
> > After comparing these two resulrs one can see the difference of 57 MB.
> Could
> > you please tell me why this much difference is observed.
> All those starts are fabulous but I still have no idea what kind of table we
> are talking about here. Can you please generate the CREATE TABLE statement
> (see my signature for details) and make sure to include all keys, indexes
> and constraints.
> And please, please, please remember (once again) that the method in books
> online is a *rough estimate*... 57 MB over more than 2 million rows is
> really not that alarming to me, and I'm amazed this is turning into a
> two-day discussion.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>|||The methods you're already using have worked fine for me. Again, 57 MB is
really not that big of a difference to me. When we are buying and
configuring disks we round to the terabyte, not the MB...
As for generating DDL, if you look at http://www.aspfaq.com/5006 you will
see that all you have to do is right-click the table in Enterprise Manager
and hit "All Tasks > Generate SQL Script"... you shouldn't have to write it
from scratch and if it takes more than two minutes there's something
seriously wrong.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
news:D16F7B3C-0E86-4C41-B6BD-F90031E36A6C@.microsoft.com...
> Hi,
> Thanks for the quick response and I apologize to test your patience. I
> assumed that the estimated figure that I would get would be the closest
one.
> Are there any other methods with wich one can get more closer estimates?
If
> yes than can you give pointers to those. If not then can I have some
pointers
> to resources which would expain as to why such difference is seen between
the
> estimated figures and actual figures?
> PS: I would be able to send you the DDL and sample data tomorrow as I
would
> need some more time to generate it.
> Thanks once again...
> Regards,
> Vaibhav|||Yes Aron I know that it would take less than 2 minutes to generate a script
of a table . Actually yesterday when I read your last reply it was alredy the
time to leave from the office, hence I could not send you the required
details. It seems I am disturbing you too much.
Thanks a lot mate for all the help provided till now.
Bye for now...
"Aaron [SQL Server MVP]" wrote:
> The methods you're already using have worked fine for me. Again, 57 MB is
> really not that big of a difference to me. When we are buying and
> configuring disks we round to the terabyte, not the MB...
> As for generating DDL, if you look at http://www.aspfaq.com/5006 you will
> see that all you have to do is right-click the table in Enterprise Manager
> and hit "All Tasks > Generate SQL Script"... you shouldn't have to write it
> from scratch and if it takes more than two minutes there's something
> seriously wrong.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Vaibhav" <Vaibhav@.discussions.microsoft.com> wrote in message
> news:D16F7B3C-0E86-4C41-B6BD-F90031E36A6C@.microsoft.com...
> > Hi,
> >
> > Thanks for the quick response and I apologize to test your patience. I
> > assumed that the estimated figure that I would get would be the closest
> one.
> > Are there any other methods with wich one can get more closer estimates?
> If
> > yes than can you give pointers to those. If not then can I have some
> pointers
> > to resources which would expain as to why such difference is seen between
> the
> > estimated figures and actual figures?
> >
> > PS: I would be able to send you the DDL and sample data tomorrow as I
> would
> > need some more time to generate it.
> >
> > Thanks once again...
> >
> > Regards,
> > Vaibhav
>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment