Statistics Strategy

문제

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.