블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

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

CPU 사용율 높은 쿼리 확인

2018. 10. 27. 22:39 SQL SERVER


SELECT 

-- using statement_start_offset and

-- statement_end_offset we get the query text

-- from inside the entire batch
                                 DB_NAME(qt .dbid) dbname,
                                 OBJECT_NAME(qt .objectid) [object_name],
        SUBSTRING(qt .TEXT, ( qs.statement_start_offset / 2 ) + 1,

         ( ( CASE qs.statement_end_offset

               WHEN - 1 THEN DATALENGTH(qt .TEXT)

               ELSE qs .statement_end_offset

             END - qs. statement_start_offset ) / 2 ) + 1) AS [Text] ,

     qs.execution_count ,

     qs.total_logical_reads ,

     qs.last_logical_reads ,

     qs.total_logical_writes ,

     qs.last_logical_writes ,

     qs.total_worker_time ,

     qs.last_worker_time ,

-- converting microseconds to seconds

     qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,

     qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,

     qs.last_execution_time ,

     qp.query_plan

FROM sys .dm_exec_query_stats qs
-- Retrieve the query text
     CROSS APPLY sys. dm_exec_sql_text(qs .sql_handle) qt
        -- Retrieve the query plan
     CROSS APPLY sys. dm_exec_query_plan(qs .plan_handle) qp
ORDER BY qs. total_worker_time DESC -- CPU time



  

저작자표시

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

DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
posted by bedbmsguru

tempdb 경합 모니터링

2018. 10. 27. 22:38 SQL SERVER
SELECT
   session_id,
   wait_type,
   wait_duration_ms,
   blocking_session_id,
   resource_description,
   ResourceType = CASE
   WHEN PageID = 1 OR PageID % 8088 = 0 THEN 'Is PFS Page'
   WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'Is GAM Page'
   WHEN PageID = 3 OR (PageID - 1) % 511232 = 0 THEN 'Is SGAM Page'
       ELSE 'Is Not PFS, GAM, or SGAM page'
   END
FROM (  SELECT 
           session_id,
           wait_type,
           wait_duration_ms,
           blocking_session_id,
           resource_description,
           CAST(RIGHT(resource_description , LEN (resource_description)
           - CHARINDEX (':', resource_description, 3)) AS INT ) AS PageID
       FROM sys .dm_os_waiting_tasks
       WHERE wait_type LIKE 'PAGE%LATCH_%'
         AND resource_description LIKE '2:%'
) AS tab;

 

 

처리하지 못하고 대기중인 쿼리 조회

 

SELECT

[owt].[session_id],

[owt].[exec_context_id],

[owt].[wait_duration_ms],

[owt].[wait_type],

[owt].[blocking_session_id],

[owt].[resource_description],

CASE [owt].[wait_type]

WHEN N'CXPACKET' THEN

RIGHT ([owt].[resource_description],

CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)

ELSE NULL

END AS [Node ID],

[es].[program_name],

[est].text,

[er].[database_id],

[eqp].[query_plan],

[er].[cpu_time]

FROM sys.dm_os_waiting_tasks [owt]

INNER JOIN sys.dm_exec_sessions [es] ON

[owt].[session_id] = [es].[session_id]

INNER JOIN sys.dm_exec_requests [er] ON

[es].[session_id] = [er].[session_id]

OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]

OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]

WHERE

[es].[is_user_process] = 1

ORDER BY

[owt].[session_id],

[owt].[exec_context_id];

GO

 

https://sungwookkang.com/834

저작자표시

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

패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
posted by bedbmsguru

SQL SERVER 로그  정보(사용량, usage)

2018. 10. 27. 22:38 SQL SERVER

dbcc loginfo --로그정보
dbcc sqlperf( logspace); --로그 사용량 확인


--마지막 로그백업 조회
SELECT   d. name,
         MAX(b .backup_finish_date) AS backup_finish_date
FROM     master .sys. sysdatabases d
         LEFT OUTER JOIN msdb ..backupset b
         ON       b.database_name = d. name
         AND      b. type          = 'D'
GROUP BY d.name
ORDER BY backup_finish_date DESC

저작자표시

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

CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
posted by bedbmsguru

bufferpool(버퍼풀) 메모리 사용량

2018. 10. 27. 22:37 SQL SERVER

--DB별 버퍼풀 사용량
SELECT 
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)ORDER BY [Cached Size (MB)] 

DESC OPTION (RECOMPILE);

