Partition elimination

i have 2 related partition elimination questions.

1. how can know whether sql server (2005 or 2008) making use of partition elimination when executing query?  plan somehow show this?  if so, looking in plan?  i'm not @ reading plans @ point more descriptive better.

2. in terms of performance, isn't partition elimination (if possible) going produce better, in terms of performance, results?  if so, there way somehow force sql server make use of partition elimination?  provided me link:

http://msdn.microsoft.com/en-us/library/ms345599.aspx

and impression sql 2008 going better job @ partition elimination v.s. sql 2005 because of how partitions treated in sql 2008.  true?  of initial tests aren't proving out way , i'm wondering whether i'm not providing sql appropriate information such can make use of partition elimination.  i've provided sample query @ bottom this post.  there results after query interpreted microsoft db consultant.  , yes, eav type schema.

kind of getting original question #2, 'is best make use of partition elimination', compare doing a clustered index scan (table scan) v.s. using index.  , let me first i'm no db expert, far it.  believe sql, based on statistics, may decide scan clustered index of table opposed using non-clustered index because might produce better results.  guess reasoning if used non-clustered index find location of requested rows have read clustered index anyway data , if table small case scanning clustered index faster.  can same said partitions?  ever beneficial read partitions have no chance of containing data i'm looking for?  assumption no.  i believe it beneficial make use of partition elimination.

sample query (partition key of every table , indexes id column, id non-unique clustered index of each table):

select source=d.value, count=count(*)

from smallstring as d

inner join smallstring as c on d.id=c.id

inner join bool as b on c.id=b.id

inner join smallstring as on b.id=a.id

where d.name='resource'

and d.id >= 633975984000000000

and d.id < 633976416000000000

and c.name='urn:corp:framework:machine'

and c.value='svr001'

and c.id >= 633975984000000000

and c.id < 633976416000000000

and b.name='urn:corp:framework:unhealthy'

and b.value=1

and b.id >= 633975984000000000

and b.id < 633976416000000000

and a.name='urn:corp:framework:type'

and a.value='webrequest'

and a.id >= 633975984000000000

and a.id < 633976416000000000

group by d.value

order by count(*) desc

 

/*

sql2008:

 

sql server parse , compile time:

   cpu time = 0 ms, elapsed time = 0 ms.

 

(10 row(s) affected)

table 'worktable'. scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

table 'smallstring'. scan count 36, logical reads 2067, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

table 'bool'. scan count 12, logical reads 176, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

 sql server execution times:

   cpu time = 234 ms,  elapsed time = 518 ms.

 

sql2005:

 

sql server parse , compile time:

   cpu time = 0 ms, elapsed time = 0 ms.

 

(10 row(s) affected)

table 'worktable'. scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

table 'smallstring'. scan count 36, logical reads 2648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

table 'bool'. scan count 12, logical reads 215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

sql server execution times:

   cpu time = 203 ms,  elapsed time = 579 ms.

 

different plans, sql2005 uses partition elimination, sql2008 not.

*/

thanks,
nick

anyone, bueller?

SQL Server  >  SQL Server Database Engine