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

#71

You could run a profiler trace to find the longest running queries, and then
tune the query itself and/or re-design the indexing on the tables used in
those queries.

--
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,

I have tried the below script and further, the following but still there is
no improvement in SQL 2005
Could someone please advise.

- Updated the Statistics by UPDATE STATISTICS fDocHst WITH FULLSCAN
- ReIndexed the table from using Maintanance plan wizard.
- DBCC DBReIndex('fDocHst')
- DBCC IndexDefrag('DBDemo', 'fDocHst', 'fDocHst_Index1')

"Anthony Thomas" <ALThomas (AT) kc (DOT) rr.com> wrote

Quote:

You could just rebuild all of your indexes, either manually, through a
script, or using the Database Maintenece wizards.

The same is true for the hueristic indexes/statistics.

However, Uri's script will provide you better output if you're interested
in the progress.

Sincerely,

Anthony Thomas, MCDBA, MCSA
Senior Consultant
Sogeti USA

"DPM" <aa (AT) bb (DOT) cc> wrote in message
news:urZc5JupIHA.4716 (AT) TK2MSFTNGP06 (DOT) phx.gbl...
Thanks

"Uri Dimant" <urid (AT) iscar (DOT) co.il> wrote in message
news:ebOTXPrpIHA.1952 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
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 (AT) bb (DOT) cc> wrote in message
news:%23biCJ%23opIHA.1436 (AT) TK2MSFTNGP05 (DOT) phx.gbl...
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 (AT) iscar (DOT) co.il> wrote in message
news:OgHBCUipIHA.4884 (AT) TK2MSFTNGP06 (DOT) phx.gbl...
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 (AT) bb (DOT) cc> wrote in message
news:uf0mp$gpIHA.1236 (AT) TK2MSFTNGP02 (DOT) phx.gbl...
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

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

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

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

Re: SQL queries are slow in SQL Server 2005 when compared with SQL Server 2000 - 04-30-2008 , 08:52 AM