Partitioned View not operating as Documented

Hi All,
We have run into a huge problem at a large data-heavy multi-user
installation. In order to increase response times we split up a number of
tables into "Company" specific divisions to reduce the volume of records in
each Division and then placed Check constaints on each table by DIVID. We
Unioned the tables into partitioned views.
However, contrary to documentation and recommendations from this newsgroup,
even though we include DIVID in all our queries, Enterprise Manager and QA
indicate that SQL Server is opening ALL tables in the view during a Query or
an Update. I am thinking we must have done something wrong, or misunderstood
what partitoned views are supposed to do. We thought that the Check
constraints would allow SQL Server to key in immediately on the requested
divisional table. Here are some code examples:
A Table:
CREATE TABLE [PLTBDIV_ACT_DET_D00]
(
[DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
,[ACCTID] [CHAR] (10) NOT NULL
,[ACT_TYPE] [CHAR] (1) NOT NULL
,[TRANID] [CHAR] (10) NOT NULL
,[TRANDATE] [SMALLDATETIME]
,[TRANCODE] [CHAR] (5)
,[TRANMODE] [CHAR] (5)
,[TRANREF] [CHAR] (25)
,[CSHREF] [CHAR] (25)
,[REVERSED] [CHAR] (1)
,[CHECKNUM] [CHAR] (25)
,[CHECKACCTID] [CHAR] (10)
,[INV_NUM] [CHAR] (12)
,[REBILLID] [CHAR] (3)
,[INV_TYPE] [CHAR] (1)
,[TRANAMOUNT] [NUMERIC] (19,4)
,[APPLIED] [NUMERIC] (19,4)
,[AVAILABLE] [NUMERIC] (19,4)
,[COMMENT] [CHAR] (90)
,[CREATION] [SMALLDATETIME]
,[BATCHNUM] [CHAR] (12) NULL
,[CLOSING] [CHAR] (1)
,[USERID] [CHAR] (15)
)
Note the Check constraint.
The Primary Key:
ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
[PLPKDIV_ACT_DET_D00]
PRIMARY KEY CLUSTERED
(
[DIVID]
,[TRANID]
) --WITH FILLFACTOR = 10
The View is a simple Union of all tables.
Some TSQL tests run indicate that the Constraint is trusted.
EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
SELECT
OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrusted
')
The 2nd query returns 0.
Can anyone see what might be the problem? Or are partitoned views not meant
to improve performance?
Thanks to all in advance."John Kotuby" <johnk@.powerlist.com> wrote in message
news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> We have run into a huge problem at a large data-heavy multi-user
> installation. In order to increase response times we split up a number of
> tables into "Company" specific divisions to reduce the volume of records
> in each Division and then placed Check constaints on each table by DIVID.
> We Unioned the tables into partitioned views.
> However, contrary to documentation and recommendations from this
> newsgroup, even though we include DIVID in all our queries, Enterprise
> Manager and QA indicate that SQL Server is opening ALL tables in the view
> during a Query or an Update. I am thinking we must have done something
> wrong, or misunderstood what partitoned views are supposed to do. We
> thought that the Check constraints would allow SQL Server to key in
> immediately on the requested divisional table. Here are some code
> examples:
> A Table:
> CREATE TABLE [PLTBDIV_ACT_DET_D00]
> (
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
> ,[ACCTID] [CHAR] (10) NOT NULL
> ,[ACT_TYPE] [CHAR] (1) NOT NULL
> ,[TRANID] [CHAR] (10) NOT NULL
> ,[TRANDATE] [SMALLDATETIME]
> ,[TRANCODE] [CHAR] (5)
> ,[TRANMODE] [CHAR] (5)
> ,[TRANREF] [CHAR] (25)
> ,[CSHREF] [CHAR] (25)
> ,[REVERSED] [CHAR] (1)
> ,[CHECKNUM] [CHAR] (25)
> ,[CHECKACCTID] [CHAR] (10)
> ,[INV_NUM] [CHAR] (12)
> ,[REBILLID] [CHAR] (3)
> ,[INV_TYPE] [CHAR] (1)
> ,[TRANAMOUNT] [NUMERIC] (19,4)
> ,[APPLIED] [NUMERIC] (19,4)
> ,[AVAILABLE] [NUMERIC] (19,4)
> ,[COMMENT] [CHAR] (90)
> ,[CREATION] [SMALLDATETIME]
> ,[BATCHNUM] [CHAR] (12) NULL
> ,[CLOSING] [CHAR] (1)
> ,[USERID] [CHAR] (15)
> )
> Note the Check constraint.
> The Primary Key:
> ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
> [PLPKDIV_ACT_DET_D00]
> PRIMARY KEY CLUSTERED
> (
> [DIVID]
> ,[TRANID]
> ) --WITH FILLFACTOR = 10
> The View is a simple Union of all tables.
> Some TSQL tests run indicate that the Constraint is trusted.
> EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
> SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTrust
ed')
> The 2nd query returns 0.
> Can anyone see what might be the problem? Or are partitoned views not
> meant to improve performance?
>
Too little information.
Post the UNION view, along with a query againt the view, and the results for
running the query with SET STATISTICS_IO ON.
David|||John,
I don't know if it's what you're seeing, but a common confusion here
is that the *estimated* query plan shows all tables accessed with
equal cost, but the *actual* plan only accesses the relevant table.
In the query plan details, you will often see the dependence of
each table's access hinging on STARTUP_EXPR, which is
evaluated at run time before any of the tables are accessed.
If you run the query with SET STATISTICS IO ON in cases
like this, you will see all tables listed, but the number of reads
for the unneeded tables will be zero.
What exactly is indicating to you that "SQL Server is opening ALL
tables in the view" ?
Steve Kass
Drew University
John Kotuby wrote:

>Hi All,
>We have run into a huge problem at a large data-heavy multi-user
>installation. In order to increase response times we split up a number of
>tables into "Company" specific divisions to reduce the volume of records in
>each Division and then placed Check constaints on each table by DIVID. We
>Unioned the tables into partitioned views.
>However, contrary to documentation and recommendations from this newsgroup,
>even though we include DIVID in all our queries, Enterprise Manager and QA
>indicate that SQL Server is opening ALL tables in the view during a Query o
r
>an Update. I am thinking we must have done something wrong, or misunderstoo
d
>what partitoned views are supposed to do. We thought that the Check
>constraints would allow SQL Server to key in immediately on the requested
>divisional table. Here are some code examples:
>A Table:
>CREATE TABLE [PLTBDIV_ACT_DET_D00]
>(
> [DIVID] [CHAR] (3) NOT NULL CHECK (DIVID='D00')
>,[ACCTID] [CHAR] (10) NOT NULL
>,[ACT_TYPE] [CHAR] (1) NOT NULL
>,[TRANID] [CHAR] (10) NOT NULL
>,[TRANDATE] [SMALLDATETIME]
>,[TRANCODE] [CHAR] (5)
>,[TRANMODE] [CHAR] (5)
>,[TRANREF] [CHAR] (25)
>,[CSHREF] [CHAR] (25)
>,[REVERSED] [CHAR] (1)
>,[CHECKNUM] [CHAR] (25)
>,[CHECKACCTID] [CHAR] (10)
>,[INV_NUM] [CHAR] (12)
>,[REBILLID] [CHAR] (3)
>,[INV_TYPE] [CHAR] (1)
>,[TRANAMOUNT] [NUMERIC] (19,4)
>,[APPLIED] [NUMERIC] (19,4)
>,[AVAILABLE] [NUMERIC] (19,4)
>,[COMMENT] [CHAR] (90)
>,[CREATION] [SMALLDATETIME]
>,[BATCHNUM] [CHAR] (12) NULL
>,[CLOSING] [CHAR] (1)
>,[USERID] [CHAR] (15)
> )
>Note the Check constraint.
>The Primary Key:
>ALTER TABLE [dbo].[PLTBDIV_ACT_DET_D00] WITH NOCHECK ADD CONSTRAINT
>[PLPKDIV_ACT_DET_D00]
>PRIMARY KEY CLUSTERED
>(
> [DIVID]
>,[TRANID]
> ) --WITH FILLFACTOR = 10
>The View is a simple Union of all tables.
>Some TSQL tests run indicate that the Constraint is trusted.
>EXEC SP_HELPCONSTRAINT PLTBDIV_ACT_DET_D00
>SELECT
> OBJECTPROPERTY(OBJECT_ID('CK__PLTBDIV_A_
_DIVID__7DF19EE6'),'CnstIsNotTruste
d')
>The 2nd query returns 0.
>Can anyone see what might be the problem? Or are partitoned views not meant
>to improve performance?
>Thanks to all in advance.
>
>
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23jq5zRqcGHA.5048@.TK2MSFTNGP04.phx.gbl...
> John,
> I don't know if it's what you're seeing, but a common confusion here
> is that the *estimated* query plan shows all tables accessed with
> equal cost, but the *actual* plan only accesses the relevant table.
> In the query plan details, you will often see the dependence of
> each table's access hinging on STARTUP_EXPR, which is
> evaluated at run time before any of the tables are accessed.
> If you run the query with SET STATISTICS IO ON in cases
> like this, you will see all tables listed, but the number of reads
> for the unneeded tables will be zero.
> What exactly is indicating to you that "SQL Server is opening ALL
> tables in the view" ?
> Steve Kass
> Drew University
> John Kotuby wrote:
>|||Got caught up in work....
Here is a simple query that seems to be hitting only one table.
SET STATISTICS IO ON
SELECT DIVID, TRANID, INV_NUM FROM PLVWDIV_ACT_DET
WHERE DIVID = 'D02' AND ACT_TYPE = 'P' AND TRANDATE > '01/01/2006'
(1509 row(s) affected)
Table 'PLTBDIV_ACT_DET_D02'. Scan count 1, logical reads 5456, physical
reads 0,
read-ahead reads 4.
There are approximately 320,000 records in the PLTBDIV_ACT_DET_D02 table.
We join about 30 tables in the view.
We were watching Current Activity in EM at the site and refreshing every few
seconds and saw all tables in multiple partitioned views open at the same
time. In some cases every table had locks on them. We were getting timeouts
from the VB client.
When we changed our programming techniques to use Paramaterized queries that
hit the tables directly most of that contention just disappeared.
CREATE VIEW [PLVWDIV_ACT_DET]
-- DESC: UNIONED VIEW INTO ACCOUNTS DETAIL
AS
SELECT * FROM [PLTBCOM_ACT_DET] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D00] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D01] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D02] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D03] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D04] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D05] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D06] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D07] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D08] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D09] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D10] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D11] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D12] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D13] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D14] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D15] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D16] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D17] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D18] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D19] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D20] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D21] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D22] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D23] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D24] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D25] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D26] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D27] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D28] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D29] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D30] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D98] WITH (NOLOCK)
UNION ALL
SELECT * FROM [PLTBDIV_ACT_DET_D99] WITH (NOLOCK)
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eVErOIqcGHA.2404@.TK2MSFTNGP03.phx.gbl...
> "John Kotuby" <johnk@.powerlist.com> wrote in message
> news:OW40DgpcGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Too little information.
> Post the UNION view, along with a query againt the view, and the results
> for running the query with SET STATISTICS_IO ON.
> David
>