MS SQL i memorija/stanje sistema/baze
Neke skraćenice vezane za MS SQL i memoriju :
PLE – page life expectancy
DMV – dynamic management views (od SQL Servera 2005)
DMF – dynamic management functions.
DMV/DMF returns SQL Server runtime state information that can be used to monitor SQL Server health during runtime, troubleshoot the performance bottleneck/issues and proactively work on to minimize the downtime. The name of the DMV/DMF starts with “dm_”. They all reside in sys schema.
DMV/DMF fall into two categories:
*Server-scoped Dynamic Management Views and Functions – They reside in master database and provide SQL Server instance wide information.
*Database-scoped Dynamic Management Views and Functions – They reside in each database and provide database wide information.
sys.dm_exec_cached_plans – Cached query plans available to SQL Server
sys.dm_exec_sessions – Sessions in SQL Server
sys.dm_exec_connections – Connections to SQL Server
sys.dm_db_index_usage_stats – Seeks, scans, lookups per index
sys.dm_io_virtual_file_stats – IO statistics for databases and log files
sys.dm_tran_active_transactions – Transaction state for an instance of SQL Server
sys.dm_exec_sql_text – Returns TSQL code
sys.dm_exec_query_plan – Returns query plan
sys.dm_os_wait_stats – Returns information what resources SQL is waiting on. Every time SQL Server is forced to wait for a resource it records the wait. Odatle se vidi šta je sporo : CPU, RAM, HDD ili nešto treće. Metrics for the columns returned when querying sys.dm_os_wait_stats is cumulative. Like all Dynamic Management Object information these values accumulate over time and are wiped-clean at a SQL Server service restart.
sys.dm_os_performance_counters – Returns performance monitor counters related to SQL Server
sys.dm_os_sys_info
sys.dm_os_process_memory – prikazuje koliko memorije SQL troši u ovom trtenutku
