블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

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
2021. 11. 23. 16:41 SQL SERVER

링크 참조

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

 

SQL Server Wait Statistics: Tell me where it hurts

Learn how to use SQL Server Wait Statistics to help diagnose performance problems.

www.sqlskills.com

 

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        -- These wait types are almost 100% never a problem and so they are
        -- filtered out to avoid them skewing the results. Click on the URL
        -- for more information.
        N'BROKER_EVENTHANDLER', -- https://www.sqlskills.com/help/waits/BROKER_EVENTHANDLER
        N'BROKER_RECEIVE_WAITFOR', -- https://www.sqlskills.com/help/waits/BROKER_RECEIVE_WAITFOR
        N'BROKER_TASK_STOP', -- https://www.sqlskills.com/help/waits/BROKER_TASK_STOP
        N'BROKER_TO_FLUSH', -- https://www.sqlskills.com/help/waits/BROKER_TO_FLUSH
        N'BROKER_TRANSMITTER', -- https://www.sqlskills.com/help/waits/BROKER_TRANSMITTER
        N'CHECKPOINT_QUEUE', -- https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
        N'CHKPT', -- https://www.sqlskills.com/help/waits/CHKPT
        N'CLR_AUTO_EVENT', -- https://www.sqlskills.com/help/waits/CLR_AUTO_EVENT
        N'CLR_MANUAL_EVENT', -- https://www.sqlskills.com/help/waits/CLR_MANUAL_EVENT
        N'CLR_SEMAPHORE', -- https://www.sqlskills.com/help/waits/CLR_SEMAPHORE
 
        -- Maybe comment this out if you have parallelism issues
        N'CXCONSUMER', -- https://www.sqlskills.com/help/waits/CXCONSUMER
 
        -- Maybe comment these four out if you have mirroring issues
        N'DBMIRROR_DBM_EVENT', -- https://www.sqlskills.com/help/waits/DBMIRROR_DBM_EVENT
        N'DBMIRROR_EVENTS_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_EVENTS_QUEUE
        N'DBMIRROR_WORKER_QUEUE', -- https://www.sqlskills.com/help/waits/DBMIRROR_WORKER_QUEUE
        N'DBMIRRORING_CMD', -- https://www.sqlskills.com/help/waits/DBMIRRORING_CMD
        N'DIRTY_PAGE_POLL', -- https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
        N'DISPATCHER_QUEUE_SEMAPHORE', -- https://www.sqlskills.com/help/waits/DISPATCHER_QUEUE_SEMAPHORE
        N'EXECSYNC', -- https://www.sqlskills.com/help/waits/EXECSYNC
        N'FSAGENT', -- https://www.sqlskills.com/help/waits/FSAGENT
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', -- https://www.sqlskills.com/help/waits/FT_IFTS_SCHEDULER_IDLE_WAIT
        N'FT_IFTSHC_MUTEX', -- https://www.sqlskills.com/help/waits/FT_IFTSHC_MUTEX
  
       -- Maybe comment these six out if you have AG issues
        N'HADR_CLUSAPI_CALL', -- https://www.sqlskills.com/help/waits/HADR_CLUSAPI_CALL
        N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', -- https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
        N'HADR_LOGCAPTURE_WAIT', -- https://www.sqlskills.com/help/waits/HADR_LOGCAPTURE_WAIT
        N'HADR_NOTIFICATION_DEQUEUE', -- https://www.sqlskills.com/help/waits/HADR_NOTIFICATION_DEQUEUE
        N'HADR_TIMER_TASK', -- https://www.sqlskills.com/help/waits/HADR_TIMER_TASK
        N'HADR_WORK_QUEUE', -- https://www.sqlskills.com/help/waits/HADR_WORK_QUEUE
 
        N'KSOURCE_WAKEUP', -- https://www.sqlskills.com/help/waits/KSOURCE_WAKEUP
        N'LAZYWRITER_SLEEP', -- https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
        N'LOGMGR_QUEUE', -- https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
        N'MEMORY_ALLOCATION_EXT', -- https://www.sqlskills.com/help/waits/MEMORY_ALLOCATION_EXT
        N'ONDEMAND_TASK_QUEUE', -- https://www.sqlskills.com/help/waits/ONDEMAND_TASK_QUEUE
        N'PARALLEL_REDO_DRAIN_WORKER', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_DRAIN_WORKER
        N'PARALLEL_REDO_LOG_CACHE', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_LOG_CACHE
        N'PARALLEL_REDO_TRAN_LIST', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_TRAN_LIST
        N'PARALLEL_REDO_WORKER_SYNC', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_SYNC
        N'PARALLEL_REDO_WORKER_WAIT_WORK', -- https://www.sqlskills.com/help/waits/PARALLEL_REDO_WORKER_WAIT_WORK
        N'PREEMPTIVE_OS_FLUSHFILEBUFFERS', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FLUSHFILEBUFFERS
        N'PREEMPTIVE_XE_GETTARGETSTATE', -- https://www.sqlskills.com/help/waits/PREEMPTIVE_XE_GETTARGETSTATE
        N'PVS_PREALLOCATE', -- https://www.sqlskills.com/help/waits/PVS_PREALLOCATE
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', -- https://www.sqlskills.com/help/waits/PWAIT_ALL_COMPONENTS_INITIALIZED
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT', -- https://www.sqlskills.com/help/waits/PWAIT_DIRECTLOGCONSUMER_GETNEXT
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', -- https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
        N'QDS_ASYNC_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_ASYNC_QUEUE
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', -- https://www.sqlskills.com/help/waits/QDS_SHUTDOWN_QUEUE
        N'REDO_THREAD_PENDING_WORK', -- https://www.sqlskills.com/help/waits/REDO_THREAD_PENDING_WORK
        N'REQUEST_FOR_DEADLOCK_SEARCH', -- https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
        N'RESOURCE_QUEUE', -- https://www.sqlskills.com/help/waits/RESOURCE_QUEUE
        N'SERVER_IDLE_CHECK', -- https://www.sqlskills.com/help/waits/SERVER_IDLE_CHECK
        N'SLEEP_BPOOL_FLUSH', -- https://www.sqlskills.com/help/waits/SLEEP_BPOOL_FLUSH
        N'SLEEP_DBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DBSTARTUP
        N'SLEEP_DCOMSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_DCOMSTARTUP
        N'SLEEP_MASTERDBREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERDBREADY
        N'SLEEP_MASTERMDREADY', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERMDREADY
        N'SLEEP_MASTERUPGRADED', -- https://www.sqlskills.com/help/waits/SLEEP_MASTERUPGRADED
        N'SLEEP_MSDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_MSDBSTARTUP
        N'SLEEP_SYSTEMTASK', -- https://www.sqlskills.com/help/waits/SLEEP_SYSTEMTASK
        N'SLEEP_TASK', -- https://www.sqlskills.com/help/waits/SLEEP_TASK
        N'SLEEP_TEMPDBSTARTUP', -- https://www.sqlskills.com/help/waits/SLEEP_TEMPDBSTARTUP
        N'SNI_HTTP_ACCEPT', -- https://www.sqlskills.com/help/waits/SNI_HTTP_ACCEPT
        N'SOS_WORK_DISPATCHER', -- https://www.sqlskills.com/help/waits/SOS_WORK_DISPATCHER
        N'SP_SERVER_DIAGNOSTICS_SLEEP', -- https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
        N'SQLTRACE_BUFFER_FLUSH', -- https://www.sqlskills.com/help/waits/SQLTRACE_BUFFER_FLUSH
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', -- https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
        N'SQLTRACE_WAIT_ENTRIES', -- https://www.sqlskills.com/help/waits/SQLTRACE_WAIT_ENTRIES
        N'VDI_CLIENT_OTHER', -- https://www.sqlskills.com/help/waits/VDI_CLIENT_OTHER
        N'WAIT_FOR_RESULTS', -- https://www.sqlskills.com/help/waits/WAIT_FOR_RESULTS
        N'WAITFOR', -- https://www.sqlskills.com/help/waits/WAITFOR
        N'WAITFOR_TASKSHUTDOWN', -- https://www.sqlskills.com/help/waits/WAITFOR_TASKSHUTDOWN
        N'WAIT_XTP_RECOVERY', -- https://www.sqlskills.com/help/waits/WAIT_XTP_RECOVERY
        N'WAIT_XTP_HOST_WAIT', -- https://www.sqlskills.com/help/waits/WAIT_XTP_HOST_WAIT
        N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', -- https://www.sqlskills.com/help/waits/WAIT_XTP_OFFLINE_CKPT_NEW_LOG
        N'WAIT_XTP_CKPT_CLOSE', -- https://www.sqlskills.com/help/waits/WAIT_XTP_CKPT_CLOSE
        N'XE_DISPATCHER_JOIN', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_JOIN
        N'XE_DISPATCHER_WAIT', -- https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT
        N'XE_TIMER_EVENT' -- https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
        )
    AND [waiting_tasks_count] > 0
    )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
    CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO
posted by bedbmsguru
2021. 10. 21. 14:36 SQL SERVER

disable 명령어가 있지는 않지만 아래처럼 data access를 막아서 사용이 불가능하도록 하는게 가능함

 

-- Disable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'false'

-- Enable data access

EXEC sp_serveroption @server = 'linked_server_name'

,@optname = 'data access'

,@optvalue = 'true'

 

https://www.sqlservercentral.com/forums/topic/can-linked-server-be-disabled

posted by bedbmsguru
2021. 5. 25. 15:23 SQL SERVER

1. 인덱스 힌트를 제거한다. 

2. NVL 함수를  ISNULL 혹은 IFNULL 함수로 변경한다. 

3. SYSDATE 함수를 NOW() 함수로 변경한다. 

4. TRUNC(SYSDATE)를 CONVERT(DATE,GETDATE(),12)로 변경한다. 
   trunc(sysdate)를 하면 시간이 잘린 연,월,일이 나온다.  예)19/05/09

   이와 같은 역할을 하는 CONVERT를 사용한다.

5. Outerjoin 변경  

[Oracle] 
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+); 
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2; 

[MSSQL] 
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2; 
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2; 

!! From절에서 테이블명 중간에 , 가 없다는것과 ON 구문이 From절 바로 뒤에 오게 해야하는것에 주의  !!


6. TO_CHAR()를 CONVERT()으로 변환한다. 

    TO_CHAR(sysdate,'YYYYMMDD')의 경우 

    CONVERT(VARCHAR,GETDATE(),12)로 변환  .

 

아래는 추가로 다양한 형식에 맞게 사용

 

SELECT CONVERT(VARCHAR,getdate(),(120))   --//2019-05-09 2019-05-09 11:55:35
,CONVERT(VARCHAR,getdate(),(102))  --//2019.05.09 
,CONVERT(VARCHAR,getdate(),(12))  --//190509
,CONVERT(VARCHAR,getdate(),(23)) --//2019-05-09
,LEFT(CONVERT(VARCHAR,getdate(),(23)),7) --//2019-05
,LEFT(CONVERT(VARCHAR,getdate(),(3)),2) //09
;
  


7. TO_DATE()를 CONVERT()으로 변환한다. 
SELECT CONVERT(DATE,getdate(),(120))   --//2019-05-09 2019-05-09 11:55:35
,CONVERT(DATE,getdate(),(102))  --//2019.05.09 
,CONVERT(DATE,getdate(),(12))  --//190509
,CONVERT(DATE,getdate(),(23)) --//2019-05-09
,LEFT(CONVERT(DATE,getdate(),(23)),7) --//2019-05
,LEFT(CONVERT(DATE,getdate(),(3)),2) //09


8. Where rownum <= 10 을 ROW_NUMBER()OVER() 로 변환 
  이때 주의 할 점은 SELECT 절에 TOP 100 PERCENT 붙여주어야 누락없이 다 뽑음


9. Subquery에서 alias가 없다면 붙인다. 
   예를 들면 
    select a, b from ( select a, b from ttt)  
--> select t.a, t.b from ( select a, b from ttt) t 

  

10. DELETE 테이블명 쿼리문을 DELETE FROM 테이블명 쿼리문으로 바꾸어준다. 

11. Merge into의 경우는 쿼리문을 분리하여 DAO단에서 처리로직으로 변경해준다. 

     변경방법은 아래 두가지중에 하나를 선택해도 되고 다른 방법을 써도 된다. 


  

      (1) 조건이 맞는지(MATCHED)에 대한 SELECT문을 실행시켜서 결과값이 

           있으면 UPDATE문을 실행시켜 주고 결과값이 없으면 INSERT문을 

           실행시켜준다. 

           % rs.next()로 결과값이 있는지 판단 

  

      (2) UPDATE문을 실행해서 UPDATE가 일어나면 그대로 가고 UPDATE가 

           일어나지 않으면 INSERT문을 실행시킨다. 

           % ps.executeUpdate() > 0으로 UPDATE 판단 



