Did it do partition elimination?
Pedja
I'm trying to tune index configuration for some reports (dynamic sql,
different set of columns each time) that are joins of 5-6 tables. One of them
is 6 million rows (2GB), but the others are 20-30,000. Largest table doesn't
have clustered index, but only couple of nonclustered indexes. The only
common thing for all these reports is that where clause always starts with
<largetableid> = <some integer value>. There are 10 distinct values of
<largetableid> colum. Of course, this is not only condition of where clause,
there are lot of others added with AND...
The most expensive operation (estimate 97%) for all these reports is table
scan on the large table. I decided to create clustered index that includes
<largetableid>, and some other columns used for joins in these reports, and
to partition this clustered index on the same column: <largetableid>. My
logic was following:
in case that optimizer decides to go into clustered index scan, it will
eliminate at the beginning all partitions but one (because all rows for the
given largetableid value are located in teh same partition), so instead of
scanning 6 million rows, it will scan 600,000 rows. In case that it decides
to do seek, even better!
However....
To test this, I created partitioned clustered version of the same table.
When I run 2 versions of the same report together in batch, and turn on
"include actual execution plan", estimated cost for "table scan" shows 85%
cost, and for "clustered partitioned index seek" shows 15%. One would say,
great, you proved your point! Unfortunatelly, "table scan" version always
performs faster, every single time I run it (with clearing proc cache and
data buffers each time)!!!
Execution plan for clustered index seek (and even clustered index scan which
i force using index(0) hint) doesn't show usual nested loops (with constant
scan) to determine which partitions should be eliminated. Execution plan
starts with this clustered index seek (or scan), including predicate: where
largetableid=<largetableid value>, and Partition Id value of "ConstExpr1012".
So beside wondering how is it possible that table scan performs better than
clustered index seek/scan (no matter what actuall exec plan shows), I'm also
wondering about this constant value:
Does "ConstExpr1012" mean that engine operates clustered index seek/scan on
only one partition (regardless of the fact that there isn't any nested loops
between constant scan and clustered index scan to determine partition
elimination - usually Partition Id value after this nested loops would be
i.e. "PtnIds1019"))????
I have sql srv 2005, sp3...
Thanks in advance,
Pedja
Erland Sommarskog
> To test this, I created partitioned clustered version of the same table.
> When I run 2 versions of the same report together in batch, and turn on
> "include actual execution plan", estimated cost for "table scan" shows 85%
> cost, and for "clustered partitioned index seek" shows 15%. One would say,
> great, you proved your point!
As you have found out, those percentages is nothing to pay that much
attention to.
> So beside wondering how is it possible that table scan performs better
> than clustered index seek/scan (no matter what actuall exec plan shows),
> I'm also wondering about this constant value:
Too much to answer at one time with so little knowledge. Can't you at least
put the .sqlplan files somewhere for download, so that we can look at them?
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Ruben Garrigos
Apart from the useful execution plans that Erland requested it will be nice
to have some statistics io/time results in the meantime. I'm guessing that
the partitioned plan is "so good" that SQL Server decided not to go with
a parallel plan and your query gets CPU bounded. A "fast & dirty" check can
be done with the Windows Task Manager. If the "bad plan" gets plenty of CPU
power among N CPUs and the "good plan" only burns one CPU core maybe the
cost threshold for parallelism need to be adjusted for your hardware. Or
maybe the partition elimination is not working...
Remember that partition graining affects query parallelism. For SQL Server
2005 only queries that touch a single partition can be parallelized up to
MAXDOP. If you need to touch more than one partition, only one thread per
partition is used (up to MAXDOP threads).
And now... we will get the execution plans... right? :)
Regards,
Rub�n Garrig�s
Solid Quality Mentors
Blog: http://blogs.solidq.com/es/elrincondeldba