보미네

병렬처리는 어떤 제한에 의해서 전체가 병렬로 처리되지 못하는 경우와

어떤 한 이터레이터만 병렬로 처리되지 못하는 경우가 있다. 이때는 TF 8649를 설정해도 병렬처리가 불가능 하다.

그러나, TF 8649로 강제로 병렬처리되는 쿼리가 비용이 높은데도 불구하고 힌트없이 돌리면 병렬로 처리되지 않는 이유는 뭘까? 이유는 간단했다. 싱글 처리가 병렬처리보다 계산된 예상비용이 작은 경우이다. MS 에서 병렬처리가 불가능 할때 TF8649를 이용한 테스트를 하라는 것은 이걸 테스트 해보라는 의미이다.

병렬처리의 비용은 CPU 수가 많아지면 논리 코어수의 절반까지는 비용이 줄어든다.

그러므로 코어수가 작은 서버에서 병렬처리가 안되던 것이 코어수가 많아지면 병렬처리가 되는 경우도 발생하는데

이유는 같다.

impersonate 가능 사용자 만들기

http://sqlsql.tistory.com/259

--CREATE LOGIN MS_BACKUP_OP WITH PASSWORD = 'P@ssw0rd' -- 로그인생성
--GO
--USE MASTER
--GO
--CREATE USER MS_BACKUP_OP FOR LOGIN MS_BACKUP_OP  -- User 생성
--GO
--GRANT EXECUTE TO MS_BACKUP_OP -- 프로시저 실행권한
--GO
--ALTER SERVER ROLE [DISKADMIN] ADD MEMBER [MS_BACKUP_OP] -- 백업할 디바이스 생성을 위해 diskadmin 필요
--GO

--USE [DB_PLAN_GUIDE_TEST]
--GO
--CREATE USER MS_BACKUP_OP FOR LOGIN MS_BACKUP_OP
--GO
--ALTER ROLE [DB_BACKUPOPERATOR] ADD MEMBER [MS_BACKUP_OP] -- 해당 데이터베이스에 db_backupoperator role 필요
--GO

백업 권한을 위한 계정은 로그인이 불가능하게 만든다.

SYSADMIN 계정을 하나 만들어 두고 이렇게 설정하고 쓰면 될 듯

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

IF OBJECT_ID('AP_DB_BACKUP') IS NULL

 EXEC ('CREATE PROC DBO.AP_DB_BACKUP AS SELECT 1 ')

GO

ALTER PROC [DBO].AP_DB_BACKUP

  @DBNAME VARCHAR(100) = 'MASTER' -- BACKUP TARGET DATABASE

, @PATH VARCHAR(100) = 'C:\BACKUP\' -- OS BACKUP PATH 

, @BACKUP_TYPE VARCHAR(1) = 'F' -- F FULLBACKUP L LOGBACKUP D DIFFERENTIAL

, @BEFORE_TARGET_TIME_HH INT = 2 -- DELETE FILE BEFORE TIME HH

AS

-- 계정 생성후 로그인 불가 처리한다. 

EXECUTE AS login = 'sa' -- *DISKADMIN*DB_BACKUPOPERATOR*

SET NOCOUNT ON 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM

-- VERSION 1.20130104

-- BLOG, SQLSQL.TISTORY.COM

-- MAIL, MINSOUK@HOTMAIL.COM

-- xp_cmdshell 제거

-- 특정 유저로 백업

IF @BACKUP_TYPE = 'L' 

WAITFOR DELAY '00:00:03.000'

IF @BACKUP_TYPE = 'D' 

WAITFOR DELAY '00:00:10.000'

DECLARE @CAL_BEFORE_TARGET_TIME DATETIME 

   , @DEL_TARGET_DEV_NAME VARCHAR(500)

   , @CUR_TARGET_DEV_NAME VARCHAR(500)

   , @DATE VARCHAR(500)

   , @TIME VARCHAR(500)

   , @PHYSICAL_NAME VARCHAR(500)

   , @TODAY VARCHAR(8)

   , @INSTANCE_NAME VARCHAR(500)

SELECT @INSTANCE_NAME = REPLACE(@@SERVERNAME , '\','_')

     , @DATE = CONVERT(VARCHAR(100), GETDATE(), 112)

     , @TIME = REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),':','')

select @instance_name , @date, @time 

SELECT @CAL_BEFORE_TARGET_TIME = DATEADD(HH,-1*ABS(@BEFORE_TARGET_TIME_HH),GETDATE())

DECLARE @LOG_BACKUP_DELETE_TARGET TABLE 

(IDX INT IDENTITY(1,1)

,NAME VARCHAR(500)

)

INSERT INTO @LOG_BACKUP_DELETE_TARGET (NAME)

SELECT NAME

  FROM MASTER.DBO.SYSDEVICES 

 WHERE ISDATE(LEFT(RIGHT(NAME, 15),8)) = 1

   AND CAST(REPLACE(STUFF(STUFF(RIGHT(NAME, 15),12,0,':'),15,0,':'),'_',' ') AS DATETIME) < @CAL_BEFORE_TARGET_TIME

   AND NAME LIKE 'SQL'+@BACKUP_TYPE+'B_' +REPLACE(@@SERVERNAME,'\','_') +'_'+@DBNAME+'%'

 ORDER BY 1 

select * from @LOG_BACKUP_DELETE_TARGET

DECLARE @MAX_IDX INT

SELECT TOP 1 @MAX_IDX = IDX

  FROM @LOG_BACKUP_DELETE_TARGET

 ORDER BY IDX DESC

WHILE (@MAX_IDX > 0) BEGIN 

 SELECT @DEL_TARGET_DEV_NAME = NAME 

   FROM @LOG_BACKUP_DELETE_TARGET

  WHERE IDX = @MAX_IDX 

 IF @@ROWCOUNT = 0 BREAK;

 BEGIN TRY 

  EXEC SP_DROPDEVICE @DEL_TARGET_DEV_NAME,'DELFILE'  

 END TRY BEGIN CATCH

  SELECT 'ERROR !'

 END CATCH 

 SET @MAX_IDX = @MAX_IDX - 1

END

SELECT @CUR_TARGET_DEV_NAME = UPPER(

  'SQL'+@BACKUP_TYPE+'B_'

  + REPLACE(@@SERVERNAME,'\','_') + '_'

  + @DBNAME + '_' 

  + @DATE+'_'

  + @TIME

  )

  , @PHYSICAL_NAME = UPPER(

    RTRIM(@PATH+@INSTANCE_NAME) 

  + '\'

  + REPLACE(@@SERVERNAME,'\','_') + '_'

  + 'SQL'+@BACKUP_TYPE + 'B_'

  + LEFT(@DBNAME+ '_' + REPLICATE (@BACKUP_TYPE, 40),40) + '_'

  + @DATE + '_'

  + @TIME 

  + '.'

  + @BACKUP_TYPE 

  + 'BAK'

  )

SELECT @CUR_TARGET_DEV_NAME DEV_NAME

  , @PHYSICAL_NAME PATH_NAME

EXEC SP_ADDUMPDEVICE 'DISK' , @CUR_TARGET_DEV_NAME , @PHYSICAL_NAME

IF @BACKUP_TYPE = 'F'

 BACKUP DATABASE @DBNAME TO @CUR_TARGET_DEV_NAME WITH INIT 

  , NAME = @CUR_TARGET_DEV_NAME 

  , NOSKIP 

  , NOFORMAT 

  --, PASSWORD = 'P@SSW0RD' 

IF @BACKUP_TYPE = 'L'

 BACKUP LOG @DBNAME TO @CUR_TARGET_DEV_NAME

IF @BACKUP_TYPE = 'D'

 BACKUP DATABASE @DBNAME TO @CUR_TARGET_DEV_NAME WITH INIT 

  , NAME = @CUR_TARGET_DEV_NAME 

  , NOSKIP 

  , NOFORMAT 

  , DIFFERENTIAL

go

exec master.dbo.ap_db_backup 'master','e:\backup2\','F',1

--declare @BMK varchar(max)
--set @BMK = 'BACKUP SERVICE MASTER KEY TO FILE = ''E:\BACKUP_SQLTAG\' + replace(@@servername,'\','_') + '.service_master.key'' ENCRYPTION BY PASSWORD = ''P@SSW0RD'''
--exec (@BMK)

--RESTORE SERVICE MASTER KEY FROM FILE = 'E:\BACKUP_SQLTAG\filename' DECRYPTION BY PASSWORD = 'P@SSW0RD' -- [FORCE]

IF OBJECT_ID('AP_DB_BACKUP_ENZIP') IS NULL
EXEC ('CREATE PROC AP_DB_BACKUP_ENZIP AS SELECT 1 ')
GO

ALTER PROC AP_DB_BACKUP_ENZIP
(
  @DBNAME     VARCHAR(20) = 'MASTER'   -- BACKUP TARGET DATABASE
, @BEFORE_TARGET_TIME_HH INT = 0       -- DELETE FILE BEFORE TIME HH
, @COMPRESS_0123 VARCHAR(100) = '0' -- 0은 압축하지 않음
, @PASSWORD VARCHAR(100) = 'MINSOUK'
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 7ZIP COMMANDLINE 프로그램을 C:\WINDOWS\SYSTEM32 에 넣어줘야 한다.
-- SP_CONFIGURE XP_CMDSHELL 확인하고 원복한다.
-- 작업파일 시간전 예전파일을 지우고 시작한다.
-- fbak 파일이 없으면 그냥 끝난다.
-- VERSION 20131115

IF NOT EXISTS (SELECT * FROM MASTER.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AP_DB_BACKUP_ENZIP_HISTORY')
BEGIN
 EXEC ('
   CREATE TABLE MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY
   (
      IDX INT IDENTITY(1,1) PRIMARY KEY
    , DBNAME NVARCHAR(50)
    , EN_ZIP_TIME DATETIME
    , EN_ZIP_FILENAME NVARCHAR(1000)
    , PHYNAME NVARCHAR(1000)
   )
  ')
END

DECLARE @LOG_BACKUP_DELETE_TARGET TABLE
(IDX INT IDENTITY(1,1)
,NAME VARCHAR(1000)
)

DECLARE @CAL_BEFORE_TARGET_TIME DATETIME = DATEADD(HOUR,-1*ABS(@BEFORE_TARGET_TIME_HH),GETDATE())
, @PHYNAME NVARCHAR(1000)
, @CMD_STRING NVARCHAR(1000)
, @DEL_TARGET_DEV_NAME VARCHAR(1000)
, @FILE_EXISTS INT 

INSERT INTO @LOG_BACKUP_DELETE_TARGET
SELECT EN_ZIP_FILENAME
  FROM MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY
 WHERE EN_ZIP_TIME < @CAL_BEFORE_TARGET_TIME

DECLARE @MAX_IDX INT
SELECT TOP 1 @MAX_IDX = IDX
  FROM @LOG_BACKUP_DELETE_TARGET
 ORDER BY IDX DESC

WHILE (@MAX_IDX > 0)
BEGIN
 SELECT @DEL_TARGET_DEV_NAME = NAME
 FROM @LOG_BACKUP_DELETE_TARGET
 WHERE IDX = @MAX_IDX

 IF @@ROWCOUNT = 0 BREAK;

 BEGIN TRY
  SET @CMD_STRING = 'DEL '+ @DEL_TARGET_DEV_NAME
  DELETE MASTER..AP_DB_BACKUP_ENZIP_HISTORY WHERE EN_ZIP_FILENAME = @DEL_TARGET_DEV_NAME
  EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
  SELECT @CMD_STRING
  SELECT 1
 END TRY
 BEGIN CATCH
  DELETE MASTER..AP_DB_BACKUP_ENZIP_HISTORY WHERE EN_ZIP_FILENAME = @DEL_TARGET_DEV_NAME
  SELECT 'ERROR !'
 END CATCH
 SET @MAX_IDX -= 1
END

SELECT TOP 1 @PHYNAME = A.PHYNAME
  FROM MASTER..SYSDEVICES A
  LEFT OUTER JOIN MASTER..AP_DB_BACKUP_ENZIP_HISTORY B
    ON A.PHYNAME = B.PHYNAME
 WHERE NAME LIKE '%'+'SQLFB_'+ REPLACE(@@SERVERNAME,'\','_')+'_'+ @DBNAME+'%'
   AND B.PHYNAME IS NULL
 ORDER BY RIGHT (NAME, 15) DESC

IF @PHYNAME IS NULL OR @PHYNAME = ''
BEGIN
 SELECT 'EXECUTE AP_DB_BACKUP FIRST!'
 RETURN 0
END

DECLARE @TBL_SP_CONFIGURE TABLE (NAME VARCHAR(100), MINIMUM INT, MAXMUM INT, CONFIG_VALUE INT, RUN_VALUE INT)
DECLARE @PREVIOUS_XP_CMDSHELL_BIT INT
INSERT INTO @TBL_SP_CONFIGURE EXEC SP_CONFIGURE 'XP_CMDSHELL'
SELECT @PREVIOUS_XP_CMDSHELL_BIT = RUN_VALUE
  FROM @TBL_SP_CONFIGURE

IF @PREVIOUS_XP_CMDSHELL_BIT = 0
BEGIN
  EXEC SP_CONFIGURE 'XP_CMDSHELL' ,1
  RECONFIGURE WITH OVERRIDE
END

EXEC MASTER.DBO.XP_FILEEXIST @PHYNAME, @FILE_EXISTS OUT
IF @FILE_EXISTS = 1
BEGIN
 
 -- CASE SENSETIVE
 set @cmd_string = '7za.exe a -p'+@password+' -mx'+@compress_0123+' -t7z '+@phyname+'.ENZIP ' + @phyname
 -- CASE SENSETIVE

 EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
 SET @CMD_STRING = 'DEL '+ @PHYNAME
 EXEC MASTER..XP_CMDSHELL @CMD_STRING, NO_OUTPUT
 INSERT INTO MASTER.DBO.AP_DB_BACKUP_ENZIP_HISTORY (DBNAME, EN_ZIP_TIME, EN_ZIP_FILENAME, PHYNAME) VALUES (@DBNAME, GETDATE(), @PHYNAME+'.ENZIP',@PHYNAME)
END

IF @PREVIOUS_XP_CMDSHELL_BIT = 0
BEGIN
  EXEC SP_CONFIGURE 'XP_CMDSHELL' ,0
  RECONFIGURE WITH OVERRIDE
END
GO