0

I have a stored procedure that is installed on 30 databases across 4 servers. We encountered an issue last night where 3 of these stored procs are now taking over 8 minutes (one 18 minutes!) to execute while all of the others are under 1 second. 2 of the SP's are on databases under the same server, the other is on another server.

I have looked at the following and seen no improvement or rational explanation why.

  1. sp_recompile
  2. update statistics
  3. review indexes fragmentation compared with successful databases but recreated anyways.
  4. change proc for param sniffing problem/solution
  5. Reviewed execution plan for anything shocking (estimated rows was WAY off but came back to normal after #2 above
  6. drop and recreated procs with no changes
  7. watch temp db, as proc uses temp tables nothing jumped out

We have changed the proc to be more efficient and now its executing under 1 second, but I cannot let go as to what is causing the existing SP to be fast in 90% of the situations, but seriously slow in the others. Especially across 2 servers.

Due to the speed and consistency for months, I am baffled as to what might have caused this.
I have looked at database settings using sys.database comparing one to another. The only differences I see are Slow setting / Normal Database setting

  1. recovery_model 1 / 3
  2. recovery_model_desc FULL/ SIMPLE
  3. log_reuse_wait 2 / 0
  4. log_reuse_wait_desc LOG_BACKUP/ NOTHING

Could this have to the with the above settings?

The proc (which I can provide if needed, along with execution plan) reads data from tables and inserts into a couple of temp tables and returns a dataset. from existing tables it only performs selects. The tables are no bigger then 250,000 rows. Some databases where the proc is running quick are in excess of 800,000. So the 3 in question actually have a small overall data set.

Any ideas of what to check ?
What else can i do to figure this out?

0

Have you checked the CPU / Memory stats across servers where one SP is significantly different from another? (I assume your I/O are of the same performance more or less)?

I would also check various wait statistics, using Paul Randal's query as good starting point

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

  • No but I could try this, if i execute the proc on multiple databses on the same server, they all run fast with the exception of one of the offending databases. If this was a CPU/Memory issue wouldn't I see all procs executing on the same server running with similar results? – treeNinja Jul 21 '16 at 18:03
  • If the proc runs in different dbs on a same server, and there is one offending db (where the proc runs much slower), then I'd need to check underlying tables in this offending db, do they have the same indexes as those tables in other dbs? are these indexes fragmented at the same level as those tables in other dbs? If everything is the same (including offending db files are located on the same drive as other normal dbs), I would open a ticket with MS to figure it out. – jyao Jul 21 '16 at 18:41
  • I have a query that can check index fragmentation across tables. I ran this comparing 2 databases (slow and fast) and both were roughly the same. We have a batch process that recreates indexes when they reach a certain % of fragmentation and so while they were not exact, they were close. i am really at a loss. – treeNinja Jul 21 '16 at 19:20
  • Trying to dbcc dropcleanbuffers and then dbcc freeXXXcache (where XXX = system | proc ) before each run of your proc. If it still does not work, I am at a loss too. :-( – jyao Jul 21 '16 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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