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:36:49


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
 
 
 

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

Post by Uri Diman » Sat, 26 Apr 2008 01:05:50

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

 
 
 

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

Post by DPM » Sat, 26 Apr 2008 13:51:35

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.
 
 
 

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

Post by Uri Diman » Sat, 26 Apr 2008 18:07:41

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
 
 
 

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

Post by DPM » Sat, 26 Apr 2008 23:44:01

Thanks
 
 
 

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

Post by Anthony Th » Tue, 29 Apr 2008 04:03:37

ou 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

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

Post by DPM » Thu, 01 May 2008 22:43:05

i,

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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

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

Post by Tom Morea » Thu, 01 May 2008 22:52:07

ou 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...