블로그 이미지
bedbmsguru

Notice

Recent Comment

Recent Trackback

Archive

calendar

    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    
  • 30,031total
  • 0today
  • 2yesterday
2010.11.03 11:36 SQL SERVER

SELECT st.* FROM sys.sysprocesses AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
WHERE spid = 54

'SQL SERVER' 카테고리의 다른 글

SQLCMD에 관해서..  (0) 2010.11.03
System Database Restore OR move하는법  (0) 2010.11.03
특정 프로세스가 실행중인 쿼리 보기  (0) 2010.11.03
Blocking 체크  (0) 2010.11.03
인덱스 조각화 확인  (0) 2010.11.03
UPGRADE Vs MIGRATION  (0) 2010.11.03
posted by bedbmsguru
2010.11.03 11:36 SQL SERVER

SELECT  tl.request_session_id AS WaitingSessionID
       ,wt.blocking_session_id AS BlockingSessionID
       ,wt.resource_description
       ,wt.wait_type
       ,wt.wait_duration_ms
       ,DB_NAME(tl.resource_database_id) AS DatabaseName
       ,tl.resource_associated_entity_id AS WaitingAssociatedEntity
       ,tl.resource_type AS WaitingResourceType
       ,tl.request_type AS WaitingRequestType
       ,wrt.[text] AS WaitingTSql
       ,btl.request_type BlockingRequestType
       ,brt.[text] AS BlockingTsql
FROM    sys.dm_tran_locks tl
        JOIN sys.dm_os_waiting_tasks wt
        ON tl.lock_owner_address = wt.resource_address
        JOIN sys.dm_exec_requests wr
        ON wr.session_id = tl.request_session_id
        CROSS APPLY sys.dm_exec_sql_text(wr.sql_handle) AS wrt
        LEFT JOIN sys.dm_exec_requests br
        ON br.session_id = wt.blocking_session_id
        OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS brt
        LEFT JOIN sys.dm_tran_locks AS btl
        ON br.session_id = btl.request_session_id

'SQL SERVER' 카테고리의 다른 글

System Database Restore OR move하는법  (0) 2010.11.03
특정 프로세스가 실행중인 쿼리 보기  (0) 2010.11.03
Blocking 체크  (0) 2010.11.03
인덱스 조각화 확인  (0) 2010.11.03
UPGRADE Vs MIGRATION  (0) 2010.11.03
SQL SERVER Function  (0) 2010.11.03
posted by bedbmsguru
2010.11.03 11:35 SQL SERVER

--sys.dm_db_index_physical_stats(디비ID, object_ID, indexid(null은 모든인덱스), partition_number , mode(Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED.)

select a.index_id, name, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('테이블이름'), NULL, NULL, NULL) AS a
inner join sys.indexes AS b
 on a.object_id = b.object_id and a.index_id = b.index_id

'SQL SERVER' 카테고리의 다른 글

특정 프로세스가 실행중인 쿼리 보기  (0) 2010.11.03
Blocking 체크  (0) 2010.11.03
인덱스 조각화 확인  (0) 2010.11.03
UPGRADE Vs MIGRATION  (0) 2010.11.03
SQL SERVER Function  (0) 2010.11.03
Log Shipping 잘 되고 있는지 체크용 쿼리  (0) 2010.11.03
posted by bedbmsguru

티스토리 툴바