Please enable JavaScript.
Coggle requires JavaScript to display documents.
Manage and monitor SQL Server instances (Monitor database activity…
Manage and monitor SQL
Server instances
Monitor database activity
Monitor current sessions
Currant session DMVs
sys.dm_exec_connections
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_tran_session_transactions
sys dm_exec_session_wait_stats
sys.dm_os_waiting_tasks
Identify sessions that cause blocking activity
DMV's
[sys].[dm_exec_requests]
[sys].[dm_os_waiting_tasks]
sp_who
sp_who2
Identify sessions that consume tempdb resources
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_version_store
Configure the data collector
The purpose is capturing server-level metrics and query performance over time
It was designed for historical analysis, trend analysis, and troubleshooting/optimizing past events.
Management data warehouse
The MDW is a repository used by the data collector
to store the metrics that are collected.
Data collector
The data collector is made up of a number of scheduled SQL Server Agent jobs and SSIS packages that collect metrics through a number of DMOs
Reports
Returns information about the wait queue of tasks that are waiting on resources.
Monitor queries
Query Store
Collects telemetry and metrics about all queries that are executed within the database
[sys].[query_store_wait_stats]
sp_query_store_force_plan
sp_query_store_force_plan
Extended Events and trace events
Light weight performance monitoring
system
Identify problematic execution plans
sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_plan_attributes
Troubleshoot server health using Extended Events
Every instance of SQL Server has a default Extended Event session created called
system_health
Manage indexes
Identify and repair index fragmentation
Index fragmentation can potentially be the root cause for poor query performance
In simpler environments, rebuilding the index daily or weekly will improve the performance
In larger environments, rebuilding indexes can take a long time, can cause blocks and reduce the flash storage. Therefore reorganizing the index is a better solution.
Determining the level of fragmentation for a given table or index will help you determine the correct remediation strategy
Identify and create missing indexes
sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
Identify and drop underutilized indexes
sys.dm_db_index_usage_stats
sys.indexes
Manage existing columnstore indexes
Manage statistics
Monitor SQL Server instances