SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

Post by DPM » Fri, 25 Apr 2008 22:36:54


Hi,



I have installed SQL2000 and SQL2005 on the same computer accessing the same
DB



When run the following simple queries the performance is as follows

Could some one please advice on how improve the performance on these queries
to run on SQL2005 ?

Are there any settings to be done in SQL2005 ?



SELECT Int_Key FROM fDocHst

Records 20422

SQL2000 - Uses an Index scan, reads 30, avg. dur 63

SQL2005 - Uses an Index scan, reads 30, avg. dur 406



SELECT Int_Key, DocumentNo, Sts, Rev, Title, Category, IntApprvd,
AvlDwgFmts, IntAdr, Type

FROM fDocHst

Records 20422



SQL2000 - Uses a Clustered Index scan, reads 2342, avg. dur 700

SQL2005 - Uses a Clustered Index scan, reads 2342, avg. dur 1261
 
 
 

SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

Post by DPM » Fri, 25 Apr 2008 22:55:47

Hi,

I have installed SQL2000 and SQL2005 on the same computer accessing the same
DB
When run the following simple queries the performance is as follows
Could some one please advice on how improve the performance on these queries
run on SQL2005
Are there any settings to be done in SQL2005 ?

SELECT Int_Key FROM fDocHst
Records 20422
SQL2000 - Uses an Index scan, reads 30, avg. dur 63
SQL2005 - Uses an Index scan, reads 30, avg. dur 406

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Category, IntApprvd,
AvlDwgFmts, IntAdr, Type
FROM fDocHst
Records 20422
SQL2000 - Uses a Clustered Index scan, reads 2342, avg. dur 700
SQL2005 - Uses a Clustered Index scan, reads 2342, avg. dur 1261

 
 
 

SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

Post by Chris Woo » Sat, 26 Apr 2008 05:15:15

What are the builds of SQL2000 and SQL2005?

Chris
 
 
 

SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

Post by Ajay Rengu » Sat, 26 Apr 2008 12:03:49


Hi,

Try to keep both SQL Servers to latest Service Packs ...

Thanks
Ajay Rengunthwar
MCTS,MCDBA,MCAD
 
 
 

SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000

Post by DPM » Sat, 26 Apr 2008 13:20:53

Hi Chris,

Following is what I figured out.
There is no build mensioned except for one, therefore the versions are as
follows.

SQL 2000
Enterprise manager - ver 5.0, build 2195, SP4
Profiler - 8.00.194
Query Analyzer - 8.00.194

SQL 2005
MS SQL Management Studio - 9.00.3042.00
MDAC - 2000.080.0194.00
MS Analysis Services Client tools - 2005.090.3042.00