PapaStevens
asked on
Statistics problems after upgrading from SQL 2000 to SQL 2005
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The situation grows more and more confusing. We had the timeouts again today. The fix was to update the statistics on a table that has only 385,060 rows. Short of profiling this table, is there any way of tracking this down to find the cause?
ASKER
As an update, the issue happened again today. Updating the statistics resolved the problem again, but this time I was able to save the execution plans before and after updating the statistics. I expected to see some substantial differences, but both plans were identical. The only difference, of course, is that the procedure ran in 3 seconds afterward as opposed to 2 minutes before.
ASKER
It appears the issue is related to the Auto Statistics Update feature in 2005. We're going to switch to use asynchronous automatic statistics update to see if that resolves the issue. I'm awarding points to dbaduck for responding and attempting to help.
ASKER