Hello.
sp_sysmon report a lot of ULC "flushs" by system logs records
The I/O writes are low on all "data and logs" databases
Data and log are splitted on the "main used" 4 tempdbs (on ramdisk)
and all applicative's databases
Any idea about the cause of theses SLR ULC flushs?
Code:
tempdb
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full
device_fragments size usage
------------------------------ -------------
master_dxl_r_ASE1#2_01 3.0 MB data only
tempdb_dx_r_RAM_01 768.0 MB data only
tempdb_l_r_RAM_01 1280.0 MB log only
device segment
------------------------------ ------------
master -- unused
tempdb_dx_r_RAM_01 default
tempdb_dx_r_RAM_01 system
tempdb_l_r_RAM_01 logsegment
tempdb2
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full, user created temp db
device_fragments size usage
------------------------------ -------------
tempdb2_dx_r_RAM_01 768.0 MB data only
tempdb2_l_r_RAM_01 1280.0 MB log only
device segment
------------------------------ -----------
tempdb2_dx_r_RAM_01 default
tempdb2_dx_r_RAM_01 system
tempdb2_l_r_RAM_01 logsegment
tempdb3
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full, user created temp db
device_fragments size usage
------------------------------ -------------
tempdb3_dx_r_RAM_01 768.0 MB data only
tempdb3_l_r_RAM_01 1280.0 MB log only
device segment
------------------------------ ----------
tempdb3_dx_r_RAM_01 default
tempdb3_dx_r_RAM_01 system
tempdb3_l_r_RAM_01 logsegment
tempdb4
select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full, user created temp db
device_fragments size usage
------------------------------ -------------
tempdb4_dx_r_RAM_01 768.0 MB data only
tempdb4_l_r_RAM_01 1280.0 MB log only
device segment
------------------------------ --------
tempdb4_dx_r_RAM_01 default
tempdb4_dx_r_RAM_01 system
tempdb4_l_r_RAM_01 logsegment
Code:
sampling sampling ________Percent_ULC_Flushes_to_Xact_Log_______
started ended Full end change Si.log pages
at at ULC Transa databa record unpin others
-------- -------- ------ ------ ------ ------ ------ ------
09:00:43 09:15:43 0.2% 34.6% 0.6% 50.2% 8.7% 5.7%
09:16:02 09:17:03 0.2% 42.4% 0.7% 47.1% 4.0% 5.6%
09:17:14 09:18:14 0.4% 39.1% 0.7% 47.7% 5.4% 6.7%
09:18:26 09:19:26 0.4% 38.6% 0.7% 48.1% 6.2% 6.0%
Code:
sampling sampling ________per_sec_ULC_Flushes_to_Xact_Log_______ ULC log Max ULC LOG _per_seconde__ AVG
started ended Full end change Si.log pages Records ULC semap semap transactionLOG writes
at at ULC Transa databa record unpin others by tran size wait wait writes allocs log pg
-------- -------- ------ ------ ------ ------ ------ ------ ------- ----- ----- ----- ------ ------ --------
09:00:43 09:15:43 2.1 326.8 5.6 474.5 82.5 54.1 41.6 16384 0.4 0.6 211.4 784.2 0.26962
09:16:02 09:17:03 1.8 354.3 6.0 394.0 33.2 46.6 20.7 16384 0.5 0.2 169.2 375.1 0.45111
09:17:14 09:18:14 3.2 347.9 6.2 424.5 47.8 59.6 26.3 16384 0.5 0.5 170.5 467.8 0.36452
09:18:26 09:19:26 4.3 445.6 8.3 554.8 71.3 69.5 27.3 16384 0.4 0.5 226.5 620.8 0.36488
Code:
DEVICE DETAILS for tempdb_dx_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.2 47.0 3.7%
09:16:02 09:17:03 0.0 0.4 69.2 9.6%
09:17:14 09:18:14 0.0 0.0 29.7 5.5%
09:18:26 09:19:26 0.0 0.1 47.1 3.8%
DEVICE DETAILS for tempdb2_dx_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 13.3 3.7 71.5 6.9%
09:16:02 09:17:03 0.0 0.0 14.5 2.0%
09:17:14 09:18:14 0.0 0.1 22.8 4.3%
09:18:26 09:19:26 0.0 0.1 32.0 2.6%
DEVICE DETAILS for tempdb3_dx_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.1 13.5 1.1%
09:16:02 09:17:03 0.0 0.1 14.1 2.0%
09:17:14 09:18:14 0.0 0.0 16.4 3.1%
09:18:26 09:19:26 0.0 0.1 20.3 1.6%
DEVICE DETAILS for tempdb4_dx_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 24.3 5.2 112.0 11.1%
09:16:02 09:17:03 0.0 0.1 37.0 5.1%
09:17:14 09:18:14 0.0 0.1 34.0 6.3%
09:18:26 09:19:26 0.0 0.2 43.2 3.5%
DEVICE DETAILS for tempdb_l_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.6 44.7 3.6%
09:16:02 09:17:03 0.0 0.5 36.1 5.0%
09:17:14 09:18:14 0.0 0.7 50.1 9.4%
09:18:26 09:19:26 0.0 0.9 71.8 5.9%
DEVICE DETAILS for tempdb2_l_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.9 1.7 47.4 3.9%
09:16:02 09:17:03 0.0 0.2 18.6 2.6%
09:17:14 09:18:14 0.0 0.4 30.0 5.7%
09:18:26 09:19:26 0.0 0.5 38.1 3.1%
DEVICE DETAILS for tempdb3_l_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.2 19.5 1.5%
09:16:02 09:17:03 0.0 0.1 12.7 1.8%
09:17:14 09:18:14 0.0 0.3 22.9 4.3%
09:18:26 09:19:26 0.0 0.4 30.8 2.5%
DEVICE DETAILS for tempdb4_l_r_RAM_01
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 1.3 2.7 75.0 6.2%
09:16:02 09:17:03 0.0 0.6 47.6 6.6%
09:17:14 09:18:14 0.0 0.5 41.6 7.8%
09:18:26 09:19:26 0.0 0.6 55.3 4.5%
DEVICE DETAILS for tempdbbatch_dxl_r_tempdb1
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.0 0.5 0.0%
09:16:02 09:17:03 0.0 0.0 0.1 0.0%
09:17:14 09:18:14 0.0 0.0 0.5 0.1%
09:18:26 09:19:26 0.0 0.0 1.6 0.1%
DEVICE DETAILS for tempDBAIBM_dxl_r_tempdb1
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 -------- -------- -------- 0.0%
09:16:02 09:17:03 -------- -------- -------- 0.0%
09:17:14 09:18:14 -------- -------- -------- 0.0%
09:18:26 09:19:26 -------- -------- -------- 0.0%
DEVICE DETAILS for master
sampling sampling _________Per_Second_________ %%%%%%
started ended APF nonAPF Utili-
at at Reads Reads Writes zation
-------- -------- -------- -------- -------- ------
09:00:43 09:15:43 0.0 0.0 1.3 0.1%
09:16:02 09:17:03 0.0 0.0 1.3 0.2%
09:17:14 09:18:14 0.0 0.0 1.4 0.3%
09:18:26 09:19:26 0.0 0.1 1.5 0.1%
09:19:36 09:20:36 0.0 0.0 1.5 0.1%
dBforums
>
ASE 12.5.4 and ULC system logs records flushs
04-08-11, 17:09
Linear Mode
