Bora.Choi

-- 리소스 관리자 category view
-- veiw any definition, 변경시 control server 
select * from sys.resource_governor_configuration  with (nolock)
SELECT 
 classifier_function_id
 ,object_schema_name(classifier_function_id) AS 'Classifier UDF schema' 
 ,object_name(classifier_function_id) AS 'Classifier UDF name'
 ,is_enabled
FROM sys.resource_governor_configuration
GO
-- resource pool info
select * from sys.resource_governor_resource_pools  with (nolock)
select * from sys.resource_governor_workload_groups with (nolock)
 
 
-- 리소스 관리자를 변경했을 때 재구성 문 필요 여부
select * from sys.dm_resource_governor_configuration   with (nolock)
 
select * from sys.dm_resource_governor_workload_groups  with (nolock)
select * from sys.dm_resource_governor_resource_pools as with (nolock)

select p.pool_id, g.group_id, p.name, p.statistics_start_time
 , p.total_cpu_usage_ms,p.used_memgrant_kb, p.active_memgrant_kb
 , p.min_cpu_percent, p.max_cpu_percent, p.min_memory_percent, p.max_memory_percent
 , g.name, g.importance,g.total_cpu_usage_ms, g.max_dop
 , g.total_query_optimization_count, g.active_parallel_thread_count
from sys.dm_resource_governor_resource_pools as p with (nolock)
 join sys.dm_resource_governor_workload_groups as g with (nolock)
  on p.pool_id = g.pool_id
order by p.pool_id

메모리 사용을 대기하는 쿼리의 정보를 반환합니다. 

메모리 대기를 하지 않는 쿼리는 나타나지 않으며, 이 쿼리에 실행되는 내역이 많다면 메모리 할당 대기가 많다는 의미 입니다. 

-- memory used
select top 50
  --mem.session_id,req.group_id, -- sql 2008 이상 가능
  db_name(req.database_id) as db,
  mem.request_time, mem.grant_time,
  mem.granted_memory_kb,mem.used_memory_kb, mem.required_memory_kb, 
  mem.query_cost,mem.timeout_sec,mem.wait_time_ms as 'wait_time',
  object_schema_name(sql_text.objectid,sql_text.dbid) as schema_n,
  object_name(sql_text.objectid, sql_text.dbid) as spname,
  CASE WHEN   req.statement_end_offset = -1 and ( (req.statement_start_offset / 2)  > DATALENGTH (sql_text.text) )
            THEN  convert(varchar(4000), 
                 substring(isnull(sql_text.text, '') 
               , 1, ( ( DATALENGTH (sql_text.text) - 1 )/2 ) + 1 ) )
            ELSE  convert(varchar(4000), substring(isnull(sql_text.text, '') 
             , (req.statement_start_offset / 2) + 1
             , (( case when req.statement_end_offset = -1 then DATALENGTH (sql_text.text) else req.statement_end_offset end 
               - req.statement_start_offset ) /2 ) + 1) )
            END  as query_text,
  ses.login_name, ses.host_name, ses.program_name,
  mem.scheduler_id
from sys.dm_exec_requests as req with (nolock)
  join sys.dm_exec_sessions as ses with (nolock) on req.session_id = ses.session_id
  join sys.dm_exec_query_memory_grants as mem with (nolock) on req.request_id = mem.request_id
    and ses.session_id = mem.session_id
  cross apply sys.dm_exec_sql_text(req.sql_handle) as sql_text
where ses.is_user_process = 1  and wait_type <> 'WAITFOR' 
  --and req.database_id = db_id('tiger')
  -- and mem.grant_time is null -- not allocation 
order by used_memory_kb desc

1. I/O Pending 조회

-- =====================================
-- I/O pending
-- 어떤 row도 반환되지 않아야 한다.
-- =====================================
select 
    database_id, 
    db_name(database_id) as db_name,
    file_id,
    t2.io_type, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

2. 현재 db의 i/o stall 정보

-- ==========================
-- 현재 db의 i/o _io_virtual_file
-- ==========================
declare @db_name sysname
set @db_name = null
select db_name(database_id), file_id ,io_stall_read_ms ,
    cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms' ,
    num_of_reads ,
    cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms' ,
    io_stall_write_ms ,num_of_writes ,
    cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms' ,
    io_stall_read_ms + io_stall_write_ms as io_stalls ,
    num_of_reads + num_of_writes as total_io 
from sys.dm_io_virtual_file_stats(DB_ID(@db_name),null) 
order by avg_io_stall_ms desc

3. 대량의 i/o 사용 쿼리

-- ==========================
-- 대량의 i/o기준 상위
-- ==========================
SELECT TOP 10 qt.dbid,
     object_schema_name(qt.objectid,qt.dbid) + '.' + object_name(qt.objectid,qt.dbid) [object_name],
     (qs.total_logical_reads/execution_count) as avg_logical_reads,
     (qs.total_logical_writes/execution_count) as avg_logical_writes,
     (qs.total_logical_reads + qs.total_logical_writes) / qs.execution_count as [Avg IO],
    substring (qt.text,qs.statement_start_offset/2, 
    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, 
    qt.objectid, object_name(qt.objectid) as sp_name
FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt ORDER BY [Avg IO] DESC

실행 되는 SESSION TEMPDB의 페이지 사용량 측정.

세가지 쿼리다 결과는 같음.

--각 세션별 사용되는 Page, Plan, Statement
WITH BASE AS (
  SELECT session_id, request_id,
 SUM(internal_objects_alloc_page_count) AS [internal object pages alloc used],
 (SUM(internal_objects_alloc_page_count)*1.0/128) AS [internal object alloc space in MB],
 SUM(internal_objects_dealloc_page_count) AS [internal object pages dealloc used],
 (SUM(internal_objects_dealloc_page_count)*1.0/128) AS [internal object dealloc space in MB],
 SUM(user_objects_alloc_page_count) AS [user object pages alloc used],
 (SUM(user_objects_alloc_page_count)*1.0/128) AS [user object alloc space in MB],
 SUM(user_objects_dealloc_page_count) AS [user object pages dealloc used],
 (SUM(user_objects_dealloc_page_count)*1.0/128) AS [user object dealloc space in MB]
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id
)
SELECT object_schema_name(R3.objectid,R3.dbid) + '.' + object_name(R3.objectid,R3.dbid) as [object_name], R1.session_id, R1.request_id, 
    R1.[internal object pages alloc used], R1.[internal object alloc space in MB],
 R1.[internal object pages dealloc used], R1.[internal object dealloc space in MB],
 R1.[user object pages alloc used], R1.[user object alloc space in MB],
 R1.[user object pages dealloc used], R1.[user object dealloc space in MB],
    R2.statement_start_offset, R2.statement_end_offset, R4.QUERY_PLAN, R3.text
FROM BASE R1
INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
OUTER APPLY sys.dm_exec_query_plan(R2.plan_handle) AS R4
WHERE R1.session_id > 50
ORDER BY 4 desc

select top 50   
     u.session_id,    
    s.host_name,    
    s.login_name,  
    s.status,  
    s.program_name, 
    sum(u.user_objects_alloc_page_count+u.internal_objects_alloc_page_count)*8 tempdb_space_alloc_kB,    
    sum(u.user_objects_dealloc_page_count+u.internal_objects_dealloc_page_count)*8 tempdb_space_dealloc_kB,   
    sum(u.user_objects_alloc_page_count+u.internal_objects_alloc_page_count -u.user_objects_dealloc_page_count - u.internal_objects_dealloc_page_count)*8 remaining_tempdb_space_alloc_kB    
from sys.dm_db_session_space_usage as u   
    join sys.dm_exec_sessions as s on s.session_id = u.session_id   
where u.database_id = 2   
group by u.session_id, s.host_name,  s.status, s.login_name, s.program_name   
order by 7 desc  

SELECT task.session_id, ses.host_name, ses.login_name
 ,ses.login_time, task.request_id, task.alloc,task.dealloc
    ,object_name(qt.objectid, qt.dbid) as 'SPname'
    ,req.cpu_time,req.logical_reads,req.reads, req.writes  
    ,substring(qt.text,req.statement_start_offset/2,
        (case when req.statement_end_offset = -1
        then len(convert(nvarchar(max), qt.text)) * 2
        else req.statement_end_offset end - req.statement_start_offset)/2) as query
    ,req.statement_start_offset, req.statement_end_offset, req.plan_handle, req.sql_handle
FROM 
       (
             Select session_id, request_id,
                    SUM(internal_objects_alloc_page_count) /128 as alloc,
                    SUM (internal_objects_dealloc_page_count)/128 as dealloc 
              FROM sys.dm_db_task_space_usage  with (nolock)
             GROUP BY session_id, request_id
    ) AS task
inner join  sys.dm_exec_requests AS req  with (nolock) 
       on task.session_id = req.session_id and task.request_id = req.request_id
inner join sys.dm_exec_sessions as ses with(nolock) on req.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt  
ORDER BY task.alloc DESC

• ODBC Tasks  : 5 minutes 57 seconds 
• ADO NET Tasks  : 11 seconds
• OLEDB Task
• fast load, table lock option: 5 seconds.
• table or view option : 2 minutes and 21 seconds
• sql command :  2.85 seconds.
• SQL Server Destination
• oledb destination : 2.5 seconds.
• T-SQL Tasks
• 프로시저 생성해서 작업 : 1.8 seconds!   -> 이건 같은 DB 일 때 네요.

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

인덱스 압축 했을 때 예상 사이즈 조회

  exec sp_estimate_data_compression_savings  
   @schema_name = 'dbo'
  ,@object_name = '테이블명'
  ,@index_id = 1
  ,@partition_number = null, @data_compression = 'page'  -- 압축 방법