May 21, 2008 at 2:19 pm
Hello...I have a situation that I found it is hard to believe:
I have a query runs relatively slow, the trace shows: CPU:146,969, READS: 13,158,356, Duration: 146,911, it is running on a 64bit Enterprise SQL 2005(4 CPUs, 32G RAM), it took 2:30 minutes.
I then run the same query in a 32 bit STD SQL 2005 (dual CPUs, 4G RAM), the trace shows: CPU:1,951, READS: 41,495, Duration: 2,619, it takes only 10 seconds.
In order to compare apple to apple, I restored the same database into these two servers.
It appears that the lower end server has better performance than the higher.
What could be the factor making the difference?
-Fshguo.
May 22, 2008 at 5:57 am
I'm not entirely sure, but three things come to mind. First, the engine & optimizer behave differently enough that in this situaion 32 is just better than 64. Second, differences in the optimizer and engine are simply generating different execution plans. Third, you might have differences in the statistics because I know 64 & 32 store things a bit differently. Fourth, no one expects the Spanish Inquisition, I mean, you might have a bad plan in cache on the 64 bit machine and restoring to 32 came from a fresh cache.
I'd try updating the statistics and/or rebuilding indexes on both databases after the restore. Flush the cache on both machines. Make sure you've got the same parameters (although I'm assuming you already do). Check the execution plans as well as the I/O, CPU & Execution times. See what's happening to make one slower than the other.
"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
May 22, 2008 at 6:31 am
Clearly by the number of reads, you are getting different execution plans.
Grant is correct, start with statistics.
Also check your parallelism threshold. You may want to try running the query on both machines with the MAXDOP hint set to 1. Sometimes a query written incorrectly will parappel-process itself into infinity.
May 23, 2008 at 11:55 am
I have upated the statistics, rebuild the indexes and apply Option Maxdop based on the recommendations, it looks like the situation has no change.
After I compared the execute plans for the two servers, they are different.
I am just wondering:
1) How can I make the fast execute plan to run on another server? or how can I update the exec plan?
2) Based on Grant's suggestion, how can I flush the slow plan cache
3) what parameters are refered to make sure the two servers are the same.
4) Since I restored database into another database name to the 64bit server, I think it should not use the cache any more which is from another database.
Thanks,
fshguo.
May 23, 2008 at 12:08 pm
fshguo (5/23/2008)
I have upated the statistics, rebuild the indexes and apply Option Maxdop based on the recommendations, it looks like the situation has no change.After I compared the execute plans for the two servers, they are different.
I am just wondering:
1) How can I make the fast execute plan to run on another server? or how can I update the exec plan?
2) Based on Grant's suggestion, how can I flush the slow plan cache
3) what parameters are refered to make sure the two servers are the same.
4) Since I restored database into another database name to the 64bit server, I think it should not use the cache any more which is from another database.
Thanks,
fshguo.
1) Since it's 2005, you can force the plan, but let's not go there yet. For information you can look at the USE PLAN query hint. Still, I wouldn't do that yet.
2) DBCC FREEPROCCACHE()
3) When I said parameters, I meant the query parameters. You're passing the same values to each query when testing right?
4) It should have a new plan in the cache, yes.
5) When you said that the plans were different, how were they different? Can you post them?
"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