Veritabanı uygulamaları yapan her yazılımcı, bir şekilde zamanı geldiğinde; -ki kesin gelir, veritabanı üzerinde çeşitli performans kriterlerine dikkat etmek durumda kalır. Çeşitli kontroller ile sistemin performansını ölçüp, gerekirse iyileştirmeleri bunların sonuçlarına göre yapar…

SQL Server ile uygulama geliştirirken zaman zaman kullandığım ve oldukça işime yarayan, hatta çoğu zaman hayat kurtarmaya bile destek sağlayan bir kaç SQL script’ini derledim. Benzerleri hatta daha faydalı olanlarını Google’da da çok rahat bulabilirsiniz. Burada benim işimi görenleri derleyip paylaşmak istedim, belki birilerinin daha işini görür.

En çok CPU tüketen 50 sorgu

select
 q.,
 SUBSTRING(q.text, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(q.text)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2) + 1) AS statement_text,
 qs.last_execution_time,
 qs.execution_count,
 qs.total_worker_time/1000000 as total_cpu_time_sn,
 qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
 qp.query_plan,
 DB_NAME(q.dbid) as database_name,
 q.objectid,
 q.number,
 q.encrypted
from
 (select top 50
 qs.last_execution_time,
 qs.execution_count,
 qs.plan_handle,
 qs.total_worker_time,
 qs.statement_start_offset,
 qs.statement_end_offset
 from sys.dm_exec_query_stats qs
 order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) q
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by qs.total_worker_time desc

En çok I/O yapan 50 sorgu

select
 q.,
 SUBSTRING(q.text, (qs.statement_start_offset/2)+1,
 ((CASE qs.statement_end_offset
 WHEN -1 THEN DATALENGTH(q.text)
 ELSE qs.statement_end_offset
 END - qs.statement_start_offset)/2) + 1) AS statement_text,
 qs.last_execution_time,
 qs.execution_count,
 qs.total_logical_reads as total_logical_read,
 qs.total_logical_reads/execution_count as avg_logical_read,
 qs.total_worker_time/1000000 as total_cpu_time_sn,
 qs.total_worker_time/qs.execution_count/1000 as avg_cpu_time_ms,
 qp.query_plan,
 DB_NAME(q.dbid) as database_name,
 q.objectid,
 q.number,
 q.encrypted
from
 (select top 50
 qs.last_execution_time,
 qs.execution_count,
 qs.plan_handle,
 qs.total_worker_time,
 qs.total_logical_reads,
 qs.statement_start_offset,
 qs.statement_end_offset
 from sys.dm_exec_query_stats qs
 order by qs.total_worker_time desc) qs
cross apply sys.dm_exec_sql_text(plan_handle) q
cross apply sys.dm_exec_query_plan(plan_handle) qp
order by qs.total_logical_reads desc

Index’lerin fragmentation oranları

SELECT
 ps.object_id,
 i.name as IndexName,
 OBJECT_SCHEMA_NAME(ps.object_id) as ObjectSchemaName,
 OBJECT_NAME (ps.object_id) as ObjectName,
 ps.avg_fragmentation_in_percent,
 ps.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') ps
INNER JOIN sys.indexes i ON i.object_id=ps.object_id and i.index_id=ps.index_id
WHERE avg_fragmentation_in_percent > 5 AND ps.index_id > 0
ORDER BY avg_fragmentation_in_percent desc

Eksik index’leri sorgulama

select TOP 10 DB_NAME(id.database_id) as databaseName,
 id.statement as TableName,
 id.equality_columns,
 id.inequality_columns,
 id.included_columns,
 gs.last_user_seek,
 gs.user_seeks,
 gs.last_user_scan,
 gs.user_scans,
 gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans) as ImprovementValue
from sys.dm_db_missing_index_group_stats gs
INNER JOIN sys.dm_db_missing_index_groups ig on gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details id on id.index_handle = ig.index_handle
order by avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) desc