보미네

누군가 tempdb 가 이상해요 라고 연락이 왔다. 아래 문서와 쿼리를 이용해 문제를 해결한다.

tempdb 를 core 수만큼 만드는 것은 tempdb 유지관리 비용으로 인해 allocation contention 해결 비용보다 더 클 수 도 있다는 설명도 들어있는 좋은 문서이다. workload 에 따라서 튜닝방법은 달라져야 한다. 
 

CREATE database perf_warehouse
GO
USE perf_warehouse
GO

CREATE TABLE tempdb_space_usage (
  -- This represents the time when the particular row was
  -- inserted
  dt datetime DEFAULT CURRENT_TIMESTAMP,
  -- session id of the sessions that were active at the time
  session_id int DEFAULT null,
  -- this represents the source DMV of information. It can be
  -- track instance, session or task based allocation information.
  scope varchar(10), 
  -- instance level unallocated extent pages in tempdb
  Instance_unallocated_extent_pages bigint,
  -- tempdb pages allocated to verstion store
  version_store_pages bigint,
  -- tempdb pages allocated to user objects in the instance
  Instance_userobj_alloc_pages bigint,   
  -- tempdb pages allocated to internal objects in the instance
  Instance_internalobj_alloc_pages bigint,
  -- tempdb pages allocated in mixed extents in the instance
  Instance_mixed_extent_alloc_pages bigint,
  -- tempdb pages allocated to user obejcts within this sesssion or task.
  Sess_task_userobj_alloc_pages bigint,   
  -- tempdb user object pages deallocated within this sesssion
  -- or task.
  Sess_task_userobj_deallocated_pages bigint,
  -- tempdb pages allocated to internal objects within this sesssion
  -- or task
  Sess_task_internalobj_alloc_pages bigint,
  -- tempdb internal object pages deallocated within this sesssion or
  -- task
  Sess_task_internalobj_deallocated_pages bigint,   
  -- query text for the active query for the task 
  query_text nvarchar(max) 
)
go

-- Create a clustered index on time column when the data was collected
CREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)
go

 

 

아래 프로시저를 주기적으로 돌려 tempdb 문제를 일으키는 쿼리를 찾는다.

CREATE PROC sp_sampleTempDbSpaceUsage AS
  -- Instance level tempdb File space usage for all files within
  -- tempdb
  INSERT tempdb_space_usage (
    scope,
    Instance_unallocated_extent_pages,
    version_store_pages,
    Instance_userobj_alloc_pages,
    Instance_internalobj_alloc_pages,
    Instance_mixed_extent_alloc_pages)
  SELECT
    'instance',
    SUM(unallocated_extent_page_count),
    SUM(version_store_reserved_page_count),
    SUM(user_object_reserved_page_count),
    SUM(internal_object_reserved_page_count),
    SUM(mixed_extent_page_count)
  FROM sys.dm_db_file_space_usage
   
    -- 2. tempdb space usage per session
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages)
  SELECT
    'session',
    session_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
  FROM sys.dm_db_session_space_usage
    WHERE session_id > 50

    -- 3. tempdb space usage per active task
    --
  INSERT tempdb_space_usage (
    scope,
    session_id,
    Sess_task_userobj_alloc_pages,
    Sess_task_userobj_deallocated_pages,
    Sess_task_internalobj_alloc_pages,
    Sess_task_internalobj_deallocated_pages,
    query_text)
  SELECT
    'task',
    R1.session_id,
    R1.user_objects_alloc_page_count,
    R1.user_objects_dealloc_page_count,
    R1.internal_objects_alloc_page_count,
    R1.internal_objects_dealloc_page_count,
    R3.text
  FROM sys.dm_db_task_space_usage AS R1
    LEFT OUTER JOIN
    sys.dm_exec_requests AS R2
    ON R1.session_id = R2.session_id
    OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
  WHERE R1.session_id > 50

언제? 어떤 호스트에서? 어떤 프로그램을 이용해서? 호스트의 프로세스 아이디는? 로그인 네임은 뭘로? text 는 뭐야?

아래 그림을 보면 음...... 이제 다 찾았습니다. 
용서를 할지.....로그인 기록부터 뒤져서....기때기를 때릴지...

if object_id('tbl_audit') is not null
 drop table tbl_audit
 go
 
create table tbl_audit
(eventtime datetime
 ,hostname varchar(100)
 ,program_name varchar(100)
 ,hostprocess int
 ,loginame varchar(100)
 ,text varchar(max))
 go
 
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 100
 
 
 
if object_id('tr_delete_tblx') is null
exec ('create trigger tr_delete_tblx on tblx after delete as select 1')
 go
 
alter trigger tr_delete_tblx
on tblx
after delete
as
 if @@rowcount > 0 begin
 set nocount on  -- trigger 에서는 주의!
 declare @buffertext table
 (eventtype varchar(100),parameters varchar(100),eventinfo nvarchar(max))
  declare @text nvarchar(max)
 
 insert into @buffertext exec('dbcc inputbuffer('+@@spid+')')
  select @text = eventinfo from @buffertext
 
 insert into tbl_audit (eventtime, hostname, program_name, hostprocess, loginame, text)
  select getdate() eventime, hostname, program_name, hostprocess, loginame, @text text
   from master.dbo.sysprocesses a
   where a.spid = @@spid
end
 go
 
delete from tblx where idx = 10
 go
 
select * from tblx
select * from tbl_audit

 

복잡하게 셋팅해야하는 sqldiag xml 파일을 이제는 툴로 만들어 배포하고 있다.
좀 더 쉽게 성능을 모니터링 할 수 있다.

요런툴 자기만 쓸 수 있다고 편하게 생각하지 말자 툴은 툴이고, 현상을 분석하고 원리를 아는데 투자하는 시간을 많이 가지도록 한다.

뭐 다 알던 기능이라 흥미 있는 기능은 없다.
그래도 스크린샷 구경이나 한번 해보자
사용방법도 너무 쉬워서 아무 설명도 필요 없다. 
그래도 혹시 모르겠으면 소개 Page 의 instruction 을 읽어봐라.

다음은 Coniguration Manager 에서 간단하게 셋팅한 후 분석할 수 있는 스크린 샷이다.

위 툴은 너무 많이 알려줘 아무나 할 수 있다. 그런데, 현상에 대한 분석은 Google 신의 도움이 필요 할 수도 있다. 분석을 위한 시간이 많은 공부가 될 것이다.

이건 예전에 MS 내부 기술지원을 할 때 엔지니어가 셋팅하는 버전으로 있었는데 배포되었다. CASE 번호도 적을수 있도록 되어있다. (이건 좀 지우고 배포하지 -_-) 예전에 MS 내부 분석용 프로그램을 보니 저 넥서스에서 분석한것 외에도 sqldig 의 output 값으로 파싱해 리포트 결과를 보는 다른 툴도 있더라.

회사의 SQL Server 에 문제에 대한 선 분석이 필요하다고 생각되면 MS 에서 SQLRAP 을 받아보는 것도 좋다.

위 Config Manager 를 소개한 링크는 다음 링크를 참고한다.

http://blogs.msdn.com/b/psssql/archive/2011/05/24/pssdiag-sqldiag-configuration-manager-released-to-codeplex.aspx