노갱의 공부 기록

-- LOCK  죽이기

SELECT * FROM

(

SELECT 

request_session_id, 

'kill ' + convert(varchar(10), request_session_id) AS [KILL],

COUNT(*) AS num_locks 

FROM sys.dm_tran_locks WITH(NOLOCK) 

GROUP BY request_session_id 

) AS T

WHERE num_locks > 1 

ORDER BY num_locks desc

-- CPU 점유율이 높은 쿼리 10개

SELECT TOP 10 

 [Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_worker_time

,[Last CPU used] = last_worker_time

,[MAX CPU used] = max_worker_time

,[Execution count] = qs.execution_count

,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 

         (CASE WHEN qs.statement_end_offset = -1 

            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

,[Parent Query] = qt.text

,DatabaseName = DB_NAME(qt.dbid)

,qs.creation_time

,qs.last_execution_time

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

ORDER BY [Average CPU used] DESC; 

-- 테이블이 참조 되는 모든 것 찾기 

Select OBJECT_NAME(sysC.id) As OBjectName,

Max(sysO.xtype + ' - '+

Case sysO.xtype

When 'C' Then 'CHECK 제약 조건'

When 'D' Then '기본값 또는 DEFAULT 제약 조건'

When 'F' Then 'FOREIGN KEY 제약 조건'

When 'L' Then '로그'

When 'FN' Then '스칼라 함수'

When 'IF' Then '인라인 테이블 함수'

When 'P' Then '저장 프로시저'

When 'PK' Then 'PRIMARY KEY 제약 조건(유형은 K)'

When 'RF' Then '복제 필터 저장 프로시저'

When 'S' Then '시스템 테이블'

When 'TF' Then '테이블 함수'

When 'TR' Then '트리거'

When 'U' Then '사용자 테이블'

When 'UQ' Then 'UNIQUE 제약 조건(유형은 K)'

When 'V' Then '뷰'

When 'X' Then '확장 저장 프로시저' End) As type

From

sys.syscomments sysC

Inner Join sys.sysobjects sysO On sysC.id = sysO.id

Where

sysC.text LIke '%table%'

And sysO.xtype = 'P'

Group By sysC.id

Order By OBjectName Asc

출처: http://makeus.tistory.com/119 [makeus]

CROSS APPLY 와 JOIN  차이점 : CROSS APPLY (table-valued expression 사용가능)

-- 1. CROSS APPLY = INNER JOIN 

SELECT * FROM MEMBER AS A

CROSS APPLY 

(

SELECT * FROM EVENT_History WHERE Event='2016-1회'

AND USERID = A.USERID

) AS B

SELECT * FROM MEMBER AS A

INNER JOIN EVENT_History AS B

ON A.UserID = B.UserID 

AND B.Event='2016-1회'

>> 동일 결과 

-- 2. OUTER APPLY = LEFT OUTER JOIN

SELECT * FROM MEMBER AS A

OUTER APPLY 

(

SELECT * FROM TBL_WWW_EVENT_History WHERE EventTYPE='2016-1회'

AND USERID = A.USERID

) AS B

SELECT * FROM MEMBER AS A

LEFT OUTER JOIN EVENT_History AS B

ON A.UserID = B.UserID 

AND B.Event='2016-1회'

>> 동일 결과 

-- 차이점 

-- 3.1 apply 는 function 사용 가능

SELECT * FROM MEMBER A

CROSS APPLY -- OUTER APPLY

[dbo].[Ranking_iTVF](A.USERID,'2016-06-01','2016-06-30') B

SELECT * FROM MEMBER A

LEFT OUTER JOIN

[dbo].[Ranking_iTVF](A.USERID,'2016-06-01','2016-06-30') B

ON A.UserID =B.USERID

1. FOR XML PATH()

-- 쿼리 예

SELECT '#'+CONVERT(VARCHAR,GIFT)+': '+CONVERT(VARCHAR,COUNT(GIFT))+'명 '

FROM TBL_20160303_GIFT_TAB A WHERE USERID IS NOT NULL GROUP BY GIFT ORDER BY GIFT FOR XML PATH('')

 > 결과 

   #1: 362명 #2: 353명 #3: 184명 #4: 106명 #5: 45명 #6: 44명 #7: 45명 #8: 18명 #9: 17명 #10: 8명 #11: 5명 #12: 1명 

2. STUFF() – FOR XML PATH()

-- 쿼리 예

SELECT 

STUFF (' ,'+CONVERT(VARCHAR,GIFT)+': '+CONVERT(VARCHAR,COUNT(GIFT))+'명' ,1,1,'')

FROM TBL_20160303_GIFT_TAB  A WHERE USERID IS NOT NULL GROUP BY GIFT ORDER BY GIFT FOR XML PATH('')

2-1 STUFF 함수 

  다음 예에서는 첫 문자열 abcdef에서 2 위치의 b부터 세 문자를 삭제하고 삭제 지점에 두 번째 문자열을 삽입하여 만든 문자열을 반환     합니다.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

결과 >  aijklmnef   

SELECT STUFF (UserID,3,LEN(UserID),'**') , STUFF(UserName,2,LEN(UserName),'**')

FROM TBL_MEMBER