Breaking News: Grepper is joining You.com. Read the official announcement!

sql monitoring

Innocent Iguana answered on March 9, 2023 Popularity 6/10 Helpfulness 5/10

answer sql monitoring

sql monitoring

Comment

# MySQL-------------------------
# the currently executing queries, along with information about the user, database, and execution state.
SELECT ID, USER, DB, STATE, TIME, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'executing';
# the number of rows inserted, updated, deleted, fetched, and returned for each table in the specified database.
SELECT TABLE_SCHEMA, SUM(ROWS_INSERTED), SUM(ROWS_UPDATED), SUM(ROWS_DELETED), SUM(ROWS_READ), SUM(ROWS_SENT) FROM INFORMATION_SCHEMA.TABLE_STATISTICS WHERE TABLE_SCHEMA = 'demo' GROUP BY TABLE_SCHEMA;

# SQL Server-----------------------
# the currently executing queries, along with information about the user, database, and execution state.
SELECT session_id, login_name, database_name, status, last_request_start_time, command FROM sys.dm_exec_sessions WHERE status = 'running';
# the number of rows inserted, updated, deleted, fetched, and returned for each table in the specified database.
SELECT DB_NAME(database_id) AS DatabaseName, SUM(num_logical_reads) AS LogicalReads, SUM(num_physical_reads) AS PhysicalReads, SUM(num_page_reads) AS PageReads, SUM(num_page_writes) AS PageWrites, SUM(num_logical_writes) AS LogicalWrites FROM sys.dm_db_index_operational_stats(DB_ID('demo'), NULL, NULL, NULL) GROUP BY database_id;

# PostgreSQL-------------------------
# the currently executing queries, along with information about the user, database, and execution state.
SELECT pid, usename, datname, state, state_change, query FROM pg_stat_activity WHERE state = 'active';
# the number of rows inserted, updated, deleted, fetched, and returned for each table in the specified database.
SELECT datname, tup_inserted, tup_updated, tup_deleted, tup_fetched, tup_returned FROM pg_stat_database WHERE datname = 'demo';

# Extra things that can be done with extensions in PostgreSQL
# Enable extension
CREATE EXTENSION pg_stat_statements;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
# See extensions and libraries
\dx
show shared_preload_libraries;
# Connect to database
\connect db_name
# turn on expanded table formatting
\x
# See stats
SELECT dbid, query, total_exec_time FROM pg_stat_statements;
# See database name and database id
SELECT oid, datname FROM pg_database;
# Drop extension
DROP EXTENSION pg_stat_statements;
# Reset libraries
ALTER SYSTEM RESET shared_preload_libraries;

Popularity 6/10 Helpfulness 5/10 Language sql

Source: Grepper

Tags: monitoring sql

Link to this answer
Share Copy Link

Contributed on Mar 09 2023

Innocent Iguana

0 Answers Avg Quality 2/10

sql monitoring

Contents

More Related Answers

sql monitoring

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.