December 12, 2019 at 3:06 pm
Most likely you've hit the "tipping point" for a table(s), where instead of using an index SQL reverts to a full table scan.
Rather than the full db size, we need the sizes of the tables involved. But, based on what you've stated so far, and my general experience, assuming no other major failing in the set up, the really best solution would be to cluster the main reporting table(s) by date first. But, since it's a vendor db, you may not have that option. In that case, you'll be stuck creating covering indexes for every large query. If the vendor won't let you do even that, you'll be stuck with bad performance.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 12, 2019 at 3:24 pm
How does the vendor-managed application run the reports? Does it issue stored procedure calls, or does it build ad hoc SQL and execute that? You may have an issue with parameter sniffing.
John
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply