SQL and Star Schema 1

[talenx]

Hi all,
I was curious if anyone has heard of SQL server 2000 not optimizing it's self for a star schema.
Well... kind of...
I have a situation where the optimizer tends to display some odd results in execution plan. It wills attempt to first PULL ALL of the data from the fact table (which is huge by the way) something like 367 million last time I checked.
Anyways then it will join to the dimensional table to retrieval the filtered indexed value. It then rolls through a nested loop utilizing both indexes and finally spitting out a result. (10 min later) it’s almost like the optimizer is not take the table weight in to factor prior to execution

I have checked the index to confirm that they were valid and free of major fragmentation and also confirmed the index statistics were up to date.

Here is a dumbed down version of the SQL code

SELECT count(SP_Key) 
FROM FACT_TABLE with (nolock)
INNER JOIN DIMENSIONAL_TABLE
ON
DIMENSIONAL_TABLE.time_dimension_key = FACT_TABLE .Submitted_Date_Key
WHERE DIMENSIONAL_TABLE.date_number BETWEEN '1/1/2006 00:00:00.000' AND '3/31/2006 00:00:00.000'

Any thoughts

Thanks
Talenx

 

[mrdenny]

Your scan density on your fact table is horrible. 21% is VERY low. You want that number as close to 100% as you can get it.

You can also try doing a full scan to get some new stats.

UPDATE STATISTICS [Pharmacy_Data_Fact_TABLE] WITH FULLSCAN, ALL

This will take a while and will lock the table while it runs, so you'll need some down time to run it. It may be that you don't have enough data in your sampeling. With only 76 million records that shouldn't be a problem, but it might be.

When you did the SHOWCONTIG for the fact table did you do it on the table or the index you are trying to use?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[talenx]

Yeah, I saw that after I posted it... yuk!!
I will have to wait until this weekend to rebuild those indexes... loads are done nightly and usually inserts around 2 5 million record a night. So I need to be able to perform so index maintenance some time after loads but prior to users accessing the production data...
Fun stuff!!!!

To answer your question. Yes the indexes in both tables that are being used in the joins is included in the SHOWCONTIG results.

Thanks
TalenX

 

[talenx]

Well, I did find some time to rebuild those indexes and updated the stats. Oddly the plan has not changed it’s tune… once more, what’s even more weird is I have a clustered index on the fact table and I just noticed that when I do define a SARG value to the date range it attempts to use the clustered index and not the defined index for the column.
Hmm. How can that be… shouldn’t it use the defined index?

 

[vongrunt]

>... when I do define a SARG value to the date range it attempts to use the clustered index and not the defined index for the column.

That depends on range itself - chances to have index used in exec plan get smaller if range is larger...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[talenx]

Ok that makes since but, the odd thing here is the clustered index doesn't contain the column that the SARG range is being applied to. Yet it still uses it.
Is the clustered index looked at first when determining if the range can but scanned within the clustered index before using the non-clustered index that the column is applied to?

Thanks
TalenX

 

[vongrunt]

Clustered index scan = table scan for tables with clustered index. 'Nuff said

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

Clustered indexes are the most effecent way for SQL to do a BETWEEN lookup on a table. Not sure why it's reverting to the clustered index when it shouldn't be.

Can you post the new SHOWCONTIG for the fact table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com