Slow SQL Server 2005 Performance w/ No Internet Connection

Question

Slow SQL Server 2005 Performance w/ No Internet Connection

astoughton

SSC Journeyman

Points: 79
More actions
July 24, 2008 at 4:52 am

#381883

I have 2 systems both running SQL Server 2005 Standard Edition, each containing the same database. One computer (A) has Internet access; the other (B) is a stand-alone machine with no network connection. Both systems run Windows XP w/SP2.
There is an emormous performance difference between the two machines when executing a rather straightforward parameterized query in a stored procedure. System A executes and completes immediately for all practical purposes but System B consistenly takes 27 seconds to execute. I am running the stored procedure via Management Studio and keying in the required parameters.
I'm not certain that the lack of an Internet connection is the problem but it's the only difference I've found thus far between the 2 systems.

Viewing 8 posts - 1 through 7 (of 7 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Check the two execution plans. That's where the difference will be.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Identical hardware?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

astoughton SSC Journeyman Points: 79 More actions · Answer 3

Thanks . . .

Here's the stored proc. I ran it with these paramter values in Management Studio .@NumberOfReadings = 5, @Type = 1 and @Station = 1.

;WITH LatestMeasurement(measurement_timestamp, measurement_value)

AS

(SELECT TOP(@NumberOfReadings) measurement_timestamp, measurement_value

FROM measurement WITH (NOLOCK)

WHERE measurement_type_id = @Type and station_id = @Station

ORDER BY measurement_timestamp DESC)

SELECT maxx = (SELECT AVG(measurement_value) FROM LatestMeasurement WITH (NOLOCK))

The measurement table has about 250,000 rows. On both machines, the Execution Paln begins with a Table Scan followed by a Sort. But the suspect (slow) machine then shows a Parallelism step that I don't understand.

Plans A vs. B report:

I/O Cost is 3.4 vs. 163.7

CPU Cost 0.23 vs 0.13

Operator Cost 3.77 vs 163.837

Subtree Cost 3.77 vs 163.837

Machine A is a SONY VAIO w/ 2.6 GHz processor and 2 GB RAM

Machine B is a Lenovo ThinkCentre w/2.0 GHz processoer and 1.0 GB RAM

Both systems running XP w/SP2 & SQL 2005 Standard

I had no part in configuration of the Lenova system and am very suspicious of its setup. I noticed that even a simple Open Table selection after right-clicking a table name takes longer to execute.

Any ideas or direction much appreciated.

Al

astoughton SSC Journeyman Points: 79 More actions · Answer 4

Thanks Gail . . .

This is the reply Iposted for another respondent. Machine hardware specs listed below.

Here's the stored proc. I ran it with these paramter values in Management Studio .@NumberOfReadings = 5, @Type = 1 and @Station = 1.

;WITH LatestMeasurement(measurement_timestamp, measurement_value)

AS

(SELECT TOP(@NumberOfReadings) measurement_timestamp, measurement_value

FROM measurement WITH (NOLOCK)

WHERE measurement_type_id = @Type and station_id = @Station

ORDER BY measurement_timestamp DESC)

SELECT maxx = (SELECT AVG(measurement_value) FROM LatestMeasurement WITH (NOLOCK))

The measurement table has about 250,000 rows. On both machines, the Execution Paln begins with a Table Scan followed by a Sort. But the suspect (slow) machine then shows a Parallelism step that I don't understand.

Plans A vs. B report:

I/O Cost is 3.4 vs. 163.7

CPU Cost 0.23 vs 0.13

Operator Cost 3.77 vs 163.837

Subtree Cost 3.77 vs 163.837

Machine A is a SONY VAIO w/ 2.6 GHz processor and 2 GB RAM

Machine B is a Lenovo ThinkCentre w/2.0 GHz processoer and 1.0 GB RAM

Both systems running XP w/SP2 & SQL 2005 Standard

I had no part in configuration of the Lenova system and am very suspicious of its setup. I noticed that even a simple Open Table selection after right-clicking a table name takes longer to execute.

Any ideas or direction much appreciated.

Al

GSquared SSC Guru Points: 260824 More actions · Answer 5

Just the difference in RAM alone will often result in much slower performance on that machine.

What types of CPUs do they have? Is either one dual-core (or more)?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

astoughton SSC Journeyman Points: 79 More actions · Answer 6

Thanks GSquared

I realized that as soon as I saw the specs. I "inherited" that B machine and should not have assumed.

When I saw TableScan I added indexes for type and station columns. Dramatic improvement -- performance is now where I expected it to be.

Thaks again fo rpointing me in right direction.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Glad I could help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon