블로그 이미지
bedbmsguru

Notice

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          
  • 32,388total
  • 1today
  • 12yesterday
2018.11.30 11:44 Zabbix

참조: https://github.com/grundic/zabbix-disk-performance



1. wget https://raw.githubusercontent.com/grundic/zabbix-disk-performance/master/userparameter_diskstats.conf -O /etc/zabbix/zabbix_agentd.d/userparameter_diskstats.conf


2. wget https://raw.githubusercontent.com/grundic/zabbix-disk-performance/master/lld-disks.py -O /usr/local/bin/lld-disks.py



3. chmod 755 /usr/local/bin/lld-disks.py


4. vi /usr/local/bin/lld-disks.py


  -->skippable = {"sr", "loop", "ram"} 에 "dm-" 추가


dm- 와 sdx는 중복이므로 둘중에 하나만 하면 된다. 


5. zabbix-agent 재시작

'Zabbix' 카테고리의 다른 글

Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
SAN Switch 모니터링 적용  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
오라빅스 TableSpace 모니터링 쿼리 수정  (0) 2018.09.13
Zabbix Linux Disk IO 모니터링  (0) 2018.09.07
posted by bedbmsguru
2018.10.27 22:47 SQL SERVER

select                       distinct
                                                 ObjectType                            = o .type_desc
                                 ,                ObjectName                           = o .name
                                 ,                CodeSequence     = c_display. colid
                                 ,                Code                                        = c_display .[text]
from                         sys.objects o
inner join                 sys.syscomments c_search
                                                 on c_search .id = o .[object_id]
inner join                 sys.syscomments c_display
                                                 on c_display .id = o .[object_id]
where                      o.type_desc not in ( 'INTERNAL_TABLE','SERVICE_QUEUE' ,'SYSTEM_TABLE')
and                                          c_search.[text] like '%temp%'
order by  1, 2 , 3


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

Procedure나 Function 내용 검색  (0) 2018.10.27
SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:45 SQL SERVER

