{question}
Can you help me identify long-running SQL queries and incorrect statistics?
{question}
{answer}
To find long-running queries, query the system.connections, system.transactions, and system.nodes table:
select c.user, c.schema, c.clienthost, c.clientinfo, substr(c.sqlstring, 0, 60) as "SQL Statement (partial)",
c.runtime / 1000000 as "runtime (seconds)", c.transruntime / 1000000 as "transruntime(seconds)",
c.transid as "TransId", n.hostname, CASE WHEN t.blockedby = -1 then NULL else t.blockedby END as "BlockedBy TransId"
from system.connections c inner join (select distinct id, nodeid, sourcenode, blockedby
from system.transactions) t on t.id = c.transid and t.nodeid = c.nodeid and t.sourcenode = c.nodeid
inner join system.nodes n on n.id = c.nodeid WHERE (c.runtime / 1000000) >= {# of Seconds considered a long query}
order by runtime desc;
To find a table with incorrect statistics, query the system.indexstatistics table:
select i.schema, i.tablename, i.indexname, i.STATSTYPE, i.KEYCOUNT, i.VALID, i.MAXKEYSIZE, i.MINKEY, i.MAXKEY
from system.indexstatistics i where i.valid = 0 and i.schema <> 'SYSTEM' order by i.schema, i.tablename, i.indexname;
{answer}
Comments