Stored Procedure performance hit

  • I know about posting procedures but am simply looking for advice on what I have occurring here, not necessarily TSQL help. We're running S2K, sp4, 6Gb or memory. We have a stored procedure that had been running fairly smoothly until about 2 weeks ago. Processing time went from seconds to around 20 minutes for about the same size set of data (roughly 10K rows of data, nothing huge). The proc has not been changed in over a year. I found an interesting article on this site talking about Steve having a similar issue several years back. I know about the DBCC DROPCLEANBUFFERS and FREEPROCACHE but this is a production server so those are out (for now). I've looked in syscacheobjects and do not see my procedure in there either. I ran SHOWCONTIG, looks good, CHECKDB yielded no issues. I generated the script of the proc, dropped the proc and recreated it, executed it with recompile, generated the scripts for the two tables the proc writes to and dropped/recreated them, no improvement. Now for the strange part - I generated the proc (same script as the original), as a new proc, but insert/update to copies of the 2 original tables and it processes in seconds. Now I am at a total loss of where else to look. Any tips or maybe tips using profiler to try and figure this out? Due to our maintenance schedule, I cannot reboot the server until late next week. This has totally got me stumped. I can provide code samples and table layouts if desired, but again, I'm looking for pointers on where else I might possibly look or a tool to help hunt this down. TIA.

    -- You can't be late until you show up.

  • Tom,

    Feel bad for you and it sounds like a bad plan got pulled into memory. I don't suppose you have a copy of the "good" plan? If not, grab one after you can reboot.

    I've seen issues with this where the proc brings a bad plan into memory and it doesn't seem to clear until the server reboots. Often it's one set of steps in the plan that causes issues. This happened and we spent an hour or so at TechEd talking to one of the SQL engineers from MS and he said that this happens sometimes and they didn't have a good reason why.

    If this is a big problem, I'd really get an exception for the maint plan and reboot sometime soon, grab the good plan and save it. It's possible, although scary, that you could save off the data from the system table and try updating it back in if you have issues.

  • Are the statistics accurate? You can check when they were last updated with the STATS_DATE function.

    Has the base table changed much recently?

    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
  • Not sure what you mean by "copy of the "good" plan", the script of the proc? If so, I have backups and a good copy of the database (from 4/22) sitting on another server. Statistics are good, done every Sunday. Base tables, simple data loads, maybe 25K rows over the last couple of weeks, nothing major. Table structures have not changed. I assumed it was something stuck somewhere but simply couldn't find it and that's when I started dropping/recreating. I also thought immediately of a reboot, but again, due to our cycle, kind of not an option right now. However, if I get the right person to complain LOUDLY about this issue, I'm sure an outage can be arranged sooner than originally planned! 😀

    And, it's Terry BTW, not Tom. Thanks Steve and Gail for the quick response, you ROCK!. I'll update here once I have it resolved.

    -- You can't be late until you show up.

  • Hmmm...

    Statistics are up to date.

    Proc has been dropped and recreated (which will remove plans from the cache)

    Very strange...

    Any blocking/waiting visible when the proc runs?

    Indexes fragmented?

    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
  • Indexes are good, checked that when all this started (and since). It's amazing, when I run the original, it starts locking/blocking like crazy but when I run the copy of the original, writing to two new tables, I don't see anything out of the ordinary. Again, all the same logic in the second proc, simply writes to different tables. I've tried everything I can think of so I am going to take Steve's advice and try for an outage to reboot. One other question, shouldn't I see the proc residing in syscacheobjects? I'm not sure how long data resides in this table (it currently has about 10K rows). A subject of research for another day. Again, thanks for taking your time to ponder this issue for/with me. That's why I love this site, people always willing to lend a hand!

    -- You can't be late until you show up.

  • What are the blocks/waits on? What wait type? What resource?

    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
  • It appears the reboot did the trick. Strange indeed. I should have stated in my original post that this is running on VMWare and ESX server, which was installed in March. Wonder if something isn't quite right there as I've also seen the CPU peg to 100% resulting in user calls about the system acting up....

    -- You can't be late until you show up.

  • Hi

    Please check if there are too much of temp table used to generate or process the report.

    It happend that the report size is too large and they remain in memory thus slowing the process.

    It is interesting if i can see the query please?

    Anil

Viewing 9 posts - 1 through 8 (of 8 total)

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