i've been doing some test and monitoring with profiler, because we have some random timeout occuring.
To replicate the problem i execute something similar to this to simluate a load on the server:
exec random_stored_procedure (sp with a delete, update, etc... nothing too fancy)
Everything run smooth with ~12 instance running non-stop of the previous code. The table affected by the stored procedure have about 12 rows and they have good indexes and PK to support millions of rows anyway.
The problem i had is that sometime, we have random high cpu duration(millisecond) appearing in the profiler for either the "commit transaction", the "begin transaction" or even the stored procedure. We may have 60 of them for a 10 min duration, and cpu ranging from 300ms to 3000ms.
Except these random occurence, everything is running smoothly, with cpu/duration under 16ms and read/write under 10. Also data/log file auto-grow is disabled since it was causing a slow down sometime, now the file are set with a very large initial value.
anyone might have a slight idea why this random cpu peak happen? We have an application that need more than 800 tps and it's causing lag and timeout issues