Kategori: MsSql

Ms Sql database ve tablo boyut bilgileri

Posted on by Tarık AKBAY 
--Tüm db lerin bellekteki sayfa sayılarının verir
SELECT database_id,DB_NAME(database_id),COUNT(*) "Bellekteki Sayfa Sayisi",
(COUNT(*)*8)/1024 AS "Bellekteki Sayfa Boyutu KB"
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id

--Tablonun satır sayısı ve ne kadar yer tutuğu bilgisini verir
sp_spaceused ProductMovement

--databaseleri gösterir
select * from sys.databases

--Database dosyalarını gösterir
SELECT * FROM sys.database_files

--Database bilgilerini verir
sp_helpdb DATABASE_ISMI

--Sunucu üzerinde bağlantıları ve processlerini görmek için
sp_who2

--Tablonun object IDsini verir
SELECT OBJECT_ID('Brand')
-- ObjectID nin adını verir
SELECT OBJECT_NAME('2098106515')

--Tablosunun page'leri gösterir
DBCC IND(0,'Brand',1)

--Tablonun indexlerini gösterir
SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('Brand')

 

Mssql İzleme Komutları

Posted on by Tarık AKBAY 
--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

 

Sql Server fragmentation

Posted on by Tarık AKBAY 
--Tablo indexlerinin dağılma oranı
--avg_fragmentation_in_percent alanı %5 ile %30 ise index reorganize edilir
--avg_fragmentation_in_percent alanı %30dan büyük ise index rebuild edilir
--Nasıl yapılır: tablo -> Indexes -> sağ tuş rebuild veye reorganize edilir
SELECT OBJECT_NAME(ps.OBJECT_ID),i.name, ps.index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'TABLE_NAME'), NULL, NULL, NULL , 'SAMPLED') AS PS
INNER JOIN sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
ORDER BY avg_fragmentation_in_percent DESC