bedbmsguru

http://m.blog.daum.net/partsofmemory/110

오라클 LinkedServer 를 생성하다가 rownum을 지정해서 DML SQL을 실행 할 경우 100건 이하는 잘 되는데 



100건이 넘어가면 에러가 발생 


검색을 하다보니 Linkedserver를 생성할때 fetchSize 지정이 가능하다(기본값이 100)


레지스트리에서 기본값 변경도 가능 (HLM--> SOFTWARE -->ORACLE --> KEY_OraClient12Home1-->OLEDB--> FetchSize 값 수정)




-- Linked Server 생성시 fetchsize 지정하는법
EXEC sp_addlinkedserver 
@server='서버이름' 
,@srvproduct='' 
,@provider='OraOLEDB.Oracle' 
,@datasrc='TNS이름'
,@location=null 
,@provstr=N'FetchSize=50000'
,@catalog=null

SELECT a.session_id,
a.wait_type,
a.wait_duration_ms,
a.blocking_session_id,
a.resource_description,
CASE
WHEN CAST(RIGHT(a.resource_description, LEN(a.resource_description) - CHARINDEX(':', a.resource_description, 3)) AS INT)
- 1 % 8088 = 0 THEN 'Is PFS Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 2 % 511232 = 0 THEN 'Is GAM Page'
WHEN CAST(RIGHT(a.resource_description,
LEN(a.resource_description)
- CHARINDEX(':', a.resource_description, 3)) AS INT)
- 3 % 511232 = 0 THEN 'Is SGAM Page'
ELSE 'Is Not PFS, GAM, or SGAM page'
END resourcetype,
c.text AS SQLText
FROM sys.dm_os_waiting_tasks a
INNER JOIN sys.sysprocesses b
ON
a.session_id = b.spid
OUTER APPLY sys.dm_exec_sql_text(b.sql_handle) c
WHERE a.wait_type LIKE 'PAGE%LATCH_%'
AND a.resource_description LIKE '2:%';

https://sqlmonitormetrics.red-gate.com/tempdb-allocation-contention/

운영중인 서버에서 신규서버로 DB Migration 진행하기

http://yrushka.com/index.php/sql-server/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/

SELECT IndexName = i.Name, ColName = c.Name FROM sys.indexes i INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.is_included_column = 1 ORDER BY i.Name

https://stackoverflow.com/questions/18172359/list-all-indexes-with-included-columnsnonkeys

아주 괜챃은 방법이다.

Extended Event Data 조회

http://www.sqldoubleg.com/2016/05/31/extended-events-file-target-reader/


CREATE EVENT SESSION [ALL_SQL_SAVE] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([object_name]<>N'sp_reset_connection')),
ADD EVENT sqlserver.sql_batch_completed(
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'F:\temp\ALL_QUERY.xel',max_file_size=(500),max_rollover_files=(30))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO



IF OBJECT_ID('tempdb..#ExEvent') IS NOT NULL DROP TABLE #ExEvent
SELECT IDENTITY(INT,1,1) AS RowId, object_name AS event_name, CONVERT(XML,event_data) AS event_data
INTO #ExEvent
from sys.fn_xe_file_target_read_file ( 'f:\Temp\AAA.xel',null, null, null ) ;




(39266개 행이 영향을 받음)
SELECT ISNULL(t_action.RowId, t_data.RowId) AS RowId
, ISNULL(t_action.event_name, t_data.event_name) AS event_name
, t_action.[client_hostname], t_action.[collect_system_time], t_action.[client_app_name], t_action.[session_id]
, t_data.[cpu_time], t_data.[duration], t_data.[logical_reads], t_data.[object_name], t_data.[physical_reads], t_data.[result], t_data.[row_count], t_data.[statement], t_data.[writes]
FROM (
SELECT RowId, event_name, [client_hostname], [collect_system_time], [client_app_name], [session_id]
FROM (
SELECT RowId
, event_name
, T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')AS att_name
, T2.Loc.query('.').value('(/action/value)[1]', 'varchar(max)')AS att_value
FROM #ExEvent
CROSS APPLY event_data.nodes('/event/action') as T2(Loc)
WHERE T2.Loc.query('.').value('(/action/@name)[1]', 'varchar(max)')
IN ('client_hostname', 'collect_system_time', 'client_app_name', 'session_id')
) AS SourceTable
PIVOT(
MAX(att_value)
FOR att_name IN ([client_hostname], [collect_system_time], [client_app_name], [session_id])
) AS PivotTable
) AS t_action

-- Full outer because it might be no events selected only the payload
FULL OUTER JOIN (
SELECT RowId, event_name, [cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes]
FROM (
SELECT RowId
, event_name
, T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)') AS att_name
, T3.Loc.query('.').value('(/data/value)[1]', 'varchar(max)') AS att_value

FROM #ExEvent
CROSS APPLY event_data.nodes('/event/data') as T3(Loc)
WHERE T3.Loc.query('.').value('(/data/@name)[1]', 'varchar(max)')
IN ( 'cpu_time', 'duration', 'logical_reads', 'object_name', 'physical_reads', 'result', 'row_count', 'statement', 'writes')
) AS SourceTable
PIVOT (
MAX(att_value)
FOR att_name IN ([cpu_time], [duration], [logical_reads], [object_name], [physical_reads], [result], [row_count], [statement], [writes])
) AS PivotTable

) AS t_data
ON t_data.RowId = t_action.RowId