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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:

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 a
nd
> how do they work? Any performance advantage? If not partitioned views, i
s
> 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:
[vbcol=seagreen]
> Hi,
> from my experience, Partitioned Views will actually reduce IO read (physic
al
> read and read-ahead read).
> it depends on how you split your table and file group.
> Leo
> "LC" wrote:
>|||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:
[vbcol=seagreen]
> 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 typ
e
> of architecture?
> "Leo Leong" wrote:
>