AMD vs Intel

Question

AMD vs Intel

Viewing 15 posts - 16 through 29 (of 29 total)

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

as2higpark SSC Eights! Points: 949 More actions · Answer 1

Alex,

When you are seeing issues on the new server, what is the highest wait type, I want to try to compare your issue with ours to see if there are similiarities.

We see a very high percentage of CXPACKET with next highest being SOS_SCHEDULER_YIELD

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 2

Well, the DB seems to be pretty much cached in memory, box has 128GB and DB is 50GB, there's like 60GB used so far, and executing a query the first time and second and third does not really change the execution time. I will review both execution plans and post back results!

Thanks

Alex C

Jim Sleeman-388184 (7/26/2011)
Silly question, but what happens when you run the query on the new machine twice in a row?
The reason I ask is that it sounds very much like an I/O issue and that the new machine, without any load, has to retrieve all the data from disk while the old system with a load on it may already have much of the data in memory.
I would also see if the query plans are the same on both machines.

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 3

Hi as2higpark, how can I pull those statistics during the execution of a query?

Thanks

Alex C

as2higpark (7/26/2011)
Alex,
When you are seeing issues on the new server, what is the highest wait type, I want to try to compare your issue with ours to see if there are similiarities.
We see a very high percentage of CXPACKET with next highest being SOS_SCHEDULER_YIELD

as2higpark SSC Eights! Points: 949 More actions · Answer 4

I use this query to show the current wait stats:

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK',

'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',

'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

'ONDEMAND_TASK_QUEUE', 'BROKER_EVENTHANDLER', 'SLEEP_BPOOL_FLUSH'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE);

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 5

Alexander Chacon (7/26/2011)
Hi as2higpark, how can I pull those statistics during the execution of a query?
Thanks
Alex C
as2higpark (7/26/2011)
Alex,
When you are seeing issues on the new server, what is the highest wait type, I want to try to compare your issue with ours to see if there are similiarities.
We see a very high percentage of CXPACKET with next highest being SOS_SCHEDULER_YIELD

Sounds very much like what I was seeing but what was really happening was memory related rather than cpu. What I would see is running identical queries with identical plans would return a resultset with a massively different duration on occasion.

Is "lock pages in memory" enabled for the service account?

http://msdn.microsoft.com/en-us/library/ms190730.aspx

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 6

Actual execution plans are identical!

Jim Sleeman-388184 (7/26/2011)
Silly question, but what happens when you run the query on the new machine twice in a row?
The reason I ask is that it sounds very much like an I/O issue and that the new machine, without any load, has to retrieve all the data from disk while the old system with a load on it may already have much of the data in memory.
I would also see if the query plans are the same on both machines.

as2higpark SSC Eights! Points: 949 More actions · Answer 7

Alex,

Any progress on the wait stats? My query will show the stats since the last reboot of the instance.

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 8

This is exactly what is happening, identical queries, identical actual execution plans, same dataset, old box takes 1min 11secs, new box takes 7 minutes, on every occasion tho durations differs by a few sometimes, still a huge gap every time. I checked "lock pages in memory" but it says it is not required for x64, should I still try and see?

MysteryJimbo (7/26/2011)
Alexander Chacon (7/26/2011)
Hi as2higpark, how can I pull those statistics during the execution of a query?
Thanks
Alex C
as2higpark (7/26/2011)
Alex,
When you are seeing issues on the new server, what is the highest wait type, I want to try to compare your issue with ours to see if there are similiarities.
We see a very high percentage of CXPACKET with next highest being SOS_SCHEDULER_YIELD
Sounds very much like what I was seeing but what was really happening was memory related rather than cpu. What I would see is running identical queries with identical plans would return a resultset with a massively different duration on occasion.
Is "lock pages in memory" enabled for the service account?
http://msdn.microsoft.com/en-us/library/ms190730.aspx

Jim Sleeman-388184 SSC Enthusiast Points: 172 More actions · Answer 9

Is the data on the local drive of the new machine or the SAN?

tfifield SSCrazy Eights Points: 9655 More actions · Answer 10

Alexander,

You might want to try some different comparisons. SET STATISTICS IO, TIME ON before running the query on each box. Could there be a big difference in scans/reads?

Then try setting MAXDOP to 1 for the query on each box and compare.

It seems a bit odd that you would get such a dramatic difference between the boxes when the CPU speed of the AMD box is roughly 2/3 of the Intel box.

Todd Fifield

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 11

Tried both local and SAN

Jim Sleeman-388184 (7/26/2011)
Is the data on the local drive of the new machine or the SAN?

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 12

Hello all, resolution of this appears to be not related to hardware at all but actually from going to SQL Server 2008 R1 to R2 and differences with the query plan analyzer, see post here http://www.sqlservercentral.com/Forums/Topic826976-360-2.aspx , using the hotfix did it for me, thank you all very much for your help! Specially MysteryJimbo for bringing my attention to this fix in the first place, took me long to actually do it but I must admit it does seem like there's a hardware issue at first!

Alex C

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 13

Problem resolved, see post below, I can still post stats if you'd like to? Let me know

as2higpark (7/26/2011)
Alex,
Any progress on the wait stats? My query will show the stats since the last reboot of the instance.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 14

Alexander Chacon (7/26/2011)
Hello all, resolution of this appears to be not related to hardware at all but actually from going to SQL Server 2008 R1 to R2 and differences with the query plan analyzer, see post here http://www.sqlservercentral.com/Forums/Topic826976-360-2.aspx , using the hotfix did it for me, thank you all very much for your help! Specially MysteryJimbo for bringing my attention to this fix in the first place, took me long to actually do it but I must admit it does seem like there's a hardware issue at first!
Alex C

Good news.

Alexander Chacon (7/26/2011)
This is exactly what is happening, identical queries, identical actual execution plans, same dataset, old box takes 1min 11secs, new box takes 7 minutes, on every occasion tho durations differs by a few sometimes, still a huge gap every time. I checked "lock pages in memory" but it says it is not required for x64, should I still try and see?

I read this as well which is why i didnt enable it initially. Turns out microsoft still recommend it anyway and it works.

Alexander Chacon Right there with Babe Points: 797 More actions · Answer 15

Awesome, thanks buddy, ended up doing both things and apparently that took care of everything!

Thanks

Alex C

MysteryJimbo (7/27/2011)
Alexander Chacon (7/26/2011)
Hello all, resolution of this appears to be not related to hardware at all but actually from going to SQL Server 2008 R1 to R2 and differences with the query plan analyzer, see post here http://www.sqlservercentral.com/Forums/Topic826976-360-2.aspx , using the hotfix did it for me, thank you all very much for your help! Specially MysteryJimbo for bringing my attention to this fix in the first place, took me long to actually do it but I must admit it does seem like there's a hardware issue at first!
Alex C
Good news.
Alexander Chacon (7/26/2011)
This is exactly what is happening, identical queries, identical actual execution plans, same dataset, old box takes 1min 11secs, new box takes 7 minutes, on every occasion tho durations differs by a few sometimes, still a huge gap every time. I checked "lock pages in memory" but it says it is not required for x64, should I still try and see?
I read this as well which is why i didnt enable it initially. Turns out microsoft still recommend it anyway and it works.