A curated collection of diagnostic and maintenance SQL queries for ClickHouse (19+). Each block can be copied with a single click. Queries are designed to run in clickhouse-client or DBeaver without modification.
ClickHouse stores system internals in system.* tables (metrics, logs, events, parts, etc.). Many optional capabilities rely on features like Kafka engine, dictionaries, and ZooKeeper-based replication — these are grouped under Optional Features.
Run with: clickhouse-client -mn --ignore-error (multi-line queries may require -n).
SELECT version() AS full_version,
value AS version_integer,
if(value >= 26000000, 'Yes', 'No') AS current_year_release,
if(value >= 25000000, 'Yes', 'No') AS recent_release
FROM system.metrics
WHERE metric = 'VersionInteger';
SELECT hostName() AS hostname,
now() - uptime() AS server_started,
uptime() AS uptime_seconds;
SELECT name, engine,
data_path, metadata_path,
toString(uuid) AS uuid
FROM system.databases
ORDER BY name;
SELECT min(metadata_modification_time) AS oldest_timestamp
FROM system.tables
WHERE metadata_modification_time <> '0000-00-00 00:00:00';
SELECT name, value, changed
FROM system.settings
WHERE changed != 0
OR name IN ('max_memory_usage',
'max_memory_usage_for_all_queries',
'max_memory_usage_for_user',
'max_bytes_before_external_group_by',
'max_bytes_before_external_sort',
'max_bytes_before_remerge_sort')
ORDER BY name;
SELECT sk AS database,
sum(if(otype = 'T', 1, 0)) AS tables,
sum(if(otype = 'C', 1, 0)) AS columns,
sum(if(otype = 'A', 1, 0)) AS partitions,
sum(if(otype = 'P', 1, 0)) AS parts,
sum(if(otype = 'R', 1, 0)) AS replicas,
sum(if(otype = 'D', 1, 0)) AS dictionaries,
count(*) AS total
FROM (
SELECT 'T' AS otype, database AS sk, name
FROM system.tables
UNION ALL
SELECT 'C', database, concat(table, '.', name)
FROM system.columns
UNION ALL
SELECT DISTINCT 'A', database, concat(table, '.', partition)
FROM system.parts
UNION ALL
SELECT 'P', database, concat(table, '.', name)
FROM system.parts
UNION ALL
SELECT 'R', database, table AS name
FROM system.replicas
UNION ALL
SELECT 'D', database, name
FROM system.dictionaries
) a
GROUP BY sk
ORDER BY sk;
SELECT database,
sum(if(engine = 'MergeTree', 1, 0)) AS MergeTree,
sum(if(engine = 'AggregatingMergeTree', 1, 0)) AS AggregatingMergeTree,
sum(if(engine = 'SummingMergeTree', 1, 0)) AS SummingMergeTree,
sum(if(engine = 'ReplacingMergeTree', 1, 0)) AS ReplacingMergeTree,
sum(if(engine = 'CollapsingMergeTree', 1, 0)) AS CollapsingMergeTree,
sum(if(engine = 'VersionedCollapsingMergeTree', 1, 0)) AS VersionedCollapsingMergeTree,
sum(if(engine LIKE '%Log', 1, 0)) AS LogStar,
sum(if(engine LIKE 'Replicated%', 1, 0)) AS Replicated,
sum(if(engine LIKE 'Distributed%', 1, 0)) AS Distributed,
sum(if(engine = 'View', 1, 0)) AS View,
sum(if(engine = 'MaterializedView', 1, 0)) AS MaterializedView,
sum(if(engine = 'Dictionary', 1, 0)) AS Dictionary,
sum(if(engine = 'Memory', 1, 0)) AS Memory,
sum(if(engine = 'Kafka', 1, 0)) AS Kafka,
sum(if(engine LIKE 'System%', 1, 0)) AS System,
count(*) AS all_tables
FROM system.tables
GROUP BY database
ORDER BY database;
SELECT database, name, engine,
total_rows AS rows,
formatReadableSize(total_bytes) AS hr_size
FROM system.tables
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY database, name;
SELECT database, table, name, type,
is_in_primary_key AS in_pk,
is_in_partition_key AS in_part_key,
is_in_sorting_key AS in_sort_key,
is_in_sampling_key AS in_sample_key,
comment
FROM system.columns
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY database, table, is_in_primary_key DESC, name;
SELECT database, name, status, source,
attribute.names AS attr_names,
element_count AS rows,
bytes_allocated AS bytes,
lifetime_min, lifetime_max,
last_successful_update_time,
loading_duration,
last_exception
FROM system.dictionaries
ORDER BY last_successful_update_time DESC;
SELECT database,
sum(rows) AS rows,
formatReadableSize(sum(bytes_on_disk)) AS hr_size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.parts
GROUP BY database
ORDER BY database;
SELECT name, path,
formatReadableSize(total_space) AS hr_total,
formatReadableSize(free_space) AS hr_free,
formatReadableSize(total_space - free_space) AS hr_used,
total_space, free_space,
total_space - free_space AS used_space
FROM system.disks;
SELECT database, table,
any(engine) AS engine,
sum(rows) AS rows,
formatReadableSize(sum(bytes_on_disk)) AS hr_size,
formatReadableSize(sum(data_uncompressed_bytes)) AS hr_uncompressed
FROM system.parts
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 32;
SELECT metric AS connection_type,
value AS count
FROM system.metrics
WHERE metric IN ('TCPConnection', 'HTTPConnection', 'InterserverConnection');
SELECT query_id, user, address AS host,
elapsed, query
FROM system.processes
ORDER BY query_id;
SELECT database, table,
result_part_name,
progress,
elapsed,
num_parts
FROM system.merges;
SELECT database, table,
mutation_id,
command,
create_time,
is_done,
parts_to_do,
latest_fail_reason
FROM system.mutations
ORDER BY is_done, create_time DESC
LIMIT 20;
SELECT name, auth_type,
host_ip, host_names, host_names_regexp, host_names_like,
default_roles_all, default_roles_list, default_roles_except,
storage
FROM system.users;
SELECT name, storage
FROM system.roles;
SELECT name, type, params, precedence
FROM system.user_directories
ORDER BY precedence;
SELECT coalesce(user_name, '') AS user_name,
coalesce(role_name, '') AS role_name,
access_type,
coalesce(database, '') AS database,
coalesce(table, '') AS table,
coalesce(column, '') AS column,
is_partial_revoke,
grant_option
FROM system.grants;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
round(memory_usage / 1048576) AS mem_mb,
result_rows AS res_rows,
toDecimal64(result_bytes / 1048576, 6) AS res_mb,
read_rows, round(read_bytes / 1048576) AS read_mb,
written_rows, round(written_bytes / 1048576) AS written_mb,
query
FROM system.query_log
WHERE user <> user()
AND event_time > now() - interval 1 day
ORDER BY query_start_time DESC
LIMIT 20;
SELECT toStartOfInterval(event_time, INTERVAL 3600 SECOND) AS t,
round(avg(ProfileEvent_Query), 2) AS queries_per_sec,
round(avg(CurrentMetric_Query), 2) AS running_queries,
round(avg(CurrentMetric_Merge), 2) AS running_merges,
round(avg(ProfileEvent_SelectedBytes), 2) AS selected_bytes,
round(avg(CurrentMetric_MemoryTracking), 2) AS memory_tracked,
round(avg(ProfileEvent_SelectedRows), 2) AS rows_selected_per_sec,
round(avg(ProfileEvent_InsertedRows), 2) AS rows_inserted_per_sec,
round(avg(ProfileEvent_OSCPUVirtualTimeMicroseconds) / 1000000, 1) AS cpu_cores,
round(avg(ProfileEvent_OSCPUWaitMicroseconds) / 1000000, 2) AS cpu_wait,
round(avg(ProfileEvent_OSIOWaitMicroseconds) / 1000000, 2) AS io_wait,
round(avg(ProfileEvent_OSReadBytes), 2) AS disk_read,
round(avg(ProfileEvent_OSReadChars), 2) AS fs_read
FROM system.metric_log
WHERE event_date >= toDate(now() - 86400)
AND event_time >= now() - 86400
GROUP BY t
ORDER BY t WITH FILL STEP 3600;
SELECT toStartOfInterval(event_time, INTERVAL 3600 * 24 SECOND) AS t,
round(avg(value), 2) AS load_avg_15min
FROM system.asynchronous_metric_log
WHERE event_date >= toDate(now() - 3600 * 24 * 31)
AND event_time >= now() - 3600 * 24 * 31
AND metric = 'LoadAverage15'
GROUP BY t
ORDER BY t WITH FILL STEP 3600 * 24;
SELECT toStartOfInterval(event_time, INTERVAL 3600 SECOND) AS t,
round(avg(value), 2) AS load_avg_15min
FROM system.asynchronous_metric_log
WHERE event_date >= toDate(now() - 3600 * 24)
AND event_time >= now() - 3600 * 24
AND metric = 'LoadAverage15'
GROUP BY t
ORDER BY t WITH FILL STEP 3600;
SELECT max(CurrentMetric_TCPConnection) AS max_tcp,
max(CurrentMetric_HTTPConnection) AS max_http,
max(CurrentMetric_InterserverConnection) AS max_interserver
FROM system.metric_log;
SELECT user,
count(*) AS queries,
round(sum(query_duration_ms) / 1000) AS total_duration_sec,
round(sum(query_duration_ms) / 1000 / count(*), 3) AS avg_duration_sec,
countIf(exception <> '') AS errors
FROM system.query_log
WHERE event_time > now() - interval 7 day
GROUP BY user
ORDER BY user;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
round(memory_usage / 1048576) AS mem_mb,
result_rows, toDecimal64(result_bytes / 1048576, 6) AS res_mb,
read_rows, round(read_bytes / 1048576) AS read_mb,
written_rows, round(written_bytes / 1048576) AS written_mb,
query
FROM system.query_log
WHERE event_time > now() - interval 7 day
ORDER BY query_duration_ms DESC
LIMIT 20;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
round(memory_usage / 1048576) AS mem_mb,
result_rows, query, exception
FROM system.query_log
WHERE exception <> ''
AND event_time > now() - interval 1 day
ORDER BY query_start_time DESC
LIMIT 20;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
round(memory_usage / 1048576) AS mem_mb,
result_rows, toDecimal64(result_bytes / 1048576, 6) AS res_mb,
read_rows, round(read_bytes / 1048576) AS read_mb,
written_rows, round(written_bytes / 1048576) AS written_mb,
substring(query, 1, 500) AS query
FROM system.query_log
WHERE type = 2
AND event_time > now() - interval 1 day
ORDER BY query_duration_ms DESC
LIMIT 3 BY user;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
round(memory_usage / 1048576) AS mem_mb,
result_rows,
substring(query, 1, 500) AS query,
exception
FROM system.query_log
WHERE exception <> ''
AND event_time > now() - interval 1 day
ORDER BY query_start_time DESC
LIMIT 3 BY user;
SELECT user, client_hostname AS host, client_name AS client,
query_start_time AS started,
query_duration_ms / 1000 AS duration_sec,
formatReadableSize(memory_usage) AS mem,
type,
query
FROM system.query_log
WHERE memory_usage <> 0
AND event_time > now() - interval 7 day
ORDER BY memory_usage DESC
LIMIT 5 BY type;
SELECT database, table, name AS column,
type AS data_type,
is_in_primary_key AS pk,
is_in_partition_key AS partition_key,
is_in_sorting_key AS sort_key,
is_in_sampling_key AS sample_key,
comment
FROM system.columns
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY database, table, is_in_primary_key DESC, name;
SELECT database, table,
sum(rows) AS rows,
toUInt32((max(max_time) - min(min_time)) / 86400) AS days_of_data,
formatReadableSize(sum(if(active, bytes_on_disk, 0))) AS hr_active_size,
formatReadableSize(sum(if(active = 0, bytes_on_disk, 0))) AS hr_non_active_size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.parts
GROUP BY database, table
ORDER BY database, table;
SELECT database, name AS table,
formatReadableSize(total_bytes) AS hr_size,
engine,
substring(create_table_query,
position(create_table_query, 'TTL'),
position(create_table_query, 'SETTING')
- position(create_table_query, 'TTL')) AS ttl_clause
FROM system.tables
WHERE engine NOT IN ('View', 'MaterializedView', 'Kafka', 'Dictionary')
AND engine NOT LIKE 'System%'
ORDER BY database, name;
SELECT database, engine, count(*) AS tables
FROM system.tables
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY database, engine
ORDER BY database, engine;
SELECT database, table, name AS column, type AS data_type,
is_in_primary_key AS primary_key,
is_in_partition_key AS partition_key,
is_in_sorting_key AS sorting_key,
is_in_sampling_key AS sampling_key
FROM system.columns
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
AND (is_in_primary_key OR is_in_partition_key
OR is_in_sorting_key OR is_in_sampling_key)
ORDER BY database, table,
is_in_primary_key DESC,
is_in_partition_key DESC,
is_in_sorting_key DESC,
name;
SELECT database, name AS table, engine,
formatReadableSize(total_bytes) AS hr_size
FROM system.tables
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
AND engine NOT LIKE 'System%'
AND engine NOT IN ('View', 'MaterializedView', 'Dictionary', 'Kafka')
AND create_table_query NOT ILIKE '%ORDER BY%'
ORDER BY database, name;
SELECT database,
count(distinct table) AS tables,
count(distinct partition) AS partitions,
minIf(partition, partition <> 'tuple()') AS min_partition,
maxIf(partition, partition <> 'tuple()') AS max_partition,
count(distinct name) AS parts,
sum(active) AS active_parts,
formatReadableSize(sum(bytes_on_disk)) AS hr_size
FROM system.parts
GROUP BY database
ORDER BY database;
SELECT database, table,
count(distinct partition) AS partitions,
min(partition) AS min_partition,
max(partition) AS max_partition,
count(distinct name) AS parts,
min(name) AS min_part,
max(name) AS max_part,
sum(active) AS active_parts,
sum(bytes_on_disk) AS size_bytes
FROM system.parts
GROUP BY database, table
ORDER BY database, table;
SELECT database, table,
partition, name AS part,
active, bytes_on_disk
FROM system.parts
ORDER BY database, table, partition, name;
SELECT database, table,
partition_id, name AS part,
disk, reason,
min_block_number, max_block_number, level
FROM system.detached_parts
ORDER BY database, table, partition_id, name;
SELECT cluster,
shard_num, shard_weight,
replica_num,
host_name, host_address, port,
is_local, user, default_database
FROM system.clusters;
SELECT database, table, engine,
total_replicas,
is_leader, is_readonly, is_session_expired,
future_parts, parts_to_check,
inserts_in_queue,
log_max_index, log_pointer,
queue_size, active_replicas,
queue_oldest_time, inserts_oldest_time,
last_queue_update
FROM system.replicas;
SELECT database, table,
replica_name, position,
node_name, type,
create_time, required_quorum,
source_replica,
new_part_name, parts_to_merge,
is_detach, is_currently_executing,
num_tries, last_exception,
last_attempt_time,
num_postponed, postpone_reason, last_postpone_time
FROM system.replication_queue
ORDER BY is_currently_executing DESC, create_time;
SELECT name, value, ctime, path
FROM system.zookeeper
WHERE path IN ('/', '/clickhouse')
ORDER BY path;
SELECT name, value, changed
FROM system.settings
ORDER BY changed DESC, name;
SELECT metric, value, description
FROM system.metrics
ORDER BY metric;
SELECT metric, value
FROM system.asynchronous_metrics
ORDER BY metric;
SELECT event, value, description
FROM system.events
ORDER BY event;
SELECT count(*) AS total_contributors
FROM system.contributors;
SELECT name AS random_contributor
FROM system.contributors
WHERE position(name, 'me') = 1
AND length(name) >= 3
ORDER BY length(name) ASC, sipHash64(name) ASC
LIMIT 5;
SELECT *
FROM system.part_log
ORDER BY event_date DESC, event_time DESC
LIMIT 100;
SELECT database, count(*) AS objects
FROM system.tables
WHERE engine = 'Kafka'
GROUP BY database
ORDER BY database;
SELECT database, table,
replace(assignments.topic, ',', ', ') AS topics,
num_commits, num_messages_read,
last_commit_time, last_poll_time,
if(consumer_id = '', 0, 1) AS has_consumer_id,
is_currently_used,
if(empty(exceptions.time), '', toString(exceptions.time[-1])) AS last_exception_time
FROM system.kafka_consumers
ORDER BY last_commit_time DESC, last_poll_time DESC, database, table;
SELECT database, table,
exceptions.time[-1] AS last_exception_time,
consumer_id,
exceptions.time AS all_exception_times,
exceptions.text AS all_exception_texts
FROM system.kafka_consumers
WHERE notEmpty(exceptions.time)
ORDER BY exceptions.time[-1] DESC, database, table;
SELECT database,
count(distinct table) AS tables,
count(distinct column) AS columns,
count(distinct type) AS data_types,
formatReadableSize(sum(column_data_compressed_bytes)) AS hr_compressed,
formatReadableSize(sum(column_data_uncompressed_bytes)) AS hr_uncompressed,
round((sum(column_data_uncompressed_bytes) - sum(column_data_compressed_bytes))
* 100 / sum(column_data_uncompressed_bytes), 2) AS gain_pct
FROM system.parts_columns
WHERE active
GROUP BY database
ORDER BY database;
SELECT database, type AS data_type, count() AS columns
FROM system.columns
WHERE database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
GROUP BY database, type
ORDER BY database, type;
SELECT name, code, value AS occurrences,
last_error_time, last_error_message, last_error_trace,
remote
FROM system.errors;
SELECT database, table, column,
any(type) AS data_type,
sum(column_data_compressed_bytes) AS compressed,
sum(column_data_uncompressed_bytes) AS uncompressed,
round((sum(column_data_uncompressed_bytes) - sum(column_data_compressed_bytes))
* 100 / sum(column_data_uncompressed_bytes), 2) AS gain_pct
FROM system.parts_columns
WHERE active
GROUP BY database, table, column
HAVING sum(column_data_uncompressed_bytes) > 0
ORDER BY database, table, column;
SELECT version() AS version,
hostName() AS hostname,
now() - uptime() AS server_started,
formatReadableSize(sum(bytes_on_disk)) AS total_db_size
FROM system.parts
FORMAT TabSeparated;
SELECT count() AS defined_schemata FROM system.databases;
SELECT count() AS defined_tables FROM system.tables;
SELECT count() AS active_sessions FROM system.processes;
SELECT round(count(*) / 24, 5) AS queries_per_hour
FROM system.query_log
WHERE event_time > now() - interval 1 day;
SELECT formatReadableSize(value / uptime() * 60 * 60 * 24) AS merges_per_day_hr
FROM system.events
WHERE event = 'MergedUncompressedBytes';
Generated from ch2html.sql v1.0.14 — github.com/meob/db2html