o 오라클 MergeInto 구문 

MERGE INTO SERVICE_LOG FSL1 
USING 
(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') AS DAYS, 'LS003' AS SERVICE_TYPE FROM DUAL ) FSL2 
ON (FSL1.DAYS = FSL2.DAYS AND FSL1.SERVICE_TYPE = FSL2.SERVICE_TYPE) 
WHEN MATCHED THEN 
UPDATE SET FSL1.AGE_10_CNT = FSL1.AGE_10_CNT + 1 
WHEN NOT MATCHED THEN 
INSERT (FSL1.DAYS, FSL1.SERVICE_TYPE, FSL1.AGE_10_CNT) 
VALUES (FSL2.DAYS, FSL2.SERVICE_TYPE, 1) 

-------------------------------------------------------------------------------------- 

o MySQL 구문으로 변환처리 1 
  - DAO처리에서 조건이 맞는지(MATCHED) 안맞는지(NOT MATCHED) 비교하는 SELECT 쿼리문을 실행하고 
    그 결과에 따라서 업데이트와 인서트 처리를 해준다. SELECT 결과값이 있다면 업데이트, 
    SELECT 결과값이 없다면 인서트(rs.next()로 결과값이 있는지 판단) 
  
if(getDAO().existServiceLog(conn, serviceType)) { 
    return getDAO().updateServiceLog(conn, memberInfo, serviceType); 
}else{ 
    return getDAO().insertServiceLog(conn, memberInfo, serviceType); 


1) public boolean existServiceLog(Connection conn, String serviceType) 

   SELECT SERVICE_TYPE 
   FROM SERVICE_LOG USE INDEX(PRIMARY) 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 

2) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType) 

   UPDATE SERVICE_LOG 
   SET AGE_10_CNT = AGE_10_CNT+1 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 

3) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType) 

 



   INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT) 

 


   VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0) 

--------------------------------------------------------------------------------------------------------- 
o MySQL 구문으로 변환처리 2 
  - DAO처리에서 업데이트가 되었는지를 비교하는 구문을 실행해서 업데이트가 일어나면 그대로 진행되고 
    업데이트 일어나지 않으면 인서트를 실행해 준다(ps.executeUpdate() > 0으로 UPDATE 판단) 

 



if(!getDAO.updateServiceLog(conn, memberInfo, serviceType)){ 

 


    getDAO.insertServiceLog(conn, memberInfo, serviceType); 



1) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType) 

   UPDATE SERVICE_LOG 
   SET AGE_10_CNT = AGE_10_CNT+1 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 
   을 실행해서  ps.executeUpdate() > 0;을 결과로 리턴한다. 업데이트가 
   일어나지 않으면 false를 반환하고 insert가 일어난다. 

2) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType) 

   INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT) 

 


   VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0) 



출처: https://limeeyojung.tistory.com/29 [초보개발자의 코딩기록]

 

posted by bedbmsguru
2021. 4. 26. 09:26 SQL SERVER

ALTER DATABASE [DB명] SET ENCRYPTION OFF;
GO

use DB명
DROP DATABASE ENCRYPTION KEY

 


-- 4. 암호화 상태 확인

SELECT DB_NAME(e.database_id) AS DatabaseName, 
            e.database_id, 
            e.encryption_state, 
    CASE e.encryption_state 

     WHEN 0 THEN 'No database encryption key present, no encryption' 
                WHEN 1 THEN 'Unencrypted' 
                WHEN 2 THEN 'Encryption in progress' 
                WHEN 3 THEN 'Encrypted' 
                WHEN 4 THEN 'Key change in progress' 
                WHEN 5 THEN 'Decryption in progress' 
    END AS encryption_state_desc, 
            c.name, 

     e.percent_complete 
    FROM sys.dm_database_encryption_keys AS e 

   LEFT JOIN master.sys.certificates AS c

    ON e.encryptor_thumbprint = c.thumbprint 