SELECT TOP 10
wait_type ,
max_wait_time_ms wait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM( wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM( signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM (wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys .dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zero wait_time
AND wait_type NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK' , 'BROKER_TASK_STOP' , 'BROKER_TO_FLUSH' ,
'SQLTRACE_BUFFER_FLUSH' ,'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP' , 'SLEEP_SYSTEMTASK' , 'SLEEP_BPOOL_FLUSH' ,
'BROKER_EVENTHANDLER' , 'XE_DISPATCHER_WAIT' , 'FT_IFTSHC_MUTEX' ,
'CHECKPOINT_QUEUE' , 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,
'BROKER_TRANSMITTER' , 'FT_IFTSHC_MUTEX' , 'KSOURCE_WAKEUP' ,
'LAZYWRITER_SLEEP' , 'LOGMGR_QUEUE' , 'ONDEMAND_TASK_QUEUE' ,
'REQUEST_FOR_DEADLOCK_SEARCH' , 'XE_TIMER_EVENT' , 'BAD_PAGE_PROCESS' ,
'DBMIRROR_EVENTS_QUEUE' , 'BROKER_RECEIVE_WAITFOR' ,
'PREEMPTIVE_OS_GETPROCADDRESS' , 'PREEMPTIVE_OS_AUTHENTICATIONOPS' ,
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC

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

Procedure나 Function 내용 검색  (0) 2018.10.27
SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:45 SQL SERVER

기본적으로 기본적으로 서버 로컬에서만 접속이 가능하다 원격에서 접속하기 위해서는 
"remote admin connections" 옵션을 활성화 하여야 한다.
클러스터 환경에서는 "remote admin connections"서버 구성 옵션을 반드시 활성화 시켜야한다.
클러스터 환경은 가상 IP를 통해 다른 IP로 연결되기 때문에 옵션을 활성화 하지 않으면 로컬 서버에서도 관리자 전용 연결을 사용할 수 없다.

SSMS에서 접속방법
File메뉴--> Database Engine Query
sqlcmd에서 접속방법
sqlcmd -S SQL02\ins02 -E -A :-A옵션이 DAC연결

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

Procedure나 Function 내용 검색  (0) 2018.10.27
SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
posted by bedbmsguru
2018.10.27 22:44 Powershell

#방화벽 ON|OFF -profile 없으면 전체(2012부터 가능) 
Set-NetFirewallProfile -Profile Domain -Enabled True | false

#방화벽 Rule ADD
New-NetFirewallRule -DisplayName "Allow SSR_PORT" -Direction Inbound -LocalPort 135-139 -Protocol TCP -Action Allow 

(2008 netsh 사용)netsh advfirewall firewall add rule name="SQL SERVER TCP 1433" dir=in action=allow protocol=TCP localport=1433

#방화벽 Rule 제거
Remove -NetFirewallRule -DisplayName "Allow SSR_PORT"


#등록된 RULE 켜고 끄기
Set-NetfirewallRule -DisplayName "Allow Inbound OpenVPN Client Requests" -Enabled True | false

$TargetComputer=New-CIMSession -Computername host1, host2
Set-NetfirewallRule -DisplayName "파일 및 프린터 공유(에코 요청 - ICMPv4-In)" -Enabled True -CimSession $TargetComputer

#관리할 remote server 지정
$TargetComputer=New-CIMSession -Computername hhiwp046, hhiwp047, hhiwp048, hhiwp049, hhiwp050, hhiwp051, hhiwp052
#Remove-NetFirewallRule -DisplayName "Allow SSR_PORT" -CimSession $TargetComputer
#New-NetFirewallRule -DisplayName "Allow SSR_PORT" -Direction Inbound -LocalPort 135-139 -Protocol TCP -Action Allow  -CimSession $TargetComputer


posted by bedbmsguru
2018.10.27 22:42 Powershell

 #Socket Server
function listen-port ($port= 7788) {
    $SEND_SMS = "0000"


   
        $endpoint = new-object System.Net.IPEndPoint ([ system.net.ipaddress]::any , $port )
        $listener = new-object System.Net.Sockets.TcpListener $endpoint
        $listener.start()
        $isRunning = $true

        while ($isRunning )
        {
           
                $client = $listener. AcceptTcpClient() # will block here until connection
                $stream = $client. GetStream();
                $reader = New-Object System.IO.StreamReader $stream
           
                  
                #$command = $reader.readLine().Substring(0, 4);
                $command = $reader. readLine() -split "//"
                $command.Length
                #$command = $reader.readLine()
                if ($command -eq $SEND_SMS)
                {
                    sendSMS -serverIP $command[ 2] -phoneArray $command [1]
                }
                else
                {
                    write-host $command -fore cyan
                }
           
                if($command [0] -eq "EXIT")
                {
                    $isRunning = $false
                    $reader.Dispose()
                    $stream.Dispose()
                    $client.Dispose()
                    $listener.stop()
                }
        
        }
        <#
        $reader.Dispose()
        $stream.Dispose()
        $client.Dispose()
        $listener.stop()
        #>
}



function sendSMS ($serverIP, $phoneArray)
{
    $targetArray = $phoneArray -split ","
    foreach($phone in $targetArray)
    {
        Write-Host "SMS SEND"   $phone  "`t`t"  $serverIP
    }
}

listen-port

#client code
<#
function send-msg ($message=$([char]4), $port=7788, $server="10.100.37.79") {
    $client = New-Object System.Net.Sockets.TcpClient $server, $port
    $stream = $client.GetStream()
    $writer = New-Object System.IO.StreamWriter $stream
    $writer.Write($message)
    $writer.Dispose()
    $stream.Dispose()
    $client.Dispose()
}
#>
 

posted by bedbmsguru
2018.10.27 22:41 SQL SERVER

SELECT 'CREATE LOGIN ' +name + ' WITH PASSWORD = ' +
     CONVERT(varchar (max), LOGINPROPERTY( name, 'PasswordHash'), 1 ) +
     ' HASHED, SID=' + CONVERT( varchar(max ), sid , 1)
                 FROM sys .server_principals
WHERE name in ('drm_dev', 'MyLogin2')
  AND type = 'S'
  

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

SQL SERVER Wait type 조사  (0) 2018.10.27
DAC 연결방법  (0) 2018.10.27
패스워드까지 그대로 로그인(계정) 옮기는법  (0) 2018.10.27
CPU 사용율 높은 쿼리 확인  (0) 2018.10.27
tempdb 경합 모니터링  (0) 2018.10.27
SQL SERVER 로그  정보(사용량, usage)  (0) 2018.10.27
posted by bedbmsguru
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



  

posted by bedbmsguru
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;

posted by bedbmsguru
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

posted by bedbmsguru