SQL2005 queries are slower than 2000

#81

Hi Andrew,

I guess it got confused.

What I ment was the query execution duration, when connected to a SQL2000
DB, form SQL2000 query analyzer,
read from the 2000 profiler and the 2005 profiler are the same.

But when connected to the same SQL2000 DB from SQL2005 and executed the
query, the profiler reads are more than above.

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Quote:

What exactly do you mean by query duration? How are you determining that?
If profiler says the duration is the same for both then it is the same.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"DPM" <aa (AT) bb (DOT) cc> wrote in message
news:e%23F9qOBrIHA.1772 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
Hi Andrew,

I used a SQL 2000 DB and connected with SQL2000 and SQL2005 on the same
computer in two sessions.
Ran the profilers separately for 2000 and 2005 for the same DB on the
same computer.
This ensures that the data, indexes and computer hardware is the same.

Profiler durations and reads are the same in 2000 and 2005 profiler.
But the problem is the query execution duration in 2005 is more as per
below stats.

Any more thoughts are appreciated.

"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote in message
news:%23ezzJUxqIHA.3940 (AT) TK2MSFTNGP03 (DOT) phx.gbl...
How are you getting the duration? In SQL2005 trace collects in
Microseconds where as 2000 uses milliseconds.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"DPM" <aa (AT) bb (DOT) cc> wrote in message
news:OmVPBlsqIHA.1164 (AT) TK2MSFTNGP04 (DOT) phx.gbl...
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')

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL2005 queries are slower than 2000

microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering

Re: SQL2005 queries are slower than 2000 - 05-04-2008 , 10:51 PM