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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » High percentage of Failed Auto-Params/sec --...

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

Rate Topic

Display Mode

Topic Options

Author

Message

SQLHeap

Posted Tuesday, November 13, 2012 9:04 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 20, 2013 3:01 PM
Points: 347, Visits: 409

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 that one...

Post #1384142

« Prev Topic | Next Topic »

Permissions