• I'm going to give you my usual answer.

    Analyse, investigate and identify exactly what's happening. Don't guess, you're just going to waste time. Don't try stuff at random, it doesn't work. Don't look at one thing in isolation, it's seldom one thing.

    Identify exactly what runs when the app team requests their data, get exact performance metrics for each step, then and only then can you identify exactly what is the problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass