Horizontal Table Partitioning HELP!!

[talenx]

Hi all,

I was wondering it anyone has successfully implemented horizontal table partitioning?
I have written some simple logic to dynamically create, load and drop the partitioned tables ... which seems to work very well.
One thing I have noticed is in development I have defined a date key (integer based) thinking that the joining methodology would take care of the check constraint. But as it turns out ... it doesn't

Noted in the BOL
[blue]
Apply Conditions Directly to the Fact Table
For the best query performance, all queries must place conditions on the filter key directly in the fact table. A query that places the constraint on a second table, such as a Dates dimension table, will include all partitions in the query. Standard star join queries into a UNION ALL fact table work well:

Create star query WHERE clauses in the standard way, by placing conditions on attributes of any dimension table that is not partitioned.
Include attributes from the partitioning dimension (Dates).
Design queries against a partitioned dimensional schema exactly as you would against a non-partitioned schema, with the exception that conditions on dates are most effective when placed directly on the date key in the fact table.
If each partition table has a clustered index with date as the first column in the index, the cost of going to all partitions to resolve an ad hoc query is relatively small. Predefined queries, such as those that generate standard reports or that incrementally update downstream databases, should be written as efficiently as possible.


[/blue]

This is an issue... seeing that it doesn't take advantage of a star schema.
Example

SELECT * FROM FACT_TABLE 
INNER JOIN DIMENSION_TABLE
ON
PARTITION_KEY = DIMENSION_KEY
WHERE DIMENSION_NAME = 'XYZ'

Both FACT_TABLE.PARTITION_KEY and DIMENSION_TABLE.DIMENSION_KEY are indexed.

This result would attempt to include ALL partitioned table verses if you had filtered on the fact table.
Can this be right????
I would assume that the analyzer would be able to build a indexed list from the dimension table based on the where clause then applied that list within a nested loop against the partition table check constraint...

Any Thoughts
Thanks
TalenX

 

[Catadmin]

First, a question. Are you using SQL 2000 or SQL 2005? Partitioning varies a great deal between the two.

Thanks,



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[talenx]

Hi Catadmin,

We are currently using SQL Server 2000 sp 3a, though I have been eyeing 2005 very cool new partitioning logic... though is has a pretty heavy price tag... Most likely that management is going to want to see if 2000 will make due before making the jump.

Thanks
TalenX

 

[talenx]

Is it just me or does the BOL not make since as to why this is what it is... is Microsoft expecting you to store dimensional data in you fact tables??? That can't be right... (Other wise what’s the point of partitioning your data if you can take advantage of the whole purposes of the partitioning logic.)

BTW sorry i forgot to inculde the BOL link:

https://www.microsoft.co.ke/technet/prodtechnol/sql/2000/reskit/part5/c1861.mspx

If I understand this correctly Microsoft states if the filtered object is NOT in the partitioned fact table the optimizer will pull ALL partitioned tables within the view into the plan... in hence is scanning across every partitioned table with the view (ignoring the possibility that the data may only reside in one table?

Maybe I’m missing something but I would think the optimizer would be intelligent enough to scan only the table constraints within the partition table and THEN bring only those valid partitioned tables into the plan...

hmm... at least is sounds logical in my head.

Any Thoughts

Thanks TalenX

 

[Catadmin]

Haven't done partitioning in SQL Server 2000 yet, so I am unsure of the answer to this. Dimensional data smacks of Cubes and Analysis Services, which I would think doesn't go along with partitioned tables very well.

Let me do a little research and see what I can find. In the meantime, if someone else has an answer for TalenX, please post.

Thanks,



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[talenx]

From what I have read SQL 2000 can support partitioning, currently we are in an EDW architecture, load times are unreal , I think I have squeezed every bit of performance I m gonna get out of it. Not to mention we are going to be a move to a new SAN which will obviously bump up performance. Though I would like to see if I can perform again additional performance changes prior to making the move... see that the enhanced hardware will make it more difficult to pinpoint the underlining issues.
the partitioning schema I had built work well in development ( then again that’s on a small grade server that is not a production standards with a DA disk subsystem. so there isn't any disk partitioning in place.)

Unfortunately I haven’t been able to find any other documentation on the down fall of partitioning in SQL 2000 other then that paragraph on the BOL.

Trust me, I think i have researched everywhere... maybe I'm not looking hard enough.

Thanks
TalenX