보미네

예전에 REMARK 필요라고 마크해둔 것을 하루에 한 개씩 한번 공부해 보고 내 의견을 추가해 보겠다.

누군가 지식이 많을것 같은 사람이 설명을 하면, 모두 다 믿게 된다. 실험에서처럼....

그러나, 사람은 틀릴 수 있고 완벽하지 않다. 그래서 아키텍처의 이해와 데이터의 이해 기반으로 다시 해석해 보는것이 필요 할수도 있다. 아래 까만글은 아가씨의 필기이고, 파란 내용은 김민석이 추가한 내용이고, 틀릴수 있다. 누구든지 틀린 내용이 보이면 알려주세요~

누가 맞다 틀리다가 아니라 강의한 내용의 검증과 해석을 나름 해본 것이다. 이번 강의도 잘못 설명한 것이 좀 있지만, 나름 좋은 내용이다.

1. top(1) 인데 왜 Index Seek가 아니고 scan인가?

 [remark 필요]

2. MS kb942982
   원격 로그인의 권한이 부족하면, 성능 문제가 발생 할 수가 있다.
   통계 정보가 필요시 최적화 실패 가능... DBCC SHOW STATISTICS() 수행 권한 필요..
   SQL2012 sp1에서 해결 됨.  이전 버젼은 원격 로그인에 대한 충분한 권한이 있는지 확인해 봐야 한다.
   항상 호출되는 것은 아니다.
  
   http://msdn.microsoft.com/ko-kr/library/ms174384.aspx
   통계 개체를 보려면 테이블의 소유자이거나 sysadmin 고정 서버 역할, db_owner 고정 데이터베이스 역할 또는 db_ddladmin 고정 데이터베이스 역할의 멤버여야 합니다.
   SQL Server 2012 SP1에서는 권한 제한을 수정하여 사용자가 SELECT 권한을 통해 이 명령을 사용할 수 있게 되었습니다. 다음 요구 사항에서는 명령을 실행하기 위해
   SELECT 권한이 있어야 합니다.
  
   연결서버에 db_ddladmin sql2012 sp1 이전 사용자는 권한을 줘야 하는군.
  
3. UNION 쿼리 결과에 대해서 집계를 하는 작업
 개별적으로 집계를 구하고 결과를 UNION 후 집계하는게 더 좋다.
 
4. 임시 테이블 캐싱으로 인한 통계 예측 오류
 sql2005+
 임시 테이블 값이 실행 계획 최적화에 관련된 경우 통계 예측(재사용) 오류 발생
 해결 방법은 ?
 
 1. option recompile : 임시 테이블에 대한 통계에 대해서는 리컴파일을 하지 않는다.
 2. 임시 테이블에 대한 재컴파일이 일어날 수 있도록 한다.
   - 개체 ddl alter를 해서 재컴파일이 일어나도록 유도 한다. (option recompile을 같이 쓴다)
     사용하기 어렵겠다 ㅠㅠ... 이름 충돌...
   - ddl index를 생성해서 리컴파일 유도를 한다.
   프로시져 with recompile <-- 아예 캐슁이 안 된다... 그냥 adhoc 쿼리 이다..
 [remark 필요]

   
5.  병렬 처리되는 TOP 성능이 나쁘다?
 maxdop에 따른 다른 결과 . 일관되지 않은 재현... (폴 화이트)
 [remark 필요]

 
6. 병렬처리시 STATISTICS IO 결과를 신뢰할 수 없다면?
 논리적인 읽기 수가 부정확하게 발생.
 
 검색수 : 쓰레드 수
 
 Trace flag : querytraceon 8649 강제 병렬 처리...
 병렬처리 TOP의 경우 검색수 잘못 나온다.... I/O도 잘못 나온다...

 [remark 필요]

7.  SELECT 결과 변수 할당 시 PEO 동작 안함 - 폴 화이트
 PEO - SQL2008+ parameter embedding optimization
 option(recompile) 힌트
 
 파라메터 스니핑 문제 해결 방안 중 하나..
 
 벗,,,, 결과를 변수에 할당하면 동작을 안 한다.
 
 top 1도 동일 함... (교성왈)..ㅋㅋㅋ
 결론은 변수에 할당 하지 말아라....
 select
  @value = customerid
 from 테이블
 where 
 with option(recompile)

 [remark 필요]

 
