1

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?

Improve this question
9
  • is the update updating all rows ? how big is the table ? What was the query waiting for - waitstats ? We would need a query plan (actual plan xml - use pastebin and link it here).
    – Kin Shah
    Commented Oct 5, 2016 at 14:21
  • @Kin It isn't updating any records. Instead it's deleting all the records in the table for the current fiscal year and repopulating the aggregated data for that fiscal year in the stored procedure. The table has less than 60,000 records. I'm not sure what the query was waiting for, how do I identify that? I am running the execution plan currently.
    – JJT
    Commented Oct 5, 2016 at 14:32
  • There are a number of things that could affect the execution time. Is regular maintenance being run on the indexes? Does the stored procedure accept input values? If so, do the input values change? If the values do change it could be that the execution plan was created based on "bad" variables. Have the table(s) increased in row count? I also agree with @Kin that you may also need to look at waitstats. Alternatively to using pastebin you can upload the execution plan to brentozar.com/pastetheplan
    – Adam
    Commented Oct 5, 2016 at 14:34
  • @Adam I believe they have a maintenance schedule for the indexes. The stored procedure accepts an input value, but is optional (if no value is provided, the current fiscal year is assumed). The value would have been the same for the last 3 months. The tables that I'm pulling from (the vendor tables) increase in records every night. I will look into the waitstats.
    – JJT
    Commented Oct 5, 2016 at 14:38
  • (the vendor tables) increase in records every night. this means that the stats will be outdated thereby producing bad plan. Either update stats after the load or use option (recompile) query hint.
    – Kin Shah
    Commented Oct 5, 2016 at 14:49

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • 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.
    – Kin Shah
    Commented Oct 6, 2016 at 19:56
  • I hope you don't mind. I've added your comment to the answer in case it helps someone. Thanks for the info.
    – JJT
    Commented Oct 6, 2016 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.