SQL Server 2012 – Stored Procedure Execution Time Optimization

optimizationsql serversql-server-2012stored-procedures

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?

Best Answer

I received a follow-up from the database administrator. So I will post his message as the answer.

I tried again after receiving his message and sure enough the stored procedure ran in under 2 minutes.

His email:

Seems that the issue was a deadlock on the db. I killed the locked processes and it seems fine now.

The system update essentially caused rehashing in some of the tables and the replication job to your datamart hung up because of this.

Please try again and let me know.

Edit: Additional Information

If you have the same issue, please also consider the comment from @Kin below since I am unable to confirm 100% that the DBA actually did what he said.

@Kin: Well, SQL Server will automatically choose the deadlock victim based on the log usage (the process using less log will be killed unless you have deadlock priority set). I think, it is a blocking issue not a deadlock as thats where you kill the offending process.

Related Question