블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (413)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total139,119
Today56
Yesterday67

달력

« » 2016.08
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

공지사항

worker thread

분류없음 / 2016.08.07 13:17

declare @session_limit int 

declare @current_worker_cnt int

declare @session_current int 

declare @max_worker_threads int 

select @session_limit = cast(value_in_use as int) from sys.configurations where name = 'user connections'

select @max_worker_threads = cast(value_in_use as int) from sys.configurations where name = 'max worker threads'

select @session_current = count(*) from sys.dm_exec_sessions 

select @current_worker_cnt = count(*) from sys.dm_os_workers 

select 

  @session_limit session_limit

, @session_current current_session_cnt 

, @max_worker_threads max_worker_thread

, @current_worker_cnt current_worker_cnt

, s.scheduler_id

, quantum_used

, is_preemptive

, context_switch_count

, state

, w.last_wait_type

, processor_group

, tasks_processed_count

, w.task_address

, t.session_id 

, se.original_login_name

, se.host_name

, se.program_name 

, r.command 

, r.cpu_time

, r.total_elapsed_time

, r.reads

, r.writes

, r.logical_reads 

, r.sql_handle

, r.query_hash 

, q.dbid

, q.text 

from sys.dm_os_workers w

join sys.dm_os_schedulers s

on w.scheduler_address = s.scheduler_address 

left join sys.dm_os_tasks t

on t.task_address = w.task_address

left join sys.dm_exec_sessions se

on t.session_id = se.session_id

left join sys.dm_exec_requests r 

on se.session_id = r.session_id 

outer apply 

    ( 

    select top 1 * from sys.dm_exec_sql_text (sql_handle) 

    ) q

where r.command is not null

order by quantum_used desc 

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

MEMORY

분류없음 / 2016.08.07 13:06


//SELECT '01' ORDERKEY,'OS_MEMORY_TOTAL' TYPE, PHYSICAL_MEMORY_KB PAGES_KB FROM SYS.DM_OS_SYS_INFO

//UNION ALL 

//SELECT '02' ORDERKEY,'SQL_VISIBLE_TARGET_KB' TYPE, VISIBLE_TARGET_KB PAGES_KB FROM SYS.DM_OS_SYS_INFO

//UNION ALL 

//SELECT '02' ORDERKEY,'SQL_COMMITTED_KB' TYPE, COMMITTED_KB PAGES_KB FROM SYS.DM_OS_SYS_INFO

//UNION ALL 

//SELECT '03' ORDERKEY,'OS_MEMORY_CLERKS_TOTAL' TYPE, SUM(PAGES_KB) PAGES_KB FROM SYS.DM_OS_MEMORY_CLERKS

//UNION ALL 

//SELECT * 

//FROM 

//    ( 

//    SELECT TOP 20 '04' ORDERKEY, TYPE, SUM(PAGES_KB) PAGES_KB  FROM SYS.DM_OS_MEMORY_CLERKS

//    GROUP BY TYPE 

//    ORDER BY 3 DESC 

//    ) A 

//ORDER BY 1,3 DESC 





//-- 데이터베이스별 캐시 사이즈 

//set nocount on

//set transaction isolation level read uncommitted  

//DECLARE @total_buffer INT;


//SELECT @total_buffer = cntr_value

//FROM sys.dm_os_performance_counters 

//WHERE RTRIM([object_name]) LIKE '%Buffer Manager'

//AND counter_name = 'Database Pages';


//;WITH src AS

//(

//SELECT 

//database_id, db_buffer_pages = COUNT_BIG(*)

//FROM sys.dm_os_buffer_descriptors

//--WHERE database_id BETWEEN 5 AND 32766

//GROUP BY database_id

//)

//SELECT

//[db_name] = CASE [database_id] WHEN 32767 

//THEN 'Resource DB' 

//ELSE DB_NAME([database_id]) END,

//db_buffer_pages,

//db_buffer_MB = db_buffer_pages / 128,

//db_buffer_percent = CONVERT(DECIMAL(6,3), 

//db_buffer_pages * 100.0 / @total_buffer)

//FROM src

//ORDER BY db_buffer_MB DESC; 




//-- 인덱스별 사이즈 캐시 사이즈

//set nocount on

//set transaction isolation level read uncommitted  


//;WITH src AS

//(

//SELECT

//[Object] = o.name,

//[Type] = o.type_desc,

//[Index] = COALESCE(i.name, ''),

//[Index_Type] = i.type_desc,

//p.[object_id],

//p.index_id,

//au.allocation_unit_id

//FROM

//sys.partitions AS p

//INNER JOIN

//sys.allocation_units AS au

//ON p.hobt_id = au.container_id

//INNER JOIN

//sys.objects AS o

//ON p.[object_id] = o.[object_id]

//INNER JOIN

//sys.indexes AS i

//ON o.[object_id] = i.[object_id]

//AND p.index_id = i.index_id

//WHERE

//au.[type] IN (1,2,3)

//AND o.is_ms_shipped = 0

//)

//SELECT top 10 

//src.[Object],

//src.[Type],

//src.[Index],

//src.Index_Type,

//buffer_pages = COUNT_BIG(b.page_id),

//buffer_mb = COUNT_BIG(b.page_id) / 128

//FROM

//src

//INNER JOIN

//sys.dm_os_buffer_descriptors AS b

//ON src.allocation_unit_id = b.allocation_unit_id

//WHERE

//b.database_id = DB_ID()

//GROUP BY

//src.[Object],

//src.[Type],

//src.[Index],

//src.Index_Type

//ORDER BY

//buffer_pages DESC;



저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

workthread

분류없음 / 2016.08.06 20:38

https://msdn.microsoft.com/ko-kr/library/ms190219.aspx

저작자 표시 비영리 변경 금지
신고
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바