선호하는 성능 튜닝 요령

선호하는 성능 튜닝 요령

현재 상태로는 이 질문은 우리의 Q&A 형식에 적합하지 않습니다.우리는 답변이 사실, 참고 자료 또는 전문 지식에 의해 뒷받침되기를 기대하지만, 이 질문은 토론, 논쟁, 여론 조사 또는 연장된 토론을 요청할 가능성이 높습니다.만약 이 질문이 개선될 수 있고 다시 열릴 수 있다고 느낀다면,안내를 위해 헬프 센터를 방문합니다.

마감됨11년 전에.

성능 조정이 필요한 쿼리 또는 저장 프로시저가 있을 때 가장 먼저 시도하는 것은 무엇입니까?

여기 제가 항상 최적화에 대해 묻는 사람에게 주는 편리한 물건 목록이 있습니다.
우리는 주로 Sybase를 사용하지만, 대부분의 조언은 전반적으로 적용될 것입니다.

예를 들어 SQL Server에는 성능 모니터링/튜닝 비트가 많이 포함되어 있지만, 이와 같은 기능이 없는 경우에는 다음을 고려해 보겠습니다.

제가 본 문제의 99%는 조인에 테이블을 너무 많이 배치해서 발생합니다.이에 대한 해결책은 조인의 절반(일부 테이블 사용)을 수행하고 결과를 임시 테이블에 캐시하는 것입니다.그런 다음 해당 임시 테이블에서 나머지 쿼리 조인을 수행합니다.

쿼리 최적화 체크리스트

• 기본 테이블에 대한 UPDATE 통계 실행
  • 많은 시스템에서 이 작업을 매주 예약된 작업으로 실행합니다.
• 기본 테이블에서 레코드 삭제(삭제된 레코드를 보관할 수 있음)
  • 하루에 한 번 또는 일주일에 한 번 자동으로 이 작업을 수행하는 것을 고려합니다.
• 인덱스 재구성
• 테이블 재구성(bcp 데이터 출력/수신)
• 데이터베이스 덤프/다시 로드(급격하지만 손상을 해결할 수 있음)
• 보다 적합한 새로운 인덱스 구축
• DBCC를 실행하여 데이터베이스에 손상 가능성이 있는지 확인합니다.
• 잠금 / 교착 상태
  • 데이터베이스에서 실행 중인 다른 프로세스가 없는지 확인
    • 특히 DBCC
  • 행 또는 페이지 수준 잠금을 사용하고 있습니까?
  • 쿼리를 시작하기 전에 테이블을 단독으로 잠급니다.
  • 모든 프로세스가 동일한 순서로 테이블에 액세스하는지 확인합니다.
• 지수를 적절하게 사용하고 있습니까?
  • 조인은 두 식이 정확히 동일한 데이터 유형인 경우에만 인덱스를 사용합니다.
  • 인덱스의 첫 번째 필드가 쿼리에서 일치하는 경우에만 인덱스가 사용됩니다.
  • 적합한 경우 군집화된 인덱스를 사용합니까?
    • 범위 데이터
    • 값 1과 값 2 사이의 WHERE 필드
• 작은 결합은 좋은 결합입니다.
  • 기본적으로 최적화 도구는 한 번에 표 4만 고려합니다.
  • 즉, 테이블이 4개 이상인 조인에서는 최적이 아닌 쿼리 계획을 선택할 가능성이 높습니다.
• 가입 해제
  • 이음매 좀 끊어주실래요?
  • 임시 테이블에서 외부 키 사전 선택
  • 조인의 절반을 수행하고 결과를 임시 테이블에 넣습니다.
• 당신은 올바른 종류의 임시 테이블을 사용하고 있습니까?
  • #temp테이블은 보다 훨씬 더 나은 성능을 발휘할 수 있습니다.@table볼륨이 큰 변수(행이 여러 개임).
• 요약 테이블 유지 관리
  • 기본 테이블에 트리거를 사용하여 빌드
  • 매일 / 매시간 / 등을 구축합니다.
  • 애드혹 빌드
  • 점진적으로 구축 또는 해체/재구축
