million rows user table and growing

For our web site, we now have a million users and we have one user table
right now..
We would like to consider scaling this out as we grow to 5 -10 million users
and not have it in one table.
Every time a user logs on or changes profile,etc, we dont want to cause
contention or blocking on this table.. so whats the best way to go about
this ?
If i partition the table, whats the best way to go about partitioning it ?
Thanks> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
As long as you have appropriate indexing, I wouldn't expect performance or
concurrency problems regardless of table size. A few million rows really
isn't that large nowadays and, in my option, doesn't warrant partitioning.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Perhaps your company should consider hiring an experienced DBA' One that
can guide you proactively instead of reactively and successfully get you to
the level you wish to achieve.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks|||Focus on indexing strategy , statistics , as 1 million rows is not that
much.
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Hassan" <hassan@.test.com> wrote in message
news:%23ytph$8XIHA.4196@.TK2MSFTNGP04.phx.gbl...
> For our web site, we now have a million users and we have one user table
> right now..
> We would like to consider scaling this out as we grow to 5 -10 million
> users and not have it in one table.
> Every time a user logs on or changes profile,etc, we dont want to cause
> contention or blocking on this table.. so whats the best way to go about
> this ?
> If i partition the table, whats the best way to go about partitioning it ?
> Thanks

Million records table

Hi everybody,
I set up a merge replication on Sql2K with one publisher/distributor
and 6 anonymous subscribers that run msde.
The db is growing bigger and bigger and i can't understand why.
I noticed that some of the MS_xxxxx tables are very large: in
particular one is 16.7 millions and another over 770K. Is there a way
to reduce/shrink these tables?
Thanks
Lorenzo
How many rows are being modified (inserted/updated/deleted) in the replicated
articles? I would expect this to be a similar order of magnitude to the
msmerge-contents, msmerge_tombstone tables. These tables will be
automatically cleared down during synchronization as part of the meta data
cleanup naturally run in merge by sp_mergemetadataretentioncleanup so
normally you don't need to do anything - the rows will be removed as they
reach the retention period defined in the publication.
HTH,
Paul Ibison
|||On 17 Mag, 17:32, Paul Ibison <Paul.Ibi...@.Pygmalion.Com> wrote:
> How many rows are being modified (inserted/updated/deleted) in the replicated
> articles? I would expect this to be a similar order of magnitude to the
> msmerge-contents, msmerge_tombstone tables. These tables will be
> automatically cleared down during synchronization as part of the meta data
> cleanup naturally run in merge by sp_mergemetadataretentioncleanup so
> normally you don't need to do anything - the rows will be removed as they
> reach the retention period defined in the publication.
> HTH,
> Paul Ibison
Hi Paul,
i don't know exactly haw many rows are being modified, but i can tell
you that the largest replicated table counts less than 130000 records
and i'm sure only a small part of these are changed/added/deleted
the msmerge_tombstone now counts almost 7 millions rows
i'm having some timeout troubles when i synchronize and i often get
the "can't enumerate changes" error
now i'm checking all my tables and indexes to see if some are missing
(the publication is partitioned with dynamic filtering)
thanks a lot for your help
lorenzo
|||Paul Ibison:
> I'd do a join between the msmerge_tombstone table and sysmergearticles. Make
> it a group by on article name and count the records per article then compare
> this to the rowcounts. It may be that there are other articles/publications
> contributing that you haven't accounted for, but even if not, this should
> clarify the situation a bit.
> HTH,
> Paul Ibison
that was a great idea, Paul
i found that there are over 5 million rows relating to a view that
returns 4800!
now i know what to investigate ;-)