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

Tech-Archive recommends: Speed Up your PC by fixing your registry

Thanks


"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:ebOTXPrpIHA.1952@xxxxxxxxxxxxxxxxxxxxxxx
DPM
--UPDATE STATISTICS ON CURRENT DATABASE

DECLARE @tablename varchar(80)

DECLARE @SQL AS NVARCHAR(200)

DECLARE TblName_cursor CURSOR FOR

SELECT name FROM sys.tables

OPEN TblName_cursor

FETCH NEXT FROM TblName_cursor

INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'UPDATE STATISTICS dbo.[' + @TableName + '] WITH FULLSCAN '

EXEC sp_executesql @statement = @SQL

FETCH NEXT FROM TblName_cursor

INTO @tablename

END

CLOSE TblName_cursor

DEALLOCATE TblName_cursor

"DPM" <aa@xxxxx> wrote in message
news:%23biCJ%23opIHA.1436@xxxxxxxxxxxxxxxxxxxxxxx
Hi Uri,

Please could you let me know how to update the statistcs ?
For the below mensioned queries the execution plans are similar just only
with an Index scan or Clustered Index scan.


"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:OgHBCUipIHA.4884@xxxxxxxxxxxxxxxxxxxxxxx
DPM
Did you update staistics after upgrading? Also take a look at
execution plans it might be changed as sql server engine is changed
meaning the optomizer is much smarter to create an more efficient
execution plan



"DPM" <aa@xxxxx> wrote in message
news:uf0mp$gpIHA.1236@xxxxxxxxxxxxxxxxxxxxxxx
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










.

Relevant Pages