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:34: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
 
 
 

1. Equivalent SQL Code of Pivot(SQL Server 2005) in SQL Server 2000

2. How to rung SQL 2005 db on SQL 2000 server - no unique SQL 2005 st

I converted a database from Sql 2000 to Sql 2005 - no problems. Added
function to the database. I did not use any new functions unique to Sql
2005, as far as I know. I need to allw this database to run on both SQL 2000
and SQL 2005. When I attempted to load the database on SQL 2000 local
server, I received the following message:
Error 602 - Could not find Row in SysIndex for ID 7, Object ID 1, Index ID 1.
Run DBCC checktable on SysIndex.

I have never run this and I do not know if this is an on going problem. Any
information would be appreciated.

Thank You.

Jack
LitePipe Management

3. Confusion - Reporting services, VS 2005, SQL Server 2005, VS 2003, SQL Server 2000

4. SQL Server 2000 and SQL Server 2005 on Virtual Servers

5. Migration from SQL 2000 Enterprise Server to the SQL 2005 Enterprise Server (64 bit-Itanium Server)

6. Can we install SQL Server 2000 on same server with SQL Server 2005

7. Why use SQL Server 2005 over SQL Server 2000 with Project Server 2007?

8. SQL Server 7 and SQL Server 2000 SQL QUERY

9. SQL 2005 linked server to SQL 2000, need to run instcat.sql

10. How to rung SQL 2005 db on SQL 2000 server - no unique SQL 200

11. SQL 2005 linked server to SQL 2000, need to run instcat.sql

12. SQL 2005 linked server to SQL 2000, need to run instcat.sql.

13. SQL 2000 + SQL 2005 Express Compatibility (restore SQL 2005 DB with 2000 DB)

14. SQL Server 2005 Express Beta messes up your SQL Server 2000, BEWAR