Link to home
Start Free TrialLog in
Avatar of VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY

asked on

I'm Confused on I/O and logs issue-Real experts answer required

Hello Experts,

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.
http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx
Referncing above articles my questin are:
[/i]

Open in new window


1.

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 ;
[/i]

Open in new window

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

Open in new window

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?
ASKER CERTIFIED SOLUTION
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial