제목 그대로 SQL Server 2005에서 다른 서버의 MSDB를 새로운 머신으로
복구할 경우에 AGENT의 JOB이 제대로 수행되지 않는 문제가 발생할 수 있습니다.
이때 AGENT JOB의 오류 로그를 보면 아래와 같은 오류가 발생하는 것을 확인 할 수 있습니다.
2009-10-22 16:46:23 - ! [125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
2009-10-22 16:46:23 - ! [125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)
문제의 원인은 AGENT에서 사용하는 DLL파일의 경로를 msdb.dbo.syssubsystems에 저장하고 있으며,
다른 서버로 이전되면서 SQL이 설치된 경로가 변경되는 경우에는 DLL의 위치를 확인 할 수 없어
오류가 발생하게 됩니다. 이때 UPDATE를 통해서 경로를 변경해서 처리 할 수도 있으며 아래와 같이
시스템 SP를 사용 할 수 도 있습니다.
use msdb
go
delete from msdb.dbo.syssubsystems
exec msdb.dbo.sp_verify_subsystems 1
go
SQL Server 에이전트 재시작.
자세한 내용은 아래 KB를 참고 하시길 바랍니다.
msdb 데이터베이스 또는 SQL Server 2005의 syssubsystems 테이블을 변경할 때 복원하거나 때 오류 메시지: "하위 시스템 % 로드할 수 없습니다."
http://support.microsoft.com/kb/914171
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQL Server 2005 Standard Edition X64환경에 CU4 설치 및 시작 추적 플래그 845 설정 하게 되면
아래와 같이 Lock Pages in memory 정책을 지원하는 Using Locked pages for buffer pool 이라는
메시지를 확인 할 수 있습니다.
혹시 x64환경에서 SQL Server 2005 Standard Edition을 사용하신다고
Working Set 이 Trim되는 현상이 있다면 테스트 후 반듯이 CU4를 적용하시는 편이 좋습니다.
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
SQL Server 2005 이상 버전에서는 아래의 DMV를 통하여 어떠한 테이블에 인덱스가 없어 성능적으로 영향을 주는지 확인 할 수 있습니다.
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details
또한 예전에 소개하였듯이 SQL Server 2008에서는 그래픽 실행계획에서 missing index에 대한 정보도 바로 확인 할 수 있습니다.
하지만 여전히 missing index로 잡히게 한 쿼리의 원본은 쉽게 확인 할 수가 없습니다.
그래서 이번에 소개하는 내용은 조금 더 쉽게 missing index를 유발한 쿼리를 찾아보는 것입니다.
XML 실행계획에 이 missing index에 대한 정보도 같이 저장 되어있기에 전에 소개했던 DMV를 조금 응용하면 특정 테이블에 어떤 쿼리로 인해 missing index가 등록되었는지 쉽게 확인 할 수 있을 것입니다.
자세한 내용은 아래를 참고하세요.
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT --TOP 10
db_name
,sp_name
,sp_text
,[statement_text]
,X.*
,[creation_time]
,[ExecutionCount]
FROM
(
SELECT
db_name(qt.dbid) AS 'db_name'
,qt.text AS 'sp_text'
, substring(qt.text, (qs.statement_start_offset/2)+1
, ((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text
, qs.creation_time
, qs.execution_count AS 'ExecutionCount'
, cast(qp.query_plan as xml) as query_plan
,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)Y
CROSS APPLY
(
SELECT
c.value('(./@Impact)[1]','float') as missing_index_Impact
,c.value('(./sql:MissingIndex/@Database)[1]','varchar(100)') + '.'+
c.value('(./sql:MissingIndex/@Schema)[1]','varchar(100)') + '.'+
c.value('(./sql:MissingIndex/@Table)[1]','varchar(100)') as [missing_index_Table]
FROM Y.query_plan.nodes('//sql:MissingIndexGroup')B(C)
)X
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
제목 : 프로시저 캐시의 특정 타입만(SQLCP) 초기화 시키자.
64bit환경에서의 native한 대용량 메모리 지원으로 프로시저 캐시 영역도 따라 늘어났습니다.
분명 이러한 변화된 부분은 이전의 32bit 환경 보다 보다 좋은 성능을 가져올 수 있게 해주었습니다.
하지만, 프로시저 캐시 크기가 증가됨에 따라 예전에 보기 힘들었던 문제가 발생할 수가 있습니다.
아래의 표로 프로시저 캐시의 공간을 계산 할 수 있습니다.
만약 8GB의 가용 메모리를 가지고 있는 SQL Server 2005 SP2 시스템인 경우 대략 3.4GB를 프로시저 캐시로 사용할 수 있습니다.
많은 ad-hoc쿼리가 발생되어 이 공간 메모리를 모두 사용하고 있고, 저장된 계획을 재사용하지 못한다면 쓸데없는 메모리 낭비 및 계속 컴파일이 발생하게 됩니다. 이것은 전체적인 시스템으로 본다면 비효율적이라고 볼 수 있습니다.
위와 같은 환경이라면 당연히 SP 등을 이용하여 컴파일된 실행계획을 가지게 모든 쿼리를 변경하는 것을 고려해야 합니다.
하지만 지금 당장 할 수 있는 작업이 아니기에, 다른 방법을 찾아봐야 합니다.
<SQL 버전별 최대 프로시저 캐시 메모리 크기 구하기>
|
SQL Server Version |
Internal Memory Pressure Indication in Cachestore |
|
SQL Server 2005 RTM & SP1 |
75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB |
|
SQL Server 2005 SP2 |
75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB |
|
SQL Server 2000 |
SQL Server 2000 4GB upper cap on the plan cache |
다른 방법에 대해 찾기 위해서는 우선 프로시저 캐시 저장 구조에 대해서 조금 알아 볼 필요가 있습니다. 간단하게 프로시저 캐시에 대해서 알아보도록 하겠습니다.
프로시저 캐시는 아래와 같이 4가지 타입으로 저장됩니다.
l CACHESTORE_OBJCP(Object Plans)
- SP 함수의 컴파일된 계획
l CACHESTORE_SQLCP(SQL Plans)
- prepared, ad-hoc쿼리의 컴파일된 계획
l CACHESTORE_PHDR(Bound Trees)
- 뷰, 제약조건, 기본값 등
l CACHESTORE_XPROC (Extended Stored Procedures)
- 확장 저장 프로시저
프로시저 캐시의 대부분의 용량을 차지하는것은 CACHESTORE_OBJCP 또는 CACHESTORE_SQLCP입니다. 위에서 설명하고 있듯이 CACHESTORE_OBJCP는 SP 및 함수 입니다. 즉 매번 재 컴파일을 하지 않아도 되는 계획입니다. 하지만 CACHESTORE_SQLCP의 경우 ad-hoc쿼리이기에 매번 재컴파일 할 수 있는 가능성이 매우 크게 됩니다.
대부분 프로시저 캐시가 커져서 문제가 되는 경우는 CACHESTORE_SQLCP 크기가 늘어남으로써 문제가 되고 있습니다. 그렇다면, 재컴파일도 하지 않고 적정한 크기를 가지는 CACHESTORE_OBJCP는 그냥 두고 매번 재컴파일하여 비효율 적인 CACHESTORE_SQLCP만을 프로시저 캐시에서 초기화 하는 것이 보다 효율적인 방법일 수 있습니다.
우선 서버에 타입별 프로시저 캐시 크기를 메모리 관련 DMV로 확인 할 수 있습니다.
select * from sys.dm_os_memory_clerks
where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')
그럼 프로시저 캐시를 초기화 하기 위해서는 DBCC FREEPROCCACHE를 사용하게 되며,
이 DBCC 구문은 모든 프로시저 캐시를 초기화 하게 됩니다.
하지만 이것은 비효율일 수 있습니다. 대부분의 메모리를 차지하고 있는건 SQLCP이고
초기화 시켜야 할 것도 SQLCP이기에, OBJCP까지 같이 초기화 되기 때문입니다.
그럼, SQLCP만 초기화 시키는 방법은?
아래를 참고 하세요.
초기화 이전에는 CACHESTORE_SQLCP의 single_pages_kb가 44088KB였으나,
DBCC FREESYSTEMCACHE 구문을 실행하면 552KB로 변경된 것을 확인 할 수 있습니다.
select * from sys.dm_os_memory_clerks
where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')
DBCC FREESYSTEMCACHE('SQL Plans')
select * from sys.dm_os_memory_clerks
where type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')
다른 프로시저 캐시에 대해서도 내릴 수 있습니다.
DBCC FREESYSTEMCACHE('OBJECT Plans')
DBCC FREESYSTEMCACHE('BOUND Trees')
http://blogs.msdn.com/sqlprogrammability/archive/tags/Procedure+Cache/default.aspx
송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
