That setting is set to false currently. I haven't tried that, so will enable it and see if it makes a difference thanks.

Ok will do, thanks.

Thanks for that.

Unfortunately I didn't collect the wait statistics. I will check next time and also see how full the cache gets before it causes slow performance in order to...

Hi spaghettidba,

I ran your clerk memory usage script and it provided me with a figure in Kb's during one of my slow performance periods.

This equated to around 2GB.

So my question...

thanks that's great. I can run that while performance is good and bad and then compare figures.

Thanks for that.

For the Plan Cache in perfmon, what should the counters be to consider performance to be good and what should they not be over in order to consider...

Will check the execution plans for the queries I have identified as running slower than usual.

How would I check for ASYNC_NETWORK_IO? would this be from Perfmon?

Yes I think it might be that.

I'll check it out thanks.

Already checked application server, logs, the server itself and cannot spot anything.

Will check network connections.

Thanks.

The interaction between application and database becomes slow all of a sudden.

I have run a trace but no slow queries found (all running under 5 seconds).

Checked CPU, memory, disks on...

Yes statistics are updated via a maintenance plan every night (full scan).

When the slowdown occurred previously, running update stats manually would restore performance.

Now it doesn't. Only clearing the cache restores...

Thanks to all of you for your help.

It was for SQL Server 2008 R2.

So to use extended events would I just create a new session in SSMS 2012? I used ShowplanXML for my trace.

I see. Thanks for that.

Ok thanks for that.