...and suddenly a stored procedure takes too much time

Question

...and suddenly a stored procedure takes too much time

nonlinearly

Old Hand

Points: 360
More actions
July 17, 2015 at 1:55 am

#300321

Hi, I have a sp that was taking very little time (about 34 sec). But suddenly is stacked. It is running and running and running but not LOCKED neither SUSPENDED. It is always RUNNABLE. I have made Index and statistics optimization but nothing. I looked into execution plan but everything seems ok. All the time is in 3 indexes that are Index Seek and not Table Scan!!! So why is stacked... I do not know how much time it takes because I have to stop it. (SQL SERVER 2008 R2, the database was migrated from SQL SERVER 2000)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Probably bad plan, parameter sniffing or similar, maybe due to stale stats. Can you post the actual execution plan (not the estimated, the estimated is going to be useless in this case)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nonlinearly Old Hand Points: 360 More actions · Answer 2

I can't retrieve Actual Plan because the query never ending to show me the AP! But I have looked in the statistics of the tables that participate in the query and found that the last update was 1 year ago. Is that enough to blame them?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Depends. Have the tables changed (data wise) in the last year?

Do you have scheduled index and statistics maintenance? Do you have auto_update_statistics turned on?

It might be stats, but it might be something else, hard to tell.

If you run the query from SSMS and add the OPTION(RECOMPILE) hint, does it run fast or does it run forever?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nonlinearly Old Hand Points: 360 More actions · Answer 4

It was the statistics. I issue the command EXEC sp_updatestats 'RESAMPLE'.

Thanks

e4d4 SSCertifiable Points: 5777 More actions · Answer 5

Too quick assumption. It still can be a parameter sniffing that mention Gila. You can use sp_BlitzCache http://www.brentozar.com/archive/2014/05/introducing-sp_blitzcache/[/url] or check min and max values from sys.dm_exec_procedure_stats, if there are huge difference it can be a signal of parameter sniffing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

nonlinearly (7/18/2015)
It was the statistics. I issue the command EXEC sp_updatestats 'RESAMPLE'.

Not necessarily. Updating the stats would have invalidated all plans, so if it was a bad plan, the stats update would have 'fixed' it (temporarily probably)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass