블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
  • total
  • today
  • yesterday
2019. 9. 3. 10:49 SQL SERVER

오라클 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

posted by bedbmsguru
2019. 8. 30. 09:04 SQL SERVER

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/

posted by bedbmsguru