Sql-server – getting Table scan on a partitioned table query

partitioningsql serversql-server-2008sql-server-2008-r2

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table.

When I run the following:

SELECT  *
FROM    dbo.INVENTORY_BREAK
WHERE   IR_ID IN ( 100, 101, 102 )

I get the following execution plan:

enter image description here

It states Table Scan (HEAP). Not quite sure what it means in the context of a partitioned table. I also don't see that it uses any kind of index. And yet, it must, because the query comes back fairly fast (e.g. the table has 6 million rows).

So, my questions are:

  1. What does Table Scan (HEAP) mean for a partitioned table?
  2. Does it indeed use an index, perhaps behind the scenes?
  3. Is there anything I need to do to improve efficiency?

Best Answer

The query did do partition elimination.

If you look at the tooltip, the Actual Partition Count says 3, which means it touched 3 partitions. (The Seek Predicate section also confirm this.)

The reason why it appears as a table scan is because it's a table scan with partition elimination. I'm not sure how else to explain that.

No indexes were used for this. Table partitioning is implemented deeper down in the database internal structures than an index, so partition elimination is very efficient by nature (given a suitable predicate, which you provided).

That said, the predicate you've given is suitable only for partition elimination, and (very likely) not much else because it's not very selective. If all the rows from those partitions are required, this is already close to an optimal solution -- creating a useful clustered index is usually recommended.

Related Question