Why would running a SQL query overnight break my database for the following day?

I apologise in advance if this is a duplicate question (which I bet it will be). I had a good search through and found similar questions but nothing that seemed an exact match.

I wrote a report that takes around 30 minutes to run. It does a lot of number crunching and I am quite happy that it takes so long to execute but this means the report can't be interactive. Basically the report has to calculate a profit and loss report as if the company has lost exactly one client. Then it puts that client back and runs the report again as if the next client was lost, etc. I think 30 minutes is actually rather good performance considering how intensive this is.

Users are happy with this report being updated nightly and then they can see the cached results the following day. So the plan was to run the report overnight and save the output somewhere.

I wrote a stored procedure to do this and I can run it during the day in around 30 minutes. However, when the report runs overnight as a SQL Agent job it NEVER completes. It kicks off at 9pm and when I get into work the next day I will find my inbox flooded with emails saying that SQL Server is broken, isn't performing properly, etc. I kill the job and everything goes back to normal.

Except it doesn't go back to normal. What happens next is that ANY report that is run against the same database will timeout. The only way to correct this is to either wait a day (without running the overnight report) or to run another query against the same database directly from SSMS as this seems to reset the problem for some reason.

Obvious questions:

• is it the SQL Agent job causing the problem (maybe permissions)? No, if I run the SQL Agent Job during the day it works fine, it's some sort of timing issue I think.

• have you tried moving the schedule? I have tried everything from running it at as early as 6pm to running it at as late as 6am and it doesn't seem to make any difference.

• what else runs overnight? There are a wh

My gut is that it's related to parameter sniffing; when you call the TVF's, do you pass in parameterized values or hard-coded values? For example:

SELECT * FROM FormatMonthEndReport(2013, 1);

will perform differently than

DECLARE @p1 int = 2013, @p2 int = 1; SELECT * FROM FormatMonthEndReport(@p1, @p2);

StackExchange Database Administrators Q#48956, answer score: 3