Reading...
Play button
Play button
Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
26 Cards in this Set
- Front
- Back
- 3rd side (hint)
|
What are DMV's?
|
Dynamic Management Views were introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine,
|
|
|
|
Two types of dynamic management views
|
1. Server-scoped DMV: Stored in Master Database
2. Database-scoped DMV: Specific to each database |
|
|
|
Permissions required to Execute DMVs [Security]
|
Server Scoped - Grant VIEW SERVER STATE to user
Database scoped - Grant VIEW DATABASE STATE to user |
|
|
|
DMV names all start with
|
sys.dm_
|
|
|
|
To what schema do DMVs belong?
|
sys
|
|
|
|
What are some DMV categories? (6)
|
1. SQL Server OS related [Hardware Resources] DMVs
2. Database related DMVs 3. Index related DMVs 4. Execution related DMVs 5. IO related DMVs 6. Transaction related DMVs |
|
|
|
SQL Server OS Related DMVs (5)
SQL Server IO Related DMVs (2) |
1 sys.dm_os_sys_info
2 sys.dm_os_hosts 3 sys.dm_os_schedulers 4 sys.dm_os_memory_clerk 5 sys.dm_cluster_nodes 1 sys.dm_io_pending_io_requests 2 sys.dm_io_virtual_file_stats |
5 - os 2-io
|
|
|
1 sys.dm_os_sys_info
|
information about the SQL Server machine,
available resources and the resource consumption. |
|
|
|
2 sys.dm_os_hosts
|
hosts registered with SQL Server 2005. This view also provides the resources used by each host
|
|
|
|
3 sys.dm_os_schedulers
|
CPU bottleneck in the SQL Server machine if runnable task counts show high values
|
|
|
|
4 sys.dm_io_pending_io_requests
|
I/O requests pending in SQL Server side
|
|
|
|
5 sys.dm_io_virtual_file_stats
|
I/O statistics for data and log files
|
|
|
|
6 sys.dm_os_memory_clerk
|
how much memory SQL Server has allocated through AWE or
get the memory consumption by internal components of SQL Server 2005 |
|
|
|
sys.dm_os_waiting_tasks
|
returns information about wait queue of SQLOS tasks that are waiting on some resource such as blocking and latch contention
|
|
|
|
Database Related DMV (4)
|
sys.dm_db_file_space_usage
sys.dm_db_session_space_usage sys.dm_db_task_space_useage sys.dm_db_partition_stats |
|
|
|
sys.dm_db_file_space_usage
|
space usage information of TEMPDB database
|
|
|
|
sys.dm_db_session_space_usage
|
number of pages allocated and de-allocated by each session for the database
|
|
|
|
sys.dm_db_partition_stats
|
page and row-count information for every partition in the current database.
|
|
|
|
sys.dm_os_performance_counters
|
SQL Server / Database related counters maintained by the server
|
|
|
|
INDEX Related DMV (4)
|
sys.dm_db_index_usage_stats
sys.dm_db_index_physical_stats sys.dm_db_missing_index_columns sys.dm_db_missing_index_details |
|
|
|
sys.dm_db_index_usage_stats
|
get useful information about the index usage for all objects in all databases. This also shows the amount of seeks and scan for each index.
|
|
|
|
Execution Related DMVs (4)
|
sys.dm_exec_sessions
sys.dm_exec_connections sys.dm_exec_requests sys.dm_exec_sql_text |
|
|
|
sys.dm_exec_sessions
|
1. will give information on each session connected to SQL Server.
2. is similar to running sp_who2 or 3. querying Master..sysprocesses table |
|
|
|
sys.dm_exec_connections
|
shows all the connection info to SQL Server ie: Network Protocol, TCP port, connection authorization.
|
|
|
|
sys.dm_exec_requests
|
Returns one row for each request executing in SQL Server
|
|
|
|
sys.dm_exec_sql_text
|
returns the text of a SQL statement given a SQL handle
|
|
