December 12, 2019 at 3:02 pm
Hi all,
I'm the classic accidental DBA and I'm trying to troubleshoot an issue with a vendor-managed application for indirect tax calculations.
Now, I know that more details might be required, but first I wanted to let you know the issue I'm having and work out the best way to look into it.
So, without detail, the strange behavior I'm seeing is this.
If I run a custom report for the first two weeks of a month, the query runs in about 2 mins and returns results. If I do the same for the second two weeks of the month I get the same. All good so far.
But, if I try to run the report for three weeks or a full month, the report never completes and eventually blows the 160GB of tempdb space. If I just run for either of the two week timeframes, tempdb is hardly touched at all. I don't understand this.
Any tips on what to look at first to determine what might be going on here?
Regards
Farren
Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
4 x cpu
64GB
Database in question 372GB
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) A socialist is someone who will give you the shirt off *someone else's* back.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply