We recently upgraded a very busy SQL Server 2000 - moved it to all new hardware and to SQL Server 2005 Enterprise edition on a two-node active/passive cluster.
We've got one application that periodically encounters timeouts. These timeouts started after we migrated to 2005. There are five tables involved, only one or two that are what I would call very large.
Originally, I assumed it was an indexing issue. Defragmenting the indexes didn't do the trick, but rebuilding the indexes did. That's when I started to suspect there was some issue with the statistics.
We have a weekly index defrag job that runs on Sunday nights.This seems to be fine for all other applications that touch this server. Being as we were still seeing problems, I created a daily job to update the statistics on all of the tables involved at 4:45 am.
However, 30% or more of the time, we'll run into the same problem at 9am or 9:30am. Updating the statistics again on the largest table tends to resolve the problem. I have been unable to identify any process that would cause harm to the statistics, and all the indexes are in fantastic shape - less than 2% fragmented.
I've been hammering away at this problem for weeks, and have come no closer to a fix. Any help would be terrific. I'd like to identify why the statistics become a problem so frequently and how I can stop it or fix it before we get customer timeouts.