Large table management and Partitioned Views

Just trying to get feedback from people on their practical experience with
managing large tables (>250mil rows). Has anyone used Partitioned Views and
how do they work? Any performance advantage? If not partitioned views, is
there anything else one can do in a large enterprise environment to manage
large tables?Hi,
from my experience, Partitioned Views will actually reduce IO read (physical
read and read-ahead read).
it depends on how you split your table and file group.
Leo
"LC" wrote:
> Just trying to get feedback from people on their practical experience with
> managing large tables (>250mil rows). Has anyone used Partitioned Views and
> how do they work? Any performance advantage? If not partitioned views, is
> there anything else one can do in a large enterprise environment to manage
> large tables?|||Thanks Leo,
Is the reduction in IO reads due to the fact the tables are split
up/smaller? Are there any other benefits or caveats to moving to this type
of architecture?
"Leo Leong" wrote:
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physical
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
> > Just trying to get feedback from people on their practical experience with
> > managing large tables (>250mil rows). Has anyone used Partitioned Views and
> > how do they work? Any performance advantage? If not partitioned views, is
> > there anything else one can do in a large enterprise environment to manage
> > large tables?|||before I implemented it, i did a testing in a test environment.
i split a table into 6 where each table is stored in separate data file.
the partition key for the table is TYPR, let say.
so, when i ran a query with "SET STATISTIC IO ON" to select record based on
TYPE = "A", i found that SQL Server will only read the data from the table
that stores TYPE = "A" only.
of course, when the table is split, data file that stores the child tables
is smaller. so, relatively, it will fasten the process of searching a record.
another benefit of partitioned view is you can do distributed database
environment. that means, those child tables can be stored in > 1 database
servers.
Leo
"LC" wrote:
> Thanks Leo,
> Is the reduction in IO reads due to the fact the tables are split
> up/smaller? Are there any other benefits or caveats to moving to this type
> of architecture?
> "Leo Leong" wrote:
> > Hi,
> >
> > from my experience, Partitioned Views will actually reduce IO read (physical
> > read and read-ahead read).
> > it depends on how you split your table and file group.
> >
> > Leo
> >
> > "LC" wrote:
> >
> > > Just trying to get feedback from people on their practical experience with
> > > managing large tables (>250mil rows). Has anyone used Partitioned Views and
> > > how do they work? Any performance advantage? If not partitioned views, is
> > > there anything else one can do in a large enterprise environment to manage
> > > large tables?