Snargables (10/11/2013)
i have a table that has 600 mill recs. i have an update to it that modifies 79 k records a day. Up to last saturday at 8am this process ran in 20-30 mins. Now it's taking 3 hours. I defraged the index it's using because it was fraged 40 %. I just updated stats. Is there anything else i should do?
That is a sufficiently small fraction such that the most efficient way to do the update is with an index seek. Ensure whatever column(s) you use to interrogate the base table on for the update has a non-clustered index on it/them (or the clustered index if it matches already) and it should be very efficient and fast. If the index exists and IS being used then check for blocking with sp_whoisactive. If the index exists but is NOT being used - force it with a hint.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service