보미네

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

[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

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

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

 

문제

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

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.