sp_updatestats now running longer and causing timeouts/blocks

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Byrd - 17 Mar 2008 14:14 GMT

After more than 2.5 years of running the sp_updatestats on our production
(OLTP) SQL 2005 SP2a CU3 server we have suddenly noticed an abrupt change in
runtime behavior. Prior to 3/6/2008 this job was scheduled to run daily
(6:30am). The job historically completed in 9 – 11 min. with no effect on
user access or system locks. As of 3/6 executing sp_updatestats now takes 55
– 65 min. to complete. In addition to the longer run time, we are seeing
stale I/O warnings in the SQL error log (I/O taking longer than 15 sec to
complete) and an serious increase blocking locks. The locks rapidly escalate
in severity causing serious disruptions in service to our applications.

The following steps have been taken without success to resolve this issue:
1.    Ran dbcc checkdb on affected db – All test successful, no errors reported
2.    Rebuilt all indexes, including primary keys
3.    Ran dbcc updateusage

Even under very heavy load (such as index rebuids on very large tables >
40GB) we see no errors. We have updated all hardware drivers and firmware to
recommended levels.

Any advice on where to look next would be appreciated.

Signature

Cheers,
MByrd

Reply to this Message

Andrew J. Kelly - 17 Mar 2008 15:30 GMT

Have you run any actual hardware tests? The I/O warnings are a pretty good
sign you have issues with the storage subsystem but unfortunately it doesn't
narrow it down any. Updating the drivers is a good start but you probably
have an intermittent issue with the controllers or the disks somewhere. I
would run some exhaustive disk tests.

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> After more than 2.5 years of running the sp_updatestats on our production
> (OLTP) SQL 2005 SP2a CU3 server we have suddenly noticed an abrupt change
[quoted text clipped - 21 lines]
>
> Any advice on where to look next would be appreciated.

Reply to this Message

Mike Byrd - 17 Mar 2008 15:51 GMT

Our SAN vendor has run 2 days of exhaustive tests and keep telling us nothing
has changed. I agree with you that it is probably a hardware problem, but
have no way to force the SAN vendor to change their stance. What can I do on
my side to force the issue with them? Any advice would be appreciate.

Signature

MByrd

> Have you run any actual hardware tests? The I/O warnings are a pretty good
> sign you have issues with the storage subsystem but unfortunately it doesn't
[quoted text clipped - 27 lines]
> >
> > Any advice on where to look next would be appreciated.

Reply to this Message

Andrew J. Kelly - 17 Mar 2008 19:25 GMT

Well are the tests done solely at the SAN level? IF so they missed half the
potential cause of the issues. It could still be drives on the Server for
the HBA, the HBA itself, the Fiber to the switch or from the switch to the
SAN or the switch. You may have to run a utility such as SQLIOstress to
stress the entire path yourself. This utility takes SQL Server out of the
equation all together by the way.

http://support.microsoft.com/kb/231619

Signature

Andrew J. Kelly SQL MVP
Solid Quality Mentors

> Our SAN vendor has run 2 days of exhaustive tests and keep telling us
> nothing
[quoted text clipped - 45 lines]
>> >
>> > Any advice on where to look next would be appreciated.

Reply to this Message

Linchi Shea - 18 Mar 2008 02:38 GMT

I agree that you may want to run disk I/O tests yourself or together with
your SAN folks. What they told ou might be the truth, but might not be
focused on exactly where it mattered to you.

Linchi

> Our SAN vendor has run 2 days of exhaustive tests and keep telling us nothing
> has changed. I agree with you that it is probably a hardware problem, but
[quoted text clipped - 32 lines]
> > >
> > > Any advice on where to look next would be appreciated.

Reply to this Message