8.  Shared Lock이 필요 한가... 폴 화이트...
 X 잠금 행에 S잠금은 차단?? (Lock optimization)
 예전에 인옥형이랑 야그했던 것...
 
9. 자동증가열의 통계정보 추적

 "Branding"
  증가열 자동 인식
  추적 플래그 2388 + DBCC SHOW_STATISTICS()가 좀 다르게 나온다.. 오호.......... 20% 변경하는 알고리즘을 알 수가 있다.. 오호.. ^^

 [remark 필요]

  http://sqlscope.wordpress.com/2012/02/
  통계 정보를 이해하는데 아주 유용 하다.
  
  - 자동 통계 정보 갱신 시기
  처음 500건 바뀌었을 때, 이후 20% 변경시 통계 정보 갱신.
  
 테이블 최대값 검색 후 반영
  추적 플래그 2389 (asc) , 2390
  - 자동 증가열 예를 들어 최신 날짜.  (통계 정보가 없는 것)
   SELECT *
   FROM inc
   WHERE data > '날짜'
   OPTION ( querytrace 2389, recompile)
    nc_index 통계 선열에 covered index가 존재하면,,,,,
    비용은 크지 않다...
  - 자동 증가열이 아니면 2390
   SELECT *
   FROM inc
   WHERE data > '날짜'
   OPTION ( querytrace 2390, recompile)
   
 SQL2012 SP1+에서는 새로운 함수 활용.
  http://judydba.tistory.com/391
  sys.dm_db_stats_properties  변경된 데이타 건수 추가 됨.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
##. 컨설팅 사례

10. 엄청난 카운터 갯수
 
 엄청난 갯수의 카운터 + 파일 사이즈 적게 ㅡ/ㅡ  수집 간격 3초, 4초 ??
 엄청난 파일들을 가지고 차트로 분석하고~
 
 - 성능카운트 90~80개 정도
 - 스팟성~일 경우~ 수집 간격을 짧게
  [remark 필요]
 
11. 쿼리 옵티마이져 최적화 단계 정보 확인(메타 누적 정보)

 http://technet.microsoft.com/ko-kr/library/ms175002.aspx
 참고 ) http://sqlintegrity.blogspot.kr/2012/07/sql-server-optimization-using-dmv.html
 참고 ) http://beyondrelational.com/modules/1/justlearned/0/tips/8666/sql-server-find-how-many-times-hints-has-been-used-in-your-queries.aspx
 오호...  이런것도 있네... https://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/
 
 final cost
  - 총 최적화 수입니다.
  - 최적화된 계획의 평균 예상 비용(내부 비용 단위)입니다.

 select *
 from sys.dm_exec_query_optimizer_info
 
12. 헐.... lock requests/sec  11,946,739
 
 lock requests/sec가 올라갈 경우....
 - 대량의 데이타를  access  하는 경우
 - 잠금 격리 수준이 높을 경우
 
 
13. 클러스터 서버에서는 sp_server_diagnostics를 이용해서 시스템에 대한 상태를 체크
 20초 단위, cpu 1초 씀
 

14. index 상태값에 disable이라는 상태가 있네 ㅡ/ㅡ?

 인덱스 사용안함 ... 이거 어디서 볼 수 있는 거야?
 Disabling Indexes
 http://msdn.microsoft.com/en-us/library/ms177406(v=sql.105).aspx
 http://msdn.microsoft.com/en-us/library/ms173760.aspx
 
 select
  is_disabled, *
 from sys.indexes
 
15. MEMOBJ_INDEXSTATSMGR memory object
 missing index statistics
 
16. 추적 플래그 2371 이슈
 trace 2371, 데이타 증가에 맞게 통계 시점.. sql2008 r2 sp2+
 http://blogs.msdn.com/b/saponsqlserver/archive/2011/09/07/changes-to-automatic-update-statistics-in-sql-server-traceflag-2371.aspx
 http://www.sqlservercentral.com/blogs/steve_jones/2012/10/22/trace-flag-2371-and-statistics/
 
 새벽시간
  유지 관리 계획 마법사를 통해서 통계 업데이트 사용할 때 이슈 발생
  기본 샘플링 비율이 100% <-- 헉.... 이러기..? 역시 UI를 쓰면 안되겠다

 서비스 중(샘플링 0.39% 예측 갯수에 문제가 발생... .ㅜㅜ)
  통계정보 왜곡 발생
  쿼리 리컴파일
  고부하 실행계획 생성
 
 통계 업데이트 시간 산정 중요.
 별로도의 샘플링 비율을 가지고 간다면 2371를 고민해야 함. 
[remark 필요]

17. 집계 함수 성능 차이
 MIN은 NULL값을 반환하고 첫번째 값을 가져온다.
 MAX는 마지막 값을 가져온다.
 
 
18. getdate() -> 변수 할당해서 변경..

19. 유일값과 전체 집계 한 번에 구하기..
 

CREATE LOGIN SQLTAG_GOD WITH PASSWORD = 'P@ssw0rd' -- 로그인생성
GO
USE MASTER
GO
CREATE USER SQLTAG_GOD FOR LOGIN SQLTAG_GOD  -- User 생성
GO
ALTER SERVER ROLE SYSADMIN ADD MEMBER SQLTAG_GOD
GO

USE [master]
GO
DENY CONNECT SQL TO SQLTAG_GOD
GO
ALTER LOGIN SQLTAG_GOD DISABLE
GO

USE [DBA]
GO
CREATE USER SQLTAG_GOD FOR LOGIN SQLTAG_GOD  -- User 생성
GO
-- ALTER ROLE DB_OWNER ADD MEMBER SQLTAG_GOD
GO

use dba
go
if object_id ('tblx') is not null
drop table tblx
go

create table tblx
(idx int)
go

insert into tblx values (1)
go

if object_id('usp_dynamic_querytraceon') is null
exec ('create proc usp_dynamic_querytraceon as select 1 ')
go

use dba
go

alter proc usp_dynamic_querytraceon
--with execute as owner -- 정적 sql 일때는 execute as owner 만 있으면 된다.
as
execute as login ='sqltag_god'
exec ('SELECT count(*) cnt FROM dbo.tblx  OPTION (QUERYTRACEON 8649)')
-- adhoc 일때는 planguide 로 먹는다.
-- dynamic sql 일때는 execute as login 으로 IMPERSONATE 권한을 가진 사용자가 실행하면 된다.
go

CREATE LOGIN sida WITH PASSWORD = '1234' -- 로그인생성
GO
USE MASTER
GO
CREATE USER sida FOR LOGIN sida  -- User 생성
GO
USE dba
GO
CREATE USER sida FOR LOGIN sida  -- User 생성
GO
GRANT EXECUTE TO sida
go

-- sida 는 [SQLTAG_GOD] 으로 변신할수 있는 힘을주겠다.........
use [master]
GO
GRANT IMPERSONATE ON LOGIN::[SQLTAG_GOD] TO sida -- WITH GRANT OPTION
GO
-- end

-- 다른 session 에서 sida 로 로그인 해 아래 스크립트를 실행해 본다.
use dba
go
exec usp_dynamic_querytraceon
go

-- IMPERSONATE  권한이 있으면 다음과 같은 쿼리도 가능하다.

EXECUTE ('SELECT * FROM dbo.tblx  OPTION (QUERYTRACEON 8649)') AS LOGIN='sqltag_god'

sa 이름이 바뀌지 않았다면  sa 로 해도 됩니다.
 

좀 아쉽다.....

아직도 Buffer Pool 용량제한, I/O 성능등은 제어하지 못한다.

2014 에서는 가능해 보인다.

하지만

2012 에서 20개의 pool 에서 62개로 pool 이 늘어나고

경쟁방식의 맥스 cpu 사용량과 더불어 hard cap 이 가능한 것은 좋아 보이기도 한다.

또한 BP 을 제외한 모든 메모리 영역은 제어 가능하다.

