Stored Proc slow on secondary only

Question

Post reply

Stored Proc slow on secondary only

bartedgerton

SSCrazy

Points: 2062
More actions
October 21, 2013 at 1:41 pm

#299480

I've got a database in an Availability Group with a readable secondary. An SSRS report calls a read-only stored procedure from the readable secondary.
The procedure has been returning results in a couple seconds normally, but, today, it's running in excess of 12 minutes, although has not been changed in months.
When I execute the proc on the primary, results are in a couple seconds, but the secondary takes 12 minutes or more. I've tried FREEPROCCACHE on the secondary and I've even tried sp_recompile, but no luck changing performance at all.
Both servers are identical, and the secondary shows no locks. Avg. Disk Queue Length stays below 1, and cache hit ratio is 99% or better. Both are VMWare servers running Win 2K8R2/SQL Server 2012. I don't see any indication of any over-taxed resources. Plenty of free memory and CPU utilization generally averaging around 50%.
Any ideas? I'm thinking about rebooting the secondary machine, but I hate mysteries that go away with a reboot.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Are the indexes and stats actually being rebuilt on the secondary?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bartedgerton SSCrazy Points: 2062 More actions · Answer 2

That seems like what the issue is, however, there is no rebuilding index on the secondary, but it's a logged event, so, I think, rebuilds/reorgs on the primary should update the secondary.

the proc in question creates a couple of temp tables, performs aggregates in CTE's, etc.. probably could be optimized, but that doesn't explain the drastic performance drop from primary to secondary. I'm thinking maybe there is a tempdb issue possibly?

I also played with the date input parameters, and, on the secondary, there is a drastic drop from one date range (about 2 months) to one more day. As in:

EXEC myProc '2013-08-15', '2013-10-31'; -- results in under 2 seconds

EXEC myProc '2013-08-14', '2013-10-31'; -- results in 12 minutes

There is no gradual degrade of performance as the date range grows. It's one date that it goes instantly from good to unacceptable. On the primary, both date ranges perform under 2 seconds. Any date range on the primary works fine, actually.. even a 5 year span returns results quickly. the number of records from the first date range is 121 rows, and 124 for the second, so not a drastic change in the number of records returned.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Everything has a "tipping point". 1 Extra row can cause an execution plan to change drastically.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

There are a number of things that could cause differences. I'd focus first on the execution plans for each query. Are they the same? If not, your issues are narrowed down to possible differences in statistics, constraints, keys or indexes. If they're the same, the differences could be elsewhere.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning