I have an SSRS report that pulls it's source from a vendor's MSSQL 2012 database. It's an extensive dataset so I've created a stored procedure that runs at the same time every morning.
There's quite a bit happening in that stored proc, but I had optimized it to run in about a minute and a half. It did so for the last 3 months without any issues until this morning. When I came in and checked the logs, I saw that the sp was still running (2 hours later). I waited a bit, then stopped it thinking it might just be hanging.
I tried executing it again and it's been running for over an hour again.
The only thing I can think of that changed was an update that the vendor applied to the database yesterday.
I tried to run a query to see if there were any locks on the tables that I'm pulling data from, but I don't have sufficient permissions. I also ran
sp_who2 to get some info as I read on here that it might help out, but all I've learnt is the following:
Status = RUNNABLE |
BlkBy = . |
Command = SELECT INTO |
CPUTime = 47 |
DiskIO = 0
I'll be contacting the database administrator to troubleshoot, but I'd like to get an idea of what I can suggest, since the hourly rate for support services is very expensive and they often try to extend the call beyond what is necessary.
Is there something to look at right away when a stored procedure suddenly becomes inefficient overnight?