High percentage of Failed Auto-Params/sec -- 93%

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  November 13, 2012 at 9:04 am

  #270888

  I have procedure that has a high number of parameters (hundreds) that executes nested cursors and other procedures (ugh) on a SQL08r2 Ent version. In the past when running, it finishes in under 15 mins. Just lately it runs for hours. Tried a server reboot, normal run times for a few days, now back to very slow. I have queried the following information and am considering doing a forced parameterization only during that procedure run. The failed auto-params is at 93% and SQL Plans Cache hit ratio is 52%. Not sure why it works for a few days then slows way down though. Any thoughts?

  counter_name-----------cntr_value--cntr_value--percentage

  SQL Compilations/sec----766270-----13825------1.804194344

  SQL Re-Compilations/sec-766270-----1073-------0.140028972

  counter_name-----------percentage

  Failed Auto-Params/sec--93.24236517

  Safe Auto-Params/sec---1.754385965

  instance_name---------------------base--counter--cache_hit_ratio

  _Total-----------------------------4498--4051-----90.06224989

  Bound Trees------------------------556---495------89.02877698

  Extended Stored Procedures---------923---915------99.13326111

  Object Plans------------------------1421--1420----99.92962702

  SQL Plans---------------------------683--361------52.85505124

  Temporary Tables & Table Variables--915---860-----93.98907104

  There is an exception to every rule, except this one...