DMCDMC Bilgi Teknolojileri
Microsoft SQL Server

For SQL Server deep DMV coverage

From Always On Availability Group to Query Store, Hekaton to CDC, TDE certificate expiry to kill SPID actions — a comprehensive DMV and catalog view scan suite curated by a Microsoft Data Platform MVP.

Request Demo See capability matrix
SQL Server 2008R2 2012 / 2014 / 2016 2017 / 2019 / 2022 Azure SQL DB Managed Instance Synapse Analytics
70+DMV / Catalog View
46Diagnostic Tab
14+Operational Action
SQL 2022Ledger compatible
Performance & Query

Query Store, plan cache, missing index — deep analysis

Query Store

Query Store integration

Query text, plan history and runtime stats are read from Query Store. Automatic plan regression detection, forced plans management.

  • get_query_store_health
  • get_query_store_regressions
  • get_query_store_forced_plans
  • force_query_plan / unforce_query_plan
Plan Cache

Plan cache & deep dive

Plan cache top consumers, last actual stats per plan handle, parameter values, memory grants.

  • get_plan_cache_analysis
  • get_query_plan_stats_last(plan_handle)
  • get_query_memory_grants
  • get_execution_plan (XML parse)
Missing Index

Missing index & advisor

Missing index group analysis, impact score, duplicate index detection, fragmentation, online rebuild and fill factor analysis.

  • get_missing_index_recommendations
  • get_missing_index_groups
  • get_duplicate_indexes
  • get_index_maintenance_advisor
Wait Stats

Wait analysis

Wait categorization, session-level wait, query-level wait, and 1-168 hour trend window (sparkline + min/max/avg).

  • get_wait_stats + detail
  • get_session_wait_stats
  • get_query_wait_stats
  • Wait stats trend (hourly)
Hekaton

In-Memory OLTP & Columnstore

Memory-optimized tables, garbage collection, checkpoint files, columnstore segment fragmentation, dictionary stats, deltastore.

  • get_hekaton_stats (SQL 2014+)
  • get_columnstore_health
  • Segment fragmentation
  • Dictionary & deltastore
Live Monitor

Active & live query

Active sessions, live query monitor, session input buffer (last SQL executed by the SPID), parallel query analysis.

  • get_active_sessions
  • get_live_query_monitor
  • get_session_input_buffer(spid)
  • get_parallel_query_analysis (CXPACKET)
High Availability

Always On AG, WSFC, log shipping

AG Detail

Availability Group depth

AG status, replica role, sync mode, failover ready, log send queue, redo queue, lag KB and time, secondary readable status.

  • get_alwayson_ag_detail
  • Replica role + state
  • Lag (KB + sec)
  • Read-only routing
Endpoint

AG endpoint & routing

AG endpoint state, listener TCP port reachability, encryption + authentication settings, read-only routing list.

  • get_ag_endpoint_health
  • Listener TCP test
  • Endpoint encryption
  • RO routing list
WSFC

Cluster & quorum

Windows Server Failover Cluster nodes, status, quorum type, vote count, witness health, dynamic quorum decisions.

  • get_wsfc_status
  • get_cluster_quorum_status
  • Node state + vote
  • Witness type
Log Shipping

Log shipping & backup

Log shipping primary / secondary status, backup chain validation, critical lag alarm (> 1 hour), recovery model changes.

  • get_log_shipping_status
  • get_backup_chain_validation(7d)
  • get_backup_history
  • Recovery model audit
Replication

Transactional replication

Publication, subscription, distribution agent backlog, latency, failed commands, agent history.

  • get_replication_status
  • Distribution backlog
  • Agent latency
  • Failed commands
Snapshots

DB Snapshots inventory

Database snapshot list, source DB, sparse file size, creation date, stale snapshot warning.

  • get_db_snapshots_inventory
  • Source DB relation
  • Sparse file size
  • Age analysis
Security & Compliance

TDE, Always Encrypted, Ledger and audit

TDE

TDE status & certificate expiry

Transparent Data Encryption-enabled databases, certificate expiration dates and early warning (30 days prior). Lost certificate = lost data access.

  • get_tde_status
  • get_tde_certificate_expiry
  • Encryption state per DB
  • 30/60/90 day warning
Always Encrypted

AE / DDM / RLS / Ledger

Always Encrypted column list, Dynamic Data Masking-enabled columns, Row Level Security policies, SQL 2022+ Ledger blockchain verification.

  • Always Encrypted columns
  • Dynamic Data Masking
  • Row Level Security
  • Ledger (SQL 2022+)
Audit

SQL Server Audit

Audit specifications, target file/log, action groups, check whether SQL Audit is enabled, and audit log review.

  • get_audit_status
  • get_audit_logs(limit)
  • Action group analysis
  • Failed login spike
Logins

Login security & lockout

SQL logins and Windows logins, password policy, expiry, default DB. Login lockout policy and account status.

  • get_logins_security
  • get_failed_logins(24h)
  • Login lockout policy
  • Password expiry
Permissions

Permission matrix & orphan users

User / role / login relationship matrix, sysadmin count, public role permissions, orphan users (users without a login) detection.

  • get_permission_matrix (TTL 300s)
  • get_orphan_users
  • Excessive sysadmin warning
  • Public role grants
XE & Crypto

Extended Events & crypto

Active XE sessions, ring buffer event rate, XE overhead alarm, crypto provider list (key management).

  • get_xe_sessions
  • get_extended_events_ring_stats
  • Crypto providers
  • Change tracking status
Operational Actions

Safe intervention from the UI

All operational actions run with a confirmation dialog, are written to the audit log and pass through role-based permission checks. No need to open SSMS.

Kill SPID

Terminate problematic session. Protected from system SPID (≤ 50).

POST /kill-spid

Update Statistics

Table or statistic-level FULLSCAN.

POST /update-statistics

Shrink Log File

DBCC SHRINKFILE NO_INFOMSGS.

POST /shrink-log

Force Plan

Query Store plan force / unforce.

force_plan / unforce_plan

DBCC CHECKDB

PHYSICAL_ONLY or full integrity check.

run_dbcc_checkdb

Rebuild Index

Online rebuild (ENT) + WAIT_AT_LOW_PRIORITY.

rebuild_index_online

Recompile Procedure

Cached plan invalidation via sp_recompile.

recompile_procedure

Resource Governor

Resource pool / workload group runtime tuning.

update_resource_pool
api/v2/instance.py FastAPI 
# Sorunlu SPID'i sonlandır — system SPID koruması otomatik
POST /api/v2/instance/{source_id}/kill-spid
Body: { "spid": 147, "reason": "long-running blocker" }

# Yanıt:
{
  "status": "killed",
  "spid": 147,
  "audit_id": "a8f3...",
  "protected": false     # sistem SPID değil
}
Memory & CPU

Memory clerks, scheduler & NUMA

Memory Clerks

Memory clerks & OS memory

SQL Server process memory, OS commit, memory clerks (CACHESTORE, USERSTORE, MEMORYCLERK_*) consumption, buffer pool stats.

  • get_memory_clerks
  • get_process_memory
  • get_os_memory
  • get_buffer_pool_stats
Workers

Workers, scheduler, NUMA

Worker thread status, runnable queue, scheduler health, NUMA topology distribution, max worker threads config.

  • get_workers_summary
  • get_scheduler_health
  • get_numa_topology
  • get_resource_governor
TempDB

TempDB & version store

TempDB file distribution, allocation contention, version store usage, alarm via tempdb watchdog.

  • get_tempdb_details
  • get_tempdb_version_store
  • Allocation contention
  • tempdb_watchdog
Loaded Modules

Loaded modules & modules

