SQL 2000 Performance

[niall5098]

HI THERE,

I have 2 server similar in spec:

PROD SERVER:

Operating System: Windows Server 2003, Standard Edition (5.2, Build 3790) Service Pack 2 (3790.srv03_sp2_gdr.090805-1438)
Processor: Intel(R) Xeon(TM) CPU 3.20GHz (4 CPUs), ~3.2GHz
Memory: 3584MB RAM
Page File: 2120MB used, 2844MB available

SQL Server 2000 SP4

DEV SERVER:

Operating System: Windows Server 2003, Standard Edition (5.2, Build 3790) Service Pack 2 (3790.srv03_sp2_gdr.090805-1438)
Processor: Intel(R) Pentium(R) III Xeon processor (4 CPUs), ~2.5GHz
Memory: 3326MB RAM
Page File: 3118MB used, 2103MB available

SQL Server 2000 SP4

If i run the same query against both server i get faster data retrieval from the dev box.

Optimisation jobs are in place.

The only difference i can see is that the paging file had 1GB more on the dev server.

If i added 1 GB more to the prod box, might i see an improvement on the performance?

Thanks in advance.

 

[DrSql]

The rule of thump for pagefile is 1.5 of the total memory and reccomned to leave 512 on C:\ for OS and reamining can be distrubuted on other drives. That ebing said I would suggest compare the the db and table fragmentation you can use DBCC SHOWCONTIG and look for SCAN DENSITY. Here is a good article

http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p1.aspx

If both DB schema and data are same then I would sugget running a re-index or run the query through Tuning advisor.

Good luck.


Dr.Sql
Good Luck.

 

[mrdenny]

Look at the execution plan for both queries. I'm guessing that they have different execution plans.

Odds are your statistics are out of date on the production system.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[niall5098]

Dbcc showcontig revealed more fragmentation on the dev box, which makes the situation a bit more bizarre.

Execution plans for certain queries were shown to be the exact same.

 

[DrSql]

Few more questions

You verified the fragmentation for all tables used in the query?
How much data you are querying as output?
Does it have lot of joins?
Can you check the buffer cache and disk io?
Is your prod and dev using same type of disk in tiers?
I would still explore running a complete re-index try to re-run your query.
Also review the entire health of the systems and verify that while your query runs in Prod is there any blocks or locks arise. Assume there is no connection in DEV and probability of having any blocks is less.
Please respond with your answers.
Thanks


Dr.Sql
Good Luck.