Mssql İzleme Komutları

--Top IO kullanan
SELECT TOP 10
creation_time
,       last_execution_time
,       total_logical_reads AS [LogicalReads]
,       total_logical_writes AS [LogicalWrites]
,       execution_count
,       total_logical_reads+total_logical_writes AS [AggIO]
,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS [AvgIO]
,      st.TEXT
,       DB_NAME(st.dbid) AS database_name
,       st.objectid AS OBJECT_ID
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes > 0
AND sql_handle IS NOT NULL
ORDER BY [AggIO] DESC


--Top CPU kullanan
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
 ORDER BY qs.total_worker_time DESC -- CPU time

SELECT
allocated_page_file_id as PageFID
,allocated_page_page_id as PagePID
,allocated_page_iam_file_id as IAMFID
,allocated_page_iam_page_id as IAMPID
,object_id as ObjectID
,index_id as IndexID
,partition_id as PartitionNumber
,rowset_id as PartitionID
,allocation_unit_type_desc as iam_chain_type
,page_type as PageType
,page_type_desc as PageTypDesc
,page_level as IndexLevel
,next_page_file_id as NextPageFID
,next_page_page_id as NextPagePID
,previous_page_file_id as PrevPageFID
,previous_page_page_id as PrevPagePID
FROM sys.dm_db_database_page_allocations(DB_ID('VERITABANI_ISMI'), OBJECT_ID('COMPANY'), NULL, NULL, 'DETAILED')

SELECT allocated_page_page_id "Sayfa No",
extent_page_id "Extent Ýlk Sayfasý", 
allocated_page_iam_page_id,
is_allocated,
is_mixed_page_allocation,
page_type,
page_type_desc
FROM sys.dm_db_database_page_allocations(
	DB_ID('VERITABANI_ISMI'), OBJECT_ID('COMPANY'), NULL, NULL, 'DETAILED')

SELECT OBJECT_NAME(object_id), * FROM sys.dm_db_database_page_allocations(
DB_ID('VERITABANI_ISMI'), NULL, NULL, NULL, 'DETAILED')

--Tabloların kullanım boyutlarını verir
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

 

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir