October 16, 2015 at 3:38 pm
I have a large partitioned table, it is partitioned by clustered index. All remaining non-clustered indexes are not partitioned.
If I select execution plan from a query that uses non-clustered index, it properly chooses index scan on it, but in "ordered" property it has false, thereby selecting a Sort operation as a next step making all plan inefficient.
I simplified and abstracted my case, and here is the code I use if somebody wants to recreate a situation. I compare non-partitioned table with partitioned one.
First, I create and populate a table with 100k records and create clustered and non-clustered indexes:
if object_id('t1') is not null
drop table t1
go
create table t1
(c1 smalldatetime,
c2 int,
c3 varchar(100)
)
go
set nocount on
INSERT INTO t1
SELECT DATEADD(dd, RAND() * 3000 ,'01/01/2008'),
floor(rand()*10000),
REPLACE(CAST(NEWID()AS VARCHAR(40))+CAST(NEWID()AS VARCHAR(40)),'-','');
go 100000
create clustered index idx_c1
on t1(c1)
create index idx_c2
on t1(c2)
Now, if I run a simple query :
select c1 from t1
order by c2
it chooses index scan on c2 with Ordered = true.
Next, I copy it to another table and partition it on a partition scheme ps_test:
select *
into t2
from t1
create clustered index idx_c1
on t2(c1)
on ps_test(c1)
create index idx_c2
on t2(c2)
Now I run an execution plan for this query:
select c1 from t2
order by c2
It selects index scan on c2 with (Ordered = false) + Sort + parallelism.
Why? Does really the fact that this table is partitioned breaks the "orderness" of this index? I thought that partitioning will improve performance, not degrade it. Unless I am doing something wrong. Please advise.
October 23, 2015 at 7:21 am
The primary benefit of partitioning is administrative - backup, restore, archiving. It can help data load performance and can also help the performance of queries that access data from a subset of the partitions. Do a search for "partition elimination"; there are plenty of articles on it. As your query is selecting every row in the table, partitioning isn't going to help performance.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply