블로그 이미지
010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (500)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2024.3
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

공지사항

최근에 올라온 글

# sql 2016 는 SP3 업그레이드 필요 (CSAP), 아래내용 적용
# sql 2017 19는 할 필요 없음
# New-SelfSignedCertificate 생성
New-SelfSignedCertificate -Type SSLServerAuthentication -Subject "CN=$env:COMPUTERNAME" `
-DnsName ("{0}" -f [Systehttp://m.Net.Dns]::GetHostByName($env:computerName).HostName),'localhost' `
-KeyAlgorithm "RSA" -KeyLength 2048 -HashAlgorithm "SHA256" -TextExtension "2.5.29.37={text}1.3.6.1.5.5.7.3.1" `
-NotAfter (Get-Date).AddMonths(36) -KeySpec KeyExchange -Provider "Microsoft RSA SChannel Cryptographic Provider" `
-CertStoreLocation "cert:\LocalMachine\My"

# 결과 
PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\My

Thumbprint                                Subject                                                                                                                                   
----------                                -------                                                                                                                                   
aaa5E3ddd742dd7AE1D4A8C648FC556F5B9D90003  CN=HOSTNAME.....                                                                                       

# 서버의 인증서 조회 
dir cert:\LocalMachine\My
mmc 에서 인증서 항목을 추가하고 local computer 의 인증서를 조회해 export 가능
인증서에 해당 사용자 권한을 추가해 줘야 함

# SQL Server 2016 의 경우 아래의 방법으로 등록 (SqlServerConfigurationManager 에서 로딩이 안되더라)
$regPath = 'HKLM:\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQLServer\SuperSocketNetLib'
$thumbprint = 'aaa5E3ddd742dd7AE1D4A8C648FC556F5B9D90003'
Set-ItemProperty $regPath -Name 'Certificate' -Value $thumbprint
# 이건 강제하고 싶을때만 하면 된다. 
#Set-ItemProperty $regPath -Name 'ForceEncryption' -Value 1 -Type Dword

# sql 2019 이상에서는 네트워크 설정에서 인증서 로딩 (로딩 전 이전 서버의 인증서 복구 해두어야 한다.)
# 아래 링크 2개 잘 읽어봐~
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/manage-certificates?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver15#step-2-configure-encryption-settings-in-sql-server

# SqlServerConfigurationManager 에서 restart

Posted by 보미아빠
, |

데드락 발생시 extended events 로 캡처 후 내용을 xdl로 저장하면 아래와 같은 그래프로 볼 수 있다. 

잠금 리소스가 "개체 잠금"이라는 특이한 현상 발생

 

 

waitresource =  OBJECT: 5:1829893886:8 이거라고 한다. (databaseid, objectid, lockPartition)

clustered type primary key만 있는 테이블이며, 형 변환 없이 정확히 쿼리하는데 deadlock 이 발생함

 

윈인은 테이블 생성시 allow_row_locks 와 allow_page_locks 를 disable 해두었음.

 

 

해결 방법

ALTER INDEX PK_XX ON XX
REBUILD WITH (ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )

 

Deadlock XDL

<deadlock>
 <victim-list>
  <victimProcess id="process19727d3bc28" />
 </victim-list>
 <process-list>
  <process id="process19727d3bc28" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:8 " waittime="616" ownerId="132371886315" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x27c1e900460" lockMode="X" schedulerid="20" kpid="1476" status="suspended" spid="9848" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi031.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886315" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
        WHERE         xxid = @P0
          AND         startDate = CAST(@P1 AS CHAR(8))
          AND        endDate = CAST(@P2 AS CHAR(8))
          AND         memberId = CAST(@P3 AS VARCHAR(20))                                   </inputbuf>
  </process>
  <process id="process1972770f848" taskpriority="0" logused="0" waitresource="OBJECT: 5:1829893886:0 " waittime="616" ownerId="132371886316" transactionname="DELETE" lasttranstarted="2023-09-25T09:34:29.420" XDES="0x1938d11c460" lockMode="X" schedulerid="9" kpid="10696" status="suspended" spid="10300" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2023-09-25T09:34:29.420" lastbatchcompleted="2023-09-25T09:34:29.420" lastattention="1900-01-01T00:00:00.420" clientapp="Microsoft JDBC Driver for SQL Server" hostname="avdataapi019.cafe" hostpid="0" loginname="xx" isolationlevel="read uncommitted (1)" xactid="132371886316" currentdb="5" currentdbname="xxBBS5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="132" stmtend="550" sqlhandle="0x02000000dfb0370dbca820a19a4c8bdee0c0272fe194f8980000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000))DELETE FROM dbo.XX
        WHERE         xxid = @P0
          AND         startDate = CAST(@P1 AS CHAR(8))
          AND        endDate = CAST(@P2 AS CHAR(8))
          AND         memberId = CAST(@P3 AS VARCHAR(20))                                   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <objectlock lockPartition="8" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1905d4c0680" mode="IX" associatedObjectId="1829893886">
   <owner-list>
    <owner id="process1972770f848" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process19727d3bc28" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
  <objectlock lockPartition="0" objid="1829893886" subresource="FULL" dbid="5" objectname="xxBBS5.dbo.XX" id="lock1054afcec00" mode="X" associatedObjectId="1829893886">
   <owner-list>
    <owner id="process19727d3bc28" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="process1972770f848" mode="X" requestType="wait" />
   </waiter-list>
  </objectlock>
 </resource-list>
</deadlock>

 

확인용 쿼리

SELECT name, allow_row_locks, allow_page_locks
FROM sys.indexes
	AND allow_row_locks = 0 
	AND allow_page_locks = 0

 

Posted by 보미아빠
, |
-- 언어 변경
cmd /c chcp 437

-- 상태 모니터링
netsh int tcp show global
netsh int tcp show chimneystats
netstat -nt | findstr /i offloaded

-- 변경
netsh int tcp set global chimney=enabled 
netsh int tcp set global chimney=disabled

 

TCP Chimney offload is deprecated technology: Windows Server 2022, Windows Server 2019, Windows Server 2016

Posted by 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함