• Thanks to Koen, Pete and Gazareth -- it turns out that it was parameter sniffing causing the problem.


    As Paul Harvey used to say, here's the rest of the story:

    SSRS report has run fine for past couple of months (5-10 seconds to come up); after a rollout of an update to our LOB app over the weekend, it's now taking "forever" (2-3 hours) for this same report to run. Tempdb is filling up, users are complaining, dogs and cats living together, ... :w00t: So we yanked the report down. The other 40+ reports are running fine -- just this one seems to have lost it's mind.

    Running the query from SSMS pointing to any environment ran fine. Running the report from Test SharePoint pointing to any environment (including production) ran find (5-10 seconds). Only the combination of running report on production SharePoint pointing to prod database caused horrible delays. Querying the SP_ReportServerIntegrated database was showing a 1000X increase in the report's data retrieval from the previous week.

    I had switched the report from T-SQL text in the report to a stored procedure that specifically mapped the parameters to internally declared variables to get around the parameter sniffing issue. Of course this worked fine and dandy in all of the environments I tested in. So I didn't think initially it was a param sniffing problem. Eventually in a maintenance window, I was able to run the report using the new stored procedure on prod SharePoint pointing to prod db and performance was back to usual speeds. So it was a problem with parameter sniffing.

    The good news is that everything is working fine now.

    Thanks,

    Rob