We help IT Professionals succeed at work.
Get Started

Statistics problems after upgrading from SQL 2000 to SQL 2005

230 Views
Last Modified: 2012-05-05
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.

Comment
Watch Question
SQL Server Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE