Hi , We are getting an issue in Executing a SP. This SP take the data from group of table from one Database and insert into a table of second database. Till day before yesterday it was working fine but from yesterday the SP Didnot execute. Normally it take an hour to execute but now from last 10 hour it is executing. I have defrag and rebuilt tha index on the database. What should be the possible cause for this issue. Please help me out to sort out this as it is very critical. Thanks Basit
Check also whether statistics are up to date. If the tables are quite big, then when you insert new records, but there was large amounth of date before, then the statistics could be not accurate. I encountered a similiar problem about a year ago. A SP worked correctly and finished wiithin a ew seconds. Then from day to day the SP execution started to be a few hours. There was a job scheduled for statistics update, but admins missed, that it was not executed for a few days. Then after the statistics update SP finished within a few seconds.
Lots of areas to look into. Is the database, logs, and tempdb on local disk or SAN? Have any changes been made to the SQL Instance such as memory allocation? Has a lot more data been loaded in the past few days? The database it is inserting into, it is at capacity and set to auto grow by 1 mb? Like I said, lots of things could be impacting this sproc.
Trad makes excellent points, but let me add a few. First, when something was working acceptably and then suddenly stops, a good question (but not the only question!) to ask is: What changed? Along those lines, did someone create new indexes? Add another process that runs at the same time as this procedure? Did overall usage of the server suddenly increase for some reason? Did perhaps someone evenc hange the procedure itself? Also, with such a dramatic difference, one area I would start looking at is blocking. Is another process locking up resources that this process needs? And in general, this may just be a good time to do some optimization on that procedure and look at the entire algorithm to see if it can be better executed, avoids RBAR, is supported by proper indexes, etc.
I would suggest you start up a filtered Profiler trace and determine where the SP stalls. Right now you are in the dark - perhaps knowing where the procedure has issues can allow deeper troubleshooting. Another thing to do would be to run System Monitor and add some counters from the Logical Disk set; queue length, seconds/transaction, bytes/transaction, transactions/second, etc. to see if your I/O subsystem is overworked. As @Trad alluded - there are many places to look at to determine the underlying cause and you are in the best position to determine what bottlenecks may impact your SP. Also, I agree with @TimothyAWiseman in that chances are something changed... if you can determine it you are probably a long way towards resolving your issue.
How much data, in terms of size and number of rows, does the SP transfer between tables? What kind of disk latency are you seeing? What are you seeing in terms of waits? Does the execution plan provide any hints?