Hello, how is everyone doing? this qury below used to take 5 to 10 minutes to execute. it has been like this as developer said for 2 years. Now it is taking more than one hour and a half to execute. all indexes and statistics are updated every week. the application is PeopleTools 8.43.... does anyone know what could be the problem? help is really appreciated.

Qury:

UPDATE T1 SET Col_1 = NULL, Col_2 =

T1.Col_5 = '10000' AND T1.Col_6 IN ( SELECT

T2_Alias.Col_3 = 999999 AND T2_Alias.Col_a = T1.Col_a

AND T2_Alias.Col_b = T1.Col_b)

Try the following....and make sure no other spid is blocking this update and you can use SP_WHO2 ACTIVE to get the blocking information...

How many rows it is updating? If you see blocking then you can modify your script to use WHILE loop to update 10/100/1000 rows at at a time depends on the the blocking and also use WAITFOR DELAY command give the option to other SPIDs to finish their work...

 Note: Make sure you have the right indexes and they are being use, if not then you can force the query to use...and check the query plan...

SELECT
T2_Alias.Col_a into #test
FROM T2 T2_Alias
join T1 T1_Alias ON
T2_Alias.Col_a = T1.Col_a
AND T2_Alias.Col_b = T1.Col_b
WHERE
T2_Alias.Col_3 = 999999

UPDATE T1_Alias SET T1_Alias.Col_1 = NULL, T1_Alias.Col_2 =
'Data', T1_Alias.Col_3 = 999999, T1_Alias.Col_4 = '2'
FROM T1 T1_Alias
JOIN #test T2_Alias on T2_Alias.Col_a = T1_Alias.Col_6

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx

http://www.databasejournal.com/features/mssql/article.php/1466951/SQL-Server-Performance-Tuning-for-SQL-Server-Developers.htm

http://209.85.173.132/search?q=cache:xEpS4RL48SoJ:download.microsoft.com/download/d/a/1/da1df8ae-a930-4a86-8e43-d92cc12deccc/sqlhol120%2520-%2520database%2520tuning.doc+SQL+serverquery+performance+tuning&hl=en&ct=clnk&cd=4&gl=us

Thanks Mohammed for your help, its updating 8000 records, the T1 record count is 2000000 . I will try what you've mentioned and let you know. 

Mohammed,  I actually installed a version of the Prod DB in my machine,, removed auto create and update stats, and forced a weekly update stats and reindex. still the query took more time. in execution plan, 49% nonClus index seeks, 49% nested loop, does this mean indexes are OK?

Look at the columns that are being updated ... Then look at the indexes on the table. Are those columns indexed? Are they perhaps even in the clustered index?

If you're updating indexed columns, the indexes will be updated too - this takes extra time. If it's a clustered index, then all the non-clustered indexes will be updated too - this will take even longer time.

The actual update query has #test in its FROM clause. If there is no PK or index declared on this temp table, and the number of rows in there is considerable, then this would explain the difference if there used to be much less records in #temp ...

I would rewrite the IN clause either to a JOIN or an EXISTS. Sometimes this tremendously helps speeding up things.

 I will try that too, thanks Frankkalis