First of i apologies to all my doubts in one question. I'm putting all in one b'cos it all relate to performances .As you all know , performance needs to monitor from all kind of metrics.That's reason I'm clubbing my question.
Kindly bare with me.
[i]The following commands can be used to determine the number of non-replicated vs. replicated transactions. A transaction log with a high percentage of non-replicated transaction will cause latency as the Log Reader scans over transaction to be ignored. These can be database maintenance transactions such as an online reinidex or data modifications to table not being replicated. A 25gig transaction log may take up to 15 minutes to scan depending on the IO subsystem.
Referncing above articles my questin are:
1. fn_dblog ->When i run it's returns me approx 2500 record.
a.What does it mean, is log under stress?
b.is there any standard baseline value?
C:How intrepret and read those values?
d:Purpose of this function
[i]SELECT DB_NAME(database_id) AS [Database Name] , file_id , io_stall_read_ms , num_of_reads , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS
[avg_read_stall_ms] , io_stall_write_ms , num_of_writes ,
CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [avg_write_stall_ms] , io_stall_read_ms + io_stall_write_ms AS [io_stalls] , num_of_reads +
num_of_writes AS [total_io] , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
+ num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]FROM sys.dm_io_virtual_file_stats(NULL, NULL)ORDER BY avg_io_stall_ms DESC ;
IN TEST ENV- When i run above query avg.write for some file shows 1500 ms and read 25 ms
and for some file it shows avg read as 300ms and write 25 ms.
In production -avg.write for some file shows 300 ms and read 25 ms
and for some file it shows avg read as 30ms and write 250 ms,
My doubt is,as per microsoft suggest avg I/O shouldn't be morethan 20 ms.But in the production all components are working fine.But in the test env there is performance lag.
Am i investigating in the right way?What value for avg io stall is acceptable for each corresponding file?
[i]My top waits
[/i]wait_type wait_time_s Pert%
WRITELOG 163710.2 29.63
PAGELATCH_EX 78702.41 14.25
SOS_SCHEDULER_YIELD 56319.84 10.19
CXPACKET 55898.42 10.12
BACKUPBUFFER 38336.53 6.94
ASYNC_NETWORK_IO 31898.53 5.77
PAGEIOLATCH_SH 18993.78 3.44
PAGEIOLATCH_EX 14538.09 2.63
OLEDB 13280.5 2.4
LCK_M_IX 12883.34 2.33
LATCH_EX 8873.14 1.61
LCK_M_X 8385.48 1.52
IO_COMPLETION 8056.89 1.46
LCK_M_U 7628.39 1.38
PAGEIOLATCH_SH 7099.39 1.29
ASYNC_IO_COMPLETION 6899.53 1.25
30% waits are on writelog.What should I do to bring better?or reduce this wait.
My recovery internal time is 0.Should need to increase it?