DLLs loaded in the SQL Server process, third-party modules, agent jobs and job step failures.

  • get_loaded_modules
  • get_agent_jobs
  • get_agent_job_step_failures
  • get_database_mail_status
Full Capability Matrix

70+ DMV / Catalog Views, 14+ actions

The table below summarizes the monitoring and action capabilities implemented in the SQL Server provider of SentinelDB360.

Capability Description DMV / Command
Performance & Query
Top queriesCPU/IO/duration-based rankingsys.dm_exec_query_stats
Query StorePlan history + regressionssys.query_store_*
Plan cacheTop consumers + plan handle deep divedm_exec_cached_plans
Missing indexSuggested index + impactdm_db_missing_index_*
Index physical statsFragmentation, page countdm_db_index_physical_stats
Memory grantsActive query memorydm_exec_query_memory_grants
High Availability
Always On AGReplica state, sync mode, lagdm_hadr_database_replica_states
AG endpointEndpoint state + listenersys.tcp_endpoints
WSFC quorumCluster nodes + quorumdm_hadr_cluster*
Log shippingPrimary/secondary lagmsdb.dbo.log_shipping*
Backup chain7-day chain validationmsdb.dbo.backupset
ReplicationDistribution backlogdistribution.dbo.MSrepl_*
Security & Compliance
TDE statusEncryption state per DBdm_database_encryption_keys
TDE certificate expiry30/60/90 day warningsys.certificates
Always EncryptedAE column listsys.column_encryption_keys
Row Level SecurityActive policiessys.security_policies
Ledger (SQL 2022+)Blockchain verificationsys.database_ledger_*
SQL Server AuditAudit specificationssys.server_audits
Failed logins (24h)Spike detectionxp_readerrorlog
Permission matrixUser / role / login (TTL 300s)sys.database_permissions
Storage & I/O
File I/O latencyRead/write ms per filedm_io_virtual_file_stats
I/O delta engine10s sample intervalget_io_virtual_file_stats_delta
Disk volumesDrive utilizationdm_os_volume_stats
DB growth historyAutogrow eventsdefault_trace
VLF countLog file VLF analysisDBCC LOGINFO
Log space usagePer-DB log used %dm_db_log_space_usage
Memory, CPU & Workers
Memory clerksCACHESTORE, USERSTORE, MEMORYCLERKdm_os_memory_clerks
OS memoryProcess commit + system commitdm_os_sys_memory
Worker threadsActive / runnable / idledm_os_workers
SchedulersHealth + work_queue_countdm_os_schedulers
NUMA topologyMemory node + CPU nodedm_os_nodes
Resource GovernorPool / workload groupdm_resource_governor_*
Operational Actions
Kill SPIDSystem SPID protection (≤50)KILL @spid
Update statisticsFULLSCAN table / statisticUPDATE STATISTICS ... WITH FULLSCAN
Shrink logNO_INFOMSGSDBCC SHRINKFILE
Force / unforce planQuery Store plan controlsp_query_store_force_plan
RecompileCached plan invalidationsp_recompile
DBCC CHECKDBPHYSICAL_ONLY or fullDBCC CHECKDB
Rebuild index onlineWAIT_AT_LOW_PRIORITYALTER INDEX REBUILD
Resource pool updateMAX_CPU_PERCENT runtimeALTER RESOURCE POOL
Cloud & Specialized
Azure SQL DBDTU/vCore statsdm_db_resource_stats
Elastic poolPool resource statsdm_elastic_pool_resource_stats
HekatonMemory-optimized statsdm_db_xtp_*
ColumnstoreSegment fragmentationdm_db_column_store_*
CDCChange Data Capture healthcdc.change_tables
Service BrokerPoison message detectionsys.transmission_queue
Linked serversConnection + providersys.servers

Start monitoring SQL Server in 15 minutes

From SQL 2008R2 to SQL 2022 and Azure SQL — agentless setup, MVP-curated.

Request Demo Full Platform