February 16, 2011 at 4:18 am
Hi,
I have a sp that runs in under a few seconds in prod but takes over 1hr in uat which has gradually been increased day by day. Can someone please advise of where I should make a start? The only difference is hardware.
Much appreciated.
February 16, 2011 at 4:31 am
Are you sure the only difference is hardware? How much different is that hardware? Are there multiple CPU's in UAT but not in Dev or vice versa? What are the differences in memory? Is the data different between the two? Are the statistics up to date in both servers? Have you checked the execution plan of the query on both machines? Have you checked for resource contention or blocking on the UAT server?
Without a lot more information, it's hard to know what's going wrong.
"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
February 16, 2011 at 4:34 am
Hi,
I know memory and cpus are 1/4 of production! Stats are updated daily. Is there a script that I can run to look at other stats?
Thanks
February 16, 2011 at 5:28 am
No one script is going to tell you what you need. There are just too many variables that you haven't defined for me to suggest a single point of focus. Have you validated that the execution plans are the same or different? I'd at least start there. But it could be contention, it could be blocking... it's just hard to say. Do the two databases have identical sets of data and are you using identical parameters with the query?
"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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply