I have a view:



Create View vallrequest_test2

as

Select * from Archive..report

UNION All

select * from production..report



It queried against as follows:



Select * from vallrequest_test2 where cn = '12345'



There is a primary key on one field, and cn has an Index on it. This is
a large table, 85 columns and 13 indexes, with 10 million rows in
Production and 20 million in Archive. The Production db is very active
with lots of inserts, updates and selects, but very little deletes.



When I run the execution plan, I am getting an Index Seek in Archive on
CN, but an Index Scan in Production on the PrimaryKey index. If I run
the statements independently or run the union statement, not the view,
both run an Index Seek.



To complicate matter, I have the same setup on a Report Server that is
populated by replication from the original server, but the same view
uses the correct index and does a seek. The run time is <1 sec on the
report server compared to 30 - 45 seconds on the production server.



I have different statistics on the 2 serversm, and I think that might be
causing this problem, but am unsure. Both are set to auto create and
update statistic.



Any direction and help would be much appreciated!!



Thanks,



Stephen Dyckes

LazyDBA in training




-----------------------------------------
The information contained in this e-mail message is intended only
for the personal and confidential use of the recipient(s) named
above. This message may be an attorney-client communication and/or
work product and as such is privileged and confidential. If the
reader of this message is not the intended recipient or an agent
responsible for delivering it to the intended recipient, you are
hereby notified that you have received this document in error and
that any review, dissemination, distribution, or copying of this
message is strictly prohibited. If you have received this
communication in error, please notify us immediately by e-mail, and
delete the original message.


MS Sql Server LazyDBA home page