DB를 관리/운영 하다보면 점점 DB가 느려짐을 느끼는데...

 

여러가지가 있지만 DB 측면에서만 확인 해 보자면 아래와 같은 원인을 찾을 수 있다.

 

1) Data 누적에 따른 IO 증가

     - Data가 늘어난다고 해서 비례해서 속도가 느려지는건 아니다.

        인덱스가 정상적으로 잡혀 있고 정상적인 쿼리 계획에 의한 실행이라면

        거의 동일한 속도를 유지할수 있다.

        인덱스 관리, 쿼리 확인 하여 IO 높은 쿼리를 수정하는 등의 지속적인 관리 필요

 

2) 인덱스 단편화에 따른 실행비용 증가

    - 쿼리 실행계획 확인했는데 모두 seek로 클러스터, 넌클러스터 인덱스를 잘 타고 있는데도

        인덱스의 단편화 현상이 심화 되어 속도 지연현상이 일어날 수 있다.

        보통 인덱스가 10% 이상 깨지면 재구성을 해줘야 한다고 함

 

      o 인덱스 조각화 확인

        DBCC SHOWCONTIG (테이블명)

        SELECT * FROM sys.dm_db_index_physical_stats

        결과 항목 중 scan Density(스캔밀도) 항목이 90% 이상에 미치지 못하면 성능에 좋지 않으므로

        인덱스 재구성 해줘야함

         DBCC DBREINDEX (테이블명, 인덱스명) -- 주의! 테이블 사이즈가 클경우에는 인덱스 단위로 재구성해주는게

         좋고 테이블단위, DB 단위로 할 경우에는 온라인으로 진행하도록 해야함

 

     o 인덱스 사용량 분석

         select * from sys.dm_db_index_usage_stats

 

3) 사용량 증가에 의해 컴파일(cpu) 증가

      - DB서버 cpu를 증가시키는 원인중에 하나가 쿼리 재컴파일에 있다.

          쿼리가 호출되면 해당 쿼리를 컴파일 하게 되는데 문제는 무거운 쿼리의 컴파일 횟수가 많아지면

          자연스레 cpu가 증가 하게 되는것이다.

         이를 방지하기 위해 캐쉬된 쿼리를 사용하는것인데, 캐쉬된 쿼리를 사용하기 위해서는 Stored Procedure,

         동적쿼리 실행 중 sp_executeSql 이다.  (exec(@sql) 로는 사용을 자제하자! ^^)

         실행계획에 쿼리를 캐쉬해서 재사용을 하도록 하여 재컴파일도 하지 않고 이에따라 속도향상도 할 수 있다.

 

         o 캐쉬 정보 확인

         -- DBCC FLUSHPROCINDB(8) -- 선택DB 캐쉬 삭제

         -- DBCC FREEPROCCACHE -- 모든 DB의 캐쉬 삭제

         select objtype, dbid, usecounts, status, sqlbytes, sql from master.dbo.syscacheobjects

         objType에 'Adhoc', Prepared', 'Proc' 등을 확인 할 수 있다, Prepared가 sp_executeSql 이다.

        

         o MSSQL은 실행계획에 의해 쿼리를 실행한다. 비용을 기반으로 하여 최적화 된 실행 계획을

             생성하는데, Ad-hoc 쿼리는 매번 실행계획을 생성하는데 반해 Sotred Procedure는 캐쉬에 저장하여

             재사용을 할 수 있음

             Profiler 에서 cpu가 높은 쿼리 를 확인 해보고 이벤트에 SP:CacheHit, SP:CacheInsert 를 확인 체크하여

             추적을 하여 해당 쿼리들에 대해 튜닝 여뷰를 결정 하도록 한다.

 

4) 통계 미 업데이트로 인한 최적화되지 못한 실행계획 생성

      - 통계는 테이블의 테이터 분포 상태를 나타내는 정보이고, 최적의 실행 계획을 생성하기 위해 이용되는

           테이블 정보이다.

           통계는 기본적으로 테이블의 데이타 변경이 10% 이상 되었을경우 자동 갱신 되는데 문제는 데이타가

           1천만건 이상이라던지 대용량 일경우에 100만건이 되기전까지 업데이트를 하지 않는다는 것이다.

           실행계획에 의해 쿼리를 실행하는데 현재 데이타가 100만건 가까이 실데이타와 계획된 데이타가 다를경우

           예상된 결과보다 많은 데이타가 나오면서 계획되지 않은 결과를 출력하면서 성능이 저하될 수 있다.

          이럴때는 통계 업데이트를 하여 정상적인 실행계획을 갖도록 처리해줘야한다.

 

         o DB의 모든 통계 정보 갱신

            sp_updatestats 

            (주위! 운영중에 갱신을 하게되면  실행계획을 잡아야 하므로 느려질수 있으므로 한가할때 실행해주도록 )

         o 통계 정보 확인

            sp_helpstats 테이블명

            DBCC SHOW_STATISTICS (테이블명, 통계명)

 

5) block, lock 에 서비스 지연

     o 블로킹 모니터링

         sp_lock (status 열)

         sp_who or sp_who2 (blk, blkby 열 0 이상이면 블로킹 있는거임)

           sp_who2 실행시 이상있는 ID가 발견되면, ProgramName 확인 -> .Net Sqlclient Data Provider 일경우 아래 쿼리

           실행하여 쿼리 상세 정보 확인 

           

           => dbcc inputbuffer(SPID)

           

     o 블로킹 방지

          - with (nolock), with(readpast) 옵션 사용

          - lock timeout 설정 - set lock_timeout <시간

 

6) 쿼리 실행계획 관련 옵션

    Set Statistics Profile { ON | OFF } -- 쿼리 결과에 실행 계획을 포함
    Set Statistics IO {ON | OFF}     -- 페이지의 입출력 수를 알 수 있다.
    Set Showplan_All { ON | OFF }   -- 실행 계획만 보는 옵션 (실행계획을 도식화로 보여주지 않고 텍스트로 출력)

 

※ 성능 모니터를 이용한 분석

    - SQLServer:Statistics : Batch Requests/sec (초당 실행되는 쿼리)

    - SQLServer:Statistics : SQL Compilations/sec, Recompilation(재 컴파일이 낮아야 함)

    - SQLServer:Plan Cache: Cache Hit Ratio (캐시 적중률)

      -> 90% 이상으로 유지되어야함, 수치가 낮을수록 캐쉬는 의미가 없음

         적정 수준 98%를 유지할 수 있도록 해야함. 이를 위해서는 데이터 참조의 지역성을 잘

         고려하여 알맞은 캐시 공간을 확보해야한다.

    - SQLServer:Memory

    - Processor : % Processor Time 등  

[출처] MS-SQL 운영 팁|작성자 연우빠

[ASP]

<%
function asin(x)
    asin = atn(x / sqr(1 - x ^ 2))
end function

function acos(x)
    acos = M_PI_2 - asin(x)
end function

function getMeter(lat1, lng1, lat2, lng2)
  if (lat1 = lat2) and (lng1 = lng2) then
      getMeter = 0
  else
  pi = 4 * atn(1)

  e10 = lat1 * pi / 180
  e11 = lng1 * pi / 180
  e12 = lat2 * pi / 180
  e13 = lng2 * pi / 180

  c16 = 6356752.314140910
  c15 = 6378137.000000000
  c17 = 0.0033528107

  f15 = c17 + c17 * c17
  f16 = f15 / 2
  f17 = c17 * c17 / 2
  f18 = c17 * c17 / 8
  f19 = c17 * c17 / 16

  c18 = e13 - e11
  c20 = (1 - c17) * tan(e10)
  c21 = atn(c20)
  c22 = sin(c21)
  c23 = cos(c21)
  c24 = (1 - c17) * tan(e12)
  c25 = atn(c24)
  c26 = sin(c25)
  c27 = cos(c25)

  c29 = c18
  c31 = (c27 * sin(c29) * c27 * sin(c29)) + (c23 * c26 - c22 * c27 * cos(c29)) * (c23 * c26 - c22 * c27 * cos(c29))
  c33 = (c22 * c26) + (c23 * c27 * cos(c29))
  c35 = sqr(c31) / c33
  c36 = atn(c35)
  c38 = 0
  if c31 = 0 then
   c38 = 0
  else
   c38 = c23 * c27 * sin(c29) / sqr(c31)
  end if

  c40 = 0
  if (cos(asin(c38)) * cos(asin(c38))) = 0 then
   c40 = 0
  else
   c40 = c33 - 2 * c22 * c26 / (cos(asin(c38)) * cos(asin(c38)))
  end if

  c41 = cos(asin(c38)) * cos(asin(c38)) * (c15 * c15 - c16 * c16) / (c16 * c16)
  c43 = 1 + c41 / 16384 * (4096 + c41 * (-768 + c41 * (320 - 175 * c41)))
  c45 = c41 / 1024 * (256 + c41 * (-128 + c41 * (74 - 47 * c41)))
  c47 = c45 * sqr(c31) * (c40 + c45 / 4 * (c33 * (-1 + 2 * c40 * c40) - c45 / 6 * c40 * (-3 + 4 * c31) * (-3 + 4 * c40 * c40)))
  c50 = c17 / 16 * cos(asin(c38)) * cos(asin(c38)) * (4 + c17 * (4 - 3 * cos(asin(c38)) * cos(asin(c38))))
  c52 = c18 + (1 - c50) * c17 * c38 * (acos(c33) + c50 * sin(acos(c33)) * (c40 + c50 * c33 * (-1 + 2 * c40 * c40)))

  c54 = c16 * c43 * (atn(c35) - c47)

  getMeter = FormatNumber((c54/1000),1) '키로미터
  end if
end function
%>

 

[사용예]

'전주역 : 35.849773, 127.161796
'전주시청 : 35.824112, 127.148078
response.write(getMeter(35.849773, 127.161796, 35.824112, 127.148078) &"km")

[결과]

3.1km

 

 

[MSSQL]

select
(
  6371     /* 6371->키로미터 단위, 3959->마일단위 */
  * acos(
    cos( radians(lat1) )
    * cos( radians(lat2))
    * cos(
      radians(lng2) - radians(lng1)
    )
    + sin( radians(lat1) )
    * sin( radians(lat2) )
  )
) AS distance
from 테이블

 

[Java Script]

function getmeter(lat1, lon1, lat2, lon2) {
  delta_lon = deg2rad(lon2) - deg2rad(lon1);
  
  distance = Math.acos(Math.sin(deg2rad(lat1)) * Math.sin(deg2rad(lat2)) + Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) *
  Math.cos(delta_lon)) * 3963.189; //마일
  
  gap = parseInt(distance * 1609.344);
  return gap;
}

function deg2rad(val) {
  var pi = Math.PI;
  var de_ra = ((eval(val))*(pi/180));
  return de_ra;
}



출처: https://horangi.tistory.com/263 [노을빛호랑이의 연습장]


 

SSMS에서 Table Design 항목에서 Description 함께 보기


퓨터\HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\14.0\DataProject 에 있는 


SSVPropViewColumnsSQL70


SSVPropViewColumnsSQL80


의 기본값인 1,2,6 값을 1,2,6,17 로 변경해주고 SSMS를 재구동 하면 위의 사진처럼 Description(설명) 항목이 보여진다.


위의 숫자가 의미하는 바는 아래와 같다.



1. Column Name

2. Data Type

3. Length

4. Precision

5. Scale

6. Allow Nulls

7. Default Value

8. Identity

9. Identity Seed

10. Identity Icrement

11. Row GUID

12. Nullable

13. Condensed Type

14. Not for Replication

15. Formula

16. Collation

17. Description

Windows 10 (Win 8.1) 에서 Visual Basic 6(VB6) 설치 방법
    비주얼 스튜디오 6 (1998), VS6(VC6, Visual C++ 6, Cpp6) install in Windows 8.1

0. 비주얼 스튜디오 6은 임의의 폴더에, 설치CD를 복사한 후, Setup.exe를 실행해서 설치한다

1. 파일 속성 변경    
    Setup.exe -> 속성 -> 호환성 탭
        -> 호환모드 : Windows XP (서비스 팩 3) 선택
        -> 설정 : 관리자 권한으로 이 프로그램 실행 체크

//============
2. Microsoft Virtual Machine For Java 설치 하지 않기(단계 뛰어 넘기, skip)


- 방법1 : 추천

VS6 Install\IE4\ 폴더를 삭제하거나 이름 변경


//===============

- 방법2 : 비추

    - C:\Windows 와 C:\Windows\System32 폴더에 msjava.dll 파일을 생성한다
    - 관리자 권한 으로 다음 명령 실행
copy nul C:\Windows\msjava.dll
copy nul C:\Windows\system32\msjava.dll



//============
3. 설치 구성요소 선택
    - Visual C++ 6.0 관련된 항목은 모두 설치 하도록 설정한다
    유니코드 라이브러리가 설치되지 않아 생기는 문제 방지 cannot open file "mfc42ud.lib"
    
    - '데이터 액세스' 는 체크 해제 
        - 안하면 나중에 '시스템을 변경하는 중입니다' 에서 멈춤




//====================

// 아래의 과정은 꼭 필요한 것은 아닙니다. 에러가 나는 경우에 필요


- 옵션(필요한 경우)

4. 설치후 MSDEV.exe 실행 에러 문제 해결
    - 파일 백업 : 비등록 라이센스 메시지 피하기 위해
This product is licensed to:
Unregisterd
Unauthorized copy
    C:\Program Files (x86)\Microsoft Visual Studio\Common\MSDev98\Bin\MSDEV.exe


    - VS6 service pack 6폴더에서  VS6sp61.cab 압축 해제후 
.\Vs6sp6\VS6sp61\Common\msdev98\bin 폴더를 다음으로 복사
C:\Program Files (x86)\Microsoft Visual Studio\Common\MSDev98\Bin


    - 백업한 msdev.exe 파일 다시 원래 폴더로 복사


    - 다음 파일들 속성변경
        속성 -> 호환성 탭
        -> 호환모드 : Windows 8 선택
        -> 설정 : 관리자 권한으로 이 프로그램 실행 체크
msdev.exe - Enterprise 
msdevP.exe - Professional 
msdevS.exe - Standard 


    - 파일이름이 msdev.exe 이면 에러 발생 -> 이름 변경
        - msdevP.exe -> msdevP-org.exe
        - msdev.exe -> msdevP.exe


    - .dsw 확장명을 msdevP.exe에 연결 

https://docs.microsoft.com/ko-kr/sql/ssms/download-sql-server-management-studio-ssms


SQLSERVER 2008~2017까지 커버됩니다.


필요에따라 링크에 포함된 보안패치를 설치해야할 필요가 있습니다.

iframe 내부에서 사용하는 php에


header('P3P: CP="NOI CURa ADMa DEVa TAIa OUR DELa BUS IND PHY ONL UNI COM NAV INT DEM PRE"');


를 추가하면됨

--------------- 1.  DB 분리하기

 

sp_detach_db DB명

 

-- 혹시 사용중이라고 나오면 로그인 사용자 kill
-- sp_who -- 로그인 사용자 확인
-- kill spid--로그인 사용자 쥑이기
-- 그리고 SMS DB같은 경우 문자보내기 서비스가 실행중이므로 서비스를 중지 시키고,
-- 로그인사용자 kill 시키고 분리

 

----------------- 1. 분리 끝

 

 

-----------------  2. DB 연결
-- 로그 파일이 있는 경우 sp_attach_db 사용
 sp_attach_db @dbname='DB명', @filename1='파일경로\ball.mdf', @filename2='파일경로\ball_log.ldf'

 

--데이터 파일만 있은 경우 sp_attach_single_file_db 사용
sp_attach_single_file_db @dbname='DB명', @physname='파일경로\baseball.mdf'

-----------------  2. DB 연결 끝


1. read

 Properties ini = new Properties();
 String mode="";
 ini.load(new FileInputStream("/sdcard/init.ini")); 
 mode=ini.getProperty("version"); 


2.write

Properties ini = new Properties();
ini.put("version", "default"); // property Add
ini.setProperty("version", "single"); 
ini.store(new FileOutputStream("/sdcard/FTPTemp/init.ini",true), "Initialize"); //save

installing service pack 5 for Visual Basic 6 was problematic in windows 7. The installer checks the MDAC version and gives the warning that MDAC 2.5 should be installed as a minimum (I am sure a higher version of MDAC is installed in windows 7). After the warning it quits the setup procedure. 

 

To get around this issue the following worked for me: 

In the enu folder edit the following file: sp598ent.stf  

 

From the following line remove the number 36: 

Old line: 13 Group 28 36 38 29 30 32 26 27 14 25 16 17 20 18 19 15 39 21 22 24 23 43 

New line: 13 Group 28 38 29 30 32 26 27 14 25 16 17 20 18 19 15 39 21 22 24 23 43  

 

Delete the following 2 lines replacing them with just carriage returns: 

36 Depend "27 ? : 37" 

37 IsWin95 CustomAction "sp598ent.dll,CheckForMDAC"  

 

Save the file and run the installer again. 



rdcm27.zip


+ Recent posts