Issue with a table that is 75%-80% of the database size

Hello All,
I need help to figure out what I could do about a big
issue that we have with our database size.
Our database easily grows to a size over 250 GB and up.
And the majority of the data (about 75%) is stored in
this one table (tb_content)with several columns in which
we are storing text in its text data type column.
The database is used for online transaction processing
application.
Users connect to the database through a Query application
to query for a particular row of this big table
(tb_content). Of course the query inlcudes joining few
other tables in the database as well. After the result is
returned to the users, users would update the data in
serveral tables using the application.
The concerns are to improve the response time for each
query and reduce the time that it takes to backup the
database.
We were able to improve the query response time by
implementing Full-text indexing.
Any suggestions or recommendations that would help to
reduce the size of a table that holds 75%-80% of the data
content of a database to help to improve the query
response time and the time that it takes to backup the
database. Is there a such thing as to create a
particulare table of a database on a different filegroup?
How about partitioning a particulare table?
I appreciate your help!
Thank you,
-Mitra
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:008d01c48eb2$150f1790$a401280a@.phx.gbl...
> Any suggestions or recommendations that would help to
> reduce the size of a table that holds 75%-80% of the data
Have you considered normalizing your data instead of storing it as a big
blob of text? This would almost certainly result in a lot of the
performance increase and size decrease you're looking for.
|||Hello Adam,
You mentioned:

>Have you considered normalizing your data instead of
storing it as a big
>blob of text?
our table, tb_content, only have three columns:
Create Table tb_content
(
id int, --PK
size int, -- size of the content
content text -- the text of the messages
)
This table is normalized.
Thanks,
-Mitra
>--Original Message--
>"Mitra Fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:008d01c48eb2$150f1790$a401280a@.phx.gbl...
data
> Have you considered normalizing your data instead of
storing it as a big
>blob of text? This would almost certainly result in a
lot of the
>performance increase and size decrease you're looking
for.
>
>.
>
|||"mitra fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:2ddf01c48eb9$45515ab0$a601280a@.phx.gbl...
> This table is normalized.
What are you storing in the contents column? Please post a few rows of
sample data.
|||Here is a sample data:
id_col size_col content_col
1 24 Please review the notes.
2 46 Please email or call me when you
hear from Bob.
3 59 Did you hear the new news
about John moving to a new place.
We are storing the message body of emails in text in the
content column.
Please note the issue with this table seems to be that we
have too many rows (millions or billions)that leads to
75%-80% of the size of the database.
Thank you,
-Mitra

>--Original Message--
>
>"mitra fatolahi" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2ddf01c48eb9$45515ab0$a601280a@.phx.gbl...
> What are you storing in the contents column? Please
post a few rows of
>sample data.
>
>.
>
|||mitra fatolahi wrote:[vbcol=seagreen]
> Hello Adam,
> You mentioned:
>
> our table, tb_content, only have three columns:
> Create Table tb_content
> (
> id int, --PK
> size int, -- size of the content
> content text -- the text of the messages
> )
> This table is normalized.
> Thanks,
> -Mitra
Despite the fact that the table consumes 75% of the overall database
size, what issue or issues are you having with it? You can move the Text
column to another filegroup if you want with the TEXTIMAGE_ON {
filegroup | DEFAULT } option in Create Table. Or you can move the whole
table.
You can also save some the smaller text data in the row itself and the
larger text data using a pointer to the data using the the
sp_tableoption text in row function if performance is a concern.
If the table is highly-transactional, then consider moving it to a
mirrored drive array instead of a RAID 5 array.
You could partition the table into multiple tables using a partitioned
view (see BOL)
Other ways to reduce size may be to use a clustered PK in the ID column
instead of a non-clustered index or compress the text data and store as
binary.
David G.
|||Okay, your table certainly is normalized. I misunderstood what kind of data
you were storing.
Unless you can delete/archive some of the older data in the table, there's
really not a lot you can do to improve this situation. You could
potentially speed up queries by adding a SMALLDATETIME column and allowing
(or forcing) users to query based on date as well as text. You could also
potentially speed things up a bit using the "text in row" option (look up in
BOL for more information).
As for speed of backup, that's fully dependant upon data size. However, you
could consider using a third-party backup tool with built-in compression,
which would probably help quite a bit. Have you tried SQL Lite Speed (
http://www.imceda.com/LiteSpeed_Description.htm )? It's an excellent
product and really speeds up backups.
"mitra fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:2ead01c48ec1$b7d33100$a601280a@.phx.gbl...
> Here is a sample data:
> id_col size_col content_col
> 1 24 Please review the notes.
> 2 46 Please email or call me when you
> hear from Bob.
> 3 59 Did you hear the new news
> about John moving to a new place.
> We are storing the message body of emails in text in the
> content column.
> Please note the issue with this table seems to be that we
> have too many rows (millions or billions)that leads to
> 75%-80% of the size of the database.
> Thank you,
> -Mitra
>
|||mitra fatolahi wrote:
> Here is a sample data:
> id_col size_col content_col
> 1 24 Please review the notes.
> 2 46 Please email or call me when you
> hear from Bob.
> 3 59 Did you hear the new news
> about John moving to a new place.
> We are storing the message body of emails in text in the
> content column.
> Please note the issue with this table seems to be that we
> have too many rows (millions or billions)that leads to
> 75%-80% of the size of the database.
> Thank you,
> -Mitra
In addition to my other comments (in my other post), have you considered
storing the text as IDs into a table of valid comments. If you can at
least fit some of the common comments into a consolidated list, you can
just add a FK to the comments table. You could then use a text column
for any comments that do not fit into the standard.
Or you could use something like prinf() function provides, where there
are placeholders for specific data in the string, but the long text
itself is standardized.
For example, "Please email or call when you hear from %s" as a FK and
then just store the %s value in the table.
I guess there are many ways you could go here.
David G.
|||SQL Server 2000 does not support table partitioning. However, if you OLTP
query is simple enough, you might be able to use partitioned view to achieve
a similar result: create serveral tables with the same schema, then create a
view to union them together. If this is possible, then you can use put each
table in its own filegroup and use a filegroup based backup stragtegy.
Regarding reducing backup time, is your database recovery model simple or
full? Have you tried database differential backup?
SQL Server 2005 beta 2 has a table partitioning feature that might help both
your query response time and backup time. Please see this article for some
overview of SQL Server 2005 for DBA:
http://www.microsoft.com/technet/pro...n/sqlydba.mspx
Wei Xiao
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mitra Fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:008d01c48eb2$150f1790$a401280a@.phx.gbl...
> Hello All,
> I need help to figure out what I could do about a big
> issue that we have with our database size.
> Our database easily grows to a size over 250 GB and up.
> And the majority of the data (about 75%) is stored in
> this one table (tb_content)with several columns in which
> we are storing text in its text data type column.
> The database is used for online transaction processing
> application.
> Users connect to the database through a Query application
> to query for a particular row of this big table
> (tb_content). Of course the query inlcudes joining few
> other tables in the database as well. After the result is
> returned to the users, users would update the data in
> serveral tables using the application.
> The concerns are to improve the response time for each
> query and reduce the time that it takes to backup the
> database.
> We were able to improve the query response time by
> implementing Full-text indexing.
> Any suggestions or recommendations that would help to
> reduce the size of a table that holds 75%-80% of the data
> content of a database to help to improve the query
> response time and the time that it takes to backup the
> database. Is there a such thing as to create a
> particulare table of a database on a different filegroup?
> How about partitioning a particulare table?
> I appreciate your help!
> Thank you,
> -Mitra
>
>