partitioned views Explain Plan

Hello I created a partitioned view on my database db1 and It comes from the UNION ALL of 2 tables (I split 1 very big table in 2 smaller tables... Horizontal partition!!) that are on the same db1. When I query them I receive in the EXECUTION PLAN an Item called CONCATENATION of both input tables, This seems to be more expensive than using only one big table so partitioning the big one gets none sense.. I would like to know if this is normal and If I'm doing well with partitioning.

Please see the script below:

CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1),
[CDPRECIO] [varchar] (12),
[CDTIP_HECT_DTO_PAG] [varchar] (2),
[FEGENERACION] [datetime] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2006_CK] CHECK ([FEGENERACION] < '2007-01-01 00:00:00.000')
GO

CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (

[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,

[CDCONCEPTO] [varchar] (12) NOT NULL ,

[CDTIPO_VALOR] [char] (1),

[CDPRECIO] [varchar] (12),

[CDTIP_HECT_DTO_PAG] [varchar] (2),

[FEGENERACION] [datetime] NOT NULL ,

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD

CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED

(

[NMSEC_NOVEDAD]

) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_FEGENERACION2007_CK] CHECK ([FEGENERACION] > '2006-12-31 00:00:00.000'),
GO
CREATE VIEW TREP_NOVEDADES AS
SELECT * FROM TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM TREP_NOVEDADES_2007
GO

-- Here is where I query the view...
SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

Am I doing anything wrong ?

Initially I'd try creating a NONCLUSTERED Index on the FEGENERACION column in each table.

Chris

|||

I would tend to dissent in this case; it looks to me like this is a case in which you are going to have plan crossover and the optimizer will abandon the index in favor of some scan -- either a clustered index scan or a table scan.

Julian:

When have a great deal of data and you perform the search:

SELECT * FROM TREP_NOVEDADES_2006 WHERE FEGENERACION < '2006-05-23 00:00:00.000'

You are invariably going to get stuck with a long-running execution plan. In this case you are going to get somewhere around half of the records in your 2006 table. You cannot benefit from a simple index because the potential number bookmark lookups would make for a worse execution plan than a table scan. The only index that MIGHT be of some benefit would be a cover index that had its first column be (as Chris suggested) the FEGENERACION column. However, since your SELECT statement is for ALL columns I doubt that I would build such a cover index.

To me, the moral of the story is that if you are truely going to read something on the order of half of the data in a table you need to face the reality that you are going to do a table scan. Avoid such queries as much as you can.

|||

I guess I should have explained my reasoning... ;)

My thinking was that the table containing the more recent data would be eliminated quickly during execution of the query that was provided. As long as the statistics are up to date then surely this would result in an index seek in the more recent table - bookmark lookups would not be required as there would be no rows that met the criteria.

I would imagine that a table scan would be performed on the other table(s) rather than a seek and subsequent lookup, for the reasons that you mentioned, and that similar performance to the non-partitioned table would be seen.

Chris

|||

Please forgive me, Chris, I have messed up a little bit.

I walked over to the next building to fetch a liter of water and realized that although the index would probably not apply in this particular instance that Chris' suggested index was probably still a good suggestion. It will help in many instances in which you are filtering by date/time -- it just is not likely to help when you are selecting something like half the table.

Sorry, I messed up.

Kent

( You beat me; I tried to make amends before you felt compelled to respond; you are right )

|||

You've not messed anything up - you made some valid points in your post!

It's good to have an answer challenged and then to feel compelled to justify it. Anyway, my suggestion was only my opinion - it may or may not help the OP.

Chris

:)

|||Agreed. :-)|||Thank you so much guys for your point of view and your help...|||God Damn't ... I did what Chris said (those NONCLUSTERED indexes) ... and now subtree cost is sooooo low.... I hope It boost my queries...

Thank you so much

Partitioned View performance

What is wrong with my partitioned view?
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.

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
>