Also, I'd recommend that the ensure that the stats are updated asynchronously, otherwise the queries running against them have to wait until the updates are completed before they can access...

It's possible that the issue is statistics related, rather than index.

Particularly if some of the tables are large and busy, hte statistics for that day may be returning an estimated...

Your trousers would be terribly uncomfortable and weigh about a...

Paul White (2/22/2010)

Can I ask why you are wondering, and why it is so...

You can't have an mvarchar(max) as a key column (a searchable column) in an index. Only as an included column

For index maintenance, tere's a brilliant and very well regarded script here

http://sqlfool.com/2009/06/index-defrag-script-v30/

Works extremely well 'out of the box' but can be configured to meet any particular requirements you have.

Hi,

you can use the following to check PLE. A value of around 300 is recommended by MS as the minimum for an OLTP system

select

object_name, counter_name,...

One issue with replication is, if you're using transactional replication and the downtime is caused by someone running

DELETE

FROM ReallyCriticalTable

-- Oops, forgot the where clause

this is going to take down...

Sounds like hard trimming.

This covers this issue and provides a link to further information

http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

It can also - from what I've seen - happen if there's a problem with your hardware,...

GilaMonster (1/6/2010)

Same here. Worst I've...

:w00t:

Um - external to your campany, or external to accepted reality?

Good question. I'll have to have a dig there.

It was from a MS article on lock escalation to the effect that threshold for Lock Escalation was c....

Any idea how many rows are normally deleted by your delete process?

If it's more than about 5000, then you're likely to be getting table locks due to lock escalation, in...

Michelle Ufford, aka SQLFool, has a very well regarded script to do what the Guru recommends;

http://sqlfool.com/2009/06/index-defrag-script-v30/

As well as Grant's recommendations, personally I'd also set the Auto Update Statistics Asynchronously to True if you're using Auto Update Statistics