Suddenly timeouts are everywhere. Eeek! 2

[Sheco]

I am suddenly getting timeouts when my app tries to update/insert anything.

This is a new problem just started happening today and is currently impacting all 100 desktop users running against a specific instance of the db.

No errors occur when the same client app rungs against a copy of the db restored from last month. The copy is hosted on the same clustered servers so I don't think it is a hardware or network error.

The files are set to grow automatically. Any ideas?

 

[gmmastros]

Are there any locks?

Run:
[tt][blue]sp_who2[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

could be a lot of things

stats out of date
fragmentation
blocking/locking
runaway transaction

run sp_who2 and check the BlkBy column

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Sheco]

Nobody blocked and DBCC CHECKDB found nothing

 

[Sheco]

Also forced a failover in the cluster and the same timeouts.

 

[SQLDenis]

are the stats up to date, run a query in query analyzer to see
do you have antivirus running? perhaps it decided to do a scan

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Sheco]

sp_updatestats seems to have worked

 

[SQLDenis]

now the question is do you have auto stats enabled?

run this,

SELECT DATABASEPROPERTYEX( db_name() , 'IsAutoUpdateStatistics' )

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Sheco]

Returned 1

 

[SQLDenis]

Okay that is strange, i assume you were in the correct DB when you ran this

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Sheco]

Yes. I found out another person was poking at the DB at the same time. They arn't sure what they might have done besides make a complete backup.

 

[SQLDenis]

it is also possible the db was expanding at that time, someone ran a big query etc etc etc

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Sheco]

We kicked everyone out before we started messin with it. Now unsure what to tell the users about why it is working and what I'm gonna do to keep it from happening again.

Think maybe add update statistics to the maint plan.

 

[SQLDenis]

if you do a mass insert or update every now and then it makes sense to update the stats manually after that happens

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions