MSSQL

MSSQL에서는  WITH ROLLUP을 사용한다.

SELECT country, product, sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

ROLLUP 적용 전 (왼쪽) VS ROLLUP을 적용한 모습(오른쪽)

countryproductsum(profit) countryproductsum(profit)

ROLLUP은 집계한 기준값을 NULL값으로 대체한다. COALESCE을 활용하면 원하는 텍스트를 넣을 수 있다.

( MySQL에서는 IFNULL로 대체 가능)

SELECT COALESCE(country,"ALL countries") as country, 
       COALESCE(product,"ALL products") as product, 
       sum(profit) FROM sales GROUP BY country, product WITH ROLLUP;

countryproductsum(profit)

SQL로 중복 데이터를 확인 및 삭제하는 법을 알아본다.

사용 데이터

idfirst_namelast_nameemail

출처 : MYSQLTUTORIAL (아래 Reference에 명시)

중복 데이터 확인

공통

GROUP BY와 HAVING을 사용하면 된다. 중복을 확인할 컬럼의 수에 따라 조건을 추가해주면 된다.

SELECT first_name, last_name, email, COUNT(*) as cnt
FROM contacts
GROUP BY email, first_name, last_name
HAVING COUNT(email) > 1 AND COUNT(first_name) > 1 AND COUNT(last_name) > 1;

first_namelast_nameemailcnt

중복 데이터 삭제

중복 데이터를 삭제하기 위해서는 먼저 남길 데이터를 제외한 데이터를 구분할 컬럼(e.g. id)의 값을 구해야 한다.

MySQL

삭제할 데이터 id 확인

SELF JOIN을 통해 중복된 row의 id를 확인하는 방법이지만 결과를 보면 데이터가 오히려 중복되어 더 생기는 현상이 있다.

(삭제 쿼리에 활용할 경우, 문제는 없어보임)

SELECT t1.*
FROM contacts t1 JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

데이터 삭제 쿼리

MySQL에서 지원하는 DELETE JOIN 방식을 활용한다.

contacts(t1) 테이블에서 FROM 절 이후 JOIN된 결과에 해당하는 ROW들을 삭제한다는 의미이다.

DELETE t1 FROM contacts t1 
JOIN contacts t2
ON t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email
WHERE t1.id > t2.id;

PostgreSQL & MySQL 8

WINDOW FUNCTION을 지원하는 경우, ROW_NUMBER를 활용하여 처리할 수 있다.

PARTITION BY로 중복된 컬럼을 지정하고, ROW_NUM > 1을 설정하여 삭제할 데이터만 가져올 수 있다.

삭제할 데이터 id 확인

SELECT *
FROM ( SELECT id, first_name, last_name, email,
       ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
       FROM contacts ) a
WHERE row_num > 1
ORDER BY first_name; -- 단순 정렬 용도

idfirst_namelast_nameemailrow_num

간단한 데이터 삭제 쿼리

데이터가 적을 경우, 간단하게 IN 으로 해당 id들을 제거할 수 있다.

DELETE FROM contacts
WHERE id IN (SELECT id
             FROM ( 
                 SELECT id, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, email) as row_num
                 FROM contacts 
                 ) tmp
             WHERE row_num > 1);

데이터 삭제 쿼리

PostgreSQL에서는 DELETE + USING 방식을 활용한다. 데이터가 많은 것을 고려할 경우, 이 방법을 먼저 사용해야 할 듯하다.

JOIN 과 ON 대신 USING과 WHERE이 사용된다.

DELETE FROM contacts t1 
USING contacts t2
WHERE t1.first_name=t2.first_name AND t1.last_name=t2.last_name AND t1.email=t2.email AND t1.id > t2.id;

속도가 느릴 경우

데이터가 너무 많을 경우, 일부분씩 나누어서 DELETE를 하는 것을 고려해볼 수 있다. (특히, 실 사용중인 DB인 경우)

위에서 설명한 삭제할 id를 미리 테이블로 만들어 DELETE JOIN에 사용하는 것이다. (JOIN 연산을 단순화한다.)

=====================================================================
1. 테이블을 생성하면서 테이블의 데이타 복사
select * into 생성될테이블명 from 원본테이블명
=====================================================================

2. 테이블 구조만 복사하겠다면
select * into 생성될테이블명 from 원본테이블명 where 1=2
=====================================================================

3. 테이블이 이미 생성되어 있는경우 데이타만 복사
insert into 카피될테이블명 select * from 원본테이블명
=====================================================================

4.특정 데이타만 복사 하겠다면
insert into 카피될테이블명 select * from 원본테이블명 where 검색조건
=====================================================================

5.다른 테이블조인해서 UPDATE 하기
UPDATE 엡데이트 테이블 명
SET 업테이트할 컬럼 = 업데이트 가져오는 컬럼
FROM 엡데이트 테이블 명 A, 다른테이블 명 B 
WHERE 조인조건
=====================================================================

6. 프로시저 검색 
SELECT * FROM INFORMATION_SCHEMA.ROUTINES

SELECT object_name(parent_id) [target], name
FROM sys.triggers
ORDER BY object_name(parent_id)

exec sp_readerrorlog

SELECT S.SPID, S.LOGINAME, S.LOGIN_TIME, S.LAST_BATCH, C.CLIENT_NET_ADDRESS 
FROM sys.sysprocesses S, sys.dm_exec_connections C 
WHERE S.spid = C.SESSION_ID 

Select top 20

 [사용 된 평균 CPU] = total_worker_time / qs.execution_count

, [사용 된 총 CPU] = total_worker_time

, [실행 횟수] = qs.execution_count

, [개별 쿼리] = 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)

, [상위 쿼리] = qt.text

, DatabaseName = DB_NAME (qt.dbid)

FROM sys.dm_exec_query_stats qs

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

ORDER BY [사용 된 총 CPU] DESC, [사용 된 평균 CPU] DESC;

[MSSQL] CPU 사용율과 메모리 사용율 관련

Database 서버가 언젠가부터 CPU 혹은 메모리 과부화가 발생하기 시작했다..

원인 발생 여부를 파악하기 위해 여러 검색을 통해 알아봤으나

분석 후 알아낸점은... 동시다발적으로 여러 트랜잭션 및 클라이언트의 쿼리 처리 등등으로 인한

복합적인 문제로 낙인...ㅠㅠ.... 결국 프로그램에서 처리해주지 않으면 안될것 같아, 개발이슈로 띄우고

개발하기로 했으나.. 알아보던 과정이 너무 여기저기 널린 얘기들과 정확히 이렇다 하는 내용이 없었다..

정리해두기로..

메모리 관련된 내용은 추후 업데이트 하기로하고 당착한 CPU 사용율에 좀더 신경을 써보자.

1. 떠돌아 다니는 쿼리문 + 해결방법

1) Sp_Who2 를 통한 문제 프로세스 검색 및 Kill

- Management 실행 => sp_Who2

- CPU 높은 놈을 찾자. Sp_Who2 내장 함수(?)의 경우 SQL 서비스단은 종료시키지 못한다.

(Status 가 Background 라던지.. Background 라던지...)

- 조건은 Status 가 RUNNABLE 상태, CPUTime 의 값이 유독 높은놈이 있다. 물론 해당 SPID를 가진 친구가 어떤 작업을 하는지

확인정도는 당연히 해야겠다. => DBCC INPUTBUFFER([SPID])

- 확인되었고 그놈을 죽여도 된다면.. 죽여버리자(?)! => KILL [SPID]

- 정리 : sp_Who2 => DBCC INPUTBUFFER([SPID]) => KILL [SPID]

2) 부하가 큰 쿼리문을 검색해서 프로그램 내 혹은 프로시저 등등에서 직접 수정하자!

- 간단보기와 상세보기로 나뉜다. (아래가 상세보기)

SELECT TOP 20

[Average CPU used] = total_worker_time / qs.execution_count

,[Total CPU used] = total_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)

FROM sys.dm_exec_query_stats qs

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

ORDER BY [Total CPU used] DESC, [Average CPU used] DESC;

SELECT TOP 20

[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;

3) 직접 모니터링해서 해당 프로세서를 죽이자!!!!! (!?)

- 성능모니터 실행 => 성능-모니터링 도구-성능 모니터 => 카운터 추가

- 사용 가능한 카운터에서 Thread 하위의 %Processor Time / ID Thread / Thread State / Thread Wait Reason 선택

- 선택한 개체의 인스턴스에서 sqlserv 명칭 죄다 선택..

- 모니터링...