By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Different query plans

P: n/a
I have 2 SQL databases which are the same and are giving me different
query plans.
select s.* from hlresults h
inner join specimens s on s.specimen_tk = h.specimen_tk
where s.site_tk = 9 and s.location in ('ABC','WIAD')
and s.date_collected between '2/1/2003' and '2/3/2006'
order by s.location, s.date_collected
Both boxes have the same configuration, the only difference is that one

of them is a cluster.
The Acluster box is taking twice as long to run the query.
I have run statistics on both, and the cluster is still creating a
bitmap and running some parallelism which the other box is not.
Also, the the first step, the A1 box estimates the rows returned to be
around 80K and the actual rows returned is about 40K - subtree cost =
248. The Acluster box estimates 400K - subtree cost=533!
After running statistics, how can it be so off?
I've also reindexed to no avail . . .
any insight would be very much appreciated. We just moved to this new
system and I hate that the db is now slower -
A1:
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 0 0
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 90 90
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 4 4
max server memory (MB) 4 2147483647 14336 14336
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 4096 4096
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 0 0
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Acluster:
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 0 0
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 90 90
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 4 4
max server memory (MB) 4 2147483647 14336 14336
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 4095 4095
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 0 0
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0

Apr 13 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
traceable1 (tr*****@gmail.com) writes:
I have 2 SQL databases which are the same and are giving me different
query plans.
...
Both boxes have the same configuration, the only difference is that one
of them is a cluster.
So they have the same number of CPUs?
I have run statistics on both, and the cluster is still creating a
bitmap and running some parallelism which the other box is not.
Also, the the first step, the A1 box estimates the rows returned to be
around 80K and the actual rows returned is about 40K - subtree cost =
248. The Acluster box estimates 400K - subtree cost=533!
After running statistics, how can it be so off?


You could try running UPDATE STATISTICS WITH FULLSCAN on the involved
tables, to be really sure that you have factored that part out.

Also, try adding OPTION (MAXDOP 1) on the cluster. Parallelism is
sometimes good, but sometimes it's bad...

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 13 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.