Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Odd performance query behavior on very large 40 TB partitioned table in distributed view, maybe it's tipping? RE: Odd performance query behavior on very large 40 TB partitioned table in distributed view, maybe it's tipping?

  • July 16, 2013 at 9:01 pm

    #1633060

    jallmond (7/16/2013)

    UPDATE: For anyone that may encounter a similar issue.

    I've dug a bit deeper, and I think what is happening is that sometimes the generated query plan undergoes partition elimination at compile time versus at runtime. When at runtime, I have verified that the number of executions is zero for tables with check constraints outside my range.

    I still don't quite understand why the non-clustered index is sometimes ignored when it should be covering, but I have had the most luck with the FORCESEEK query hint.

    Thanks for posting the follow-up. Lots of posters don't realize that this makes the forum MUCH more valuable as people searching can find problems AND solutions!

    I think another of the root causes is that DPVs just never got any real loving from the product team and haven't had much attention in many years I don't think.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service