July 7, 2011 at 11:08 am
Hello
We are using SQL server database snapshots of mirrored databases for reporting. Both Database servers were recently upgraded to 2008 R2.
There is a view in a "Working Database" which unions 2 tables from 2 different database snapshots (One is production table and other is archive table). I have to use a "working database" because the snapshots are different name from the actual database....so, due to naming conventions of the objects in the view, it causes an issue.
Now, if I run a query on the working database like such:
SELECT
MAX(TranDateTime) as LastActivityDate, CustomerID
INTO
#lastactivitydate
FROM
WorkDatabase.dbo.vwTransaction_view
GROUP BY
CustomerID
(Please note I have a non-clustered Index on TranDateTime,CustomerID on both the tables being used in the view and a clustered index on a another field which for all purposes is not required in the above query)
When I check the Query Plan, it insists on doing a clustered index scan (also has a "yellow exclamation mark" on the clustered index scan icon of execution plan). Even adding a with index hint doesn't change anything.
Now, I took the same query, ran it against the production database and that seems to use the non-clustered index, as it should without changing a thing. The results come out in less than 5 seconds...
Can please someone explain what's going on?
Thanks
Anish
July 7, 2011 at 11:21 am
The sql you posted is a view, could you post the SQL from the view? What happens if you run the same sql that the view uses to get the data you want, do you still get the index scan? I think the issue is in your view and not in the select you posted.
July 7, 2011 at 11:22 am
Different stats in different databases.
Without a Where clause specific to those columns, it's going to have to do an index scan, either clustered or non-clustered, to get all the data you are asking for. It's judging that the cost of scanning the clustered index is less than the nonclustered in one case, and reversing that in the other. That's usually a sign of stats being more recent/applicable on one than the other.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 7, 2011 at 11:36 am
Thanks GSquared you are absolutely right!
For now I have figured out another method to get what I want
Thanks again for your response.
Anish
July 8, 2011 at 6:27 am
Why do you need to figure out a different method of getting what you want? If the data you need is best provided to you by means of an index scan, what's the harm in that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply