SP Perfomance Issue

Question

SP Perfomance Issue

NickDBA

Hall of Fame

Points: 3792
More actions
October 27, 2014 at 6:17 am

#300507

Hi experts
I have two machines running SQL Server 2008R2 Developer Edition. I have an sp that takes 26 seconds to run on one machine, and nearly 10 minutes on the other.
DBs are identical (one is a copy of the other via restoring of a backup). Indexes are the same and stats updated on both.
Similar set up in terms of default SQL Server installation options, disk config, placement of system DBs and user DBs. Essentially, the two machines look the same with the exception of one having 8GB ram and the other 4GB ram (no memory pressure though)
Using SET STATISTICS IO ON shows me that on one machine (taking 10mins) there is nearly double the amount of logical reads, and interestingly on that machine there is nothing in the proc cache for that sp.
When I run profiler I can see that, down to the sp:stmt level there are literally more reads for the exact same stmts.
I don't understand this behaviour and am running out of options to explore. Any help would be grately appreciated.
Thanks in advance...

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Statements and execution plans?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Agree with Gail.

Look to the default ANSI settings on both servers, max degree of parallelism and the cost threshold for parallelism. Deviations in these settings can lead to differences in execution plans.

"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

NickDBA Hall of Fame Points: 3792 More actions · Answer 3

Thanks for the response....it turns out the devil was in the detail and the sql server versions were not exactly the same. One was RTM and the other SP1. I've updated the RTM version and now executions of the said SP are performing much better. Although im still baffled by the difference in reads and lack of plan caching which was happening but is now fixed.

Thanks again 🙂