아직도 시끄러운 이웃은......이사가는게(다른 인스턴스로) 답인듯 하다.........

RG의 timeout 설정은 재미있는 설정인듯 하다.

Resource Governor in SQL Server 2012.docx

USE [MASTER]
GO

DROP DATABASE MB500
DROP DATABASE MB1000
DROP DATABASE MB200
GO

/****** OBJECT:  DATABASE [READAHEAD]    SCRIPT DATE: 2013-07-07 오후 3:23:18 ******/
CREATE DATABASE MB500
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB500', FILENAME = N'F:\MSSQL\MB500.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB500_LOG', FILENAME = N'F:\MSSQL\MB500.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
CREATE DATABASE MB1000
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB1000', FILENAME = N'F:\MSSQL\MB1000.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB1000_LOG', FILENAME = N'F:\MSSQL\MB1000.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
CREATE DATABASE MB200
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB200', FILENAME = N'F:\MSSQL\MB200.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB200_LOG', FILENAME = N'F:\MSSQL\MB200.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

USE MB500
GO

SELECT TOP 125000 ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB500
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO
USE MB1000
GO
SELECT TOP (125000*2) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB1000
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO
USE MB200
GO
SELECT TOP (125000/5*2) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB200
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO

-- 테스트 쿼리
SELECT COUNT(*) FROM MB200.DBO.MB200
SELECT COUNT(*) FROM MB500.DBO.MB500
SELECT COUNT(*) FROM MB1000.DBO.MB1000
GO

-- BP 용량 조회
SELECT TYPE, SUM(PAGES_KB) PAGES_KB
  FROM  SYS.DM_OS_MEMORY_CLERKS
 GROUP BY TYPE
 ORDER BY PAGES_KB DESC
GO

SELECT LEFT(CASE DATABASE_ID WHEN 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DATABASE_ID) END, 20) AS DATABASE_NAME
  , COUNT(*)AS BUFFERED_PAGE_COUNT
  , COUNT(*) * 8192 / (1024 * 1024) AS BUFFER_POOL_MB
  FROM SYS.DM_OS_BUFFER_DESCRIPTORS
 GROUP BY DB_NAME(DATABASE_ID) ,DATABASE_ID
HAVING LEFT(CASE DATABASE_ID WHEN 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DATABASE_ID) END, 20) LIKE 'MB%'
 ORDER BY 1
GO

USE MB200
GO

CREATE CLUSTERED INDEX CL_MB200 ON MB200 (IDXINT)
GO

USE MB500
GO

CREATE CLUSTERED INDEX CL_MB500 ON MB500 (IDXINT)
GO

USE MB1000
GO

CREATE CLUSTERED INDEX CL_MB1000 ON MB1000 (IDXINT)
GO

CREATE LOGIN MB1000 WITH PASSWORD = 'MB1000', CHECK_POLICY = OFF
CREATE LOGIN MB500 WITH PASSWORD = 'MB500', CHECK_POLICY = OFF
CREATE LOGIN MB200 WITH PASSWORD = 'MB1000', CHECK_POLICY = OFF

USE MB1000
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

USE MB500
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

USE MB200
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

CREATE RESOURCE POOL MB1000POOL
CREATE RESOURCE POOL MB500POOL
CREATE RESOURCE POOL MB200POOL

CREATE WORKLOAD GROUP MB1000GROUP USING MB1000POOL
CREATE WORKLOAD GROUP MB500GROUP USING MB500POOL
CREATE WORKLOAD GROUP MB200GROUP USING MB200POOL
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

USE MASTER
GO

CREATE FUNCTION CLASSIFIER_V1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @val varchar(32)
 SET @val = 'default';
 IF 'MB1000' = SUSER_SNAME()
  SET @val = 'MB1000GROUP';
 ELSE IF 'MB500' = SUSER_SNAME()
  SET @val = 'MB500GROUP';
 ELSE IF 'MB200' = SUSER_SNAME()
  SET @val = 'MB200GROUP';
 RETURN @val;
END
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

SELECT * FROM sys.dm_resource_governor_configuration
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups