티스토리 툴바

블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (252)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total59,637
Today13
Yesterday81

달력

« » 2014.04
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      

공지사항

http://blogs.msdn.com/b/psssql/archive/2014/04/09/sql-server-2014-tempdb-hidden-performance-gem.aspx


2012 pcu2 에도 적용이 되어 있다고 합니다. (서비스팩 2 를 이야기 합니다.)

임시테이블 많이 쓰는곳은 패치 하시면 좋을듯 합니다. 

저작자 표시 비영리 변경 금지
Posted by 보미아빠

lob compaction 이 힘들때 work-around 입니다.

 

/* 샘플 데이터베이스 생성 */

CREATE DATABASE lob ON  PRIMARY
( NAME = N'lob', FILENAME = N'f:\MSSQL\lob.MDF' , SIZE = 225280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
 LOG ON
( NAME = N'lob_LOG', FILENAME = N'f:\MSSQL\lob_LOG.LDF' , SIZE = 225280KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
GO


USE lob
GO

IF OBJECT_ID ('TBLX') IS NOT NULL
DROP TABLE TBLX
GO

CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,COLA TEXT
)
GO

SET NOCOUNT ON

DECLARE @IDX INT
SET @IDX = 0

DECLARE @LOB_DATA VARCHAR(MAX)

SET @LOB_DATA = REPLICATE(cast('A'as varchar(max)), 20000)


WHILE (1=1)
BEGIN
 INSERT INTO TBLX (COLA) VALUES(case when @idx % 2 = 1 then @LOB_DATA else left(@lob_data,10) end)
 SET @IDX = @IDX + 1
 IF @IDX > 3000 BREAK
END
GO

SELECT TOP 100 DATALENGTH(CAST(COLA AS VARCHAR(MAX))) FROM TBLX
GO

CREATE UNIQUE CLUSTERED INDEX CL_TBLX ON TBLX (IDX)
GO

/* 샘플 데이터베이스 생성 완료 */

 

/* lob 를 공백으로 업데이트 하기전 공간 사용량 */

DBCC EXTENTINFO ('lob','TBLX')

go

 

-- 490개 행에 pfs_bytes 가 꽉 차 있음

 

/* 일정기간보다 오래된 text 컬럼을 공백으로 update 하는 시뮬레이션 */

update tblx set COLA ='' where idx > 1000
go

 

/* lob 를 공백으로 업데이트 한 후 공간 사용량 */

DBCC EXTENTINFO ('lob','TBLX')

 

-- 220개 행이 출력되며, pfs_bytes 의 중간 중간 비어있는것을 알수 있음 (알박기 되어 있어 extents 를 날릴수 없음)

 

 

/* sp_spaceused 를 이용한 공간 사용량 확인 */

exec sp_spaceused tblx

go

 

-- unused 공간이 2MB 있음

 

 

/* text 공간이 삭제된 row 를 다른 빈 테이블로 옮기고 삭제한 후 다시 원복시킴 */  

if object_id ('tbly') is not null
drop table tbly
go
 

select *
into tbly
from (
 select idx from tblx where idx > 1000 -- lob 는 select list 에서 빼야 한다. 
 ) a
go 

delete from tblx where idx > 1000
go

 

set identity_insert tblx on
go

 

insert into tblx (idx)
select idx from tbly
go

 

/* 작업완료 된 후 pfs_bytes 현황 */  

DBCC EXTENTINFO ('lob','TBLX')
go

 

-- 174개의 extenst 가 사용되었으며, 중간 중간 있던 알박기 페이지는 모두 삭제 되었음

 

 

/* sp_spaceused 를 이용한 공간 사용량 확인 */

exec sp_spaceused tblx

go

 

-- 완전히 공간이 확보된 모습 

 

 

 

 

저작자 표시 비영리 변경 금지
Posted by 보미아빠

table audit

분류없음 / 2014/03/27 15:31

/* 참고자료 express edition 에서도 가능합니다. */

create SERVER AUDIT AuditDataAccess
TO FILE ( FILEPATH ='C:\ttt\' )
WHERE object_name = 'SensitiveData' and database_name = 'testtest' ;

 

create SERVER AUDIT SPECIFICATION [ServerAuditSpec_ObjectAccess]
FOR SERVER AUDIT AuditDataAccess
add (SCHEMA_OBJECT_ACCESS_GROUP)
WITH (STATE = ON)

  

http://blogs.msdn.com/b/sqlsecurity/archive/2012/10/03/filter-sql-server-audit-on-action-id-class-type-predicate.aspx

http://technet.microsoft.com/ko-kr/library/cc280386.aspx

 

 

아래 스크립트는 EE 에서만 가능한 스크립트이며, SE 에서 동작하게 수정 할 수 있습니다.

보안 관련해서 국내 몇몇 컨설팅 업체가 EE 만 된다고 강의나 컨설팅을 하는데 잘못된 정보 입니다.

2012에서는 모든 버전에서 predicate 와 server audit 을 이용해 테이블 수준의 감사가 가능합니다.

 

-- 우리는 특정 데이터베이스의 특정 테이블에 억세스한 로그를 감사 하려고 한다.

-- 1. 대상 데이터베이스 생성
use master
go

if exists (select * from master.dbo.sysdatabases where name ='auditTargetSampleDB')
begin
 alter database auditTargetSampleDB set single_user with rollback immediate
 drop database auditTargetSampleDB
end

create database auditTargetSampleDB
go

use auditTargetSampleDB
go

create table tsalary
(idx int identity(1,1)
,name nvarchar(100)
,salary int
)
go

insert tsalary values ('minsouk1@sqltag.org', 100000000)
insert tsalary values ('manha@sqltag.org', 100000000)
go

-- 우리가 관심 있는 정보
select * from tsalary --< 궁금하지만 보면 안되는 정보 보면 누가, 언제, 어디서, 어떤쿼리로 조회 했는지 알고 싶다.
-- 우리가 관심 있는 정보
go


-- 2. 로그온 트리거 작성
-- 로그인을 먼저 해야 로그인정보를 남길 수 있으므로, logon trigger 를 이용하기로 한다.
-- 로그온 하면, session reuse count 를 다시 남기기로 했다. server audit 은 너무 많은 로그를 남기고 관리하기 불편하다.

IF EXISTS (SELECT * FROM SYS.SERVER_TRIGGERS WHERE NAME = 'ConnectionInfoLogonTrigger')
DROP TRIGGER ConnectionInfoLogonTrigger ON ALL SERVER
GO

use master
go

if exists (select * from master.dbo.sysdatabases where name ='DBA')
begin
 alter database DBA set single_user with rollback immediate
 drop database DBA
end

create database DBA
go

USE DBA
GO

if object_id('tLogonTriggerLog') is not null
drop table tLogonTriggerLog
go

create table tLogonTriggerLog
(
EventType nvarchar(100)
,DayPostTime datetime2
,DayLastSessionPostTime datetime2 -- 마지막 일자별 last session time
,SessionResueCnt bigint -- 세션 resue count
,SPID int
,ServerName nvarchar(100)
,LoginName nvarchar(100)
,LoginType nvarchar(100)
,SID nvarchar(100)
,ClientHost nvarchar(100)
,net_transport nvarchar(40)
,protocol_type nvarchar(40)
,auth_scheme nvarchar(40)
,local_net_address nvarchar(48)
,local_tcp_port int
--,client_net_address nvarchar(48)
,physical_net_transport nvarchar(40)
,program_name nvarchar(100)
)
GO

create clustered index cl_tLogonTriggerLog on tLogonTriggerLog (ClientHost, program_name, loginName, DayPostTime)
go

CREATE TRIGGER ConnectionInfoLogonTrigger
ON ALL SERVER
FOR LOGON
AS
set nocount on
set transaction isolation level read uncommitted

declare @sysdatetime datetime2 = SYSDATETIME()
declare @eventdata xml = eventdata()

if exists
(
 select *
   from dba.dbo.tLogonTriggerLog
  where
     SPID = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
    and ClientHost = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
    and program_name = program_name()
    and loginName = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
    and DayPostTime >= convert(varchar(10), getdate(), 120)
)
begin
  update dba.dbo.tLogonTriggerLog set
      --DayLastSessionPostTime = @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)')
   DayLastSessionPostTime = @sysdatetime
    , SessionResueCnt = SessionResueCnt + 1

  where SPID = @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
    and ClientHost = @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
    and program_name = program_name()
    and loginName = @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
    and DayPostTime >= convert(varchar(10), getdate(), 120)
end else begin 
 insert into dba.dbo.tLogonTriggerLog values (
    @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
  , @sysdatetime
  , @sysdatetime
  , 0
  , @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)')
  , @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)')
  , @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)')
  , @eventdata.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(100)')
  , @eventdata.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(100)')
  , @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(100)')
  , CAST(CONNECTIONPROPERTY('net_transport') AS NVARCHAR(40))
  , CAST(CONNECTIONPROPERTY('protocol_type') AS NVARCHAR(40))
  , CAST(CONNECTIONPROPERTY('auth_scheme') AS NVARCHAR(40))
  , CAST(CONNECTIONPROPERTY('local_net_address') AS NVARCHAR(48))
  , CAST(CONNECTIONPROPERTY('local_tcp_port') AS INT)
  --, CAST(CONNECTIONPROPERTY('client_net_address') AS NVARCHAR(48))
  , CAST(CONNECTIONPROPERTY('physical_net_transport') AS NVARCHAR(40))
  , program_name()
  )
end
GO

-- 3. audit 저장소 정의
use master
go

if exists (select * from sys.server_audits where name = 'Specipic_Object_Security_Audit')
begin
 ALTER SERVER AUDIT [Specipic_Object_Security_Audit]
 WITH (STATE = OFF)

 drop server audit [Specipic_Object_Security_Audit]
end

CREATE SERVER AUDIT [Specipic_Object_Security_Audit]
TO FILE
( FILEPATH = N'd:\audit\'
 ,MAXSIZE = 100 MB -- 충분하게 확장하길 바란다.
 ,MAX_ROLLOVER_FILES = 30 -- 충분하게 확장하길 바란다.
 ,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
)
go

-- 4. server audit 시작
ALTER SERVER AUDIT [Specipic_Object_Security_Audit] WITH (STATE = ON)
GO

-- 5. database audit 작성
-- 해당 데이터베이스로 이동
USE auditTargetSampleDB ;
GO
-- Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Salary_Tables
FOR SERVER AUDIT Specipic_Object_Security_Audit
ADD (SELECT , INSERT, update, delete
     ON dbo.tsalary BY dbo /* 추가하고 싶은것들 다 삽입 */ )
WITH (STATE = ON) ;
GO

-- 6. 다른세션을 하나 열고 tsalary 를 조회한다.
-- 우리가 관심 있는 정보
use auditTargetSampleDB
go

select * from tsalary --< 궁금하지만 보면 안되는 정보 보면 누가, 언제, 어디서, 어떤쿼리로 조회 했는지 알고 싶다.
-- 우리가 관심 있는 정보

select * from tx
-- 7. audit 조회
-- 아래 쿼리로 확인한다.
-- 만든 파일의 정확한 경로 및 파일을 확인 후 조회한다.

 

SELECT
    SERVER_PRINCIPAL_NAME
  , DATEADD(HH,DATEDIFF(HH, GETUTCDATE(), CURRENT_TIMESTAMP), EVENT_TIME) AS [LOCALPOSTTIME]
     , PHYSICAL_NET_TRANSPORT
     , CLIENTHOST
  , LOCAL_TCP_PORT
  , ACTION_ID
  , DATABASE_NAME
  , STATEMENT
  FROM SYS.FN_GET_AUDIT_FILE ('D:\audit\Specipic_Object_Security_Audit_06FF647C-0D85-4389-B2BE-37D3B3BCDF2E_0_130151744368530000.sqlaudit',DEFAULT,DEFAULT) A
 OUTER APPLY
       ( SELECT TOP 1 *
        FROM DBA.DBO.TLOGONTRIGGERLOG
    WHERE LOGINNAME = SERVER_PRINCIPAL_NAME
      AND SPID = SESSION_ID
   AND DayLastSessionPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time)
    ORDER BY DAYLASTSESSIONPOSTTIME DESC
  )  b
 --WHERE ACTION_ID in ('SL', 'IN') -- select insert 만 잡았다. 적절한 action 을 넣어 검사
 ORDER BY EVENT_TIME DESC


 -- 8. test query 
--SELECT DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS [localPostTime], *
--  FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) a
-- outer apply
--       ( select top 1 *
--        from dba.dbo.tLogonTriggerLog
--    where LoginName = server_principal_name
--      and spid = session_id
--   and DayPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time))  b
-- WHERE ACTION_ID ='SL'
-- ORDER BY EVENT_TIME DESC


--SELECT DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time) AS [localPostTime], *
--  FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) a
-- outer apply
--       ( select row_number() over( order by (select null)) rn, *
--        from dba.dbo.tLogonTriggerLog
--    where LoginName = server_principal_name
--      and spid = session_id
--   and DayPostTime <= DATEADD(hh,DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_time))  b
-- WHERE ACTION_ID ='SL'
-- ORDER BY EVENT_TIME DESC

-- 검사시
-- SELECT * FROM dba.dbo.tLogonTriggerLog
-- SELECT * FROM sys.fn_get_audit_file ('D:\audit\Payrole_Security_Audit_30DA5D0E-FF0C-462D-A735-05FA9B584A4E_0_130117783222490000.sqlaudit',default,default) order by event_time desc

 

--USE [master]
--GO

--CREATE SERVER AUDIT SPECIFICATION [HIPPA_Audit_Specification]
--FOR SERVER AUDIT [Specipic_Object_Security_Audit]
--ADD (SUCCESSFUL_LOGIN_GROUP)
--WITH (STATE = OFF)
--GO

http://msdn.microsoft.com/en-us/library/cc280448.aspx

 

저작자 표시 비영리 변경 금지
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함