블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (431)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total179,976
Today8
Yesterday49

달력

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

공지사항

불쌍한 서버들이 많아 이 글을 읽고, 혹시나 아직도 불쌍한 서버가 있다면, 꼭 구해 주세요 ~ (불쌍해.....ㅜ.ㅜ)

[2000 에서 확인 32bit without AWE]

dbcc memorystatus

 Dynamic Memory Manager         Buffers    
------------------------------ -----------
QueryPlan                      205678
205678 * 8 = 1645424  (한강으로 GoGo~)

[2005 에서 확인 32bit with AWE]

AWE Allocated                                                   5349376

CACHESTORE_OBJCP (Total)                                          KB

SinglePage Allocator                                            31280

CACHESTORE_SQLCP (Total)                                          KB

SinglePage Allocator                                            740208 KB(한강으로 GoGo~)

 

 

[2008 에서 확인 쿼리 64bit 24GB MaxMemory]

SELECT TOP(20) [TYPE], SUM(SINGLE_PAGES_KB) AS [SPA MEM, KB]

  FROM SYS.DM_OS_MEMORY_CLERKS

 GROUP BY [TYPE] 

  WITH ROLLUP

 ORDER BY SUM(SINGLE_PAGES_KB) DESC;

제일 상단의 총 합계가 얼마인가요?

 4GB 좀 넘어요?  역쉬 (한강으로 GoGo~)

SQL Server 2000 32bit 를 쓰고 있는데 플랜 메모리가 1.6GB 이다.

SQL Server 2000, 2005, 2008 32bit AWE를 쓰고 있는데 플랜 메모리가 700MB 이다.

SQL Server 2005 ~ 2008 64bit 쓰고 있는데, 플랜 메모리가 4GB 정도 되더라.

이럼 개발자랑 DBA 랑 두손 부여잡고 한강으로 가시길 바랍니다. (농담 입니다. ^^;;)

위 양은 쓸수 있는 최대양을 다 쓰고 있는 모습 입니다.

 

이런 서버들은 대부분 Plan Cache 를 재활용 하지 못하는 구조 입니다.

원인은 다양한데요, 

 

1.  top 절이 파라메터 바인딩 되지 못한다.

2. where 절 다음에 오는 predicate 가 파라메터 바인딩 되지 못한다.

3. dynamic sql 을 파라메터 바인딩 시키지 않고 그냥 exec (@sql) 로 돌린다.

4. 이외 영역의 이슈는 각 메모리 영역별로 리서치 해보시길 바랍니다.

 

등등의 이슈가 있습니다. (잘 튜닝된 서버의 플랜 캐시 메모리 사이즈는 100 MB를 넘지 않습니다. )

자기가 운영하는 서버가 이런 사황에  있다면,  빨리 고쳐 Memory / CPU / DISK Subsystem 을 보다 효율적으로 사용 할 수 있도록 고쳐 주세요 ~

 

더 상세한 내용은 다음을 참고 하세요

http://msdn.microsoft.com/en-us/library/ee343986.aspx



select
 type,
 sum(virtual_memory_reserved_kb) as [VM Reserved],
 sum(virtual_memory_committed_kb) as [VM Committed],
 sum(awe_allocated_kb) as [AWE Allocated],
 sum(shared_memory_reserved_kb) as [SM Reserved],
 sum(shared_memory_committed_kb) as [SM Committed],
 sum(multi_pages_kb) as [MultiPage Allocator],
 sum(single_pages_kb) as [SinlgePage Allocator]
from sys.dm_os_memory_clerks
group by type
order by 8 desc

 

 

select
  type,
  sum(virtual_memory_reserved_kb) as [VM Reserved],
  sum(virtual_memory_committed_kb) as [VM Committed],
  sum(awe_allocated_kb) as [AWE Allocated],
  sum(shared_memory_reserved_kb) as [SM Reserved],
  sum(shared_memory_committed_kb) as [SM Committed],
  sum(pages_kb) as [MultiPage Allocator]
 
 from sys.dm_os_memory_clerks
 group by type
 order by 7 desc

신고
Posted by 보미아빠

아래 스크립트는 비 효율적인 프로그램 디자인으로 인해 플랜이 무수히 생기는 과정을 시뮬레이션 하고
해당 플랜 서버에서 제거 하는 방법입니다.

1. 쿼리에서 처음부터 바인드 변수 처리
2. OPTION (RECOMPILE) 을 이용해 플랜이 생기지 않도록 처리
3. 생긴플랜 지우기 등을 생각해 볼 수 있을듯 합니다.

일단 sql 모니터링 프로그램이 죽어도 프로그램을 고쳐주지 않으므로 제거하는 방법을 생각해 보았습니다.

-- 테스트 테이블 만들기
SET NOCOUNT ON

IF OBJECT_ID('TBLX') IS NOT NULL
DROP TABLE TBLX
GO

CREATE TABLE TBLX
(IDX INT IDENTITY(1,1)
,C1 INT
)
GO

