Thanks Ninja, I just added that one to my list of queries to try.

I ran this on the problem DB and no stats are more than 0.3% modified, so...

Thanks to both of you, but stats are in good shape, we run a full update weekly on all indexes, plus we ran a midweek one last week as well,...

Ninja,

They are OLTP queries and the bane of my existence....

They are dynamically generated so in many cases they are single use plans.....we are currently fighting with development on that...

Awesome, thanks guys.

I am going to set to MAX of 0 and Cost Threshold of 18, the query to see the plan caches actually showed a TON of them around...

...forgive the dumb newbie...I was confusing the 2 settings.

Sorry, I have not touched the Cost threshold setting yet, only the max setting. The cost is at 5 and the Max...

Thanks everyone, I will take a look at that blog, just for some more detail on our system.

It is a mix system, the clients do a TON of transaction...

Thanks everyone for the help on the holiday weekend.

I was able to run the query provided to see the cumulative WAIT stats since last restart. Which has been the...

Thanks Guys,

I will try to setup the some traces on Tuesday, during business hours (only time we really see bad perf).

As far as the memory settings, we were...

Also wanted to add that we are seeing the CPUs (average of all 24) spike to around 50 - 70% sporatically throughout the work day. The spikes generally last 5...

The disk subsystem is presented storage from a Dell EQ array. Both, the previous and current setups were on this, and seek times are under 10ms, so we have more...

Unfortunately, backups (both onsite and offsite) have the corruption.

Yes, we have a process of moving older backups offsite, but because of the large number of databases that we manage (greater...

Thanks Gail, that is what I feared. I have already started writing some BCP scripts to pull the data out into a new DB shell. We have weekly CHECKDB jobs...

No Problem:

DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8921, Level 16, State 1, Line 1

Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a...

Thanks Gail, but we are out of luck with the backup. We run hourly TRAN logs, nightly DIFFs and weekly FULLs, but by the time this issue bubbled up, our...