• SET SHOWPLAN ON으로 쿼리 계획 확인
• SET STATS IO를 사용하여 실제로 어떤 일이 발생하는지 확인
• 플러그마를 사용하여 인덱스 강제 적용: (index: myindex)
• SET FORCE PLAN ON을 사용하여 테이블 순서 강제 적용
• 매개 변수 스니핑:
  • 저장 프로시저를 2개로 나눕니다.
  • proc1에서 proc2 호출
  • @parameter가 proc1에 의해 변경된 경우 최적화 도구가 proc2에서 인덱스를 선택할 수 있습니다.
• 하드웨어를 개선할 수 있습니까?
• 당신은 몇 시에 운영합니까?좀 더 조용한 시간이 있습니까?
• 복제 서버(또는 기타 중단 없는 프로세스)가 실행되고 있습니까?정지시킬 수 있습니까?실행합니다.시간당?

1. 당신의 머릿속에서 쿼리를 실행하는 최적의 경로에 대해 잘 알고 있어야 합니다.
2. 쿼리 계획을 항상 확인합니다.
3. IO 및 CPU 성능을 모두 검사할 수 있도록 STATS를 설정합니다.반드시 쿼리 시간(다른 활동, 캐시 등에 의해 영향을 받을 수 있음)이 아니라 이러한 숫자를 줄이는 데 집중합니다.
4. 연산자에 많은 수의 행이 들어오지만 적은 수가 나오는지 확인합니다.일반적으로 인덱스는 들어오는 행 수를 제한하여 디스크 읽기를 저장합니다.
5. 가장 큰 비용 하위 트리에 먼저 초점을 맞춥니다.하위 트리를 변경하면 전체 쿼리 계획이 변경될 수 있습니다.
6. 일반적인 문제는 다음과 같습니다.
   • 조인 수가 많은 경우 SQL Server가 조인을 확장하도록 선택한 다음 WHERE 절을 적용할 수 있습니다.일반적으로 WHERE 조건을 JOIN 절로 이동하거나 조건이 줄선 파생 테이블로 이동하여 이 문제를 해결할 수 있습니다.보기는 동일한 문제를 일으킬 수 있습니다.
   • 차선의 조인(LOOP vs. HASH vs. MERGE)저의 경험칙은 맨 위 행이 맨 아래 행에 비해 매우 적은 경우 LOOP 조인을 사용하고, 집합이 거의 동일하고 순서가 매겨진 경우 MERGE를 사용하며, 다른 모든 경우 해시를 사용하는 것입니다.조인 힌트를 추가하면 이론을 테스트할 수 있습니다.
   • 매개 변수 스니핑입니다.저장된 프로세스를 처음에 비현실적인 값으로 실행한 경우(예: 테스트용), 캐시된 쿼리 계획이 프로덕션 값에 적합하지 않을 수 있습니다.재컴파일을 사용하여 다시 실행하면 확인해야 합니다.저장된 일부 프로시저의 경우, 특히 다양한 크기 범위(예: 오늘과 어제 사이의 모든 날짜 - INDEX SEEK를 포함하거나 작년과 올해 사이의 모든 날짜 - INDEX 스캔을 사용하는 것이 더 좋습니다)를 처리하는 경우에는 매번 다시 컴파일하여 실행해야 할 수도 있습니다.
   • 잘못된 들여쓰기...좋습니다. SQL Server는 이 문제에 문제가 없지만 포맷을 수정하기 전까지는 쿼리를 이해하는 것이 불가능합니다.

약간 주제에서 벗어났지만 만약 당신이 이 문제들을 통제한다면...
하이 레벨 및 하이 임팩트.

• 높은 IO 환경의 경우 디스크가 RAID 10 또는 RAID 0+1용인지, RAID 1 및 raid 0의 일부 중첩된 구현용인지 확인합니다.
• 1500K 미만의 드라이브는 사용하지 마십시오.
• 디스크가 데이터베이스에만 사용되는지 확인합니다.IE 로깅 없음 OS 없음.
• 자동 확대 또는 유사한 기능을 해제합니다.데이터베이스가 예상되는 모든 저장소를 사용하도록 합니다.반드시 현재 사용되고 있는 것은 아닙니다.
• 형식 쿼리에 대한 스키마 및 인덱스를 설계합니다.
• 로그 유형 테이블(삽입 전용)이고 DB에 있어야 하는 경우 색인화하지 마십시오.
• 보고 할당(복잡한 선택과 많은 조인)을 수행하는 경우 별이나 눈송이 스키마를 사용하여 데이터 웨어하우스를 생성해야 합니다.
• 성능의 대가로 데이터를 복제하는 것을 두려워하지 마십시오!

CREATE INDEX

사용자의 용가한있확다니인합에 합니다.WHERE그리고.JOIN◦이렇게 가 크게향상됩니다.이렇게 하면 데이터 액세스 속도가 크게 향상됩니다.

환경이 데이터 마트 또는 웨어하우스인 경우 인덱스는 거의 모든 가능한 쿼리에 대해 풍부해야 합니다.

트랜잭션 환경에서는 인덱스 유지 관리가 리소스를 끌지 않도록 인덱스 수를 줄이고 인덱스 정의를 전략적으로 수행해야 합니다.(인덱스 유지관리는 인덱스의 잎을 기본 테이블의 변화를 반영하기 위해 변경해야 하는 경우입니다.INSERT, UPDATE,그리고.DELETE운영.)

또한 색인의 필드 순서에 유의하십시오. 필드가 선택적일수록(높은 카디널리티) 색인의 초기에 표시되어야 합니다.예를 들어 중고차를 문의한다고 가정해 보겠습니다.

SELECT   i.make, i.model, i.price
FROM     dbo.inventory i
WHERE    i.color = 'red'
  AND    i.price BETWEEN 15000 AND 18000

가격은 일반적으로 카디널리티가 더 높습니다.색상은 수십 가지에 불과할 수 있지만, 가격은 수천 가지에 이를 수 있습니다.

지수들 이색인중서에,,서,idx01쿼리를 충족하는 더 빠른 경로를 제공합니다.

CREATE INDEX idx01 ON dbo.inventory (price, color)
CREATE INDEX idx02 ON dbo.inventory (color, price)

이는 색상 선택보다 적은 수의 자동차가 가격 포인트를 충족시켜 쿼리 엔진에 분석할 데이터를 훨씬 적게 제공하기 때문입니다.

필드 순서만 다를 뿐 매우 유사한 인덱스 두 개를 사용하여 하나의 쿼리(성, 성)와 다른 하나의 쿼리(성, 이름)를 빠르게 처리하는 것으로 알려져 있습니다.

여기서 MySQL을 가정하면 EXPLE을 사용하여 쿼리에서 무슨 일이 일어나고 있는지 확인하고 인덱스가 가능한 한 효율적으로 사용되고 있는지 확인하고 파일 정렬을 제거합니다.고성능 MySQL: 최적화, 백업, 복제 등은 MySQL Performance Blog와 마찬가지로 이 주제에 대한 훌륭한 책입니다.

최근에 배운 방법은 SQL Server가 업데이트 문에서 필드뿐만 아니라 로컬 변수도 업데이트할 수 있다는 것입니다.

UPDATE table
SET @variable = column = @variable + otherColumn

또는 더 읽기 쉬운 버전:

UPDATE table
SET
    @variable = @variable + otherColumn,
    column = @variable

재귀 계산을 구현할 때 복잡한 커서/조인을 대체하는 데 사용했으며 성능도 많이 향상되었습니다.

다음은 성능을 크게 향상시킨 세부 정보와 코드 예제입니다.링크

@Terrapin은 null과 병합 사이에 언급할 가치가 있는 몇 가지 다른 차이점이 있습니다(나에게는 큰 것인 ANSI 준수 외에도).

통합 대IsNull

SQL Server에서 where 절에 OR을 사용하면 성능이 향상될 수 있습니다.OR을 사용하는 대신 두 개의 선택 항목을 선택하고 결합합니다.1000배의 속도로 동일한 결과를 얻을 수 있습니다.

where 절 - 인덱스 사용 확인 / 어리석은 작업이 수행되고 있지 않은지 확인

where SomeComplicatedFunctionOf(table.Column) = @param --silly

저는 일반적으로 조인부터 시작할 것입니다. 한 번에 하나씩 쿼리에서 각 조인을 제거하고 쿼리를 다시 실행하여 문제가 있는 특정 조인이 있는지 확인합니다.

모든 임시 테이블에서 인덱스와 기본 키(거의 항상)를 만들기 위해 고유한 제약 조건(해당되는 경우)을 추가합니다.

declare @temp table(
    RowID int not null identity(1,1) primary key,
    SomeUniqueColumn varchar(25) not null,
    SomeNotUniqueColumn varchar(50) null,
    unique(SomeUniqueColumn)
)

@데이비드 M

여기서 MySQL을 가정하고 EXPLE을 사용하여 쿼리에서 무슨 일이 일어나고 있는지 확인하고 인덱스가 가능한 한 효율적으로 사용되고 있는지 확인합니다.

SQL Server에서 실행 계획을 사용하면 동일한 작업을 수행할 수 있습니다. 실행 계획은 어떤 인덱스가 히트하는지 등을 알려줍니다.

SQL 성능 트릭 자체는 아니지만 관련이 있습니다.

미리 컴파일된 데이터를 데이터베이스에서 가져오는 것보다 메모리에서 직접 가져오는 것이 훨씬 빠르기 때문에 가능한 경우 memcached를 사용하는 것이 좋습니다.MySQL에도 memcached가 내장되어 있습니다(타사 제품).

인덱스 길이가 가능한 한 작아야 합니다.이렇게 하면 DB가 파일 시스템에서 한 번에 더 많은 키를 읽을 수 있으므로 조인 속도가 빨라집니다.이것은 모든 DB에서 작동하지만 MySQL에 대한 특정 권장 사항이라는 것을 알고 있습니다.

저는 항상 바인딩 변수를 사용하는 것을 습관화했습니다.RDBMS가 SQL 문을 캐시하지 않으면 바인딩 변수가 도움이 되지 않을 수 있습니다.그러나 바인딩 변수를 사용하지 않으면 RDBMS는 쿼리 실행 계획 및 구문 분석된 SQL 문을 다시 사용할 수 없습니다.http://www.akadia.com/services/ora_bind_variables.html 의 비용 절감 효과는 엄청날 수 있습니다.저는 대부분 Oracle과 함께 작업하지만 Microsoft SQL Server도 거의 동일한 방식으로 작동합니다.

제 경험에 따르면, 바인딩 변수를 사용하는지 여부를 모른다면 아마 사용하지 않을 것입니다.응용 프로그램 언어가 지원하지 않는 경우 지원하는 언어를 찾으십시오.때때로 쿼리 B에 바인딩 변수를 사용하여 쿼리 A를 수정할 수 있습니다.

그 후에는 DBA와 상의하여 RDBMS의 가장 큰 문제가 무엇인지 알아봅니다."이 쿼리가 느린 이유는 무엇입니까?"라고 물어서는 안 됩니다.그건 의사에게 맹장을 제거해 달라고 부탁하는 것과 같습니다.물론 쿼리가 문제일 수 있지만 다른 문제가 발생할 가능성도 있습니다.개발자로서 우리는 코드 라인의 관점에서 생각하는 경향이 있습니다.선이 느리면 해당 선을 고정합니다.그러나 RDBMS는 매우 복잡한 시스템이며 느린 쿼리는 훨씬 더 큰 문제의 증상일 수 있습니다.

너무 많은 SQL 튜닝 팁은 화물 숭배 아이돌입니다.대부분의 경우 문제는 사용하는 구문과 관련이 없거나 최소한으로 관련되어 있으므로 일반적으로 가장 깨끗한 구문을 사용하는 것이 좋습니다.그런 다음 쿼리가 아닌 데이터베이스를 조정하는 방법을 찾기 시작할 수 있습니다.구문은 실패할 때만 수정합니다.

성능 조정과 마찬가지로 항상 의미 있는 통계를 수집합니다.조정 중인 사용자 환경이 아니면 벽시계 시간을 사용하지 마십시오.대신 CPU 시간, 행 가져오기 및 디스크에서 읽은 블록과 같은 항목을 확인하십시오.사람들은 너무 자주 잘못된 것을 위해 최적화합니다.

번째 단계 계획을 !쿼리 실행 계획을 확인하십시오!
bad -> 파일
루프 -> warningNested루프 ->
a Nested 첩의이블캔스루테프된뒤중> -! -> Doom!

은 통계 정보입니다.
TIME 은 통계 시간을 설정합니다.

WITH(NoLock)를 사용하여 쿼리를 실행하는 것은 제 경우 거의 일반적인 작업입니다.수십 기가바이트의 테이블에서 쿼리를 실행하지 않고 실행하는 것이 적발된 사람은 누구든지 꺼내어 총살당합니다.

가능하면 쿼리에서 NOT IN 쿼리를 LEFT OUTER JOIN으로 변환합니다.예를 들어 표 2의 외부 키에서 사용되지 않는 표 1의 모든 행을 찾으려면 다음 작업을 수행할 수 있습니다.

SELECT *
FROM Table1
WHERE Table1.ID NOT IN (
    SELECT Table1ID
    FROM Table2)

그러나 이를 통해 훨씬 더 나은 성능을 얻을 수 있습니다.

SELECT Table1.*
FROM Table1
LEFT OUTER JOIN Table2 ON Table1.ID = Table2.Table1ID
WHERE Table2.ID is null

필터링할 clm을 기준으로 테이블 인덱싱

• 모든 테이블 앞에 dbo.를 붙여 재컴파일을 방지합니다.
• 쿼리 계획을 보고 테이블/인덱스 검색을 검색합니다.
• 2005년에 관리 보기에서 누락된 인덱스를 검색합니다.

나는 사용하기를 좋아합니다.

isnull(SomeColThatMayBeNull, '')

오버

coalesce(SomeColThatMayBeNull, '')

내가 연합이 당신에게 주는 여러 주장의 지지가 필요하지 않을 때.

http://blog.falafel.com/2006/04/05/SQLServerArcanaISNULLVsCOALESCE.aspx

주의사항:

• CURSOR 루프를 모두 풀고 집합 기반 UPDATE / INSERT 문으로 변환합니다.
• 다음과 같은 응용 프로그램 코드가 있는지 확인합니다.
  • 대량의 레코드 집합을 반환하는 SP를 호출합니다.
  • 그런 다음 응용 프로그램에서 각 레코드를 살펴보고 SP를 호출하여 레코드를 업데이트합니다.
  • 이것을 한 번의 트랜잭션으로 모든 작업을 수행하는 SP로 변환합니다.
• 문자열을 많이 조작하는 모든 SP.이는 데이터가 올바르게 구조화되지 않았다는 증거입니다.
• 바퀴를 다시 발명하는 모든 SP.
• SP는 1분 이내에 무엇을 하려고 하는지 이해할 수 없습니다!

SET NOCOUNT ON

실제로 사용할 필요가 없는 한 일반적으로 저장 프로시저 내의 첫 번째 줄@@ROWCOUNT.

SQL Server에서 잠금 해제 지시문을 사용합니다.이를 통해 일반적으로 다른 트랜잭션이 완료될 때까지 기다릴 필요 없이 선택 명령을 완료할 수 있습니다.

SELECT * FROM Orders (nolock) where UserName = 'momma'

필요하지 않은 곳이라면 커서를 제거합니다.

많은 행이 함수를 호출할 Sprocs에서 함수 호출을 제거합니다.

제 동료는 함수 호출(예: 사용자 ID에서 마지막 로그인 날짜 가져오기)을 사용하여 매우 넓은 레코드 집합을 반환했습니다.

최적화 작업을 수행하면서 저장 프로시저의 함수 호출을 함수의 코드로 대체했습니다.저는 많은 스프록의 실행 시간을 20초 이상에서 1초 미만으로 줄였습니다.

시스템 프로시저는 모두 "sp_"로 시작하므로 저장 프로시저 이름 앞에 "sp_"를 붙이지 마십시오. SQL Server는 호출될 때 프로시저를 찾기 위해 더 열심히 검색해야 합니다.

더티 읽기 -

set transaction isolation level read uncommitted

트랜잭션 무결성이 절대적으로 필요하지 않은 데드락 방지(일반적으로 사실

항상 SQL Profiler(네스트 수준이 많은 저장 프로시저인 경우) 또는 쿼리 실행 계획자(네스트가 없는 몇 개의 SQL 문인 경우)로 먼저 이동합니다.이 두 가지 툴 중 하나로 문제를 즉시 발견할 수 있는 경우가 90%에 달합니다.

언급URL : https://stackoverflow.com/questions/18783/favourite-performance-tuning-tricks