為何 mdf/ndf 沒有什麼 IO,但 ldf 卻有大量的IO

朋友的問題,這很難猜,需要透過 SQL Trace/Profiler 分析

舉兩個可能性如下:

1.交易大多 Rollback

2.雖然沒有交易,但延長 Recovery Interval,但所做的大部分行為在短時間內互相抵消,所以不須在 Checkpoint 時同步到 mdf

image

create database testIO

go

use testIO

go

–大量 Rollback

select ‘大量 Rollback’

select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

into #tmp

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id

where right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) like ‘testIO%’

go

begin tran

create table t(c1 char(7000))

go

insert t values(‘a’)

go

insert t select * from t

go 10

delete t

drop table t

rollback

go

select #tmp.[資料庫檔案],t.[等候I/O 總時間(毫秒)]-#tmp.[等候I/O 總時間(毫秒)] [等候I/O 總時間(毫秒)],

t.[讀取次數]-#tmp.[讀取次數] [讀取次數],t.[讀取byte]-#tmp.[讀取byte] [讀取byte],

t.[等待讀取(ms)] -#tmp.[等待讀取(ms)] [等待讀取(ms)],t.[寫入次數]-#tmp.[寫入次數] [寫入次數],

t.[寫入byte]-#tmp.[寫入byte] [寫入byte],t.[等待寫入(ms)] -#tmp.[等待寫入(ms)] [等待寫入(ms)]

from (select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id) t

join #tmp

on t.[資料庫檔案]=#tmp.[資料庫檔案]

go

drop table #tmp

go

–預設 Recovery Interval

select ‘預設 Recovery Interval’

select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

into #tmp

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id

where right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) like ‘testIO%’

create table t(c1 char(7000))

go

insert t values(‘a’)

go

insert t select * from t

go 10

delete t

go

drop table t

select #tmp.[資料庫檔案],t.[等候I/O 總時間(毫秒)]-#tmp.[等候I/O 總時間(毫秒)] [等候I/O 總時間(毫秒)],

t.[讀取次數]-#tmp.[讀取次數] [讀取次數],t.[讀取byte]-#tmp.[讀取byte] [讀取byte],

t.[等待讀取(ms)] -#tmp.[等待讀取(ms)] [等待讀取(ms)],t.[寫入次數]-#tmp.[寫入次數] [寫入次數],

t.[寫入byte]-#tmp.[寫入byte] [寫入byte],t.[等待寫入(ms)] -#tmp.[等待寫入(ms)] [等待寫入(ms)]

from (select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id) t

join #tmp

on t.[資料庫檔案]=#tmp.[資料庫檔案]

go

drop table #tmp

–延長 Recovery Interval

select ‘延長 Recovery Interval’

select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

into #tmp

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id

where right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) like ‘testIO%’

create table t(c1 char(7000))

go

exec sp_configure ‘recovery interval (min)’,15

reconfigure

insert t values(‘a’)

go

insert t select * from t

go 10

delete t

go

drop table t

select #tmp.[資料庫檔案],t.[等候I/O 總時間(毫秒)]-#tmp.[等候I/O 總時間(毫秒)] [等候I/O 總時間(毫秒)],

t.[讀取次數]-#tmp.[讀取次數] [讀取次數],t.[讀取byte]-#tmp.[讀取byte] [讀取byte],

t.[等待讀取(ms)] -#tmp.[等待讀取(ms)] [等待讀取(ms)],t.[寫入次數]-#tmp.[寫入次數] [寫入次數],

t.[寫入byte]-#tmp.[寫入byte] [寫入byte],t.[等待寫入(ms)] -#tmp.[等待寫入(ms)] [等待寫入(ms)]

from (select right(mf.physical_name,charindex(‘\’,reverse(mf.physical_name),1)-1) [資料庫檔案],

divfs.io_stall [等候I/O 總時間(毫秒)],

divfs.num_of_reads [讀取次數],divfs.num_of_bytes_read [讀取byte],divfs.io_stall_read_ms [等待讀取(ms)],

divfs.num_of_writes [寫入次數],divfs.num_of_bytes_written [寫入byte],divfs.io_stall_write_ms [等待寫入(ms)]

from sys.dm_io_virtual_file_stats(NULL,NULL) divfs

join sys.master_files mf on mf.database_id=divfs.database_id and mf.file_id=divfs.file_id) t

join #tmp

on t.[資料庫檔案]=#tmp.[資料庫檔案]

go

drop table #tmp

go

use master

go

drop database testIO

go

exec sp_configure ‘recovery interval (min)’,0

reconfigure

4 Comments

  1. Tony
    Posted 2012 年 11 月 21 日 at 03:04:08 | Permalink | 回覆

    請問Recovery Interval的設定時機,假如是一個交易頻繁的系統,是否需要把Recovery Interval調大,如果調大之後,會影響到當系統出問題時去做Restore的資料嗎(平時都會10分鐘做一次Transaction log的備份)

    • Posted 2012 年 11 月 21 日 at 03:31:22 | Permalink | 回覆

      你可以嘗試調大,資料依然正確,但會造成系統重啟時間較久,因為要做較多的 redo/undo

      • Tony
        Posted 2012 年 11 月 22 日 at 06:32:03 | Permalink

        感謝您的回答,因目前系統會以C#使用TVP的方式寫資料進DB,我調了Recovery Interval為5分鐘,但會發生Timeout的情形,是不是使用TVP有限制使用個數呢或是有其他的限制

  2. Posted 2012 年 11 月 22 日 at 06:51:25 | Permalink | 回覆

    這應該要看你 SQL Server 為何執行慢,這或許是 Command Timeout,應該與 Recovery Interval 無關…

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: