SQL2005 queries are slower than 2000

#1

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#2

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#3

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#4

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#5

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#6

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#7

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#8

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#9

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')

#10

You may have an issue with the disk. Have you tried doing a disk defrag?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

"DPM" <aa (AT) bb (DOT) cc> wrote

Hi,

Using a P4 computer, 2.8GHz, 958 RAM the performance are as follows,
Please could someone advise what can be done to improve the performance in
SQL2005.

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

SELECT Int_Key, DocumentNo, Sts, Rev, Title, Int_Change, CheckedOut,
Category, Int_Apprvd, AvlDwgFmts, Int_Adr, Type
FROM fDocHst
SQL2000 - Uses a Clustered Index scan, reads 2345, avg. dur 447
SQL2005 - Uses a Clustered Index scan, reads 2345, avg. dur 901

Tried the following but there wasn't a improvement in SQL2005

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN for all
the tables in the DB
- ReIndexed the table from using Maintanance plan wizard.
- Also tried DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('Tb159Demo', 'fDocHst', 'fDocHst_Int_Key')