posted by bedbmsguru
2021. 4. 22. 15:54 SQL SERVER

아래 참조

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO WITH DB_CPU_Stats AS ( SELECT DatabaseID, isnull(DB_Name(DatabaseID),case DatabaseID when 32767 then 'Internal ResourceDB' else CONVERT(varchar(255),DatabaseID)end) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms], SUM(total_logical_reads) AS [Logical_Reads], SUM(total_logical_writes) AS [Logical_Writes], SUM(total_logical_reads+total_logical_writes) AS [Logical_IO], SUM(total_physical_reads) AS [Physical_Reads], SUM(total_elapsed_time) AS [Duration_MicroSec], SUM(total_clr_time) AS [CLR_Time_MicroSec], SUM(total_rows) AS [Rows_Returned], SUM(execution_count) AS [Execution_Count], count(*) 'Plan_Count' FROM sys.dm_exec_query_stats AS qs CROSS APPLY ( SELECT CONVERT(int, value) AS [DatabaseID] FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS F_DB GROUP BY DatabaseID ) SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [Rank_CPU], DatabaseName, [CPU_Time_Hr] = convert(decimal(15,2),([CPU_Time_Ms]/1000.0)/3600) , CAST([CPU_Time_Ms] * 1.0 / SUM(case [CPU_Time_Ms] when 0 then 1 else [CPU_Time_Ms] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU_Percent], [Duration_Hr] = convert(decimal(15,2),([Duration_MicroSec]/1000000.0)/3600) , CAST([Duration_MicroSec] * 1.0 / SUM(case [Duration_MicroSec] when 0 then 1 else [Duration_MicroSec] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Duration_Percent], [Logical_Reads], CAST([Logical_Reads] * 1.0 / SUM(case [Logical_Reads] when 0 then 1 else [Logical_Reads] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Reads_Percent], [Rows_Returned], CAST([Rows_Returned] * 1.0 / SUM(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Rows_Returned_Percent], [Reads_Per_Row_Returned] = [Logical_Reads]/(case [Rows_Returned] when 0 then 1 else [Rows_Returned] end), [Execution_Count], CAST([Execution_Count] * 1.0 / SUM(case [Execution_Count] when 0 then 1 else [Execution_Count] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Execution_Count_Percent], [Physical_Reads], CAST([Physical_Reads] * 1.0 / SUM(case [Physical_Reads] when 0 then 1 else [Physical_Reads] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physcal_Reads_Percent], [Logical_Writes], CAST([Logical_Writes] * 1.0 / SUM(case [Logical_Writes] when 0 then 1 else [Logical_Writes] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_Writes_Percent], [Logical_IO], CAST([Logical_IO] * 1.0 / SUM(case [Logical_IO] when 0 then 1 else [Logical_IO] end) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Logical_IO_Percent], [CLR_Time_MicroSec], CAST([CLR_Time_MicroSec] * 1.0 / SUM(case [CLR_Time_MicroSec] when 0 then 1 else [CLR_Time_MicroSec] end ) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CLR_Time_Percent], [CPU_Time_Ms],[CPU_Time_Ms]/1000 [CPU_Time_Sec], [Duration_MicroSec],[Duration_MicroSec]/1000000 [Duration_Sec] FROM DB_CPU_Stats WHERE DatabaseID > 4 -- system databases AND DatabaseID <> 32767 -- ResourceDB ORDER BY [Rank_CPU] OPTION (RECOMPILE);

 

dba.stackexchange.com/questions/83058/how-to-get-cpu-usage-by-database-for-particular-instance

posted by bedbmsguru
2021. 4. 6. 14:43 SQL SERVER

USE msdb

GO

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_log;

GO

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

GO

 

www.sqlrx.com/large-msdb-database-from-sysmaintplan_logdetail-table/

posted by bedbmsguru
2020. 12. 9. 16:32 SQL SERVER

1.  sp_lock 실행
  Lock 내용 확인. Mode가 X 인 것들을 확인하고, spid 를 기억해 둔다.

  보통 spid 가 여러개 중복되어 출력되는 경우가 많다.
  예) exec sp_lock


2. dbcc inputbuffer(spid)
  클라이언트에서 MSSQL로 보낸 최종 명령문을 표시한다.

  1번에서 Mode 가 X 인 것의 spid를 입력하면 해당 내용이 출력된다.
  예) dbcc iniputbuffer(60)

 

3. sp_who spid

  1번에서 확인된 spid 를 입력하면 관련 정보를 표시해 준다.

  예) exec sp_who 60

 

4. kill spid

  Lock을 확인했으면 해당 spid 를 입력하여 해당 프로세스를 끝낸다.

  예) kill 60

 

 



출처: https://walkingfox.tistory.com/27 [걸어가는 여우]

posted by bedbmsguru
2020. 12. 8. 16:00 SQL SERVER

-- When will a SQL login password expire?  SELECT SL.name AS LoginName        ,LOGINPROPERTY (SL.name'PasswordLastSetTime'AS PasswordLastSetTime        ,LOGINPROPERTY (SL.name'DaysUntilExpiration'AS DaysUntilExpiration        ,DATEADD(ddCONVERT(intLOGINPROPERTY (SL.name'DaysUntilExpiration'))                   , CONVERT(datetimeLOGINPROPERTY (SL.name'PasswordLastSetTime'))) AS PasswordExpiration        ,SL.is_policy_checked AS IsPolicyChecked        ,LOGINPROPERTY (SL.name'IsExpired'AS IsExpired        ,LOGINPROPERTY (SL.name'IsMustChange'AS IsMustChange        ,LOGINPROPERTY (SL.name'IsLocked'AS IsLocked        ,LOGINPROPERTY (SL.name'LockoutTime'AS LockoutTime        ,LOGINPROPERTY (SL.name'BadPasswordCount'AS BadPasswordCount        ,LOGINPROPERTY (SL.name'BadPasswordTime'AS BadPasswordTime        ,LOGINPROPERTY (SL.name'HistoryLength'AS HistoryLength  FROM sys.sql_logins AS SL  WHERE is_expiration_checked = 1  ORDER BY LOGINPROPERTY (SL.name'PasswordLastSetTime'DESC

 

 

http://gallery.technet.microsoft.com/scriptcenter/When-will-a-SQL-login-d6fbb6df

posted by bedbmsguru
2020. 10. 13. 11:09 SQL SERVER

SELECT CAST(event_Data AS xml) AS event_data FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

 

DROP TABLE IF EXISTS #myXeData

 

SELECT CAST(event_Data AS XML) AS StatementData
INTO #myXeData
FROM sys.fn_xe_file_target_read_file('R:\SqlStatements*.xel', NULL, NULL, NULL)

 

 

SELECT * FROM #myXeData

 

 

SELECT
StatementData.value('(event/@name)[1]', 'varchar(50)') AS event_name
,StatementData.value('(event/@timestamp)[1]', 'datetime2(0)') AS time_stamp
,StatementData.value('(event/action[@name="database_name"]/value)[1]','nvarchar(128)') AS database_name_
,StatementData.value('(event/data[@name="duration"]/value)[1]','bigint') / 1000 AS duration_ms
,StatementData.value('(event/data[@name="cpu_time"]/value)[1]','bigint') /1000 AS cpu_ms
,StatementData.value('(event/data[@name="physical_reads"]/value)[1]','bigint') AS physical_reads
,StatementData.value('(event/data[@name="logical_reads"]/value)[1]','bigint') AS logical_reads
,StatementData.value('(event/data[@name="row_count"]/value)[1]','bigint') AS row_count
,StatementData.value('(event/data[@name="statement"]/value)[1]','nvarchar(50)') AS statement_
FROM #myXeData AS evts
--ORDER BY time_stamp
ORDER BY duration_ms DESC

 

 

https://dba.stackexchange.com/questions/226704/how-to-view-xevent-event-file-data

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

sql server Lock정보 확인  (0) 2020.12.09
패스워드 만료(Expiration) 조회  (0) 2020.12.08
SQL Formatter  (0) 2020.06.05
Foreign key로 참조하는 테이블 확인 쿼리  (0) 2020.03.25
sql server 2012 Recommended Trace flag  (0) 2020.02.13
posted by bedbmsguru
2020. 6. 5. 10:45 SQL SERVER

SQL Server Management Studio에 플러그인 방식으로 설치된다.

 

가독성이 좋구만..

 

http://architectshack.com/PoorMansTSqlFormatter.ashx#License__Redistribution_17

posted by bedbmsguru