Bora.Choi

DECLARE @TABLE_NAME SYSNAME, @INDEX_ID INT
SET @TABLE_NAME = 
SET @INDEX_ID = 

-- REBUILD
SELECT A.*, B.ROWS, A.ROWS-B.ROWS AS DIFF_ROWS, B.ROWS*100.0/A.ROWS as '%'
FROM SYS.PARTITIONS A WITH(NOLOCK) JOIN SYS.PARTITIONS B WITH(NOLOCK) 
	ON A.OBJECT_ID=B.OBJECT_ID AND A.INDEX_ID=B.INDEX_ID 
	AND A.PARTITION_NUMBER=B.PARTITION_NUMBER AND A.ROWS<>B.ROWS
WHERE A.OBJECT_ID=OBJECT_ID(@TABLE_NAME)
	AND A.INDEX_ID= @INDEX_ID
	AND A.ROWS-B.ROWS>0
	

-- NEW
SELECT * FROM SYS.PARTITIONS WITH(NOLOCK) WHERE OBJECT_ID = OBJECT_ID(@TABLE_NAME)
SELECT * FROM SYS.SYSPROCESSES WITH(NOLOCK) WHERE SPID = 

begin try  
if (select convert(int,value_in_use) from sys.configurations where name = 'default trace enabled' ) = 1 
begin 
declare @curr_tracefilename varchar(500) ; 
declare @base_tracefilename varchar(500) ; 
declare @indx int ;

select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
set @curr_tracefilename = reverse(@curr_tracefilename);
select @indx  = patindex('%\%', @curr_tracefilename) ;
set @curr_tracefilename = reverse(@curr_tracefilename) ;
set @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' 


select  @@SERVERNAME as server_name
,	    (dense_rank() over (order by StartTime desc))%2 as l1
,       convert(int, EventClass) as EventClass
,       DatabaseName
,       Filename
,       (Duration/1000) as Duration
,       StartTime
,       EndTime
,       (IntegerData*8.0/1024) as ChangeInSize 
from ::fn_trace_gettable( @base_tracefilename, default ) 
where EventClass >=  92      and EventClass <=  95        and ServerName = @@servername 
order by StartTime desc ;   
end     else    
select -1 as l1, 0 as EventClass, 0 DatabaseName, 0 as Filename, 0 as Duration, 0 as StartTime, 0 as EndTime,0 as ChangeInSize 
end try 
begin catch 
select -100 as l1
,       ERROR_NUMBER() as EventClass
,       ERROR_SEVERITY() DatabaseName
,       ERROR_STATE() as Filename
,       ERROR_MESSAGE() as Duration
,       1 as StartTime, 1 as EndTime,1 as ChangeInSize 
end catch

--1. tempdb SizeSELECT name , db.log_reuse_wait_desc , ls.cntr_value  AS size_kb , lu.cntr_value AS used_kb , CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)    AS used_percent , CASE WHEN CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT) > .5 THEN    CASE     /* tempdb special monitoring */     WHEN db.name = 'tempdb'       AND log_reuse_wait_desc NOT IN ('CHECKPOINT', 'NOTHING') THEN 'WARNING'      /* all other databases, monitor foor the 50% fill case */     WHEN db.name <> 'tempdb' THEN 'WARNING'     ELSE 'OK'     END   ELSE 'OK' END   AS log_status FROM sys.databases db JOIN sys.dm_os_performance_counters lu  ON db.name = lu.instance_name JOIN sys.dm_os_performance_counters ls  ON db.name = ls.instance_name WHERE lu.counter_name LIKE  'Log File(s) Used Size (KB)%' AND ls.counter_name LIKE 'Log File(s) Size (KB)%' -- 2. temdb 사용 session,taskSELECT top 5 * FROM sys.dm_db_session_space_usage  ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT top 5 * FROM sys.dm_db_task_space_usageORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESCSELECT t1.session_id, t1.request_id, t1.task_alloc,  t1.task_dealloc, t2.sql_handle, t2.statement_start_offset,   t2.statement_end_offset, t2.plan_handleFROM (Select session_id, request_id,    SUM(internal_objects_alloc_page_count) AS task_alloc,    SUM (internal_objects_dealloc_page_count) AS task_dealloc   FROM sys.dm_db_task_space_usage   GROUP BY session_id, request_id) AS t1,   sys.dm_exec_requests AS t2WHERE t1.session_id = t2.session_id  AND (t1.request_id = t2.request_id)ORDER BY t1.task_alloc DESC

--3.  종합

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 char(7),      -- 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 collectedCREATE CLUSTERED INDEX cidx ON tempdb_space_usage (dt)goCREATE 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 > 50go

--5. 결과 쿼리 -- This query reports the maximum allocated space in tempdb over all the data points collectedSELECT  CONVERT (float, (MAX(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS max_tempdb_allocation_MBFROM     tempdb_space_usage WHERE scope = ‘instance’

--This query reports the average allocated space in tempdb over all the data points collectedSELECT       CONVERT (float, (AVG(version_store_pages +      Instance_userobj_alloc_pages +      Instance_internalobj_alloc_pages +      Instance_mixed_extent_alloc_pages)))/ 128.0    AS avg_tempdb_allocation_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- 버전 정보 저장 할당양SELECT       MAX(version_store_pages) AS max_version_store_pages_allocated,  MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’ 

SELECT  AVG(version_store_pages) AS max_version_store_pages_allocated,  AVG(version_store_pages)/ 128.0 AS max_version_store_allocated_space_MBFROM tempdb_space_usage WHERE scope = ‘instance’

-- top 5 내부 개첵 사용하는 requestSELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Max_Sess_task_allocated_pages_delta,     query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Max_Sess_task_allocated_pages_delta  DESCSELECT top 5 AVG ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))  AS Avg_Sess_task_allocated_pages_delta, query_textFROM tempdb_space_usage WHERE scope = 'task' and session_id > 50GROUP BY query_textORDER BY Avg_Sess_task_allocated_pages_delta  DESC

CPU에 해당하는 Task 보기

select  
	 ss.scheduler_id ,ss.parent_node_id,object_name(sql_text.objectid, sql_text.dbid)  as 'SP명'
	,ses.session_id , ses.host_name , ses.program_name , ses.client_interface_name 
	,ses.login_name 
	 ,substring(sql_text.text,sr.statement_start_offset/2,
			(case when sr.statement_end_offset = -1
			then len(convert(nvarchar(max), sql_text.text)) * 2
			else sr.statement_end_offset end - sr.statement_start_offset)/2) as query
from sys.dm_os_schedulers as ss with (nolock) 
inner join sys.dm_os_tasks as st with (nolock) on ss.scheduler_id = st.scheduler_id
inner join sys.dm_exec_requests as sr with (nolock) on st.request_id = sr.request_id
inner join sys.dm_exec_sessions as ses with (nolock) on sr.session_id = ses.session_id 
cross apply sys.dm_exec_sql_text(sr.sql_handle) as sql_text
-- where ss.parent_node_id = 2 -- numa 노드에서 실행되는 쿼리
-- where ss.scheduler_id = 24  -- cpu 스케줄에 할당된 쿼리
 

http://sqlblog.com/blogs/kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-from-cache.aspx

DBCC FREESYSTEMCACHE(TokenAndPermUserStore)    

--보안 캐쉬를 제거

DBCC FREESYSTEMCACHE    

--사용하지 않는 항목을 모든 캐시에서 직접 제거

DBCC FREEPROCCACHE

--프로시져 캐시에서 모든 요소를 제거

DBCC FLUSHPROCINDB (dbid)

--지정한 데이터베이스에 있는 스토어드 프로시저 캐시를 제거

Deadlock 발생시 원인과 해결법.

1. Deadlock 이유를 알고 싶으면 trace 1204 를 켜 준다.

2. Deadlock 발생되면 SQL의 에러로그에 로그가 남게 된다.

3.  에러로그를 확인해 보면 'Example_stored_proc'  프로시저에서 update 시 exclusive lock  이 발생하고 있다는 것을 확인 할 수 있다.

4. sp_helptext 'Example_stored_proc'  를 실행하여 프로시저의 내용을 확인 한다.

5. 이와 관련있는 프로시저를 확인하고 deadlock 이 발생하는 page를 확인할 수 있다.

Node:1 PAG: 9:1:18061 CleanCnt:2 Mode: SIU Flags: 0x2

6. 해당 페이지를 조사해 본다. , DBCC Page 사용

MSDN : http://support.microsoft.com/kb/83065

7.  object 확인

Use DBNAME
Select object_name(OBJECT_ID)

8. 프로시저에서 해당 object를 확인하고  update 문을 찾아 그대로 update 테스트 해 본다.

이때, 해당 컬럼에 인덱스가 있는지 확인 한다. update 시 인덱스가 없으면 table scan 을 하게되고 exclusive lock 이 발생한다.

9. Key 되는 컬럼에 인덱스를 생성 해 준다.

10. 후에 다시 실행해봐서 deadlock 이 발생하는지 확인 한다.