Data storage question

I am migrating Sybase databases to SQL 2000. For the most part I am using a
Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
problem that I'm running into is that the size of the databases are 3, 4 or
more times bigger in SQL then they where in Sybase. The database that I
moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
Some mentioned to me to how SQL is setup in regards to number of rows per
page. I've let to find anything that will help me to look this information
up. I've been through sp_configure but I didn't see anything that jump out
at me. Any help would be nice.
TIA All.
JoeIt's probably due to lots of page splits. Did you add the indexes before or
after you imported the data? Make sure each table has a clustered index and
try reindexing the table. That should free up a lot of space and then you
can do a one time shrink to a reasonable size. You will want to reindex
again after the shrink though since the shrink moves data around again.
--
Andrew J. Kelly SQL MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Thanks Andrew, I'll take a look and see about the indexes.
JD
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> It's probably due to lots of page splits. Did you add the indexes before
> or after you imported the data? Make sure each table has a clustered
> index and try reindexing the table. That should free up a lot of space
> and then you can do a one time shrink to a reasonable size. You will want
> to reindex again after the shrink though since the shrink moves data
> around again.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||reindexing your database will olso increase your performance as well as it
will organize the rows splited into pages
remember to make a backup before and after of this process and that the
database will not be abailable during this time
use dbcc dbreindex instead of dbcc indexdefrag
"Joe D" wrote:
> Thanks Andrew, I'll take a look and see about the indexes.
> JD
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> > It's probably due to lots of page splits. Did you add the indexes before
> > or after you imported the data? Make sure each table has a clustered
> > index and try reindexing the table. That should free up a lot of space
> > and then you can do a one time shrink to a reasonable size. You will want
> > to reindex again after the shrink though since the shrink moves data
> > around again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe D" <jkdriscoll@.qg.com> wrote in message
> > news:df4s9i$j23$1@.sxnews1.qg.com...
> >>I am migrating Sybase databases to SQL 2000. For the most part I am using
> >>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> >>The problem that I'm running into is that the size of the databases are 3,
> >>4 or more times bigger in SQL then they where in Sybase. The database that
> >>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> >>why' Some mentioned to me to how SQL is setup in regards to number of
> >>rows per page. I've let to find anything that will help me to look this
> >>information up. I've been through sp_configure but I didn't see anything
> >>that jump out at me. Any help would be nice.
> >>
> >> TIA All.
> >>
> >> Joe
> >>
> >
> >
>
>|||Hi Joe
Another reason for ballooning of db size could be if you have a lot of fixed
length columns that have a lot NULL values. I believe Sybase still works
like older versions of SQL Server, and any column that is NULL takes 0
bytes. In SQL Server 7 and 2000, a fixed width column takes the full number
of bytes, whether it is NULL or not..
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
> I am migrating Sybase databases to SQL 2000. For the most part I am using
> a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> The problem that I'm running into is that the size of the databases are 3,
> 4 or more times bigger in SQL then they where in Sybase. The database that
> I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> why' Some mentioned to me to how SQL is setup in regards to number of
> rows per page. I've let to find anything that will help me to look this
> information up. I've been through sp_configure but I didn't see anything
> that jump out at me. Any help would be nice.
> TIA All.
> Joe
>|||Very good point Kalen.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Another explanation for the increased space requirements is that clustered
index keys are also stored in non-clustered index leaf pages. These keys
are used as 'pointers' to the data. This can add up if you have wide
clustered index keys and a lot of non-clustered indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Morning Kalen,
This was my thought but I seemed unable to find any supporting
documentation. I appreciate the feed back.
Also, Andrew's suggestion of re-indexing has proved to be of help. Now I
need to go back through the databases that I have already migrated and
re-index those.
Thanks much,
Joe
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Are there any work around for this Dan?
Thanks for you input.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
> Another explanation for the increased space requirements is that clustered
> index keys are also stored in non-clustered index leaf pages. These keys
> are used as 'pointers' to the data. This can add up if you have wide
> clustered index keys and a lot of non-clustered indexes
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||Regarding index space requirements, I can only suggest that you re-evaluate
your indexing strategy. Keep in mind that although storing the clustered
index key in non-clustered indexes may increase disk space requirements, it
also reduces non-clustered index maintenance because the non-clustered
indexes won't need to change when page splits occur. Also, non-clustered
indexes can better cover certain queries.
As with any DBMS migration, you need to consider architectural differences
in your migration. A simple migration tool may get you part of the way
there but additional work is often needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7052$vdu$1@.sxnews1.qg.com...
> Are there any work around for this Dan?
> Thanks for you input.
> Joe
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the databases
>>are 3, 4 or more times bigger in SQL then they where in Sybase. The
>>database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>>like to know why' Some mentioned to me to how SQL is setup in regards to
>>number of rows per page. I've let to find anything that will help me to
>>look this information up. I've been through sp_configure but I didn't see
>>anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>|||Thanks Dan for your advise and it does help.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OiG%23X3vrFHA.2588@.tk2msftngp13.phx.gbl...
> Regarding index space requirements, I can only suggest that you
> re-evaluate your indexing strategy. Keep in mind that although storing
> the clustered index key in non-clustered indexes may increase disk space
> requirements, it also reduces non-clustered index maintenance because the
> non-clustered indexes won't need to change when page splits occur. Also,
> non-clustered indexes can better cover certain queries.
> As with any DBMS migration, you need to consider architectural differences
> in your migration. A simple migration tool may get you part of the way
> there but additional work is often needed.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df7052$vdu$1@.sxnews1.qg.com...
>> Are there any work around for this Dan?
>> Thanks for you input.
>> Joe
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the
>>databases are 3, 4 or more times bigger in SQL then they where in
>>Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>>in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>>in regards to number of rows per page. I've let to find anything that
>>will help me to look this information up. I've been through sp_configure
>>but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>|||Supporting documentation?
I guess you haven't read "Inside SQL Server". :-)
It shows you down to the byte level how rows are organized on your pages for
each datatype, and even has examples for null columns of large width. It
also provides the tools for you to examine the page layouts for yourself so
you don't have to take anybody's word for it. You can answer you own
questions.
The book also talks a lot about indexing strategies. (And yes, I am the
author.)
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7001$vdq$1@.sxnews1.qg.com...
> Morning Kalen,
> This was my thought but I seemed unable to find any supporting
> documentation. I appreciate the feed back.
> Also, Andrew's suggestion of re-indexing has proved to be of help. Now
> I need to go back through the databases that I have already migrated and
> re-index those.
> Thanks much,
> Joe
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Hi Joe
>> Another reason for ballooning of db size could be if you have a lot of
>> fixed length columns that have a lot NULL values. I believe Sybase still
>> works like older versions of SQL Server, and any column that is NULL
>> takes 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the
>> full number of bytes, whether it is NULL or not..
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the
>> databases are 3, 4 or more times bigger in SQL then they where in
>> Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>> in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>> in regards to number of rows per page. I've let to find anything that
>> will help me to look this information up. I've been through sp_configure
>> but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>
>