Bora.Choi

펌 : http://blogs.the451group.com/information_management/2013/02/04/updated-database-lanscape-map-february-2013/

   Update 된 Database Map 입니다. 

업무를 하다보면  월 두번째, 세번째 특정 요일을 구해야 하는 경우가 있다. 

여기서는 금요일로 하겠다. 

tempdb 복구

tempdb는 SQL Server에서 정렬, GROUP BY를 사용한 집계, 커서 사용, 임시테이블 및 테

이블변수 사용, 일부 JOIN, SORT_IN_TEMPDB 옵션을 사용한 인덱스 생성, 데이터베이스

의 복구 작업 등에서 사용됩니다. 따라서, 손상 시에는 이와 같은 작업들을 진행할 수 없게

됩니다. tempdb의 손상 시 tempdb를 사용하는 작업은 다음과 같이 913 오류를 발생하며

정상적으로 실행되지 않습니다.

또한, tempdb의 size나 경로를 잘못 설정하여 SQL Server가 시작 시 정상 상태가 아닐 경우도 문제가 발생합니다.

tempdb가 주의 대상일 경우 재 시작 만으로 복구되기도 하지만 아닐 경우도 있습니다.

이럴 경우 최소 로그 이면서 tempdb를 skip 하면서 SQL 서버를 수동으로 시작 해야 합니다.

1.     명령 프론트를 실행하고 SQL Sever 실행된 경로로 이동 한다.

2.     tempdb 없이 실행하는 추적플래그 –T3609,  자동으로 실행하는 저장 프로시저를 실행하지 않게 하는 플래그 –T4022로 실행합니다.

3.     경우에 따라 최소 옵션인 –f도 포함합니다. , -m : 단일 사용자 모드  

실행하는 명령창은 닫지 말고 그대로 둡니다.

4.     경로가 잘 못 되었거나, size가 잘 못 되었을 경우 정보를 Alter database  구문을 통해 수정 합니다.

5.     SQL Server 2000 이하의 경우 주의 대상을 정상 상태로 변경 하기 위해서

명령으로 강제로 복원 할 수도 있습니다.

6.     3번의 실행 명령창에 Ctrl + c를 입력한 다음 “Y” 키를 눌러 SQL Server를 종료후 정상적으로 서비스를 기동 합니다.

7.     tempdb에 문제가 없는지 확인 합니다. 

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

23 October 2012

by Dmitri Korotkevitch

 

http://www.simple-talk.com/sql/database-administration/partitioned-tables,-indexes-and-execution-plans-a-cautionary-tale/

 

해당 아티클이 파티션 테이블의 이슈 사항을 잘 정리해 주어서 간단 번역함.

SQL Server 2005 에서 Partitioned Tables 기능이 나왔을때 우리는 기대를 했다.
많은 데이터를 최소한의 locking 으로 Switch  할 수 있을 것이라고, 그리고 쿼리 성능에도 좋은 영향을 미칠 것이라고..

그러나 무료 제공하는 점심 같은것은 없었다.  파티션 되면서 더 많은 저장 공간이 요구되었다.

Storage space increase

파티션을에 대한 계획을 할 때 꼭, 클러스터된 인덱스 key가 포함되어야 합니다. 불행이도 파티션되어야 하는 key가 고유한 인덱스가 아닐 수도 있습니다. 이 해결책은 클러스터된 인덱스에 포함시키는 방법입니다.

non-clustered index는 clustered index 의 key point를 가지고 있습니다. 파티션 테이블은 일반적으로 대형이며, 클러스터 된 인덱스 키에 새 열을 추가하면, non-clustered index의 모든 레코드가 증가됩니다.

이 수치는 스토리지 오버헤드의 저장공간은 포함되지 않습니다.
이 증가된 용량은 백업 시간, 복구 시간도 증가하게되며, 색인 유지 보수 비용도 증가합니다.

불행하게도 이 작업에 대한 해결책은 존재하지 않습니다.

데이터가 13개월을 가지고 있고 12개월 데이터를 select 를 매년 초에 해야 한다고 한다면 당연히 날짜 Data가 파티션 key에 존재해야 하며, 파티션이 슬라이등 되어야 합니다. 그러나, 오히려 작은 단위로 데이터를 삭제 하는것이 더 효율 적입니다.

기존 테이블을 파티션 하기 위해 clustered index에 열을 추가하는 것도 쉽지 않습니다.
그나마 대안으로는 indentity 컬럼이 있을 경우 해당 월이 시작되는 값을 알아내어, 그 key로 파티션 하는 것입니다.
저장 공간 뿐 아니라, 실행계획에 대해서도 살펴봐야 합니다.

파티셔닝을 구현할 때, 물리적 데이터 레이아웃이 변경됨으로 퀴러의 실행 계획이 예기치 못한 결과를 나타낼 수도 있습니다.
그림 참고)

Listing 1 shows the structure of the Orders table, with clustered index.

Listing 1: The Orders table structure

Listing 2 inserts 524,288 records into the Orders table and creates a non-clustered index.

Listing 3: Query that selects the batch of the records

Listing 4: Table partitioning code

Figure 6 shows how SQL Server actually processes the query.

 파티션되지 않은 테이블은 top 100 조회하면 처음 나오는 값 기준으로 정렬없이 100개를 가져오니까 비교적 효율적.
 만약 파티셔닝을 구현 한다며 key가 변경되고 정렬되어 있는 것 중에 모두 데이터를 찾아야 한다. 즉, 파티션이 8개 되었다고 한다면 100의 데이터가 있는지 보다 파티션 별로 데이터를 찾아서 정렬한다.
물론, 힌트로 고정할 수도 있지만 예상치 않은 결과 이다.

Listing 8: Implementation of Step 1 of "Ideal execution plan"

윗 부분 처럼하면 경계값 에러가 발생할 수 있다. 개선책.
tempdb를 사용한다.

Listing 10: Using a temporary table in order to improve cardinality estimation

Figure 11: Using temporary table in order to improve cardinality estimation

Listing 11: Hardcode static number of partitions in order to improve cardinality estimation

Figure 12: Hardcode static number of partitions in order to improve cardinality estimation

윗 부분 처럼 하면 사용하고자 하는 메모리보다 더 많은 메모리를 사용하게 된다.  ( 일부 파티션에 데이터가 없는 경우)
파티션 수를 하드 코딩 한다. -> 파티션을 추가 되었을 경우 수정해야 한다.    이론.. ㅜㅜ;;

원문 : http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

동적으로 PIVOT을 만들 수 있는 방법입니다.

아직도 case 문이 익숙하긴 하지만, 가끔은 오히려 더 복잡해지는 case 문 때문에 PIVOT을 사용해야 한다고 느껴질 때가 있습니다.

아직 PIVOT 익숙하지는 않은데 하다보니 컬럼 고정이 되서 불편한 점이 있는데 이 방법으로 사용하면 좋을 것 같네요.

PIVOT 은 행 형태의 반환을 열로 만들어 주는 것입니다.

아래로 테스트할 데이터를 생성 합니다.

결과는 아래 처럼 행으로 나 옵니다.  그러나 열로 표시하고 싶어요. 하면 PIVOT 처리 합니다.

원하는 결과가 나왔습니다. ~

다른 데이터를 추가 해 보죠.

그리고 나서 다시 윗 쿼리를 실행 해 보면 결과는 동일하게 3개의 데이터의 집계만 나타납니다.

당연한 결과 입니다. 지금 추가된 blat는 보이지 않습니다. PIVOT는 집계해서 보여줄 컬럼을 명시해야 하기 때문입니다.   쿼리를 그럼 고쳐야 하는데 좀 더 쉽게 동적으로 할 수 있게 아래는 도와 줍니다.

원하는 결과가 도출.

와 어찌 하다가 이제서야 알게됨.

TABLEDIFF 유틸..   이름에서도 알 수 있 듯이 테이블 차이점 찾기 유틸입니다.

복제되어 있는 곳에서 데이터 불일치 문제를 해결하는데 유용하며,

batch file일 이용해서 사용 할 수 있습니다.

• 복제 게시자 역할을 하는 MicrosoftSQL Server 인스턴스에 있는 원본 테이블과 복제 구독자 역할을 하는 하나 이상의 SQL Server 인스턴스에 있는 대상 테이블을 행 단위로 비교할 수 있습니다.

• 행 개수와 스키마만 비교하여 비교 작업을 빨리 수행할 수 있습니다.

• 열 수준에서 비교할 수 있습니다.

• 대상 서버의 불일치를 해결하는 Transact-SQL 스크립트를 생성하여 원본 테이블과 대상 테이블을 일치시킬 수 있습니다.

• 결과를 출력 파일이나 대상 데이터베이스의 테이블에 기록할 수 있습니다.

데이터 형식이 sql_variant 인 열은 지원하지 않는다.

도움말 :http://msdn.microsoft.com/ko-kr/library/ms162843(v=SQL.100).aspx

• 사용권한

• 사용 법

• 결과

http://www.mssqltips.com/sqlservertip/2800/sql-server-function-to-return-a-range-of-dates/

By: Albert Hetzel   정리

일자별  sum, max 등의 집계 쿼리를 할 때 일자의 범위가 모두 포함되기어 표현되어지기를 원할 때가 있다.

그러나 실제 데이터는 일자 범위에 데이터가 없을 수도 있고 있을 수도 있다. 이럴때 어떻게 해야 할까??

물론, row_number 함수를 사용하여 corss join을 사용하면 가능하다. 그러나 이런 쿼리는 복잡하고 원하는 결과를 나타내지 않을 수도 있다.

여기서는 테이블 함수를 사용하는 방법으로 해결 합니다.

이 함수는 CTE를 사용하여 반복적으로 날짜를 반환 해 준다. (@Increment 옵션에 따라서)

DROP FUNCTION [DateRange]
GO
CREATE FUNCTION [dbo].[DateRange]
(     
      @Increment              CHAR(1),   --  일/주/달 여부
      @StartDate              DATETIME,  -- 시작 날짜
      @EndDate                DATETIME   -- 종료 날짜
)
RETURNS  
@SelectedRange    TABLE 
(IndividualDate DATETIME)
AS
BEGIN
	 ;WITH cteRange (DateRange) AS (
            SELECT @StartDate
            UNION ALL
            SELECT 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                        WHEN @Increment = 'w' THEN DATEADD(ww, 1, DateRange)
                        WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
                  END
            FROM cteRange
            WHERE DateRange <= 
                  CASE
                        WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                        WHEN @Increment = 'w' THEN DATEADD(ww, -1, @EndDate)
                        WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
                  END)
          
      INSERT INTO @SelectedRange (IndividualDate)
      SELECT DateRange
      FROM cteRange
      OPTION (MAXRECURSION 3660);
      RETURN
END
GO

실행을 해 보면 아래와 같다.

자 그럼 이제 집계하고자 하는 데이터와 조인해서 사용해 보자

임시 테이블을 사용해서 해당 날짜의 제품이 판매된 정보를 입력 하였다. 아래 데이터에서 봤을 경우 11/1 ~ 11/10 사이에

매출은 이틀만 발생 하였다.

SQL Server Reporting Services 2008 (SSRS 2008) 시작하고 일정 시간 지나면 점점 늦어지는 현상을 볼 수 있음. 

1. SSRS Configuration 의 Recycle Time 변경

2. Memory Pressure

  - XML 구성 파일이 존재한다.  Recycle Time (재활용 시간 ??) 은 마지막 으로 SSRS가 사용자가 수동으로 다시 시작 되었을 때 기준으로 분 이다. 

Recycle Time이 발생하면 SSRS내의 유휴 자원이 해지 된다. 

기본으로는 720분 또는 12시간으로 구성 되어 있다. 

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer

rsreportserver.config

720분으로 셋팅되어 있기 때문에  초기 사용자는 느린 시작으로 연결이 되고 

720분 다시 발생 할 때까지 잘 사용되다가 720분이 되면 다시 유휴 자원이 이루어지어 느린 응답이 발생한다.

재활용 시간은 마지막 날짜를 사용하는 시간 SSRS은 그 참조 점으로 다시 시작했다.

SSRS은 구성 파일에서 재활용 시간을 예약 ,현재 날짜와 시간을 그 다시 시작 시간을 사용한다.

재활용 시간이 720분로 설정되어 있으며 SSRS이 12:00에 720분 후 다음 자정 0시에서 다시 시작하는 경우

 예를 들어, SSRS 자체를 재활용합니다. 이후 720분 후 자정에 0시의 SSRS 다시 재활용되며, 등등. 

SSRS의 재활용은 자원을 확보하기 위해 무기한 계속됩니다.

SSRS 재활용 시간에 가장 좋은 시간은 각 회사의 사용에 따라 달라진다.  그것은 SSRS가 많이 활용되지 않는 경우 몇 가지 분석을 수행하는 것이 가장 좋을 수 있습니다. 

기본적으로는 12 시간마다 재활용,하지만 요청이 처리되는가보고 있으면 더 기간 동안 SSRS를 운영 중 상태로 

유지하기 더 좋을 수 있다.

여러 지사를 가지고 있는데 시간대가 틀리게 사용되어 진다면, 720분은 많이 사용하는 시간에 유휴자원이 되어 초기화 될 수 있다.

조직의 크기에 따라 SSRS를 독립적으로 사용하지 않고 SQL Server나 다른 응용프로그램과 같은 컴퓨터에 공용으로 사용할 수 있다. 

이때, 다른 응용 프로그램에서 메모리가 필요하면 Windows는 유휴 상태가 된 메모리를 빼앗는 작업을 하는데 그 피해자의 하나로서 SSRS를 결정한다.  

이 문제의 간단한 해결책은 더 많은 RAM을 추가 하는 것이지만, 대부분 가능한 상황이 아니다. 

이 방법을 해결하는 두번 째 방법은 Memory Pressure 관리할 수 있는 SSRS에 최소한 메모리를 조정하는 것이다. 

혹은 예약된 보고서를 작성하는것이다. 

최소한의 메모리를 조정하는 옵션은 "WorkingSetMinimu" 에 대한 SSRS 구성 파일에 추가하는 것이다. 

이 작업은 다른 응용 프로그램에 영향을 미칠 수 있음을 주의해야 하며, 메모리 소비수준을 꼭 모니터링 해서 결정해야 한다. 

오히려 이 수치가 높을 경우 응용프로그램 및 서버 자체가 느리거나 응답 하지 않을 수도 있다. 

1. 서버 수준 권한

--======================
--1. 서버수준의권한
--======================

select  spr.name, spr.type, spr.type_desc,
             spr.default_database_name, spr.default_language_name,
             spm.class_desc, spm.permission_name, spm.state_desc,
             suser_name(srm.role_principal_id) as server_role_name
from sys.server_principals as spr with (nolock)
       inner join sys.server_permissions as spm (nolock) on spm.grantee_principal_id = spr.principal_id
       left join sys.server_role_members as srm with (nolocK) on spr.name = suser_name(srm.member_principal_id)
where spr.type in ('S','U','K', 'G') --C, R
       --and spr.name = ''  -- 유저로하나찾기
order by spr.type,spr.name

-- 유저하나의role member 보기
select suser_name(role_principal_id), suser_name(member_principal_id)
from sys.server_role_members where member_principal_id = suser_id('')

2. DB 수준 권한

use testdb
go

EXEC sp_dbfixedrolepermission;
GO

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
    WHERE permission_name = 'SELECT';


-- 사용자 role 고정 role 권한 정보
SELECT DPR.NAME, IS_FIXED_ROLE
	--,DPR.TYPE_DESC 
	--,USER_NAME(DRM.MEMBER_PRINCIPAL_ID) AS USER_NAME
    ,USER_NAME(DRM.ROLE_PRINCIPAL_ID) AS ROLE_NAME
FROM  SYS.DATABASE_PRINCIPALS AS DPR WITH (NOLOCK)
  LEFT JOIN SYS.DATABASE_ROLE_MEMBERS DRM WITH (NOLOCK)  ON DPR.PRINCIPAL_ID = DRM.MEMBER_PRINCIPAL_ID
WHERE DPR.TYPE ='R'
ORDER BY DPR.NAME

-- role별 사용자 정보
SELECT  USER_NAME(DRM.ROLE_PRINCIPAL_ID) AS 'ROLE_NAME'
	,USER_NAME(DRM.MEMBER_PRINCIPAL_ID) AS 'LOGIN'
FROM SYS.DATABASE_ROLE_MEMBERS  AS DRM WITH(NOLOCK) 
	JOIN SYS.DATABASE_PRINCIPALS AS DPM WITH(NOLOCK) ON DRM.ROLE_PRINCIPAL_ID = DPM.PRINCIPAL_ID
WHERE DPM.TYPE ='R'
	AND DPM.IS_FIXED_ROLE = 0
ORDER BY USER_NAME(DRM.ROLE_PRINCIPAL_ID) 

select dpr.name, dpr.type_desc, dpr.default_schema_name,
       user_name(drm.member_principal_id) as user_name,
       user_name(drm.role_principal_id) as role_name
from  sys.database_principals as dpr with (nolock)
       inner join sys.database_role_members drm with (nolock)  on dpr.principal_id = drm.member_principal_id
--where name = ''  -- role이나 유저정보
order by 1,2


-- DB별로 사용자와 ROLE이 어떤 객체의 권한을 가지고 있는지 여부
select dpr.name, dpr.type_desc, dpr.default_schema_name,
	          dpm.class_desc,
	          case  when dpm.major_id = 0 then 'ALL' else obj.name end as object_name,
             dpm.permission_name, dpm.state_desc
from sys.database_principals as dpr with (nolock)
       left join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id
       left outer join sys.all_objects as obj with (nolock) on dpm.major_id = obj.object_id
--where dpr.name = '' -- 하나의유저나 혹은 role 이름이나 모두 가능
order by dpr.type, dpr.name



-- 사용자 role member 상세 보기
select dpr.name, dpr.type_desc, dpr.default_schema_name,
       user_name(drm.member_principal_id) as user_name,
       user_name(drm.role_principal_id) as role_name,
	   case  when dpm.major_id = 0 then 'ALL' else OBJECT_NAME(dpm.major_id)  end as object_name,
             dpm.permission_name, dpm.state_desc
from  sys.database_principals as dpr with (nolock)
       left join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id 
       inner join sys.database_role_members drm with (nolock)  on dpr.principal_id = drm.member_principal_id
	   --left outer join sys.all_objects as obj with (nolock) on dpm.major_id = obj.object_id
	   -- 안해도 된다.
--where dpr.type = 'R' -- DATABASE_ROLE 
		--and dpr.name = ''		
	/* S = SQL 사용자
	   U = Windows 사용자
       G = Windows 그룹
       A = 응용 프로그램 역할
       R = 데이터베이스 역할
       C = 인증서로 매핑된 사용자
       K = 비대칭 키로 매핑된 사용자 */
ORDER BY dpr.name, 5

3. 권한 제거 Script 생성

-- object 권한 제거
select schema_name(obj.schema_id) as schema1,
	   case  when dpm.major_id = 0 then 'ALL' else OBJECT_NAME(dpm.major_id)  end as object_name,
       dpm.state_desc, 
     ('REVOKE ' + dpm.permission_name COLLATE  Korean_Wansung_CI_AS
     + ' ON OBJECT::' + schema_name(obj.schema_id)+ '.' + OBJECT_NAME(dpm.major_id) + ' FROM '  + dpr.name ) 
from  sys.database_principals as dpr with (nolock)
       inner join sys.database_permissions as dpm with (nolock) on dpr.principal_id = dpm.grantee_principal_id 
       inner join sys.objects  as obj with (nolock) on dpm.major_id = obj.object_id
where  dpr.name  =''   and  dpm.permission_name = 'EXECUTE'

	  

--==========================
-- 2000용
--==========================
select name, createdate, updatedate, dbname from master..syslogins
where  name != 'sa' and name not like '#%' order by name;

exec sp_helpsrvrolemember

exec sp_helprolemember

-- 객체정보
select distinct table_catalog, grantee, table_name , PRIVILEGE_TYPE
     --('REVOKE ' + PRIVILEGE_TYPE + ' ON ' + table_name+ ' FROM '  + grantee ) 
from INFORMATION_SCHEMA.COLUMN_PRIVILEGES

select grantee, table_catalog, table_schema, table_name, privilege_type, is_grantable
from INFORMATION_SCHEMA.TABLE_PRIVILEGES
where grantee = ''

select specific_catalog, specific_name, * from INFORMATION_SCHEMA.PARAMETERS