January 29, 2014 at 7:38 am
Although this seems unlikely, I'm currently experiencing.
I have a rather large query, which completes in 5 seconds on server a, but takes 5 minutes on server b. Everything is configured pretty much the same in terms on Instance settings on both boxes.
I've noticed that in the estimated query execution plan, server b decides to use Parallelism, but server a doesn't. I don't know if this is the underlining cause to the issue on server b, but I can't work out why both servers are using the different routes?
January 29, 2014 at 9:33 am
When you say pretty much same it means identical or few things are different?
Do they have same # of processor & same amount of memory?
How much memory is allocated to SQL on both instances?
What is Max Dop in both servers?
What is COst threshold of parallelism on both ?
January 29, 2014 at 9:41 am
Neeraj Dwivedi (1/29/2014)
When you say pretty much same it means identical or few things are different?Do they have same # of processor & same amount of memory?
yeh 4 processors each. server A has less memory (where proc runs fine)
How much memory is allocated to SQL on both instances?
default setting
What is Max Dop in both servers?
0 (default)
What is COst threshold of parallelism on both ?
5 (default)
Just to add on Server B, where I'm seeing the issue a high number of waits on CXPACKET are seen, where there all in a 'suspended' state, I have queried sys.dm_os_waiting_tasks for session_id, to try and establish the non wait_type of CXPACKET, to see what is being waited on, but get nothing !
January 29, 2014 at 9:47 am
did you update statistics on both servers?
January 29, 2014 at 9:50 am
As in the index statistics?
January 29, 2014 at 10:26 am
Is the hardware identical?
Are the server settings identical?
Is the data identical?
Are statistics up to date on both servers?
No to any of those could be the reason for the difference.
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
January 30, 2014 at 8:41 am
GilaMonster (1/29/2014)
Is the hardware identical?Are the server settings identical?
Is the data identical?
Are statistics up to date on both servers?
No to any of those could be the reason for the difference.
This issue was definitely down to that particular query using parallelism, badly. I used a query hint within the SP; OPTION ( MAXDOP 1 ), which has resolved the issue.
As to why two relatively same servers are using two different query plans, I suspect this to be down to the statistics. Which I'm currently investigating.
January 30, 2014 at 8:43 am
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply