100 Production DBA & SRE Diagnostic Scripts

SELECT pid, usename, datname, state,
       now() - query_start AS query_age,
       now() - xact_start  AS xact_age,
       wait_event_type, wait_event,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state <> 'idle'
  AND now() - query_start > interval '1 minute'
ORDER BY query_start;

SELECT blocked.pid                AS blocked_pid,
       blocked.usename            AS blocked_user,
       left(blocked.query, 120)   AS blocked_query,
       blocker.pid                AS blocking_pid,
       blocker.usename            AS blocking_user,
       blocker.state              AS blocking_state,
       left(blocker.query, 120)   AS blocking_query,
       now() - blocked.query_start AS waiting_for
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocker ON blocker.pid = b.pid
ORDER BY waiting_for DESC;

SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_vacuum, last_autovacuum, last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 30;

SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC
LIMIT 30;

-- Gerekli: CREATE EXTENSION pg_stat_statements; (shared_preload_libraries)
SELECT round(total_exec_time::numeric, 0) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
       rows,
       left(query, 150) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 1. Primary Üzerinde (Run on Primary)
SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- 2. Standby Üzerinde (Run on Standby)
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2147483647, 2) AS pct_to_wraparound
FROM pg_database
ORDER BY xid_age DESC;

SELECT n.nspname AS schema_name, c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       pg_size_pretty(pg_relation_size(c.oid))       AS table_size,
       pg_size_pretty(pg_indexes_size(c.oid))        AS index_size,
       c.reltuples::bigint AS est_rows
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m','p')
  AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 25;

SELECT datname, blks_hit, blks_read,
       round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY blks_read DESC;

SELECT schemaname, relname, seq_scan, seq_tup_read,
       idx_scan, n_live_tup,
       round(seq_tup_read::numeric / NULLIF(seq_scan, 0)) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_size
FROM pg_index
GROUP BY indrelid, indkey, indclass, indoption, indexprs::text, indpred::text
HAVING count(*) > 1;

SELECT state, count(*) AS sessions,
       max(now() - state_change) AS longest_in_state
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

SELECT count(*) AS used,
       current_setting('max_connections')::int AS max_connections,
       round(100.0 * count(*) / current_setting('max_connections')::int, 1) AS used_pct
FROM pg_stat_activity;

SELECT slot_name, slot_type, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

SELECT d.datname,
       pg_size_pretty(pg_database_size(d.datname)) AS size,
       s.xact_commit, s.xact_rollback, s.deadlocks,
       s.temp_files, pg_size_pretty(s.temp_bytes) AS temp_size
FROM pg_database d
JOIN pg_stat_database s ON s.datname = d.datname
WHERE NOT d.datistemplate
ORDER BY pg_database_size(d.datname) DESC;

-- PostgreSQL 16 ve öncesi
SELECT checkpoints_timed, checkpoints_req,
       round(100.0 * checkpoints_req / NULLIF(checkpoints_timed + checkpoints_req, 0), 1) AS req_pct,
       buffers_checkpoint, buffers_clean, buffers_backend, stats_reset
FROM pg_stat_bgwriter;

-- PostgreSQL 17+
SELECT num_timed, num_requested, write_time, sync_time, buffers_written, stats_reset
FROM pg_stat_checkpointer;

SELECT calls,
       pg_size_pretty(temp_blks_written * 8192) AS temp_written,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 150) AS query
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

SELECT c.conrelid::regclass AS table_name,
       c.conname AS fk_name,
       pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] @> c.conkey
  )
ORDER BY 1, 2;

SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls, rolcanlogin, rolvaliduntil
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolreplication OR rolbypassrls
ORDER BY rolsuper DESC, rolname;

SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method IN ('trust','password','md5')
ORDER BY line_number;

SELECT p.pid, p.datname, p.relid::regclass AS table_name, p.phase,
       p.heap_blks_total, p.heap_blks_scanned,
       round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS pct_scanned,
       now() - a.xact_start AS running_for,
       left(a.query, 80) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);

SELECT schemaname, sequencename, data_type, last_value, max_value,
       round(100.0 * last_value / max_value, 2) AS pct_used
FROM pg_sequences
WHERE last_value IS NOT NULL
ORDER BY pct_used DESC
LIMIT 20;

SELECT id, user, host, db, command, time AS seconds, state,
       LEFT(info, 200) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
  AND time > 60
ORDER BY time DESC;

SELECT wait_started, wait_age,
       locked_table, locked_index, locked_type,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query, blocking_lock_mode
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

SELECT table_schema, table_name, engine, table_rows,
       ROUND(data_length  / 1024 / 1024, 1) AS data_mb,
       ROUND(index_length / 1024 / 1024, 1) AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 25;

SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql','sys','performance_schema')
ORDER BY object_schema, object_name;

SELECT schema_name,
       LEFT(digest_text, 150) AS query_pattern,
       count_star AS exec_count,
       ROUND(sum_timer_wait / 1e12, 2) AS total_sec,
       ROUND(avg_timer_wait / 1e9, 2)  AS avg_ms,
       sum_rows_examined, sum_rows_sent,
       sum_no_index_used AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- 1. Replikasyon Durumu (Classic Replica Status)
SHOW REPLICA STATUS\G
-- Alanlar: Replica_IO_Running, Replica_SQL_Running, Seconds_Behind_Source

-- 2. Performance Schema ile (MySQL 8.0+)
SELECT channel_name, service_state, last_error_message
FROM performance_schema.replication_connection_status;

SELECT channel_name, service_state, last_error_message
FROM performance_schema.replication_applier_status_by_worker;

SELECT
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_read_requests') AS read_requests,
  (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads')         AS disk_reads,
  ROUND(100 * (1 -
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_reads') /
    NULLIF((SELECT variable_value FROM performance_schema.global_status WHERE variable_name='Innodb_buffer_pool_read_requests'), 0)
  ), 3) AS hit_ratio_pct;

SELECT
  (SELECT variable_value FROM performance_schema.global_status    WHERE variable_name='Threads_connected')   AS connected,
  (SELECT variable_value FROM performance_schema.global_status    WHERE variable_name='Max_used_connections') AS max_used,
  (SELECT variable_value FROM performance_schema.global_variables WHERE variable_name='max_connections')     AS max_connections,
  (SELECT variable_value FROM performance_schema.global_status    WHERE variable_name='Aborted_connects')    AS aborted_connects;

SELECT user, SUBSTRING_INDEX(host, ':', 1) AS client_host, COUNT(*) AS sessions
FROM information_schema.processlist
GROUP BY user, SUBSTRING_INDEX(host, ':', 1)
ORDER BY sessions DESC
LIMIT 20;

SELECT t.table_schema, t.table_name, t.engine, t.table_rows
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
       ON c.table_schema = t.table_schema AND c.table_name = t.table_name
      AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
  AND c.constraint_name IS NULL
ORDER BY t.table_rows DESC;

SELECT table_schema, table_name,
       redundant_index_name, redundant_index_columns,
       dominant_index_name,  dominant_index_columns,
       sql_drop_index
FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql','sys');

SELECT db, LEFT(query, 150) AS query, exec_count,
       sys.format_time(total_latency) AS total_latency,
       no_index_used_count, rows_examined_avg
FROM sys.x$statements_with_full_table_scans
ORDER BY total_latency DESC
LIMIT 20;

SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN ('Created_tmp_tables','Created_tmp_disk_tables','Created_tmp_files');

SELECT db, LEFT(query, 150) AS query, exec_count, tmp_tables, disk_tmp_tables
FROM sys.statements_with_temp_tables
WHERE disk_tmp_tables > 0
ORDER BY disk_tmp_tables DESC
LIMIT 20;

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_rows_locked, trx_rows_modified,
       trx_mysql_thread_id AS thread_id,
       LEFT(trx_query, 200) AS query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT name, count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';

SELECT table_schema,
       COUNT(*) AS tables,
       ROUND(SUM(data_length)  / 1024 / 1024 / 1024, 2) AS data_gb,
       ROUND(SUM(index_length) / 1024 / 1024 / 1024, 2) AS index_gb,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

SELECT user, host, plugin,
       (authentication_string = '') AS no_password,
       account_locked, password_expired
FROM mysql.user
WHERE authentication_string = '' OR host = '%'
ORDER BY user;

SELECT grantee, privilege_type, is_grantable
FROM information_schema.user_privileges
WHERE privilege_type IN ('SUPER','FILE','SHUTDOWN','PROCESS')
ORDER BY grantee;

SELECT table_schema, table_name, column_name, column_type, auto_increment,
       max_val, ROUND(100 * auto_increment / max_val, 2) AS pct_used
FROM (
  SELECT t.table_schema, t.table_name, c.column_name, c.column_type, t.auto_increment,
    CASE
      WHEN c.column_type LIKE 'tinyint%unsigned'   THEN 255
      WHEN c.column_type LIKE 'tinyint%'           THEN 127
      WHEN c.column_type LIKE 'smallint%unsigned'  THEN 65535
      WHEN c.column_type LIKE 'smallint%'          THEN 32767
      WHEN c.column_type LIKE 'mediumint%unsigned' THEN 16777215
      WHEN c.column_type LIKE 'mediumint%'         THEN 8388607
      WHEN c.column_type LIKE 'int%unsigned'       THEN 4294967295
      WHEN c.column_type LIKE 'int%'               THEN 2147483647
      WHEN c.column_type LIKE 'bigint%unsigned'    THEN 18446744073709551615
      ELSE 9223372036854775807
    END AS max_val
  FROM information_schema.tables t
  JOIN information_schema.columns c
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name AND c.extra LIKE '%auto_increment%'
  WHERE t.auto_increment IS NOT NULL
    AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
) x
ORDER BY pct_used DESC
LIMIT 20;

SELECT table_schema, table_name,
       ROUND(data_length / 1048576, 1) AS data_mb,
       ROUND(data_free   / 1048576, 1) AS free_mb,
       ROUND(100 * data_free / NULLIF(data_length + index_length + data_free, 0), 1) AS free_pct
FROM information_schema.tables
WHERE engine = 'InnoDB' AND data_free > 100 * 1048576
ORDER BY data_free DESC
LIMIT 25;

SHOW BINARY LOGS;

SELECT @@log_bin AS log_bin,
       @@binlog_format AS format,
       @@binlog_expire_logs_seconds / 86400 AS retention_days;   -- 8.0+; 5.7 için @@expire_logs_days

SELECT file, count_read, total_read, count_write, total_written, total
FROM sys.io_global_by_file_by_bytes
LIMIT 20;

SELECT event_name, current_count, current_alloc, high_alloc
FROM sys.memory_global_by_current_bytes
LIMIT 15;

SELECT * FROM sys.memory_global_total;

SELECT r.session_id, r.blocking_session_id, r.status, r.command,
       r.wait_type, r.wait_time AS wait_ms,
       r.cpu_time, r.logical_reads,
       DB_NAME(r.database_id) AS db_name,
       s.login_name, s.host_name,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1,
         ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
           ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS running_statement
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.blocking_session_id DESC, r.wait_time DESC;

SELECT TOP 20
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.last_execution_time,
       SUBSTRING(t.text, (qs.statement_start_offset/2)+1,
         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
           ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text,
       DB_NAME(t.dbid) AS db_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

SELECT TOP 20
       ROUND(gs.avg_total_user_cost * gs.avg_user_impact * (gs.user_seeks + gs.user_scans), 0) AS improvement_score,
       DB_NAME(mid.database_id) AS db_name,
       mid.statement AS table_name,
       mid.equality_columns, mid.inequality_columns, mid.included_columns,
       gs.user_seeks, gs.user_scans, gs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats gs
JOIN sys.dm_db_missing_index_groups mig ON mig.index_group_handle = gs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mid.index_handle = mig.index_handle
ORDER BY improvement_score DESC;

-- Mevcut veritabanında çalıştırın
SELECT TOP 50
       OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id)        AS table_name,
       i.name AS index_name,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       CASE WHEN ips.avg_fragmentation_in_percent >= 30 THEN 'REBUILD'
            ELSE 'REORGANIZE' END AS suggestion
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000
  AND i.index_id > 0
ORDER BY ips.avg_fragmentation_in_percent DESC;

SELECT DB_NAME() AS db_name,
       f.name AS logical_name, f.type_desc, f.physical_name,
       CAST(f.size / 128.0 AS DECIMAL(18,1)) AS size_mb,
       CAST(FILEPROPERTY(f.name, 'SpaceUsed') / 128.0 AS DECIMAL(18,1)) AS used_mb,
       CAST((f.size - FILEPROPERTY(f.name, 'SpaceUsed')) / 128.0 AS DECIMAL(18,1)) AS free_mb,
       CASE WHEN f.is_percent_growth = 1 THEN CAST(f.growth AS VARCHAR(10)) + '%'
            ELSE CAST(f.growth / 128 AS VARCHAR(10)) + ' MB' END AS growth_setting
FROM sys.database_files f;

SELECT d.name AS database_name, d.recovery_model_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS last_diff,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log,
       DATEDIFF(HOUR, MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END), GETDATE()) AS hours_since_full
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY hours_since_full DESC;

SELECT TOP 15
       wait_type,
       waiting_tasks_count,
       CAST(wait_time_ms / 1000.0 AS DECIMAL(18,1)) AS wait_sec,
       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
  'SLEEP_TASK','SLEEP_SYSTEMTASK','LAZYWRITER_SLEEP','SQLTRACE_BUFFER_FLUSH',
  'BROKER_TASK_STOP','BROKER_TO_FLUSH','XE_TIMER_EVENT','XE_DISPATCHER_WAIT',
  'REQUEST_FOR_DEADLOCK_SEARCH','CHECKPOINT_QUEUE','DIRTY_PAGE_POLL',
  'HADR_FILESTREAM_IOMGR_IOCOMPLETION','LOGMGR_QUEUE','WAITFOR','FT_IFTS_SCHEDULER_IDLE_WAIT',
  'SP_SERVER_DIAGNOSTICS_SLEEP','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE',
  'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','BROKER_EVENTHANDLER','CLR_AUTO_EVENT','CLR_MANUAL_EVENT')
  AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

SELECT TOP 20
       x.event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
       x.event_data.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM (
  SELECT CAST(target_data AS XML) AS target_data
  FROM sys.dm_xe_session_targets st
  JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
  WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer'
) AS tgt
CROSS APPLY tgt.target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS q(n)
CROSS APPLY (SELECT CAST(q.n.query('.') AS XML)) AS x(event_data)
ORDER BY event_time DESC;

SELECT s.session_id, s.login_name, s.host_name, s.status,
       DB_NAME(t.database_id) AS db_name,
       at.transaction_begin_time,
       DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS open_minutes,
       st.text AS last_sql
FROM sys.dm_tran_session_transactions t
JOIN sys.dm_tran_active_transactions at ON at.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions s ON s.session_id = t.session_id
LEFT JOIN sys.dm_exec_connections c ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st
WHERE t.session_id <> @@SPID
ORDER BY at.transaction_begin_time;

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS table_name,
       i.name AS index_name, i.type_desc,
       ISNULL(s.user_seeks,0) + ISNULL(s.user_scans,0) + ISNULL(s.user_lookups,0) AS reads,
       ISNULL(s.user_updates,0) AS writes,
       (SELECT SUM(ps.used_page_count) * 8 / 1024 FROM sys.dm_db_partition_stats ps
         WHERE ps.object_id = i.object_id AND ps.index_id = i.index_id) AS size_mb
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
       ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.type_desc = 'NONCLUSTERED'
  AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
  AND ISNULL(s.user_seeks,0) + ISNULL(s.user_scans,0) + ISNULL(s.user_lookups,0) = 0
ORDER BY writes DESC;

SELECT ag.name AS ag_name, ar.replica_server_name, ar.availability_mode_desc,
       drs.database_id, DB_NAME(drs.database_id) AS db_name,
       drs.synchronization_state_desc, drs.synchronization_health_desc,
       drs.log_send_queue_size AS log_send_kb, drs.redo_queue_size AS redo_kb,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON ar.replica_id = drs.replica_id
JOIN sys.availability_groups ag ON ag.group_id = drs.group_id
ORDER BY ag.name, ar.replica_server_name, db_name;

SELECT TOP 20 s.session_id, s.login_name, s.host_name,
       (u.user_objects_alloc_page_count - u.user_objects_dealloc_page_count) * 8 / 1024 AS user_obj_mb,
       (u.internal_objects_alloc_page_count - u.internal_objects_dealloc_page_count) * 8 / 1024 AS internal_obj_mb
FROM sys.dm_db_session_space_usage u
JOIN sys.dm_exec_sessions s ON s.session_id = u.session_id
ORDER BY user_obj_mb + internal_obj_mb DESC;

SELECT SUM(user_object_reserved_page_count) * 8 / 1024 AS user_mb,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_mb,
       SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
       SUM(unallocated_extent_page_count) * 8 / 1024 AS free_mb
FROM tempdb.sys.dm_db_file_space_usage;

SELECT j.name AS job_name, j.enabled,
       CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                         WHEN 3 THEN 'Canceled' ELSE 'Unknown' END AS last_status,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS last_run,
       h.message
FROM msdb.dbo.sysjobs j
OUTER APPLY (SELECT TOP 1 * FROM msdb.dbo.sysjobhistory
             WHERE job_id = j.job_id AND step_id = 0 ORDER BY instance_id DESC) h
ORDER BY CASE WHEN h.run_status = 0 THEN 0 ELSE 1 END, last_run DESC;

SELECT TOP 30 OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
       OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name,
       sp.last_updated, sp.rows, sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS DECIMAL(10,1)) AS pct_modified
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND sp.modification_counter > 1000
ORDER BY sp.modification_counter DESC;

SELECT DB_NAME(vfs.database_id) AS db_name, mf.physical_name, mf.type_desc,
       vfs.num_of_reads, vfs.num_of_writes,
       CAST(vfs.io_stall_read_ms  / NULLIF(vfs.num_of_reads, 0)  AS DECIMAL(10,1)) AS avg_read_ms,
       CAST(vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS DECIMAL(10,1)) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Page life expectancy','Memory Grants Pending','Total Server Memory (KB)','Target Server Memory (KB)')
  AND (object_name LIKE '%Buffer Manager%' OR object_name LIKE '%Memory Manager%');

SELECT physical_memory_kb / 1024 AS os_memory_mb,
       (SELECT CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'max server memory (MB)') AS max_server_memory_mb
FROM sys.dm_os_sys_info;

SELECT p.name AS login_name, p.type_desc, p.is_disabled,
       CASE WHEN r.member_principal_id IS NOT NULL THEN 1 ELSE 0 END AS is_sysadmin
FROM sys.server_principals p
LEFT JOIN sys.server_role_members r
       ON r.member_principal_id = p.principal_id
      AND r.role_principal_id = SUSER_ID('sysadmin')
WHERE p.type IN ('S','U','G') AND p.name NOT LIKE '##%'
ORDER BY is_sysadmin DESC, p.name;

SELECT name, is_policy_checked, is_expiration_checked
FROM sys.sql_logins
WHERE is_policy_checked = 0 OR PWDCOMPARE('', password_hash) = 1;

SELECT objtype,
       COUNT(*) AS plans,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans,
       CAST(SUM(CAST(size_in_bytes AS BIGINT)) / 1048576.0 AS DECIMAL(18,1)) AS total_mb,
       CAST(SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS BIGINT) ELSE 0 END) / 1048576.0 AS DECIMAL(18,1)) AS single_use_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY total_mb DESC;

SELECT d.name, d.recovery_model_desc, d.log_reuse_wait_desc,
       ls.total_log_size_in_bytes / 1048576 AS log_size_mb,
       CAST(ls.used_log_space_in_percent AS DECIMAL(5,1)) AS used_pct,
       (SELECT COUNT(*) FROM sys.dm_db_log_info(d.database_id)) AS vlf_count
FROM sys.databases d
CROSS APPLY sys.dm_db_log_space_usage ls   -- mevcut DB için; tümü için veritabanı bazında çalıştırın
WHERE d.database_id = DB_ID();

SELECT TOP 20 q.query_id,
       COUNT(DISTINCT p.plan_id) AS plan_count,
       MIN(rs.avg_duration) / 1000 AS min_avg_ms,
       MAX(rs.avg_duration) / 1000 AS max_avg_ms,
       MAX(rs.avg_duration) / NULLIF(MIN(rs.avg_duration), 0) AS ratio,
       LEFT(qt.query_sql_text, 150) AS query_text
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY ratio DESC;

SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.program,
       s.sql_id, s.event, s.wait_class, s.seconds_in_wait,
       ROUND(s.last_call_et / 60, 1) AS active_minutes
FROM   v$session s
WHERE  s.type = 'USER'
  AND  s.status = 'ACTIVE'
  AND  s.sid <> SYS_CONTEXT('USERENV','SID')
ORDER  BY s.last_call_et DESC;

SELECT w.sid AS waiting_sid, w.username AS waiting_user,
       w.event, w.seconds_in_wait,
       b.sid AS blocking_sid, b.serial# AS blocking_serial,
       b.username AS blocking_user, b.status AS blocking_status,
       b.sql_id AS blocking_sql_id, b.prev_sql_id
FROM   v$session w
JOIN   v$session b ON b.sid = w.blocking_session
WHERE  w.blocking_session IS NOT NULL
ORDER  BY w.seconds_in_wait DESC;

SELECT df.tablespace_name,
       ROUND(df.max_mb, 0)  AS max_mb,
       ROUND(df.alloc_mb, 0) AS allocated_mb,
       ROUND(df.alloc_mb - NVL(fs.free_mb, 0), 0) AS used_mb,
       ROUND(100 * (df.alloc_mb - NVL(fs.free_mb, 0)) / df.max_mb, 1) AS used_pct_of_max
FROM (SELECT tablespace_name,
             SUM(bytes) / 1048576 AS alloc_mb,
             SUM(CASE WHEN autoextensible = 'YES' THEN GREATEST(maxbytes, bytes) ELSE bytes END) / 1048576 AS max_mb
      FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN (SELECT tablespace_name, SUM(bytes) / 1048576 AS free_mb
           FROM dba_free_space GROUP BY tablespace_name) fs
       ON fs.tablespace_name = df.tablespace_name
ORDER BY used_pct_of_max DESC;

SELECT * FROM (
  SELECT sql_id,
         executions,
         ROUND(elapsed_time / 1e6, 1) AS elapsed_sec,
         ROUND(elapsed_time / NULLIF(executions, 0) / 1e3, 2) AS avg_ms,
         ROUND(cpu_time / 1e6, 1) AS cpu_sec,
         buffer_gets, disk_reads, rows_processed
  FROM   v$sqlstats
  ORDER  BY elapsed_time DESC
) WHERE ROWNUM <= 20;

SELECT owner, object_type, object_name, status, last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
  AND  owner NOT IN ('SYS','SYSTEM')
ORDER  BY owner, object_type, object_name;

SELECT name,
       ROUND(space_limit / 1073741824, 2)   AS limit_gb,
       ROUND(space_used / 1073741824, 2)    AS used_gb,
       ROUND(space_reclaimable / 1073741824, 2) AS reclaimable_gb,
       ROUND(100 * (space_used - space_reclaimable) / space_limit, 1) AS real_used_pct
FROM   v$recovery_file_dest;

SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files
FROM   v$recovery_area_usage
ORDER  BY percent_space_used DESC;

SELECT sid, serial#, username, opname, target,
       sofar, totalwork,
       ROUND(100 * sofar / NULLIF(totalwork, 0), 1) AS pct_done,
       ROUND(elapsed_seconds / 60, 1) AS elapsed_min,
       ROUND(time_remaining / 60, 1)  AS remaining_min,
       sql_id
FROM   v$session_longops
WHERE  totalwork > 0
  AND  sofar < totalwork
ORDER  BY time_remaining DESC;

SELECT * FROM (
  SELECT owner, segment_name, segment_type, tablespace_name,
         ROUND(bytes / 1048576, 1) AS size_mb
  FROM   dba_segments
  WHERE  owner NOT IN ('SYS','SYSTEM','SYSAUX','XDB','MDSYS','CTXSYS')
  ORDER  BY bytes DESC
) WHERE ROWNUM <= 25;

-- 12.2+ : DBA_INDEX_USAGE
SELECT i.owner, i.index_name, i.table_name, i.index_type,
       NVL(u.total_access_count, 0) AS total_access_count, u.last_used
FROM   dba_indexes i
LEFT JOIN dba_index_usage u ON u.owner = i.owner AND u.name = i.index_name
WHERE  i.owner NOT IN ('SYS','SYSTEM')
  AND  i.uniqueness = 'NONUNIQUE'
  AND  NVL(u.total_access_count, 0) = 0
ORDER  BY i.owner, i.table_name;

SELECT owner, table_name, num_rows, last_analyzed, stale_stats
FROM   dba_tab_statistics
WHERE  owner NOT IN ('SYS','SYSTEM','XDB','MDSYS','CTXSYS')
  AND  object_type = 'TABLE'
  AND  (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER  BY num_rows DESC NULLS LAST;

-- Standby üzerinde
SELECT name, value, unit, time_computed
FROM   v$dataguard_stats
WHERE  name IN ('transport lag','apply lag','apply finish time');

SELECT process, status, thread#, sequence#, block#
FROM   v$managed_standby
WHERE  process IN ('MRP0','RFS');

SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour, COUNT(*) AS log_switches
FROM   v$log_history
WHERE  first_time > SYSDATE - 2
GROUP  BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER  BY 1 DESC;

SELECT group#, thread#, bytes / 1048576 AS size_mb, members, status FROM v$log;

SELECT s.sid, s.serial#, s.username, s.program, u.tablespace, u.segtype,
       ROUND(u.blocks * ts.block_size / 1048576, 1) AS temp_mb, u.sql_id
FROM   v$tempseg_usage u
JOIN   v$session s ON s.saddr = u.session_addr
JOIN   dba_tablespaces ts ON ts.tablespace_name = u.tablespace
ORDER  BY u.blocks DESC;

SELECT status, ROUND(SUM(bytes) / 1048576, 1) AS size_mb
FROM   dba_undo_extents
GROUP  BY status;

SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS begin_time,
       undoblks, txncount, maxquerylen, ssolderrcnt, nospaceerrcnt
FROM   v$undostat
WHERE  begin_time > SYSDATE - 1
ORDER  BY begin_time DESC;

SELECT name, ROUND(bytes / 1048576) AS mb FROM v$sgainfo ORDER BY bytes DESC;

SELECT name, ROUND(value / 1048576) AS mb
FROM   v$pgastat
WHERE  name IN ('aggregate PGA target parameter','total PGA allocated','maximum PGA allocated','cache hit percentage');

SELECT grantee, granted_role, admin_option
FROM   dba_role_privs
WHERE  granted_role IN ('DBA','SYSDBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')
ORDER  BY grantee;

SELECT d.username, u.account_status, u.profile
FROM   dba_users_with_defpwd d
JOIN   dba_users u ON u.username = d.username
ORDER  BY d.username;

SELECT username, account_status, expiry_date, profile FROM dba_users
WHERE  account_status = 'OPEN' AND expiry_date IS NULL;

SELECT NVL(event, 'ON CPU') AS event, wait_class,
       COUNT(*) AS ash_samples,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 1/24
GROUP  BY event, wait_class
ORDER  BY ash_samples DESC
FETCH FIRST 15 ROWS ONLY;

SELECT session_key, input_type, status,
       TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS start_time,
       TO_CHAR(end_time,   'YYYY-MM-DD HH24:MI') AS end_time,
       ROUND(elapsed_seconds / 60, 1) AS minutes,
       ROUND(output_bytes / 1073741824, 2) AS output_gb
FROM   v$rman_backup_job_details
WHERE  start_time > SYSDATE - 7
ORDER  BY start_time DESC;

SELECT lo.session_id AS sid, s.serial#, s.username, s.status, s.machine,
       o.owner, o.object_name, o.object_type,
       DECODE(lo.locked_mode, 1,'NULL',2,'ROW SHARE',3,'ROW EXCL',4,'SHARE',5,'SHARE ROW EXCL',6,'EXCLUSIVE') AS lock_mode
FROM   v$locked_object lo
JOIN   dba_objects o ON o.object_id = lo.object_id
JOIN   v$session s   ON s.sid = lo.session_id
ORDER  BY o.owner, o.object_name;

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes','sessions','transactions');

SELECT username, COUNT(*) AS sessions
FROM   v$session
WHERE  type = 'USER'
GROUP  BY username
ORDER  BY sessions DESC;

db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true, idleConnections: false } },
  { $match: { active: true, secs_running: { $gt: 5 }, ns: { $not: /^(admin|local|config)\./ } } },
  { $project: { opid: 1, op: 1, ns: 1, secs_running: 1, client: 1, planSummary: 1,
                waitingForLock: 1, command: 1 } },
  { $sort: { secs_running: -1 } }
]);

const mb = n => +(n / 1024 / 1024).toFixed(1);
const rows = db.getCollectionInfos({ type: "collection" }).map(c => {
  const s = db.getCollection(c.name).stats();
  return { collection: c.name, docs: s.count, data_mb: mb(s.size),
           storage_mb: mb(s.storageSize), index_mb: mb(s.totalIndexSize),
           avg_doc_bytes: s.avgObjSize };
}).sort((a, b) => (b.storage_mb + b.index_mb) - (a.storage_mb + a.index_mb));
console.table(rows);

const out = [];
db.getCollectionInfos({ type: "collection" }).forEach(c => {
  db.getCollection(c.name).aggregate([{ $indexStats: {} }]).forEach(i => {
    if (i.name !== "_id_" && i.accesses.ops === 0) {
      out.push({ collection: c.name, index: i.name, key: JSON.stringify(i.key), since: i.accesses.since });
    }
  });
});
console.table(out);

const st = rs.status();
const primary = st.members.find(m => m.stateStr === "PRIMARY");
st.members.map(m => ({
  member: m.name, state: m.stateStr, health: m.health,
  lag_sec: primary ? (primary.optimeDate - m.optimeDate) / 1000 : null,
  ping_ms: m.pingMs
}));
rs.printSecondaryReplicationInfo();   // 4.4 öncesi: rs.printSlaveReplicationInfo()
rs.printReplicationInfo();            // oplog penceresi

// Profiler'ı aç (100 ms üzeri): db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.aggregate([
  { $match: { op: { $in: ["query", "command", "update", "remove"] }, ns: { $not: /system\.profile/ } } },
  { $group: { _id: { ns: "$ns", planSummary: "$planSummary" },
              count: { $sum: 1 }, avg_ms: { $avg: "$millis" }, max_ms: { $max: "$millis" },
              avg_docs_examined: { $avg: "$docsExamined" }, avg_returned: { $avg: "$nreturned" } } },
  { $sort: { avg_ms: -1 } },
  { $limit: 20 }
]);

// "users" koleksiyonunda email alanı tekrar edenleri bul
db.users.aggregate([
  { $group: { _id: "$email", count: { $sum: 1 }, ids: { $push: "$_id" } } },
  { $match: { _id: { $ne: null }, count: { $gt: 1 } } },
  { $sort: { count: -1 } },
  { $limit: 50 }
], { allowDiskUse: true });

const s = db.serverStatus();
const wt = s.wiredTiger.cache;
({
  connections_current: s.connections.current,
  connections_available: s.connections.available,
  cache_max_gb: +(wt["maximum bytes configured"] / 1073741824).toFixed(2),
  cache_used_gb: +(wt["bytes currently in the cache"] / 1073741824).toFixed(2),
  cache_dirty_gb: +(wt["tracked dirty bytes in the cache"] / 1073741824).toFixed(2),
  pages_read_into_cache: wt["pages read into cache"],
  uptime_hours: +(s.uptime / 3600).toFixed(1)
});

db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true, idleConnections: true } },
  { $group: { _id: { app: "$appName", client: { $arrayElemAt: [{ $split: ["$client", ":"] }, 0] } },
              connections: { $sum: 1 }, active: { $sum: { $cond: ["$active", 1, 0] } } } },
  { $sort: { connections: -1 } },
  { $limit: 20 }
]);

db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true } },
  { $match: { waitingForLock: true } },
  { $project: { opid: 1, op: 1, ns: 1, secs_running: 1, lockStats: 1, command: 1, client: 1 } },
  { $sort: { secs_running: -1 } }
]);

sh.status();                       // genel durum
sh.isBalancerRunning();
sh.getBalancerState();

// Belirli koleksiyon için shard dağılımı:
db.getSiblingDB("mydb").orders.getShardDistribution();

const gb = n => +(n / 1073741824).toFixed(2);
db.adminCommand({ listDatabases: 1 }).databases.map(d => {
  const s = db.getSiblingDB(d.name).stats();
  return { db: d.name, data_gb: gb(s.dataSize), storage_gb: gb(s.storageSize),
           index_gb: gb(s.indexSize), collections: s.collections, objects: s.objects };
}).sort((a, b) => b.storage_gb - a.storage_gb);

db.system.profile.find(
  { planSummary: "COLLSCAN", ns: { $not: /system\.profile/ } },
  { ns: 1, op: 1, millis: 1, docsExamined: 1, nreturned: 1, command: 1, ts: 1 }
).sort({ millis: -1 }).limit(20);

const o = db.getSiblingDB("local").oplog.rs;
const first = o.find().sort({ $natural: 1 }).limit(1).next().ts;
const last  = o.find().sort({ $natural: -1 }).limit(1).next().ts;
const stats = db.getSiblingDB("local").oplog.rs.stats();
({
  size_mb: Math.round(stats.maxSize / 1048576),
  used_mb: Math.round(stats.size / 1048576),
  window_hours: +((last.getTime() - first.getTime()) / 3600).toFixed(1)
});

db.orders.aggregate([
  { $project: { size: { $bsonSize: "$$ROOT" } } },
  { $sort: { size: -1 } },
  { $limit: 10 }
], { allowDiskUse: true });

const out = [];
db.getCollectionInfos({ type: "collection" }).forEach(c => {
  db.getCollection(c.name).getIndexes().forEach(i => {
    if (i.expireAfterSeconds !== undefined) {
      out.push({ collection: c.name, index: i.name, key: JSON.stringify(i.key),
                 expire_days: +(i.expireAfterSeconds / 86400).toFixed(2) });
    }
  });
});
console.table(out);

db.getSiblingDB("admin").system.users.find(
  {}, { user: 1, db: 1, roles: 1, "credentials.SCRAM-SHA-256": { $exists: true } }
).toArray().map(u => ({
  user: u.user, db: u.db,
  roles: u.roles.map(r => r.role + "@" + r.db).join(", "),
  privileged: u.roles.some(r => ["root","dbOwner","userAdminAnyDatabase","clusterAdmin"].includes(r.role))
}));

const s = db.serverStatus();
({
  transactions: s.transactions,
  global_lock: s.globalLock.currentQueue,
  wt_concurrent: s.wiredTiger.concurrentTransactions
});

db.getSiblingDB("admin").aggregate([
  { $currentOp: { allUsers: true } },
  { $match: { $or: [ { "command.createIndexes": { $exists: true } }, { msg: /Index Build/ } ] } },
  { $project: { opid: 1, ns: 1, secs_running: 1, msg: 1, progress: 1, "command.indexes": 1 } }
]);

db.serverStatus().metrics.queryExecutor;

// mongos üzerinde hedefleme oranı: scannedObjects / returned ne kadar yüksekse,
// sorgu o kadar çok shard'a dağılıyor demektir.
db.getSiblingDB("admin").runCommand({ serverStatus: 1 }).metrics.query;

db.getSiblingDB("config").changelog.find(
  { what: /moveChunk/ },
  { time: 1, what: 1, ns: 1, details: 1 }
).sort({ time: -1 }).limit(20);