-- 데이터 삽입
INSERT INTO TBLX VALUES(1)
GO 10000

CREATE UNIQUE CLUSTERED INDEX UCL_TBLX ON TBLX (IDX)
GO

-- 비 효율적인 어플리케이션 시뮬레이션 (플랜 메모리 과도하게 사용 하도록)
DECLARE @MAX INT, @SQL_TEXT VARCHAR(8000)

SELECT @MAX = MAX(IDX) FROM TBLX
WHILE (@MAX > 0) BEGIN
 SET @SQL_TEXT = 'SELECT TOP 10 * FROM TBLX WHERE IDX ='+CAST(@MAX AS VARCHAR(100)) --+ 'OPTION (RECOMPILE)' -- 파라메터 바인딩이 되지 않도록 TOP 사용
 --PRINT @SQL_TEXT
 EXEC (@SQL_TEXT)
 SET @MAX -= 1
END
GO

-- 쓸모 없는 플랜을 제거
DECLARE @T_PLAN_HANDLE TABLE
(IDX INT IDENTITY(1,1)
,PLAN_HANDLE VARBINARY (1000))

INSERT INTO @T_PLAN_HANDLE
SELECT PLAN_HANDLE--, *
  FROM SYS.DM_EXEC_CACHED_PLANS
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
 WHERE USECOUNTS = 1
   AND OBJTYPE = 'ADHOC'
-- AND 기타조건 I모사 SQL 모니터링 쿼리?

DECLARE @MAX INT, @PLAN_HANDLE VARCHAR (1000), @SQL_TEXT VARCHAR(8000)
SELECT @MAX = MAX(IDX) FROM @T_PLAN_HANDLE

WHILE (@MAX > 0) BEGIN
 SELECT @PLAN_HANDLE = SYS.FN_SQLVARBASETOSTR( PLAN_HANDLE) FROM @T_PLAN_HANDLE WHERE IDX = @MAX
 SET @SQL_TEXT = 'DBCC FREEPROCCACHE ('+CAST(@PLAN_HANDLE AS VARCHAR(8000))+')'
 --PRINT @SQL_TEXT
 EXEC (@SQL_TEXT)
 SET @MAX -= 1
END

--DBCC MEMORYSTATUS
--******************************************************************************
--* AD-HOC 쿼리로 인해 PLAN MEMORY 영역이 비 효율적으로 사용되고 있는 상태
--******************************************************************************

CACHESTORE_SQLCP (NODE 0)                KB
---------------------------------------- -----------
VM RESERVED                              0
VM COMMITTED                             0
AWE ALLOCATED                            0
SM RESERVED                              0
SM COMMITTED                             0
SINGLEPAGE ALLOCATOR                     119232 -- 대충 넣다가 정지해서 이것보다 많은 메모리가 올라 갈 수 있습니다.
MULTIPAGE ALLOCATOR                      248

--******************************************************************************
--* 필요 없다고 판단되는 쿼리를 PLAN 에서 삭제하고 난 후의 상태
--******************************************************************************

CACHESTORE_SQLCP (NODE 0)                KB
---------------------------------------- -----------
VM RESERVED                              0
VM COMMITTED                             0
AWE ALLOCATED                            0
SM RESERVED                              0
SM COMMITTED                             0
SINGLEPAGE ALLOCATOR                     8240
MULTIPAGE ALLOCATOR                      248

 

신고
Posted by 보미아빠

Statistics Strategy

밥벌이 / 2010.09.30 19:05

문제

1. 잘 돌아가던 서버가 갑자기 CPU 100% 를 치면서 이상 현상을 보인다.
2. 갑자기 SQL Server 나 Application 서버가 Hang 이 된다. 
    프로파일러에는 EventSubClass Statistics changed 가 찍혀있다.

해결법

1. 문제의 쿼리를 찾아 힌트를 명시하고, 최적화 기반 recompile 을 막습니다.
2. statistics disable ! 최후의 선택. 이때는 컬럼이나 테이블 단위로 설정하는 것이 좋고 전체 데이터베이스에 적용하면 관리가 상당히 힘들어 집니다.

1. 플랜이 다른 쿼리찾기
SELECT *
  FROM (
    SELECT TOP 5 QUERY_HASH,  COUNT(*) CNT, MAX(SQL_HANDLE) SQL_HANDLE
      FROM SYS.DM_EXEC_QUERY_STATS  
  GROUP BY QUERY_HASH
  ORDER BY 2 DESC ) QS
 CROSS APPLY (
    SELECT TOP 1 *
      FROM SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) ) ST

2. 힌트명시 (통계 히스토그램을 보면 특정값에만 이상하게 값이 편차가 매우 큰 값이 들어있고 실제로는 다른 값들과 유사한 개수의 데이터가 들어있는 경우 입니다. 한마디로 통계가 잘 못 측정된 경우 입니다.) 
   1. index join method 
   2. specify index name
   3. option (힌트나열, keepfixed plan, maxdop ....)

http://www.sql-server-performance.com/articles/per/asynchronous_statistics_p1.aspx

ALTER DATABASE AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE AUTO_UPDATE_STATISTICS_ASYNC OFF


Plan optimality-related recompilations: The Big Picture

문제는 영문 소개 자료를 보면 잘 나와 있고, 재미있는 질문이 있었습니다. DEVDDC (미국에서 큰 개발자 컨퍼런스)에서 SQL 개발자에게 질문 했습니다. 그럼 이 기능을 Default 로 off 한 이유는 무엇인가요? SQL 개발자는 하위 호환성을 위해서 off 로 설정되어 있는것 같다. 라고 설명 하더군요. 한마디로 off 할 이유가 없다는 것이죠.



ALTER DATABASE dbname
    SET AUTO_CREATE_STATISTICS ON;

SELECT name AS "Name",
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;

SELECT name AS "Name",
    is_auto_update_stats_async_on AS "Asynchronous Update"
FROM sys.databases;

다음으로 좋은 문서 링크 드립니다. 

http://technet.microsoft.com/en-us/library/cc966425.aspx

If any of the statistics are outdated, they are updated one-at-a-time. The query compilation waits for the updates to finish. An important difference between SQL Server 2000 and SQL Server 2005 regarding this step is that in SQL Server 2005, statistics may optionally be updated asynchronously. That is, the query compilation thread is not blocked by statistics updating threads. The compilation thread proceeds with stale statistics.
DB에 해당 옵션이 활성화 되어 있어야만 이렇게 됩니다.

Identifying statistics-related recompilations

Statistics-related recompilations can be identified by the "EventSubClass" column of the profiler trace (to be described later in this paper) containing the string "Statistics changed".

Closing remarks

An issue not directly related to the topic of this document is: given multiple statistics on the same set of columns in the same order, how does the query optimizer decide which ones to load during query optimization? The answer is not simple, but the query optimizer uses such guidelines as: Give preference to recent statistics over older statistics; Give preference to statistics computed using FULLSCAN option to those computed using sampling; and so on.

There is a potential of confusion regarding the "cause and effect" relationship between plan optimality-related compilations, recompilations, and statistics creation/updates. Recall that statistics can be created or updated manually or automatically. Only compilations and recompilations cause automatic creation or updates of statistics. On the other hand, when a statistic is created or updated (manually or automatically), there is an increased chance of recompilation of a query plan which might find that statistic "interesting."

Best practices

Four best practices for reducing plan optimality-related batch recompilations are given next:

Best Practice: Because a change in cardinality of a table variable does not cause recompilations, consider using a table variable instead of a temporary table. However, because the query optimizer does not keep track of a table variable's cardinality and because statistics are not created or maintained on table variables, non-optimal query plans might result. One has to experiment whether this is the case, and make an appropriate trade-off.

Best Practice: The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used. The hint can be specified using the following syntax:

SELECT B.col4, sum(A.col1) FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 WHERE B.col3 < 100 GROUP BY B.col4 OPTION (KEEP PLAN)

Best Practice: To avoid recompilations due to plan optimality-related
(statistic update-related) reasons totally, KEEPFIXED PLAN query hint can be specified using the syntax:

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID FROM Sales.Store s INNER JOIN Sales.Customer c ON s.CustomerID = c.CustomerID WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 GROUP BY c.TerritoryID, c.SalesPersonID ORDER BY Number DESC OPTION (KEEPFIXED PLAN)

With this option in effect, recompilations can only happen because of correctness-related reasons — for example, schema of a table referenced by a statement changes, or a table is marked with sp_recompile procedure.

In SQL Server 2005, there is a slight change in behavior as described below. Suppose that a query with OPTION(KEEPFIXED PLAN) hint is being compiled for the first time, and compilation causes auto-creation of a statistic. If SQL Server 2005 can get a special "stats lock," a recompilation happens and the statistic is auto-created. If the "stats lock" cannot be obtained, there is no recompilation, and the query is compiled without that statistic. In SQL Server 2000, a query with OPTION(KEEPFIXED PLAN) is never recompiled because of statistics-related reasons, and therefore, in this scenario, no attempt is made to get a "stats lock" or to auto-create the statistic.

Best Practice: Turning off automatic updates of statistics for indexes and statistics defined on a table or indexed view will ensure that plan optimality-related recompilations caused by those objects will stop. Note, however, that turning off the "auto-stats" feature using this method is usually not a good idea because the query optimizer is no longer sensitive to data changes in those objects, and sub-optimal query plans might result. Adopt this method only as a last resort after exhausting all of the other alternatives.

 
신고

'밥벌이' 카테고리의 다른 글

read-ahead 미리읽기란?  (0) 2010.10.08
시스템이 복구중이라고 하고 시작되지 않을때  (0) 2010.10.07
sp_lock2  (4) 2010.10.06
주의대상 복구 suspect  (0) 2010.10.06
Statistics Strategy  (0) 2010.09.30
CursorTypes, LockTypes, and CursorLocations  (2) 2010.09.30
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바