mysterious slowdown every 4 minutes

Question

mysterious slowdown every 4 minutes

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

andrea.dimaio (4/4/2016)
Now i've checked perfmon counter log bytes flushed/sec and i've seen that when the mistrious event happens,
this counter GO DOWN. So it means that :
- we have an external process that generate I/O every 4 minutes (it could be a windows process??)
- when this process is active, sql server can't write flush log on disk so generate WRITELOG wait and slow performance
- when process ends everything comeback normal
Do u think is a reasonable explanation?

Absolutely.

Forget about SQL Server for a minute and look around the server - what overloads it every 4 minutes.

Do not limit your search to I/O loads only, it may be network, CPU, anything else.

_____________
Code for TallyGenerator

andrea.dimaio Mr or Mrs. 500 Points: 563 More actions · Answer 2

It's relly a mistery...

I'd like to forget sql server but it's difficult because we have 8 disk on this server and the problem appears only in 2 disk (the log disk) where are hosted logs relative to high transactional databases.

Other disks (data disks) haven't this problem.

No cpu or memory pressure in those 3-4 seconds and in general memory and cpu are ok.

I've seen the disk peack with a tool, that told me the peack is generated by sqlsrv.exe 🙁

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

I've a suggestion - set your checkpoint to a manual interval which isn't 4 mins - just to make absolutely sure.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

andrea.dimaio Mr or Mrs. 500 Points: 563 More actions · Answer 4

I will try...

Now i've to test if problem is caused by mirroring but need to waits some procedures that have to be executed...

andrea.dimaio Mr or Mrs. 500 Points: 563 More actions · Answer 5

Now i've monitored the system with PAL and i've alerts on disks with an hint:

"Experimental: HBA Queue Depth might be restricted to 32 - Consider increasing the HBA queue depth if applicable"

and also some alerts relative to memory pool paged and non-paged bytes

"More than 80% of Pool Non-Paged Kernel Memory Used"