Database Snapshot mystery

  • 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

  • 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.

  • 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

  • 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

  • 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