June 24, 2003 at 11:46 am
I've got two servers, both dual CPU 2.4GHz, hyperthreaded (look like 4), similar disk configs, etc. One has 2GB memory, one has 4GB memory.
If I issue a "select * from section" on the first server, it takes about 10 seconds. It gets as low as 6 with repeated executions, but can vary and get up to 11-14 seconds.
If I usse this on the second server, it is consitently under 2 seconds.
Both tables have 6300 rows (6363 and 6351) and the structures, indexes, etc. are the same. I run SET STATISTICS IO ON and SET STATISTICS PROFILE ON and see no physical reads on either server. I can watch the CPU and it doesn't break 5% on either server during my testing.
I checked the fragmentation and the slower server is less fragmented. 91% density v 84% on the faster server.
The databases in both cases are supposed to be the same, some minor data differences, but very, very similar. Row size is the same, IO cost, CPU cost within a percentage point or so of each other.
Any ideas?
Steve Jones
June 24, 2003 at 12:13 pm
The execution plans should be same too, Have you verify that? Is the sp_configure identical on both servers?
June 24, 2003 at 12:27 pm
Execution plan on both is clustered index scan.
sp_configure is the same, all values
memory usage is about the same on both, 1,716,328 on one, 1,717,068 on the slower one.
got me stumped. Tempted to rebuild all indexes, but I've checked about a dozen tables in two databases and they consistently take about 2-3 times as long on one server.
Steve Jones
June 24, 2003 at 12:31 pm
Same service packs and hot fixes? Run sp_updatestats?
Andy
June 24, 2003 at 6:26 pm
same, same, same. Tempted to rebuild indexes. Update stats is a good idea. Will try later.
Steve Jones
June 25, 2003 at 12:30 am
we are experiencing the same problem: we have 2 servers. server1 is much more powerfull than server2. but executing the queries on server1 takes almost as much time as server2. we already tried defragmenting, updatestats, settings, network tests,... we don't find a reason for it.
Paul Van Steenbergen
paul.vansteenbergen@belgoprocess.be
?
June 25, 2003 at 2:01 am
Have you tried benchmarking the machines to check they really are the same spec.
June 25, 2003 at 2:11 am
is the cash is the same size??
is the RAM is the same type (DDRAm, RDRam or SDRam) ?? DDRam is more faster.
did you check the Partition you put data on it .. is it formatted by default cluster size (4K) .. or you format it with 32K cluster size ? .. 32K for data and log will make the execution happens very fast.
I hope this help u.
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 25, 2003 at 2:13 am
Hi Steveo
Long shot #1 - Did you profile the statement? checking the execution plans very carefully.
Long shot #2 - any other jobs running, run perfmon at the same time with the typical counters
Long shot #3 - absolutly no chance of blocking issues?
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 25, 2003 at 2:55 am
I have come across the same situation and still I am trying to figure out what is wrong?
June 25, 2003 at 5:30 am
Did you try using the "OPTION (MAXDOP 2)" on the 4-CPU server ?
Maybe this query perform better with 2 CPUs only.
I have the following situation in here:
the developers use a 1-CPU development
server, and we have several produtcion servers, ranging from 1 to 6 CPUs.
Some processes perform better on the 1/2-CPU servers.
I think that when we are developing one application, and using multiple servers, with different number of CPUs, we must evaluate which is the optimal MAXDOP setting to use on all of them.
June 25, 2003 at 6:54 am
quote:
If I issue a "select * from section" on the first server, it takes about 10 seconds. It gets as low as 6 with repeated executions, but can vary and get up to 11-14 seconds.If I usse this on the second server, it is consistently under 2 seconds.
Are you running these tests locally or over the network? When returning results, the network can have a significant factor in the performance. Just a shot in the dark...
June 25, 2003 at 7:24 am
Steve,
I am no expert but, something you may want to look at is the Parallelism setting on the processor tab under Server Properties. There is a setting for "Minimum query plan threshold for considering queries for parallel execution (cost estimate)". The default is 5. I found on some queries that the cost estimate it came up with was to high and the queries actually ran slower in parallel (I'm guessing the overhead of running on multiple processors) than on just one processer. I bumped this setting up (or you can force it to use one processor) to a higher threshold and my queries ran much faster. I discovered this much the same way, my faster server was running some queries slower than on my other very much slower server. Give it a try.
Anthony
June 25, 2003 at 7:46 am
I'm in agreement with hurcane on possible network overhead (coming from the voice of experience 🙁 )
You might try "select * into #tmp from section" instead of a straight select, see what happens.
Randy
June 25, 2003 at 9:13 am
hurcane and Randy are on the right track. We tagged this late last night and I'll be writing it up further in an article, but it was network, but not overhead.
We checked all settings, all hardware. Everything was the same, but the slower server was set to half duplex.
This explained why the differences seemed to exaggerate with table size. The time was not in the query times, which were very similar in profiles and execution plan analysis, but different in observed results. The transfer time between the server and the workstations was substantially different.
Thanks for all the suggestions and those of you with similar problems might want to look for this.
Steve Jones
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply