asked
Query taking very long time...
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.
Excellent. Since the server is virtual, your disk most likely are not local physical disk but rather a lun provisioned to the VM Host. Also with it being a Virtual server, you are most likely being impacted by another server on the VM host. I would open a critical incident with your System Engineering (Server Team, VM Admin, SAN Admin) who ever your have to look at the overall health of the VM environment. If nothing changed on your SQL server, you are most likely experience pressure related to the VM environment. I have seen them to many times at my place of employment. Not with my production SQL environment because they are physical but with my dev, qa, or our production app servers. Things are rocking along just fine then performance hits a wall, only to find out they a added a bunch more VM's or some other server started doing some heavy processing and sucking up all the I/O on the host. This may not be your silver bullet, but it would be a darn good place to start looking. Also would be a good time to start breaking apart that SPROC to see if you can't optimize it a bit more. An hour is long time for it to be doing some inserts.
How about space in the database you are inserting the records into. How much free space is in that database? Have you ran any process to see what is going on while the sproc is running? What version of SQL Server are you running? There are loads of DMV's that could assist with troubleshooting this but something as simple as SP_WHO2 to show you that a SPID is blocking your stored procedure could be it too. There is always a reason why something that ran perfect yesterday stopped today. Something somewhere changed. Are the two databases on the same instance?
There are still some questions that if you could answer would enable me to help you are great deal more. Are your disk local or on a SAN, what version of SQL Server are you running, is the source and destination database on the same physical database, is the SQL Server physical or virtual?
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?
Thanks Every one for helping me to sort out this problem. The Problem has resoleved. I have update the statistics after rebuilting the Index on the source table.
So maybe only statistic update could be enough.. I suggest to create a job, which will periodically update the statistics, if the table is quite big and new records coming to that table do not trigger automatic update of statistics as the condition for auto update is not met.
@basit - please make sure you credit @Pavel Pawlowski by ticking his answer as he first mentioned out of date statistics in his answer.
@basit - I think @Fatherjack was referring to the "tick" that appears next to @Pavel's answer... click the tick!
