Terence Tung


Hi,

We are doing response time testing on our application with SQL Server 2000 SP4. But it is found that a query have different performance on the same DB (restore from the same backup) in different servers. The execution plans are different on the 2 servers.

Server A: the execution plan use parallelism with varying response time (from 3 sec to 64 sec). Server B: it is a single thread execution plan with faster response time (about 1 sec).

Here is the server configuration:

Server A

Dell 750, you can access Dell homepage for more detail.

CPU 每 P4 2.8 GHz CPU

Memory 每 2G

Disk 每 RAID 1 SCSI

OS 每 Windows 2003 Enterprise Edition SP1

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

sp_configure

name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 804 804
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0



Server B

Dell 2650, you can access Dell homepage for more detail.

CPU 每 Xeon 3 GH X2

Memory 每 4G

Disk 每 RAID 5 SCSI by 3 disks (C Drive) + RAID 1 SCSI (D Drive)

OS 每 Windows 2003 Enterprise Edition SP1

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

sp_configure

name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 20480 20480
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0


The problematic query:

exec sp_executesql N'
SELECT AmdAlertReferenceRecord.KeyId AS MessageKeyId
,AmdDetail.KeyId AS KeyId
,AmdDetail.AmdStatus AS AmdStatusKeyId
,AmdDetail.AttributeKeyId AS AmdAttributeKeyId
,AmdDetail.InternalSequence
,AmdDetail.Subject AS Subject
,AmdFolder.Folder AS Folder
,AmdAttribute.Description AS Description
,AmdDetail.ChangeFrom AS FromValue
,AmdDetail.ChangeTo AS ToValue
FROM AmdAlertReferenceRecord
INNER JOIN AmdVersionPair
ON AmdAlertReferenceRecord.AmdModuleCode = AmdVersionPair.ModuleCode
AND AmdAlertReferenceRecord.AmdModuleId = AmdVersionPair.ModuleId
AND AmdAlertReferenceRecord.RevisionMajorTo = AmdVersionPair.MajorTo
AND AmdAlertReferenceRecord.RevisionMinorTo = AmdVersionPair.MinorTo
AND AmdVersionPair.IsVersion = 0
INNER JOIN AmdDetail
ON AmdDetail.VersionPairKeyId = AmdVersionPair.KeyId
INNER JOIN AmdAttribute
ON AmdAttribute.KeyId = AmdDetail.AttributeKeyId
INNER JOIN AmdFolder
ON AmdAttribute.AmdFolderKeyId = AmdFolder.KeyId
WHERE TargetModuleCode = @ModuleCode
AND TargetModuleId IN (''000002374'',''000002373'',''000002339'',''000002341'',
''000002444'',''000002371'',''000002567'',''000002565'',
''000002563'',''000002771'',''000002772'',''000002467'',
''000002774'',''000002443'',''000003216'',''000003210'',
''000003209'',''000002470'',''000002686'',''000003352'',
''000002391'',''000002401'',''000002773'',''000002395'',
''000006502'',''000006501'',''000002338'',''000002336'',
''000002368'',''000003232'',''000002509'',''000002355'',
''000002784'',''000002469'',''000002653'',''000002464'',
''000002357'',''000002788'',''000002786'',''000002787'',
''000002785'',''000002789'',''000002790'',''000002685'',
''000002442'',''000003059'',''000002652'',''000002557'',
''000003362'',''000002410'',''000002445'',''000002382'',
''000002383'',''000002343'',''000002407'',''000002572'',
''000002384'',''000002344'',''000002571'',''000002468'',
''000002405'',''000003359'',''000003602'',''000003596'',
''000003594'',''000003595'',''000003591'',''000003593'',
''000003507'',''000005852'',''000003601'',''000003603'',
''000003129'',''000002775'',''000003360'',''000003130'',
''000003369'',''000003370'',''000002366'',''000003234'',
''000003233'',''000003211'',''000002390'',''000003206'',
''000003207'',''000002397'',''000003235'',''000003236'',
''000002399'',''000002687'',''000002398'',''000002400'',
''000003218'',''000003219'',''000003246'',''000002406'',
''000002688'',''000002409'',''000002408'',''000002783'',
''000002370'',''000002856'',''000002365'',''000002367'',
''000004264'',''000003871'',''000002389'',''000003361'',
''000002776'',''000002358'',''000004272'',''000004954'',
''000002995'',''000002465'',''000002364'',''000002388'',
''000003212'',''000005856'',''000003872'',''000004271'',
''000004270'',''000003506'',''000004267'',''000002369'',
''000005855'',''000004265'',''000004266'',''000003505'',
''000002512'',''000002813'',''000002570'',''000003358'',
''000005221'',''000004273'',''000002385'',''000004274'',
''000003728'',''000004952'',''000003040'',''000004953'',
''000002996'',''000003353'',''000002993'',''000004950'',
''000002356'',''000004268'',''000007427'',''000004269'',
''000002375'',''000003511'',''000003870'',''000004263'',
''000003351'',''000002558'',''000006018'',''000002472'',
''000002471'',''000002396'',''000002411'',''000003355'',
''000002340'',''000004957'',''000002566'',''000002424'',
''000002423'',''000002422'',''000003365'',''000003366'',
''000002337'',''000002345'',''000002569'',''000003363'',
''000002420'',''000002425'',''000002421'',''000003371'',
''000007454'',''000007453'',''000005853'',''000003592'',
''000003504'',''000002534'',''000002335'',''000002535'',
''000003364'',''000004956'',''000003349'',''000003067'',
''000004958'',''000003354'',''000002372'',''000004955'',
''000003039'',''000002381'',''000003509'',''000003217'',
''000002432'',''000003350'',''000002431'',''000003727'',
''000002559'',''000008533'',''000003208'',''000006663'',
''000006665'',''000006711'',''000006669'',''000002386'',
''000002387'',''000003215'',''000003128'',''000006676'',
''000005854'',''000006712'',''000006664'',''000006666'',
''000006675'',''000006674'',''000006679'',''000006678'',
''000006677'',''000002564'',''000004951'',''000008557'',
''000008559'',''000003214'',''000008558'',''000006671'',
''000006670'',''000002791'',''000005220'',''000002994'',
''000002969'',''000004949'',''000006019'',''000006017'',
''000002511'',''000003508'',''000004948'',''000002359'',
''000005847'',''000004947'',''000002354'',''000004296'',
''000003510'',''000002668'',''000004297'',''000003174'',
''000002667'',''000003368'',''000002510'',''000003173'',
''000003401'')
AND ((EXISTS (SELECT 1
FROM AmdAlertReferenceRecordTarget
WHERE AmdAlertReferenceRecordTarget.RecordKeyId = AmdAlertReferenceRecord.KeyId
AND ((AmdAlertReferenceRecordTarget.AlertToAll = 1)
OR (AmdAlertReferenceRecordTarget.TargetKeyId = @UserKeyId
AND AmdAlertReferenceRecordTarget.IsGroup = 0)
OR (AmdAlertReferenceRecordTarget.IsGroup = 1
AND AmdAlertReferenceRecordTarget.TargetKeyId IN (54,63,65)))))
OR (EXISTS (SELECT 1
FROM AmdAlertReferenceRecordDismissedTarget
WHERE AmdAlertReferenceRecordDismissedTarget.RecordKeyId = AmdAlertReferenceRecord.KeyId
AND ((AmdAlertReferenceRecordDismissedTarget.AlertToAll = 1)
OR (AmdAlertReferenceRecordDismissedTarget.TargetKeyId = @UserKeyId
AND AmdAlertReferenceRecordDismissedTarget.IsGroup = 0)
OR (AmdAlertReferenceRecordDismissedTarget.IsGroup = 1
AND AmdAlertReferenceRecordDismissedTarget.TargetKeyId IN (54,63,65)))
AND (AmdAlertReferenceRecordDismissedTarget.LastRevisionMajorDismissed < AmdAlertReferenceRecord.RevisionMajorTo
OR AmdAlertReferenceRecordDismissedTarget.LastRevisionMinorDismissed < AmdAlertReferenceRecord.RevisionMinorTo
OR AmdAlertReferenceRecordDismissedTarget.RecordModifyTime < AmdAlertReferenceRecord.ModifyTime)))
OR (NOT EXISTS (SELECT 1
FROM AmdAlertReferenceRecordDismissedTarget
WHERE AmdAlertReferenceRecordDismissedTarget.RecordKeyId = AmdAlertReferenceRecord.KeyId
AND ((AmdAlertReferenceRecordDismissedTarget.AlertToAll = 1)
OR (AmdAlertReferenceRecordDismissedTarget.TargetKeyId = @UserKeyId
AND AmdAlertReferenceRecordDismissedTarget.IsGroup = 0)
OR (AmdAlertReferenceRecordDismissedTarget.IsGroup = 1
AND AmdAlertReferenceRecordDismissedTarget.TargetKeyId IN (54,63,65))))));
', N'@ModuleCode nvarchar(4000),@UserKeyId int', @ModuleCode = N'PRWorksheetSubLine', @UserKeyId = 154
GO


Both servers return about 860 rows in the same query.

And it is strange the the
SET SHOWPLAN_TEXT ON option could not output the execution plan in the text result. I'm sorry about that I couldn't provide a execution plan in text mode.


I have tried to use OPTION (MAXDOP 1), but it still takes at least 7 sec to finish. It's still slow compare to the Server B 1 sec performance.

Comparing the difference on the execution plans,
in server A, it use 69% cost on the table AmdDetail
clustered index scan (row count 5,052,347, estimate row count 2662) and 30% cost on Hash match/Inner join AmdDetail.VersionPairKeyId = AmdVersionPair.KeyId.
In server B, it use 1% on the table AmdDetail
index seek.
the number of rows in the table AmdDetail is about 5050645.
The table has a primary key PK_AmdDetail on KeyId column
and a index IdxNC_AmdDetail on the column VersionPairKeyId.
The column VersionPairKeyId is a foreign key column reference to the PK column KeyId of the table Amd
VersionPair.

Could anyone tell me why different servers have different execution plans
Why does the single processor machine, Server A, use parallelism

Thanks a lot.
Terence





Re: Same DB, Different Performance on Different Servers

Tom Phillips


You have two totally different hardware setups. It optimizes the plan for the hardware and the database.

Server B has twice the memory and probably 5 times the hard drive thruput of Server A because of the RAID 5 and controller difference.

"Parallelism" in the execution plan is "query parallelism", which means seperate threads, not CPU parallelism. If there are multiple CPUs then the threads "might" be executed on different CPUs.







Re: Same DB, Different Performance on Different Servers

Terence Tung

Dear Phillips,

Thanks for your comments. And I agree that the hardware configuration would cause different exec plan for the same query.

However, sometimes it's found that the more powerful server (Server B) produces poor performance on some queries than Server A. Server B uses query parallelism but Server A does not. It make me feel confused during performance tuning. I have tried to use OPTION (MAXDOP 1) as a query hint to optimize the performance. But we do not prefer to use this approach since the optimizer is restricted by the query hint and may not be able to make use of the multiple threads working on multiple CPUs.

Is there any good suggestion on this situation

Thanks a lot.









Re: Same DB, Different Performance on Different Servers

Tom Phillips

Have you turned on AWE on server B and configured it for more than 2gbs of RAM Also, there are hotfixes you have not applied for SQL 2000. Try that first.

You should never need to use MAXDOP 1, that is a very bad idea. You need to profile it and figure out why it is running slower. Also, update the stats on your indexes.





Re: Same DB, Different Performance on Different Servers

Jens K. Suessmeyer

There are situations where you need / use the MAXDOP option, so in general you would not do that, but for using a custom query plan (which might be sometimes better than the proposed one of the query optimizer) one could do that.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---




Re: Same DB, Different Performance on Different Servers

Terence Tung

Hi,

Thanks for you guys comment.
But what are the hotfixes that we need to apply
I'm interested in the hotfix ID and the detail.

Thanks a lot.




Re: Same DB, Different Performance on Different Servers

Tom Phillips

Go here: http://support.microsoft.com/kb/894905/en-us

The most current version is 8.00.2187 and includes a hot fix for not all memory being used in more than 4gb environment.

This might be part of your problem.