Performance difference on identical servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Performance difference on identical servers

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-16 : 17:10:35

Guys,

We have MSSQL 2000 Server instance installed and working well on Windows 2003 Server machine [IBM X series-366] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space.

We further created an identical server instance on a new machine. More specifically, on Windows 2003 Server machine [Intel (R) Xeon (TM)] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space, we installed MSSQL 2000 Server and copied over all the dbs, applications ...

We were expecting same or similar performance (since processor speed, ram, hd, server and database configurations are all the same, with same indexes on same tables. However, for some reason, there is a noticeable difference in performance.

More specifically, I ran Profiler for 30 minutes on both servers simultaneously [same trace parameters]. The trace file of the new server is 3 times as large as that of the old one (i.e. It looks like more items are being processed). However, the average duration of the executed stored procedures is much longer on the new server than that of the old server.

Moreover, when I run same queries on 2 servers. The query on the new server always takes longer than that on the old server. And for tables where we don't have indexes, it takes much longer.

Following advice here(http://support.microsoft.com/kb/274750/), we configured our new server (just as was our old one configured) to use 15GB of RAM. I further compared the configurations of 2 servers by executing sp_configure (with advance options). The only difference I saw was that "remote proc trans" is set to off on the new server and on on the old server. I don't think it could affect this issue though.

Furthermore, the new server appears to have many more locks, as compared to the old server. Could it be because it is processing more items?

I cannot figure what is causing the queries to be slower on the new server.

Can anyone suggest anything?

Thanks a lot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 17:16:31

Maybe the physical files are defragmented?

Peter Larsson
Helsingborg, Sweden

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-16 : 17:23:31

Peter,

How can I found out the defragmentation level of the physical files?

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 17:26:08

There is a tool named CONTIG here
http://www.microsoft.com/technet/sysinternals/FileAndDisk/Contig.mspx

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 17:30:09

Put CONTIG in Windows directory.

Enter DOS mode. Goto MSSQL\Data directory.

Type CONTIG -a *.mdf

You can even defrag single files even if ms sql server service is running!
With CONTIG -v *.mdf

Don't forget about the LDF files...

Peter Larsson
Helsingborg, Sweden

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-16 : 17:51:42

Thanks a lot Peter.

I'll try this approach and see what happens!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-16 : 18:23:59

Did you run UPDATE STATISTICS and DBCC DBREINDEX on both databases? Are the execution plans the same for the identical queries? Does Performance Monitor show similar hardware performance when the queries are running?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-03-17 : 08:22:54

"There is a tool named CONTIG here"

sysinternals is a subset of Technet?

Was it always that way? I thought they were a bright bunch of out-siders ...

Kristen

Kristen
Test

22859 Posts

Posted - 2007-03-17 : 08:23:56

Flush the cache on both servers before starting Profiler?

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 14:02:49

No, Microsoft bought sysinternals for some months ago.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-03-17 : 19:41:53

"Microsoft bought sysinternals for some months ago"

Is that a good thing?

dewacorp.alliances

452 Posts

Posted - 2007-03-18 : 17:19:27

quote:
Originally posted by Kristen

Flush the cache on both servers before starting Profiler?

Kristen

Hi Kirsten

How do I flush teh cache?

V

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-19 : 00:57:49

Run these two commands on both servers so that the comparison is based upon the same baseline:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Tara Kizer
http://weblogs.sqlteam.com/tarad/

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-19 : 17:11:59

Tara: We update statistics every night. We run INDEXDEFRAG job daily as well. Would you advise to perform DBREINDEX on top of the INDEXDEFRAG?

Also, what do you mean by "hardware performance"? IO, CPU, and duration? If so, then here is what I did already: Ran profiler on both for 30 mins and then compared ave duration, ave reads for all running stored procedures. The numbers are very close to each other. No big difference.

Should I do something else?

Thanks for the advices - everyone!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-19 : 18:09:37

quote:
Originally posted by Kristen

"Microsoft bought sysinternals for some months ago"

Is that a good thing?

I would say no, it's not a good thing.

www.elsasoft.org

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-19 : 18:30:38

Guys,

The new thing I noticed by turning on STATISTICS is that the same simple query on slower environment has about 6 times the read-ahead reads, as compared to the faster environment.

Could that be the reason?

Thanks a lot

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-19 : 19:10:01

Did you run:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

on both databases prior to turning on stats and running the queries?

You can't make performance comparisons otherwise.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-20 : 10:29:39

I just did the following in both environments simultaneously:
1. CHECKPOINT
2. DBCC DROPCLEANBUFFERS
3. DBCC FREEPROCCACHE
4. SET STATISTICS TIME ON
5. SET STATISTICS IO ON
6. Query - SHOW EXECUTION PLAN
7. Ran a simple select query with a single condition

OLD
---
Table 'Test'. Scan count 1, logical reads 648672, physical reads 0, read-ahead reads 553699.

SQL Server Execution Times:
CPU time = 7125 ms, elapsed time = 150508 ms.

NEW
---
Table 'Test'. Scan count 14, logical reads 647119, physical reads 167, read-ahead reads 646585.

SQL Server Execution Times:
CPU time = 9907 ms, elapsed time = 52602 ms.

Surprisingly, the time in new environment is now less, although it is a lot in both of them.

There are also other differences, as can be seen.

Any new interpretations/ideas?

Thanks a lot!

Kristen
Test

22859 Posts

Posted - 2007-03-20 : 14:05:06

"Any new interpretations/ideas?"

Something different in the settings between the two machines!!

Is it an option to restore a backup from OldServer on to NewServer and make the time-trials on that? Fragmentation/Stats etc. should be identical then ...

Kristen

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-03-20 : 15:28:13

Kristen,

The backup from the old server has been restored on the new server. I don't have too much freedom in testing as the problematic server is currently the production server in use.

I can perform only light analysis, which will not result in too much interference.

I did not try file fragmentation yet ... still on my to-do list ...

Thank you

Kristen
Test

22859 Posts

Posted - 2007-03-21 : 02:51:49

"I did not try file fragmentation yet"

I don't think physical file fragmentation is going to effect the Scan Count of a query's execution plan!

For that I reckon there has to be a difference to the Stats or re-index done at different times on both DBs, something in cache, different ServicePack/Version, Server settings maybe - Parallelism? - and database too - e.g. Compatibility mode. Can't think of anything else that would cause a difference. But I reckon that a RESTORE of the database from OldServer is going to get you identical Stats, Re-index state and so on, so if you've done a Restore and its pretty much like-for-like I think there has to be a server configuration, or hardware, difference.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources