October 21, 2013 at 1:41 pm
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.
October 21, 2013 at 2:18 pm
Are the indexes and stats actually being rebuilt on the secondary?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2013 at 2:30 pm
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.
October 21, 2013 at 2:36 pm
Everything has a "tipping point". 1 Extra row can cause an execution plan to change drastically.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2013 at 4:47 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply