블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

 « 2024.5  »
일 월 화 수 목 금 토
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 31
  • total
  • today
  • yesterday

tempdb 모니터링 쿼리

2018. 10. 27. 22:28 카테고리 없음
;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                 EST.text, 
                 ERQ.statement_start_offset / 2, 
                 CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset 
                  THEN 0 
                 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC;

 

저작자표시
posted by bedbmsguru

Index 생성이 안되어 있는 Foreign key 찾기

2018. 10. 27. 22:21 SQL SERVER

WITH FK_ColumnCount AS
(
        SELECT kc.constraint_object_id
                , ColumnCount = max(kc.constraint_column_id)
        FROM sys.foreign_key_columns kc
        GROUP BY kc.constraint_object_id
)
, ParentIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.parent_object_id 
                                                                                AND ic.column_id = kc.parent_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_key_columns kc
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
, ReferencedBoundIndexGood AS
(
        SELECT kc.constraint_object_id 
                , FK_CC = cc.ColumnCount
                , ic.index_id
                , I_CC = COUNT(1)
        FROM sys.foreign_keys k
                INNER JOIN sys.foreign_key_columns kc ON k.object_id = kc.constraint_object_id
                INNER JOIN FK_ColumnCount cc ON kc.constraint_object_id = cc.constraint_object_id
                INNER JOIN sys.index_columns ic ON ic.key_ordinal <= cc.ColumnCount
                                                                                AND ic.object_id = kc.referenced_object_id 
                                                                                AND ic.column_id = kc.referenced_column_id
                                                                                AND ic.index_id = k.key_index_id
        GROUP BY kc.constraint_object_id 
                , cc.ColumnCount
                , ic.index_id 
        HAVING cc.ColumnCount = COUNT(1)
)
SELECT FK_Name = k.name
        , k.is_disabled
        , k.is_not_trusted
        , k.delete_referential_action_desc
        , k.update_referential_action_desc
        , ParentTable = ps.name + '.' + pt.name 
        , ParentColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.parent_object_id = c.object_id AND kc.parent_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferencedTable = rs.name + '.' + rt.name 
        , ReferencedColumns = substring((SELECT (', ' + c.name)
                                                        FROM sys.foreign_key_columns kc
                                                                INNER JOIN sys.columns c ON kc.referenced_object_id = c.object_id AND kc.referenced_column_id = c.column_id
                                                        WHERE kc.constraint_object_id = k.object_id 
                                                        ORDER BY kc.constraint_column_id 
                                                        FOR XML PATH ('')
                                                        ), 3, 4000)
        , ReferenceBoundIndex = ri.name
        , IsParentIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ParentIndexGood WHERE ParentIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END
        , IsReferenceIndexedForFK = CASE WHEN EXISTS (SELECT * FROM ReferencedIndexGood WHERE ReferencedIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
        , IsReferenceBoundToGoodIndex = CASE WHEN EXISTS (SELECT * FROM ReferencedBoundIndexGood WHERE ReferencedBoundIndexGood.constraint_object_id = k.object_id) THEN 'Yes' ELSE 'No' END 
FROM sys.foreign_keys k 
        INNER JOIN sys.tables pt ON k.parent_object_id = pt.object_id
        INNER JOIN sys.schemas ps ON pt.schema_id = ps.schema_id
        INNER JOIN sys.tables rt ON k.referenced_object_id = rt.object_id
        INNER JOIN sys.schemas rs ON rt.schema_id = rs.schema_id
        LEFT JOIN sys.indexes ri ON k.referenced_object_id = ri.object_id AND k.key_index_id = ri.index_id
ORDER BY 1

저작자표시

'SQL SERVER' 카테고리의 다른 글

실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
posted by bedbmsguru

링크드 서버(linked Server)

2018. 10. 26. 21:51 SQL SERVER

 

--Linked Server 생성

sp_addlinkedserver'test1','MSSQL','SQLOLEDB','아이피,포트','디비이름'

sp_addlinkedsrvlogin 'test1', 'false',  'local login', 'remote login', 'remote password'

 
 
--rpc out 을 열어줘야 할 경우
--link이름.디비이름.스키마.procedure 로 실행하기 위해서는 TRUE로 해야함
 EXEC sp_serveroption '링크드서버명', 'RPC OUT', TRUE

 

 

--링크드서버 계정삭제sp_droplinkedsrvlogintest_oracle,hyun_user
--링크드서버 삭제sp_dropserver test_oracle--계정과 함께 삭제sp_dropserver 'test1' , 'droplogins'

 

--링크드서버 리스트 보는 쿼리

SELECT ss .server_id,
       ss. NAME,
       'Server ' = CASE ss. server_id
                     WHEN 0 THEN 'Current Server'
                     ELSE 'Remote Server'
                   END,
       ss. product,
       ss. provider,
       ss. catalog,
       'Local Login ' = CASE sl. uses_self_credential
                          WHEN 1 THEN 'Uses Self Credentials'
                          ELSE ssp .NAME
                        END,
       'Remote Login Name' = sl .remote_name,
       'RPC Out Enabled' = CASE ss. is_rpc_out_enabled
                             WHEN 1 THEN 'True'
                             ELSE 'False'
                           END,
       'Data Access Enabled' = CASE ss. is_data_access_enabled
                                 WHEN 1 THEN 'True'
                                 ELSE 'False'
                               END,
       ss. modify_date
FROM   sys .servers ss
       LEFT JOIN sys .linked_logins sl
              ON ss .server_id = sl .server_id
       LEFT JOIN sys .server_principals ssp
              ON ssp .principal_id = sl .local_principal_id 

저작자표시

'SQL SERVER' 카테고리의 다른 글

SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
MDF DATA파일 사용량 조회  (0) 2018.10.26
Find Missing Index  (0) 2018.10.26
posted by bedbmsguru

티스토리툴바