Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
Jerry
Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>
|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>
Showing posts with label required. Show all posts
Showing posts with label required. Show all posts
Sunday, March 25, 2012
DataSizer for 2000
Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to
sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC
SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for sever
al import tables.
> I've run the DataSizer tool and it calculates the following for a table wi
th 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the follo
wing information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table s
ize est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the
calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compar
e to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get
:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-)
.
Btw, I always select from sysindexes (after updateusage) when I want to get
these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To b
e honest, I don't
remember the difference between dpages and used by heart either, so I always
look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I
was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered in
dex on the table then
> dropped the clustered index to recompact the heap pages. The 137 million
rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than
before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to
sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC
SHOWCONTIG).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for sever
al import tables.
> I've run the DataSizer tool and it calculates the following for a table wi
th 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the follo
wing information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table s
ize est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the
calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compar
e to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get
:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-)
.
Btw, I always select from sysindexes (after updateusage) when I want to get
these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To b
e honest, I don't
remember the difference between dpages and used by heart either, so I always
look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I
was initially focused
on the stale information in sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered in
dex on the table then
> dropped the clustered index to recompact the heap pages. The 137 million
rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than
before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>
DataSizer for 2000
Hi,
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for
>> several import tables.
>> I've run the DataSizer tool and it calculates the following for a table
>> with 3 INT fields and one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the
>> following information via sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table
>> size est and table size actual). Is the DataSizer tool accurate for SQL
>> 2000? Am I performing the calculation incorrectly?
>> Thanks
>> Jerry
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
>> DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for several import tables.
>> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and
>> one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the following information via
>> sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table size est and table size
>> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
>> incorrectly?
>> Thanks
>> Jerry
>>
>
I'm trying to estimate the amount of space that will be required for several
import tables.
I've run the DataSizer tool and it calculates the following for a table with
3 INT fields and one REAL field (prec - 24).
Heap Table Data Sizer
Rows in table 137,242,307
Data Row Fixed Len Col Size 16
Number of Columns in data row 4
Number of Variable Length Columns in data row 0
Max Size of Variable Length Data in data row 0
Index Key Fixed Len Col Size 0
Number of Columns in Index Key 0
Number of Variable Length Columns in index key 0
Max Size of Variable Length Data in index key 0
Page size (not configurable) 8192
Page Hdr Size (not configurable) 96
Data Row Size Calculation
Data Row Header 4
Null Bitmap 3
Variable Length Columns Total Size 0
Minimum Row Length 16
Total Row Size 23
Data rows per page 324
Data pages 423588
Table Size 3,470,032,896
This example happens to be an existing table (heap) that reports the
following information via sp_spaceused
rows = 137242307
space used = 3569632 KB
There is about a 100MB difference between the two values returned (table
size est and table size actual). Is the DataSizer tool accurate for SQL
2000? Am I performing the calculation incorrectly?
Thanks
JerrySp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to estimate the amount of space that will be required for several import tables.
> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and one
> REAL field (prec - 24).
> Heap Table Data Sizer
> Rows in table 137,242,307
> Data Row Fixed Len Col Size 16
> Number of Columns in data row 4
> Number of Variable Length Columns in data row 0
> Max Size of Variable Length Data in data row 0
> Index Key Fixed Len Col Size 0
> Number of Columns in Index Key 0
> Number of Variable Length Columns in index key 0
> Max Size of Variable Length Data in index key 0
> Page size (not configurable) 8192
> Page Hdr Size (not configurable) 96
> Data Row Size Calculation
> Data Row Header 4
> Null Bitmap 3
> Variable Length Columns Total Size 0
> Minimum Row Length 16
> Total Row Size 23
> Data rows per page 324
> Data pages 423588
> Table Size 3,470,032,896
>
> This example happens to be an existing table (heap) that reports the following information via
> sp_spaceused
>
> rows = 137242307
> space used = 3569632 KB
> There is about a 100MB difference between the two values returned (table size est and table size
> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
> incorrectly?
> Thanks
> Jerry
>|||Hey Tibor.
I did use the updateusage param of sp_spaceused.
Yea I thought it might be due to fragmentation so I created a clustered
index on the table then dropped the clustered index to recompact the heap
pages. The 137 million rows now consume 3,399,200 KB (apx 70 MB less than
the estimated and about 170 MB less than before with the fragmentation).
Interesting ;-)
Thanks again for the reply.
Jerry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sp_spaceused might use stale information (see the @.updateusage parameter
> to sp_spaceused and also DBCC UPDATEUSAGE). Also, you might have less than
> 100% full pages (see DBCC SHOWCONTIG).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for
>> several import tables.
>> I've run the DataSizer tool and it calculates the following for a table
>> with 3 INT fields and one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the
>> following information via sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table
>> size est and table size actual). Is the DataSizer tool accurate for SQL
>> 2000? Am I performing the calculation incorrectly?
>> Thanks
>> Jerry
>|||Hi Jerry,
If I take the numbers from your calculation (3,470,032,896 bytes) and compare to the numbers from
sp_spaceused (I assume) (3,399,200 KB) and convert properly to MB etc, I get:
SELECT KB, KB/1024 AS MB, KB/(1024*1024) AS GB
FROM
(
SELECT 3470032896/1024 AS KB
UNION
SELECT 3399200 AS KB
) AS i
KB MB GB
-- -- --
3388704.000000 3309.28125000000 3.23171997070312500
3399200.000000 3319.53125000000 3.24172973632812500
I.e. a difference of 10 MB, which for a 3.2GB table is pretty close, IMO :-).
Btw, I always select from sysindexes (after updateusage) when I want to get these values. I never
remember whether sp_spaceused uses reserved, dpages or the used column. To be honest, I don't
remember the difference between dpages and used by heart either, so I always look up sysindexes in
BOL.
I should also say that I didn't actually look at your formula/calculation. I was initially focused
on the stale information in sysindexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23CmI1geoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hey Tibor.
> I did use the updateusage param of sp_spaceused.
> Yea I thought it might be due to fragmentation so I created a clustered index on the table then
> dropped the clustered index to recompact the heap pages. The 137 million rows now consume
> 3,399,200 KB (apx 70 MB less than the estimated and about 170 MB less than before with the
> fragmentation).
> Interesting ;-)
> Thanks again for the reply.
> Jerry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eTrz2aeoFHA.3448@.TK2MSFTNGP12.phx.gbl...
>> Sp_spaceused might use stale information (see the @.updateusage parameter to sp_spaceused and also
>> DBCC UPDATEUSAGE). Also, you might have less than 100% full pages (see DBCC SHOWCONTIG).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23yVzzudoFHA.1444@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> I'm trying to estimate the amount of space that will be required for several import tables.
>> I've run the DataSizer tool and it calculates the following for a table with 3 INT fields and
>> one REAL field (prec - 24).
>> Heap Table Data Sizer
>> Rows in table 137,242,307
>> Data Row Fixed Len Col Size 16
>> Number of Columns in data row 4
>> Number of Variable Length Columns in data row 0
>> Max Size of Variable Length Data in data row 0
>> Index Key Fixed Len Col Size 0
>> Number of Columns in Index Key 0
>> Number of Variable Length Columns in index key 0
>> Max Size of Variable Length Data in index key 0
>> Page size (not configurable) 8192
>> Page Hdr Size (not configurable) 96
>> Data Row Size Calculation
>> Data Row Header 4
>> Null Bitmap 3
>> Variable Length Columns Total Size 0
>> Minimum Row Length 16
>> Total Row Size 23
>> Data rows per page 324
>> Data pages 423588
>> Table Size 3,470,032,896
>>
>> This example happens to be an existing table (heap) that reports the following information via
>> sp_spaceused
>>
>> rows = 137242307
>> space used = 3569632 KB
>> There is about a 100MB difference between the two values returned (table size est and table size
>> actual). Is the DataSizer tool accurate for SQL 2000? Am I performing the calculation
>> incorrectly?
>> Thanks
>> Jerry
>>
>
Wednesday, March 7, 2012
Datacenter move.. Plan for SQL Servers
We will be moving to a new datacenter and wondering if there was any article
out there to help me get started on the planning required..
ThanksThis should be no difference than planning to move an instance/database to
another server, assuming you are going to a new server in the new datacenter.
Linchi
"Hassan" wrote:
> We will be moving to a new datacenter and wondering if there was any article
> out there to help me get started on the planning required..
> Thanks
>
>|||Take a look into the article:-
http://www.forsythe.com/infrastrat&man/dcr/documents/MovingTheDataCenter.pdf
http://www.shunra.com/articles.aspx?articleId=10
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23CTPtSgOHHA.4484@.TK2MSFTNGP02.phx.gbl...
> We will be moving to a new datacenter and wondering if there was any
> article out there to help me get started on the planning required..
> Thanks
>
out there to help me get started on the planning required..
ThanksThis should be no difference than planning to move an instance/database to
another server, assuming you are going to a new server in the new datacenter.
Linchi
"Hassan" wrote:
> We will be moving to a new datacenter and wondering if there was any article
> out there to help me get started on the planning required..
> Thanks
>
>|||Take a look into the article:-
http://www.forsythe.com/infrastrat&man/dcr/documents/MovingTheDataCenter.pdf
http://www.shunra.com/articles.aspx?articleId=10
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23CTPtSgOHHA.4484@.TK2MSFTNGP02.phx.gbl...
> We will be moving to a new datacenter and wondering if there was any
> article out there to help me get started on the planning required..
> Thanks
>
Sunday, February 19, 2012
Database/Log placement
I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
quote:
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave
Database/Log placement
I know that Database 101 required that you place your log files and
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave
your databases on different phyiscal disks for the purpose of disaster
recovery. Does this still hold true if a SAN (EMC) is in the picture?
A client site configured their initial log file drive too
small, and they have proposed moving the logs to the database drive
which has plenty of free space.
They are aware that they will be losing some performance as the log
file drive was on its own spindle for the sequential writes.
TIA,
DaveAn EMC device will be able to protect you against physical failure of the
drives or LUN but can do nothing about the health of the filesystem on which
the database files reside. A corrupted filesystem can get you as badly as a
failed LUN. Having your logs on a different filesystem allows you to
restore to the point of failure by using a BACKUP LOG WITH NO_TRUNCATE if
the LUN with the physical database files goes away. That said, we live in
the real world and many of my client have to keep data files and log files
on the same filesystem. Just keep good backups.
Christian
"Dave" <dderocha@.hotmail.com> wrote in message
news:7e4fb551.0402050821.76b46122@.posting.google.com...
> I know that Database 101 required that you place your log files and
> your databases on different phyiscal disks for the purpose of disaster
> recovery. Does this still hold true if a SAN (EMC) is in the picture?
> A client site configured their initial log file drive too
> small, and they have proposed moving the logs to the database drive
> which has plenty of free space.
> They are aware that they will be losing some performance as the log
> file drive was on its own spindle for the sequential writes.
> TIA,
> Dave
Tuesday, February 14, 2012
Database Tuning Advisor - how does the max. space parameter work?
I think that the min. space required for physical design structures may be
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting
reported incorrectly. Please see the thread at the following URL:
http://groups.google.ca/group/SQL-Se...f3426d5a?hl=en
No it is not.I have tried my best to explain in the other thread based
on my interpretation of your posting
Subscribe to:
Posts (Atom)