Did SP1 fix Query Optimizer issues w/ Partitioned tables?

Hi there,

We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values!

Does anyone know if SP1 addressed this problem?

Thanks

-Walter

? Are you certain that it's scanning all of the partitions? You might want to look at the Actual Row Count in the execution plan to find out what's really happening. Partition elimination may not occur when the query is compiled -- instead, it may use a runtime value. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:fd66588f-8124-45c7-b977-6785bf117a17@.discussions.microsoft.com... Hi there, We've implemented a partitioned table to a large table within our EDW. However, we'd discovered that the Query Optimizer was not able to understand parameterized queries. It basically was scanning all the partitions even though a parameter was passed defining the partition to use. Consequently, the queries need to hard code parameter values! Does anyone know if SP1 addressed this problem? Thanks -Walter|||

Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release.

-Walter Biffi

|||? Can you describe the scenario in a bit more detail? Or better yet, post a script to reproduce the issue? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Walti@.discussions.microsoft.com> wrote in message news:a11d1a77-7f52-43cb-bf37-5798188d9a0c@.discussions.microsoft.com... Yes, I am sure that it is scanning all the partition. We had a MS champion at the site to overview the full EDW design, and he confirmed that it was a known problem and that it would have been fixed on a future SP release. -Walter Biffi

Determining the nature of partitioned tables

I apologize in advance if this is something obvious I've missed ... fresh eyes/brain and all that.

If I have a table that is using a particular partition scheme/function, is there a quick and easy way to determine which column of that table is being used for partitioning? We're examining a number of legacy structures and we're hoping to reduce the time it's going to take us to get the report management wants.

Thanks.

The partition column information is saved in sys.index_columns catalog view. use the following script to check the partition scheme and column:

select c.name AS PartitionColumn,ic.partition_ordinal AS PartitionColOrder,dsidx.[name] AS PartitionScheme
from sys.indexes AS idx, sys.columns c,
sys.index_columns ic, sys.data_spaces AS dsidx
where idx.[object_id]=object_id('dbo.YouTable')
and idx.index_id<2
and idx.[object_id]=ic.[object_id]
and idx.index_id=ic.index_id
and dsidx.data_space_id = idx.data_space_id
and dsidx.type=N'PS'
and c.object_id=ic.[object_id]
and c.column_id=ic.column_id
and ic.partition_ordinal>0

Determining the nature of partitioned tables

I apologize in advance if this is something obvious I've missed ... fresh eyes/brain and all that.

If I have a table that is using a particular partition scheme/function, is there a quick and easy way to determine which column of that table is being used for partitioning? We're examining a number of legacy structures and we're hoping to reduce the time it's going to take us to get the report management wants.

Thanks.

The partition column information is saved in sys.index_columns catalog view. use the following script to check the partition scheme and column:

select c.name AS PartitionColumn,ic.partition_ordinal AS PartitionColOrder,dsidx.[name] AS PartitionScheme
from sys.indexes AS idx, sys.columns c,
sys.index_columns ic, sys.data_spaces AS dsidx
where idx.[object_id]=object_id('dbo.YouTable')
and idx.index_id<2
and idx.[object_id]=ic.[object_id]
and idx.index_id=ic.index_id
and dsidx.data_space_id = idx.data_space_id
and dsidx.type=N'PS'
and c.object_id=ic.[object_id]
and c.column_id=ic.column_id
and ic.partition_ordinal>0