--DB에서 테이블별 버퍼풀 사용량
;WITH src AS
(
   SELECT
       [Object] = o.name,
       [Type] = o.type_desc,
       [Index] = COALESCE(i.name, ''),
       [Index_Type] = i.type_desc,
       p.[object_id],
       p.index_id,
       au.allocation_unit_id
   FROM
       sys.partitions AS p
   INNER JOIN
       sys.allocation_units AS au
       ON p.hobt_id = au.container_id
   INNER JOIN
       sys.objects AS o
       ON p.[object_id] = o.[object_id]
   INNER JOIN
       sys.indexes AS i
       ON o.[object_id] = i.[object_id]
       AND p.index_id = i.index_id
   WHERE
       au.[type] IN (1,2,3)
       AND o.is_ms_shipped = 0
)
SELECT
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type,
   buffer_pages = COUNT_BIG(b.page_id),
   buffer_mb = COUNT_BIG(b.page_id) / 128
FROM
   src
INNER JOIN
   sys.dm_os_buffer_descriptors AS b
   ON src.allocation_unit_id = b.allocation_unit_id
WHERE
   b.database_id = DB_ID()
GROUP BY
   src.[Object],
   src.[Type],
   src.[Index],
   src.Index_Type
ORDER BY
   buffer_pages DESC;

저작자표시

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

tempdb 경합 모니터링  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
posted by bedbmsguru

통계 업데이트 날짜 조회하기

2018. 10. 27. 22:37 SQL SERVER

SELECT object_name (sp. object_id) as object_name ,name as stats_name , sp .stats_id,
    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys .stats AS s
CROSS APPLY sys. dm_db_stats_properties(s .object_id, s. stats_id) AS sp
WHERE sp .object_id > 100;

그 이외버전
SELECT schema_name (schema_id) AS SchemaName,  object_name(o .object_id) AS ObjectName,
    i.name AS IndexName, index_id, o.type ,
    STATS_DATE(o .object_id, index_id) AS statistics_update_date
FROM sys .indexes i join sys. objects o
       on i .object_id = o .object_id
WHERE o .object_id > 100 AND index_id > 0
  AND is_ms_shipped = 0;
  

저작자표시

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

SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
posted by bedbmsguru

Procedure 실행횟수 확인

2018. 10. 27. 22:34 SQL SERVER

SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
ORDER BY AVG_LOGICAL_READS DESC
  


--정기적 체크를 위해 Batch Job으로 만들경우의 소스
CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT](
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT_TEMP] (
                 [DBName] [nvarchar] (128) NULL,
                 [SCHEMA_NAME] [nvarchar] (128) NULL,
                 [OBJECT_NAME] [nvarchar] (128) NULL,
                 [Execution_Count] [bigint] NULL,
                 [AVG_CPU] [bigint] NULL,
                 [AVG_ELAPSED] [bigint] NULL,
                 [AVG_LOGICAL_READS] [bigint] NULL,
                 [AVG_LOGICAL_WRITES] [bigint] NULL,
                 [AVG_PHYSICAL_READS] [bigint] NULL,
                 [write_time] [datetime] NOT NULL
) ON [PRIMARY]
  

create procedure usp_chk_proc_execute_count
AS
SET NOCOUNT ON;
INSERT INTO TBL_CHECK_PROC_EXECUTION_COUNT (DBName, [SCHEMA_NAME], [OBJECT_NAME], Execution_Count,
AVG_CPU, AVG_ELAPSED, AVG_LOGICAL_READS, AVG_LOGICAL_WRITES, AVG_PHYSICAL_READS, write_time)
SELECT A .DBName, B. [SCHEMA_NAME], B.[OBJECT_NAME] , B .Execution_Count - A .Execution_Count AS Execution_Count
,B. AVG_CPU,B .AVG_ELAPSED, B. AVG_LOGICAL_READS, B.AVG_LOGICAL_WRITES ,B. AVG_PHYSICAL_READS
, getdate () as write_time
FROM TBL_CHECK_PROC_EXECUTION_COUNT_TEMP AS A
inner JOIN
(
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
HAVING sum (execution_count) > 50000
)AS B
ON A .DBName = B .DBName
AND A .[OBJECT_NAME] = B .[OBJECT_NAME]

truncate table TBL_CHECK_PROC_EXECUTION_COUNT_TEMP

insert into TBL_CHECK_PROC_EXECUTION_COUNT_TEMP
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
      ,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
      ,sum( execution_count) AS Execution_Count
      ,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
      ,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
      ,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
      ,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
      ,sum( total_physical_reads)  / sum (execution_count) AS AVG_PHYSICAL_READS
                  , GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
      ,OBJECT_SCHEMA_NAME( object_id,database_id )
      ,OBJECT_NAME( object_id,database_id )
--ORDER BY AVG_LOGICAL_READS DESC

저작자표시

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

bufferpool(버퍼풀) 메모리 사용량  (0) 2018.10.27
통계 업데이트 날짜 조회하기  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
posted by bedbmsguru

실행중인 쿼리 확인

2018. 10. 27. 22:34 SQL SERVER
SELECT
SERVERPROPERTY('ServerName') AS sql_instance,
s.session_id
,r.STATUS
,CONVERT(varchar,r.start_time,20) start_time
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time 
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time 
,r.open_transaction_count
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY start_time
  

 

저작자표시

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

통계 업데이트 날짜 조회하기  (0) 2018.10.27
Procedure 실행횟수 확인  (0) 2018.10.27
SERVER property를 일괄로 보여주는 procedure  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
posted by bedbmsguru

SERVER property를 일괄로 보여주는 procedure

2018. 10. 27. 22:33 SQL SERVER

alter  proc sp_list_server_property
AS
DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)
INSERT INTO @props( propertyname )
SELECT 'BuildClrVersion'
UNION
SELECT 'Collation'
UNION
SELECT 'CollationID'
UNION
SELECT 'ComparisonStyle'
UNION
SELECT 'ComputerNamePhysicalNetBIOS'
UNION
SELECT 'Edition'
UNION
SELECT 'HadrManagerStatus'
UNION
SELECT 'EngineEdition'
UNION
SELECT 'InstanceName'
UNION
SELECT 'IsClustered'
UNION
SELECT 'IsFullTextInstalled'
UNION
SELECT 'IsIntegratedSecurityOnly'
UNION
SELECT 'IsSingleUser'
UNION
SELECT 'LCID'
UNION
SELECT 'LicenseType'
UNION
SELECT 'MachineName'
UNION
SELECT 'NumLicenses'
UNION
SELECT 'ProcessID'
UNION
SELECT 'ProductVersion'
UNION
SELECT 'ProductLevel'
UNION
SELECT 'ResourceLastUpdateDateTime'
UNION
SELECT 'ResourceVersion'
UNION
SELECT 'ServerName'
UNION
SELECT 'SqlCharSet'
UNION
SELECT 'SqlCharSetName'
UNION
SELECT 'SqlSortOrder'
UNION
SELECT 'SqlSortOrderName'
UNION
SELECT 'FilestreamShareName'
UNION
SELECT 'FilestreamConfiguredLevel'
UNION
SELECT 'FilestreamEffectiveLevel'
 
SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props
 UNION ALL
SELECT 'EditionID' AS  propertyname,
                                 CASE SERVERPROPERTY ( 'EditionID' )
                                                 WHEN 1804890536 THEN 'Enterprise'
                                                 WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'
                                                 WHEN 610778273 THEN 'Enterprise Evaluation'
                                                 WHEN 284895786 THEN 'Business Intelligence'
                                                 WHEN - 2117995310 THEN 'Developer'
                                                 WHEN - 1592396055 THEN 'Express'
                                                 WHEN - 133711905 THEN 'Express with Advanced Services'
                                                 WHEN - 1534726760 THEN 'Standard'
                                                 WHEN 1293598313 THEN 'WEB'
                                 END AS EditionId
UNION ALL                                                                                                           
SELECT 'IsHadrEnabled' AS propertyname,
                                 CASE SERVERPROPERTY ( 'IsHadrEnabled' )
                                                 WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'
                                                 WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'
                                 END AS IsHadrEnabled
UNION ALL                                                                                                           
SELECT 'HadrManagerStatus' AS propertyname,
                                 CASE SERVERPROPERTY ( 'HadrManagerStatus' )
                                                 WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'
                                                 WHEN 1 THEN '시작되어 실행 중입니다.'
                                                 WHEN 2 THEN '시작되지 않고 실패했습니다.'
                                 END AS HadrManagerStatus
  

저작자표시

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

Procedure 실행횟수 확인  (0) 2018.10.27
실행중인 쿼리 확인  (0) 2018.10.27
Index 생성이 안되어 있는 Foreign key 찾기  (0) 2018.10.27
링크드 서버(linked Server)  (0) 2018.10.26
Linked Server 연결 테스트(TEST)  (0) 2018.10.26
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

티스토리툴바