DBA DMVs

How would you find what is locked in SQL Server?

select * from sys.dm_tran_locks Old school : sp_lock << but it is now deprecated SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID()

What is the DMV that Returns page and row count information for each partition in the selected database.

sys.dm_db_partition_stats

What is the DMV that Returns CPU, I/O, and memory consumption information for an Azure SQL Database, one row for every 16 seconds.

sys.dm_db_resources_stats

What is the DMV that Returns the number of pages allocated and deallocated by each session for the database. This is applicable only to the tempdb data base.

sys.dm_db_session_space_usage

What is the DMV that Azure SQL Database DMV that returns information about all the waits encountered by threads during execution at the database level. This DMV shows the waits that have completed and does not show current waits.

sys.dm_db_wait_stats

What is the DMV that Returns information about all the waits encountered.

sys.dm_db_wait_stats

What dmv is used get information about cached plans and the number of times the plans were reused

sys.dm_exec_cached_plans

Which dynamic management view returns only the optimized plans that are cached?

sys.dm_exec_cached_plans

What is the DMV that Displays detailed connection information for each established connection to the instance of SQL Server

sys.dm_exec_connections

What is the DMV that Shows one row per authenticated session on SQL Server. This is a server-scoped view containing information about all active user connections and tasks.

sys.dm_exec_sessions

What is the DMV that Shows the plain text of the SQL batch identifed by the SQL_handle.

sys.dm_exec_sql_text

What system stored proc is used to get the exact text of a cached query

sys.dm_exec_sql_text

When monitoring current sessions what 6 DMVs should you be looking at?

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

What are the dm_* views map the the sys.sysprocesses table?

sys.dm_exec_connections sys.dm_exec_sessions sys.dm_exec_requests

What is the DMV that Shows the Showplan in XML format for the SQL batch provided in the sql_handle.

sys.dm_exec_query_plan

What DMO can you use to get information about query plans

sys.dm_exec_query_stats

What is the DMV that Provides aggregated performance statistics for cached query plans.

sys.dm_exec_query_stats

What system stored proc is used get information about cached plans and the number of times the plans were reused

sys.dm_exec_query_stats

What is the DMV that Returns information about every request currently executing within the SQL Server.

sys.dm_exec_requests

What is the DMV that Provides information about all the waits encountered by threads that executed for each session.

sys.dm_exec_session_wait_stats

What is the DMV that Returns the query plan in text format for a T-SQL batch or a specifc statement within the batch.

sys.dm_exec_text_query_plan

Which dmv returns the optimized plans that are cached or currently executing in a text format?

sys.dm_exec_text_query_plan

What is the DMV that Returns a row for each pending I/O request.

sys.dm_io_pending_io_requests

What is the DMV that Shows I/O statistics for the data and log files.

sys.dm_io_virtual_file_stats

What is the DMV that is Aggregated, historical look at the wait statistics for all wait types that have been encountered.

sys.dm_os_wait_stats

In Sql Server 2016, what are the DMVs used to view wait statistics?

sys.dm_os_wait_stats sys.dm_exec_sessoins_wait_stats sys.dm_os_waiting_tasks

What is the DMV that is wait statistics for currently executing requests that are experiencing resource waits.

sys.dm_os_waiting_tasks

What is the DMV that Shows information about instance-level transactions.

sys.dm_tran_active_transactions

What is the DMV that Displays a single row of state information of the transaction in the current session.

sys.dm_tran_current_transactions

What is the DMV that Displays information about transactions at the database level.

sys.dm_tran_database_transactions

What is the DMV that Shows information about currently active lock manager resources.

sys.dm_tran_locks

List some DMVs to view information about locks.

sys.dm_tran_locks sys.dm_os_waiting_tasks sys.dm_os_wait_stats

What is the DMV that Shows related information for transactions and their associated sessions.

sys.dm_tran_session_transactions

What system table does sys.dm_exec_connections map to?

sys.sysprocesses

What system table does sys.dm_exec_requests map to?

sys.sysprocesses

What system table does sys.dm_exec_sessions map to?

sys.sysprocesses

What's a good rule of thumb as to when to do a reorganization or a rebuild ?

Look at the sys.dm_db_index_physical_stats.avg_fragmentation_in_percent for 15 - 30% fragmentation... do index reorganization for greater than 30% fragmentation... do a rebuild

How do you determine fragmentation in a columnstore?

, query the [sys].[dm_db_column_store_row_group_physical_stats] DMV SELECT i.object_id, OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.index_id, i.type_desc, 100*(ISNULL(deleted_rows,0))/total_rows AS Fragmentation, s.* FROM sys.indexes AS i JOIN sys.dm_db_column_store_row_group_physical_stats AS s ON i.object_id = s.object_id AND i.index_id = s.index_id ORDER BY fragmentation DESC;

Show sql that queries current sessions

-- Query currently connected sessions with their stastics SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.nt_domain, s.nt_user_name, c.connect_time, s.login_time, s.reads, s.writes, s.logical_reads, s.status, s.cpu_time, s.total_scheduled_time, s.total_elapsed_time FROM sys.dm_exec_connections AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id; GO

Show sql on now to identify a session that is blocked

-- Using the sys.dm_exec_requests SELECT session_id, blocking_session_id, open_transaction_count, wait_time, wait_type, last_wait_type, wait_resource, transaction_isolation_level, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; GO -- Using the sys.dm_os_waiting_tasks SELECT session_id, blocking_session_id, wait_duration_ms, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL --using sys.dm_tran_locks SELECT request_session_id as s_id, resource_type, resource_associated_entity_id, request_status, request_mode FROM sys.dm_tran_locks WHERE resource_database_id = db_id('_DatabaseName_') AND request_status = 'WAIT'

How many rows are returned for each row in the sys.dm_db_index_physical_stats ?

1 However, when a table is partitioned, it effectively treats each partition as a table, so this DMV actually returns a row for each level of each partition of each index.

When were new DMVs, such as sys.dm_io_cluster_valid_path_names introduced?

2014

How can you see what is blocked?

>sys.dm_tran_locks where the request_status = 'WAIT' >sys.dm_exec_requests WHERE blocking_session_id <> 0; >sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL SELECT request_session_id as s_id, resource_type, resource_associated_entity_id, request_status, request_mode FROM sys.dm_tran_locks WHERE resource_database_id = db_id('_DatabaseName_') AND request_status = 'WAIT' SELECT tl.resource_type AS res_typ, tl.resource_database_id AS res_dbid, tl.resource_associated_entity_id AS res_entid, tl.request_mode AS mode, tl.request_session_id AS s_id, wt.blocking_session_id AS blocking_s_id FROM sys.dm_tran_locks as tl INNER JOIN sys.dm_os_waiting_tasks as wt ON tl.lock_owner_address = wt.resource_address; -- Using the sys.dm_exec_requests SELECT session_id, blocking_session_id, open_transaction_count, wait_time, wait_type, last_wait_type, wait_resource, transaction_isolation_level, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; GO -- Using the sys.dm_os_waiting_tasks SELECT session_id, blocking_session_id, wait_duration_ms, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

How would you identify table level locks in SQL Server?

Activity Monitor, sp_who2 (sp_whoIsActive)

Tell about the DMV sys.dm_database_encryption_keys

Another important DMV for troubleshooting, encryption problems. Returns information about the encryption state of a database and its associated database encryption keys. The sys.dm_database_encryption_keys view returns details about a databases encryption state and its associated DEKs. The following table shows the results returned by our SELECT statement. DbName EncryptState KeyAlgorithm KeyLength EncryptType EmpData2 1 AES 256 CERTIFICATE Notice that the EncryptType column has a value of CERTIFICATE, which confirms that a certificate was used to encrypt the DEK. Also notice that the EncryptState column shows a value of 1. This indicates that the database is in an unencrypted state. According to SQL Server documentation, the column can display any one of the values described in the following table. Value Description 0 No database encryption key present, no encryption 1 Unencrypted 2 Encryption in progress 3 Encrypted 4 Key change in progress 5 Decryption in progress 6 The certificate or asymmetric key encrypting the DEK is being changed

What is the differencs between sessions and connections in SQLServer?

Both the connections DMV (sys.dm_exec_connections) and Sessions DMV (sys.dm_exec_sessions) get their data from sysprocesses table. The connections show only external connections and each connection ( ssms gets 1 connection , new query tab in ssms gets 1 connection) has an spid . But sessions will include both external connections and internal processes running known as background processes. Run the following query and you will get a clearer picture select a.spid , a.nt_username , a.loginame , a.program_name from sys.sysprocesses a , sys.dm_exec_connections b where a .spid=b.session_id select a.spid , a.nt_username , a.loginame , a.program_name from sys.sysprocesses a , sys.dm_exec_sessions b where a .spid=b.session_id

The Sampled scanning mode for the [sys].[dm_db_index_physical_stats] dmv scans every 100th page True or False

Both true and false. it depends.... if the leaf-level contains less than 100,000 pages, all leaf-level pages are scanned to calculate additional fragmentation. For the larger tables only 1% of the table is scanned by reading every 100th page.

How can you view the names of transactions?

By inspecting the name column of the dynamic management view (DMV) sys.dm_tran_active_transactions. The default name for user transactions is user_transaction but you can assign your own name to a transaction by using explicit transactions.

You administer a Microsoft SQL Server server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do? A. Query the sys.dm_db_index_usage_stats DMV. B. Query the sys.dm_db_missing_index_details DMV. C. Use the Database Engine Tuning Advisor. D. Query the sys.dm_db_missing_index_columns DMV.

C. Use the Database Engine Tuning Advisor.

What are the sys.dm_exec_* DMOs for?

Connections, sessions, requests, and query execution

Tell about the DMV sys.dm_exec_query_plan

DMF that gets the query plan given the plan handle Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing. --The following example returns the plans and average CPU time for the top five queries. SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], Plan_handle, query_plan FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY total_worker_time/execution_count DESC; GO

What are the sys.dm_db_* DMOs for?

Database-scoped information

Tell about the DMV sys.dm_tran_current_transactions

Displays a single row of state information of the transaction in the current session.

Tell about the DMV sys.dm_db_missing_index_details

Displays detailed information about missing indexes. identifes our missing indexes, returns the identifer columns index_handle, object_id and database_id, along with the following: - equality_columns the columns that would have been useful, based on an equality predicate - inequality_columns the columns that would have been useful, based on an inequality predicate (i.e. any comparison other than "column = value") - included_columns columns that, if included, would have been useful to cover the query - statement database and schema qualifed object name of the object identifed by database_id and object_id.

Tell about the DMV sys.dm_tran_database_transactions

Displays information about transactions at the database level.

What are DMVs ?

Dynamic Management Views DMVs actually come in two flavors DMVs (dynamic management views) and DMFs (dynamic management functions) and are sometimes classified as DMOs (dynamic management objects). They allow you to get better insight into what is happening in SQL Server.

What are the sys.dm_tran_* DMOs for?

Dynamic Management view that give details about transactions

sys.dm_exec_cached_plan_dependent_objects

FUNCTION in 2008 Returns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan This DMF gives you the information about dependent objects on the current plan. You need to pass the valid plan_handle and get the dependent objects.

The Limited scanning mode for the [sys].[dm_db_index_physical_stats] dmv scans a limited number of leaf-level pages True or False

False The LIMITED mode scans the level above the leaf level of an index It can only return the logical fragmentation and the page count because it never scanned the leaf-level pages of the index.

Tell about the DMV sys.dm_tran_active_snapshot_database_transactions

For tempdb... Provides information about all active transactions that generate or potentially access row versions.

Tell about the DMV sys.dm_tran_version_store

For tempdb... Provides information about all version records in the version store. Records that dont fit in the 8192 byte limit will consume both the [record_image_frst_part] and [record_image_second_part] columns. Be careful with querying this DMV because it can be potentially wide and contain many rows. returns a virtual table that displays all version records in the version store. sys.dm_tran_version_store is inefcient to run because it queries the entire version store, and the version store can be very large. Each versioned record is stored as binary data together with some tracking or status information. Similar to records in database tables, version store records are stored in 8,192-byte pages. If a record exceeds 8,192 bytes, the record will be split across two different records. Because the versioned record is stored as binary, there are no problems with different collations from different databases. Use sys.dm_tran_ version_store to fnd the previous versions of the rows in binary representation as they exist in the version store. provides details of the version records stored in the version store in tempdb. This information is cumulative from the moment when snapshot isolation was enabled, and remains until the last transaction relying upon the row's versions is committed or rolled back. The records returned indicate where queries that need the same data from the database will go to fetch the previous version of the row, in cases where the row is being modifed under the snapshot isolation level. As is the case with the other DMVs we've reviewed, the results of this DMV reect the current point in time when the view was queried. - transaction_sequence_num the sequence number for the transaction within the version store. - version_sequence_num sequence of version records added to the version store under the same transaction_sequence_num. The version_sequence_number value will be unique for a given transaction_sequence_num value. - database_id the database that the version records come from. Surrogate key of the database (relates to sys.databases). - status the versioned record inside the store may be stored across one or two pages. The value of this column indicates whether the version spans more than one page. 0 indicates the version is stored on a single page; 1 signifes the version spans two pages. I must say this is one of those unfortunate metrics that is misleading beyond belief: 0 equals one page. 1 equals two pages. - record_length_first_part_in_bytes length of the frst part of the version record. - record_image_first_part frst varbinary(8000) part of the versioned row. - record_length_second_part_in_bytes length of the second part of the version record. - record_image_second_part second varbinary(8000) part of the versioned row.

Tell about the DMV sys.dm_db_task_space_usage

For tempdb... Provides information about the number of pages allocated and deallocated by each task in a given database. This DMV only applies to the [tempdb] system database. It contains the same [internal_objects_alloc_page_count], [internal_objects_dealloc_page_count], [user_objects_alloc_page_count] and [user_objects_dealloc_page_count] columns, as in the [sys].[dm_db_session_space_usage] DMV

Tell about the column sys.dm_db_index_physical_stats.forwarded_record_count

Forwarded records are possible only in a heap and occur when updates cause a row with variable-length columns to increase in size so that it no longer fts in its original location. If a table has many forwarded records, scanning the table can be very ineffcient. (because they are always resolved immediately as they are encountered)

What are the sys.dm_io_* DMOs for?

IO processes

What are the steps to tuning a query

Identify the query Review the plan using SHOWPLAN or sys.dm_exec_query_stats Review the individual query components to find highest costs. Outline options to improve; JOIN order, WHERE clause, index, etc. Test the options Implement

What are the sys.dm_os_* DMOs for?

Information for the operating system on which SQL Server runs

What does the sys.dm_os_wait_stats DMV do?

It's an aggregate view of all waits that occur when a requested resource is not available, a worker thread is idle typically due to background tasks, or an external event must complete first. Note that there are many wait types unrelated to locks, but you can filter by: WHERE wait_type LIKE 'LCK%' Arguably the most signifcant DMV in the Operating System category is sys.dm_os_ wait_stats. Every time a session has to wait for some reason before the requested work can continue, SQL Server records the length of time waited, and the resource that is being waited on. The sys.dm_os_wait_stats DMV exposes these wait statistics, aggregated across all session IDs, to provide a summary review of where the major waits are on a given instance. This same DMV also exposes performance (PerfMon) counters, which provide specifc resource usage measurements (disk transfer rates, amount of CPU time consumed and so on). By correlating wait statistics with resource measurements, you can quickly locate the most contested resources on your system, and so highlight potential bottlenecks. This DMV gives us a list of all the different types of wait that threads have encountered, the number of times they have waited on a resource to be available, and the amount of time waited. The values provided are running totals, accumulated across all sessions since the server was last restarted or the statistics were manually reset using the DBCC SQLPERF command The following columns are available in the view (times are all in millisecond units): - wait_type the type of wait - waiting_tasks_count the cumulative total number of waits that have occurred for the given wait_type - wait_time_ms total amount of time that tasks have waited on this given wait type; this value includes the time in the signal_wait_time_ms column - max_wait_time_ms the maximum amount of time that a task has been delayed, for a wait of this type - signal_wait_time_ms the total amount of time tasks took to start executing after being signaled; this is time spent on the runnable queue, and is pure CPU wait. There are many reasons why a certain task within SQL Server may need to wait before proceeding, which means there are many possible values for the wait_type column. Some are quite usual, such as the need to wait for a lock to be released before it can access the required resource (e.g. a data page), and these are indicated by the "normal" lock modes such as shared, intent, exclusive, and so on . Other common causes of waits include latches, backups, external operations like extended stored procedure execution, replication, resource semaphores (used for memory access synchronization), and many more. There are too many to cover them all in detail, though most of the wait types are at least listed, if not well documented, in Books Online.

What are the scanning modes used by sys.dm_db_index_physical_stats?

Limited Sampled Detailed

Tell about the advanced DMV sys.dm_os_process_memory

Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations. However, memory allocations might be performed in the SQL Server address space that bypasses internal memory management routines. Values are obtained through calls to the base operating system. They are not manipulated by methods internal to SQL Server, except when it adjusts for locked or large page allocations. This query shows how much physical memory is in use by SQL Server (which is nice, since you cannot believe Task Manager in most cases). It also shows whether you have Locked Pages in Memory enabled (which is true if locked_page_allocations_kb is higher than zero). It also shows whether the SQL Server process has been notified by the operating system that physical or virtual memory is low (at the OS level), meaning that SQL Server should try to trim its working set.

Tell about the DMV sys.dm_exec_session_wait_stats

New to SQL Server 2016, this DMV returns information about all of the waits encountered by threads for each session. Use this view to diagnose performance issues for a specifc session. Provides information about all the waits encountered by threads that executed for each session.

Tell about the DMV sys.dm_exec_query_stats

Provides aggregated performance statistics for cached query plans. The sys.dm_exec_query_stats DMV will return one row per query that is executed within a batch or stored procedure, and provides columns such as total_worker_time (CPU), total_physical_reads, total_logical_reads, which can give you a very useful overview of the system resources that your queries are eating up. contains quite a few columns that are incremented counters, and provide information about how many times a query has been executed and the resources that were used. - sql_handle identifes the batch or procedure to which a query belongs; it is used by sys.dm_exec_sql_text to get the text of the batch - statement_start_offset the starting point of the query within the batch or object to which the query belongs - statement_end_offset the end point of the query that is currently executing, within the batch or object to which the query belongs - plan_generation_num indicates the version of the query plan that has been created after a recompile; used for comparisons because the actual plan may change (or even partially change for a statement-level recompile), even though the SQL stays the same - plan_handle identifes the cached query plan for a batch or stored procedure that has been executed; used by sys.dm_exec_query_plan or sys.dm_exec_text_ query_plan to get the plan of an executed query in XML format - creation_time time the plan was created - last_execution_time last time the execution plan was used to execute a query - execution_count number of times the plan has been used to execute a query - total_worker_time, last_worker_time, min_worker_time, max_ worker_time total, last, min and max amount of time spent in CPU utilization to execute the query, based on this plan - total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads total, last, min and max number of reads to the physical hard disk system - total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes total, last, min and max number of writes to the bu?er cache to be written by lazy writer - total_logical_reads, last_logical_reads, min_logical_reads, max_ logical_reads total, last, min and max number of reads from the SQL Server cache bu?er that never had to go to the physical hard disk system to satisfy the current request; this data was read in previously and was still in cache - total_clr_time, last_clr_time, min_clr_time, max_clr_time total, last, min and max amount of time spent in the CLR processor for the query that uti lized this plan - total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time in ms, the total, last, min and max amounts of time it took to execute the entire query. Note: Time columns are in microseconds.

Tell about the DMV sys.dm_db_session_space_usage

Returns the number of pages allocated and deallocated by each session for the database. This is applicable only to the tempdb database. For tempdb... Provides information about the number of pages allocated and deallocated by each session in a given database. This DMV only applies to the [tempdb] system database.

Tell about the DMV sys.dm_exec_connections

Provides detailed information about the connections made to the database engine. Detailed information includes the authentication used (Kerberos, NTLM or SQL), the network transport protocol (shared memory or TCP), and the client IP address. The [sys].[dm_exec_connections] DMV will have information about each current connection being made to the database engine. It contains the [encrypt_option] column, describing whether encryption is enabled for a particular, connection.

Tell about the DMV sys.dm_exec_sessions

Provides information about all active user connections and internal tasks, including the client (TDS) version, client program name, client login time, login user, current session setting, resource consumption, status, context visit, and a range of metrics. Returns one row per authenticated session on SQL Server. This is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use this to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

What are the new ways to determine if statistics are outdated?

Query the [sys].[dm_db_stats_properties] DMF to determine when statistics were last updated. You will have to use the [sys].[dm_db_incremental_stats_properties] DMF for partitioned tables. Both of these DMFs will return the date and time when the statistics were last updated, the corresponding row count, rows sampled, number of steps in the histogram used by statistics, and the modifcation counter for the leading statistics column

Tell about the DMV sys.dm_exec_text_query_plan

Returns the query plan in text format for a T-SQL batch or a specifc statement within the batch. Returns the Showplan in text format for a T-SQL batch or for a specifc statement within the batch.

Tell about the DMV sys.dm_db_resources_stats

Returns CPU, I/O, and memory consumption information for an Azure SQL Database, one row for every 16 seconds.

Tell about the advanced DMV sys.dm_os_sys_info

Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server. NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_sys_info.

Tell about the DMV sys.dm_io_pending_io_requests

Returns a row for each pending I/O request. Whereas the data in the the sys.dm_io_virtual_file_stats DMF is cumulative, the sys.dm_io_pending_io_requests DMV returns a row for each currently pending I/O request, so it is interesting from the standpoint of seeing what is happening right now, at the file level. This is a useful tool for analyzing disk I/O at a more granular level than is allowed by tools such as Profler, which will summarize I/O activity at the drive level. Each time you query this DMV, it's likely that a different set of processes will be pending, so the returned data will vary each time. However, if you frequently see what seems like a high number of pending I/Os, this is evidence of some level of I/O bottleneck. Obviously, "high" is a matter of interpretation, but more than two or three could indicate an issue. This DMV returns several columns that are occasionally useful, including: - io_completion_request_address address of the I/O request - io_type type of I/O, for example 'disk' - scheduler_address relates to sys.dm_os_schedulers to get information about the scheduler coordinating this I/O request - io_handle handle for the file that is the target of the I/O request; relates to sys.dm_id_virtual_file_stats.file_handle to get the file information. However, the most interesting columns returned by this view are: - io_pending an integer value that indicates whether an I/O request is pending (returns 1) or has been completed by Windows but not yet processed by SQL Server, since it has not yet performed the context switch (returns 0); we know that the item is pending for the SQL Server storage engine, as this is the purpose of the DMV - io_pending_ms_ticks represents the total time individual I/Os are waiting in the pending queue; Books Online lists this as internal use only, but it is useful for comparing the magnitude of the waits on each file.

Tell about the DMV sys.dm_exec_cached_plans

Returns a row for each query plan that is cached by SQL Server. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans. Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Tell about the advanced DMV sys.dm_os_performance_counters

Returns a row per performance counter maintained by the server. For information about each performance counter, see Use SQL Server Objects. If the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled. If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions." This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling.

Tell about the advanced DMV sys.dm_os_memory_cache_counters

Returns a snapshot of the health of a cache in SQL Server; provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.

Tell about the advanced DMV sys.dm_tran_top_version_generators

Returns a virtual table for the objects that are producing the most versions in the version store. sys.dm_tran_top_version_generators returns the top 256 aggregated record lengths that are grouped by the database_id and rowset_id. sys.dm_tran_top_version_generators retrieves data by querying the dm_tran_version_store virtual table. sys.dm_tran_top_version_generators is an inefficient view to run because this view queries the version store, and the version store can be very large. We recommend that you use this function to find the largest consumers of the version store.

Tell about the advanced DMV sys.dm_tran_current_snapshot

Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts. If the current transaction is not a snapshot transaction, this function returns no rows. sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that sys.dm_tran_current_snapshot returns only the active transactions for the current snapshot transaction.

Tell about the DMV sys.dm_exec_procedure_stats

Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.

Tell about the DMV sys.dm_tran_session_transactions

Returns correlation information for sessions and their associated transactions. It differentiates between local and distributed transactions. Shows related information for transactions and their associated sessions.

Tell about the DMV sys.dm_db_index_usage_stats

Returns counts of the different types of index operations and the time each type of operation was last performed. This obtains statistics on how our indexes have been used to resolve queries. This DMV provides, in particular, the following columns for each index: - database_id ID of the database - object_id identifes the table or view to which the index belongs, unique only at the database level - index_id index ID, unique only to the scope of the object_id; an index_id of 0 signifes a heap (no clustered index on the table); an index_id value of 1 is always associated with the clustered index on the table, whereas index_id values greater than 1 are reserved for non-clustered indexes - user_seeks the number of times the index has been used in a seek operation (to fnd a specifc row) - user_scans number of times the index has been used by scanning the leaf pages of the index for data - user_lookups for clustered indexes only, this is the number of times the index has been used in a "bookmark lookup" to fetch the full row; this is because non-clustered indexes use the clustered indexes key as the pointer to the base row - user_updates number of times the index has been modifed due to a change in the table's data. For each of the user actions (user_* columns), there is also a corresponding last_ user_* column (e.g. last_user_seek), which records the date and time the action last occurred. Also, there is a system_* and last_system_* equivalent for each of these columns, providing statistics regarding use of the index by a system operation. Data in this DMV is cumulative, and is refreshed when the server is restarted or when the index is dropped and recreated. Statistics live on when an index is rebuilt or reorganized, and even when it is disabled and rebuilt. Queries against this DMV return all indexes (including heaps and the clustered index) that have been read or written to at least once. If an index exists but has never been used since creation, or since the statistics were refreshed, then there will be no entry for this index row in sys.dm_db_index_usage_ stats. It's an instance-wide DMV and so will return indexes for every database on the instance, but you will almost always want to limit it per database, using the database_ id to retrieve the index names for that database, via sys.indexes (as shown in Listing 5.1). Note also that the DMV does not distinguish between partitions, so if an index is physically manifested in two or more partitions, the DMV only returns a single record.

Tell about the advanced DMV sys.dm_exec_query_optimizer_info

Returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements. For example, you can use the total number of optimizations, the elapsed time value, and the final cost value to compare the query optimizations of the current workload and any changes observed during the tuning process. Some counters provide data that is relevant only for SQL Server internal diagnostic use. These counters are marked as "Internal only." Column Description counter Name of optimizer statistics event. occurrence Number of occurrences of optimization event for this counter. value Average property value per event occurrence. pdw_node_id Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse The identifier for the node that this distribution is on.

Tell about the advanced DMV sys.dm_os_memory_cache_entries

Returns information about all entries in caches in SQL Server. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache entries.

Tell about the advanced DMV sys.dm_os_latch_stats

Returns information about all latch waits organized by class. This can be used to identify the source of latch contention by examining the relative wait numbers and wait times for the different latch classes. In some situations, you may be able to resolve or reduce latch contention. However, there might be situations that will require that you to contact Microsoft Customer Support Services.

Tell about the advanced DMV sys.dm_exec_query_memory_grants

Returns information about all queries that have requested and are waiting for a memory grant or have been given a memory grant. Queries that do not require a memory grant will not appear in this view. For example, sort and hash join operations have memory grants for query execution, while queries without an ORDER BY clause will not have a memory grant. In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesnt belong to the connected tenant is filtered out. In addition, the values in the columns scheduler_id, wait_order, pool_id, group_id are filtered; the column value is set to NULL.

Tell about the advanced advanced DMV sys.dm_os_buffer_descriptors

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. In SQL Server 2017, this dynamic management view also returns information about the data pages in the buffer pool extension file. For more information, see Buffer Pool Extension. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database. NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_buffer_descriptors.

Tell about the DMV sys.dm_exec_session_wait_status

Returns information about all the waits by threads that executed for each session. This view returns the same information that is aggregated in the sys.dm_os_wait_status but for the current session and includes the session_id.

Tell about the DMV sys.dm_db_wait_stats

Returns information about all the waits encountered. In Azure SQL Database DMV that returns information about all the waits encountered by threads during execution at the database level. This DMV shows the waits that have completed and does not show current waits. Returns information about all the waits encountered by threads that executed during operation. You can use this aggregated view to diagnose performance issues with Azure SQL Database and also with specific queries and batches. Specific types of wait times during query execution can indicate bottlenecks or stall points within the query. Similarly, high wait times, or wait counts server wide can indicate bottlenecks or hot spots in interaction query interactions within the server instance. For example, lock waits indicate data contention by queries; page IO latch waits indicate slow IO response times; page latch update waits indicate incorrect file layout. Column name Description wait_type Name of the wait type. For more information... the type of wait being encountered, the current list of wait types is available from Microsoft waiting_tasks_count Number of waits on this wait type, the cumulative number of tasks that have registered this wait since the last time SQL Server services were restarted. wait_time_ms Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms. the cumulative amount of wait time registered for all tasks encountering this type of wait since the SQL Server services were restarted. max_wait_time_ms the maximum wait time for any of the tasks encountering this wait since last SQL Server service restart. signal_wait_time_ms Difference between the time that the waiting thread was signaled and when it started running. the amount of time, in milliseconds, any request waited after being signaled that the resource was freed, until a worker was assigned to handle the request. A high signal wait time is indicative of CPU issues in that the thread still had to wait for CPU assignment even after the resource being waited on was freed up. The information is cumulative for this DMV. This information is held in cache. It will be lost/reset upon services restart. Remarks This dynamic management view displays data only for the current database. This dynamic management view shows the time for waits that have completed. It does not show current waits. Counters are reset to zero any time the database is moved or taken offline. A SQL Server worker thread is not considered to be waiting if any of the following is true: A resource becomes available. A queue is nonempty. An external process finishes. These statistics are not persisted across SQL Database failover events, and all data are cumulative since the last time the statistics were reset.

Tell about the DMV sys.dm_db_operation_status

Returns information about operations performed on the database... in Azure SQL Data Warehouse This view is only available in the master database to the server-level principal login. To use this view, you must be connected to the master database. Use the sys.dm_operation_status view in the master database of the SQL Database server to track the status of the following operations performed on a SQL Database: Create database Copy database. Database Copy creates a record in this view on both the source and target servers. Alter database Change the performance level of a service tier Change the service tier of a database, such as changing from Basic to Standard. Setting up a Geo-Replication relationship Terminating a Geo-Replication relationship Restore database Delete database

Tell about the DMV sys.dm_db_file_space_usage

Returns information about the space used for each file in the database. gives information on how tempdb is being used. columns: - database_id surrogate key of the database (relates to sys.databases) - file_id the file identifer (relates to sys.database_files) - unallocated_extent_page_count extents that are reserved in the file but not currently allocated to objects - version_store_reserved_page_count number of pages that are reserved to support snapshot isolation transactions - user_object_reserved_page_count number of pages reserved to user tables - internal_object_reserved_page_count number of pages reserved to internal objects, such as work tables, that SQL Server creates to hold intermediate results, such as for sorting data - mixed_extent_page_count number of extents that have pages of multiple types user objects, version store, or internal objects, Index Allocation Map (IAM) pages, etc.

Tell about the advanced DMV sys.dm_os_sys_memory

Returns memory information from the operating system. SQL Server is bounded by, and responds to, external memory conditions at the operating system level and the physical limits of the underlying hardware. Determining the overall system state is an important part of evaluating SQL Server memory usage.

Tell about the DMV sys.dm_exec_plan_attributes

Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan. provides information about various attributes of a query plan, identifed by a plan_handle, such as the number of queries currently using a given execution plan. It returns one row for each attribute.

Tell about the DMV sys.dm_db_partition_stats

Returns page and row count information for each partition in the selected database. returns disk space oriented statistics (row count, page counts, and so on) for each object in a partition Displays information about the space used to store and manage in-row data LOB data, and row-overflow data for all partitions in a database. One row is displayed per partition. The counts on which the output is based are cached in memory or stored on disk in various system tables. In-row data, LOB data, and row-overflow data represent the three allocation units that make up a partition. The sys.allocation_units catalog view can be queried for metadata about each allocation unit in the database. If a heap or index is not partitioned, it is made up of one partition (with partition number = 1); therefore, only one row is returned for that heap or index. The sys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database. The total count for an individual table or an index can be obtained by adding the counts for all relevant partitions. The view returns the statistical columns below. - in_row_data_page_count number of pages being used for in-row object data in the partition. For an index, this is the number of leaf pages; for a clustered table or heap, it is the number of data pages. - in_row_used_page_count all pages in use for the object, including non-leaf index and index allocation map pages. - in_row_reserved_page_count all pages in use plus any pages reserved for the object, even if the pages are not currently in use. For example, when a table grows, it grows in 8 x 8 K page extents, so all these pages could be allocated to the table, even though only a single page is used. - lob_used_page_count count of pages in use for the object to store out-of-row values such as varchar(max), varbinary(max), and so on. - lob_reserved_page_count count of out-of-row pages including any that are reserved but not in use. - row_overflow_used_page_count count of pages that are in use for storing overow data, for rows that are larger than will ft on a single ~8 K page. - row_overflow_reserved_page_count count of overow pages that includes any pages that are reserved but not in use. - used_page_count total number of pages in use in the partition for any reason. - reserved_page_count total number of pages in use or reserved in the partition for any reason. - row_count number of rows in the object (or partition of an object).

Tell about the DMV sys.dm_db_index_physical_stats

Returns size and fragmentation information about the data and indexes of the specifed table or view. Provides invaluable information regarding the state of your index partitions, including type of index, index depth, and degree of fragmentation. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. These physical stats all serve to tell you of the condition of an index. Even the most potentially benefcial index in terms of optimizing query execution can become useless if it is not maintained properly. Data stored in this DMF is cumulative but refreshed when the server is restarted or when the index is dropped and recreated. Statistics live on when the index is rebuilt or reorganized, and even when it is disabled and rebuilt. The physical_stats DMF accepts the database_id, object_id, index_id, and partition_number as parameters, in order to identify the object (heap, index or partition) in question, and returns detailed "physical stats" for each partition. All the parameters can be NULL or DEFAULT if you want to return all rows, in which case the DMF will return a row for every partition in every database.

Tell about the DMV sys.dm_exec_text_query_plan

Returns the Showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences: The output of the query plan is returned in text format. The output of the query plan is not limited in size. Individual statements within the batch can be specified.

Tell about the advanced DMV sys.dm_os_memory_clerks

Returns the set of all memory clerks that are currently active in the instance of SQL Server. The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects. These objects are generally used to allocate memory in an instance of SQL Server. Memory nodes provide the interface and the implementation for low-level allocators. Inside SQL Server, only memory clerks have access to memory nodes. Memory clerks access memory node interfaces to allocate memory. Memory nodes also track the memory allocated by using the clerk for diagnostics. Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.

Tell about the advanced DMV sys.dm_os_memory_cache_clock_hands

Returns the status of each hand for a specific cache clock. SQL Server stores information in memory in a structure called a memory cache. The information in the cache can be data, index entries, compiled procedure plans, and a variety of other types of SQL Server information. To avoid re-creating the information, it is retained the memory cache as long as possible and is ordinarily removed from the cache when it is too old to be useful, or when the memory space is needed for new information. The process that removes old information is called a memory sweep. The memory sweep is a frequent activity, but is not continuous. A clock algorithm controls the sweep of the memory cache. Each clock can control several memory sweeps, which are called hands. The memory-cache clock hand is the current location of one of the hands of a memory sweep.

Tell about the DMV sys.dm_exec_sql_text

Returns the text of the SQL batch that is identifed by the specifed SQL_handle. Two columns that are of interest: - text the text of the query, unless the object is encrypted, in which case it will be NULL. - encrypted 1 if plan is encrypted, which will prevent viewing of the query text, 0 otherwise

Give an example sql query to show queries that are blocked using sys.dm_tran_locks

SELECT request_session_id as s_id, resource_type, resource_associated_entity_id, request_status, request_mode FROM sys.dm_tran_locks WHERE resource_database_id = db_id('_DatabaseName_') AND request_status = 'WAIT'

What is a query to show current blocking sessions on a server?

SELECT tl.resource_type AS res_typ, tl.resource_database_id AS res_dbid, tl.resource_associated_entity_id AS res_entid, tl.request_mode AS mode, tl.request_session_id AS s_id, wt.blocking_session_id AS blocking_s_id FROM sys.dm_tran_locks as tl INNER JOIN sys.dm_os_waiting_tasks as wt ON tl.lock_owner_address = wt.resource_address;

Give a sample query to identify underutilized indexes

SELECT DB_NAME(s.database_id) AS datase_name, OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name, i.index_id, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update FROM sys.dm_db_index_usage_stats AS s JOIN sys.indexes AS i ON s.index_id = i.index_id AND s.object_id = i.object_id WHERE s.database_id = DB_ID() --AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups)*5 GO also: SELECT DB_NAME(s.database_id) AS datase_name, OBJECT_NAME(s.object_id) AS table_ name, i.name AS index_name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update FROM sys.dm_db_index_usage_stats AS s JOIN sys.indexes AS i ON s.index_id = i.index_id AND s.object_id = i.object_id WHERE s.database_id = DB_ID() AND s.user_updates > (s.user_seeks + s.user_scans + s.user_lookups) AND s.index_id > 1;

Give a sample query to show missing indexes

SELECT g.*, statement AS table_name, column_id, column_name, column_usage FROM sys.dm_db_missing_index_details AS d CROSS APPLY sys.dm_db_missing_index_columns (d.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS g ON g.index_handle = d.index_handle ORDER BY g.index_group_handle, g.index_handle, column_id; GO

Give a query to show what Queries allocated the most space in tempdb

SELECT r.session_id, r.request_id, t.text AS query, u.allocated AS task_internal_object_page_allocation_count, u.deallocated AS task_internal_object_page_deallocation_count FROM ( SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS allocated, SUM (internal_objects_dealloc_page_count) AS deallocated FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id) AS u JOIN sys.dm_exec_requests AS r ON u.session_id = r.session_id AND u.request_id = r.request_id CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) as t ORDER BY u.allocated DESC;

Show sql that shows possible blocking culprits

SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );

What is an example query to help determine if statistics are out of date?

SELECT s.name AS statistic_name, s.auto_created, s.user_created, s.no_recompute, s.is_incremental, s.is_temporary, s.has_filter, p.last_updated, DATEDIFF(day,p.last_updated, SYSDATETIME()) AS days_past, h.name AS schema_name, o.name AS table_name, c.name AS column_name, p.rows, p.rows_sampled, p.steps, p.modification_counter FROM sys.stats AS s JOIN sys.stats_columns i ON s.stats_id = i.stats_id AND s.object_id = i.object_id JOIN sys.columns c ON c.object_id = i.object_id AND c.column_id = i.column_id JOIN sys.objects o ON s.object_id = o.object_id JOIN sys.schemas h ON o.schema_id = h.schema_id OUTER APPLY sys.dm_db_stats_properties (s.object_id,s.stats_id) AS p WHERE OBJECTPROPERTY(o.object_id, NIsMSShipped) = 0 ORDER BY days_past DESC;

Give an example sql query to show queries that are blocked using sys.dm_exec_requests

SELECT session_id, blocking_session_id, open_transaction_count, wait_time, wait_type, last_wait_type, wait_resource, transaction_isolation_level, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; GO

Give an example sql query to show queries that are blocked using sys.dm_os_waiting_tasks

SELECT session_id, blocking_session_id, wait_duration_ms, wait_type, resource_description FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL

Tell about the DMV sys.dm_io_virtual_file_stats

Shows I/O statistics for the data and log files. provides statistics on how all database files have been utilized; both data and log files are represented; excellent resource for discovering hotspots, and identifying opportunities to spread I/O over different channels For each database file that SQL Server uses, including not only the data files but also the log and full text files, the sys.dm_io_virtual_file_stats DMF gives cumulative physical I/O statistics, indicating how frequently the file has been used by the database for reads and writes since the server was last rebooted. It also provides a very useful metric in the form of the "I/O stall" time, which indicates the total amount of time that user processes have waited for I/O to be completed on the file in question. Note that this DMF measures physical I/O only. Logical I/O operations that read from cached data will not show up here. - database_id, file_id same as the parameter descriptions - sample_ms the number of milliseconds that have passed since the values for sys.dm_io_virtual_file_stats were reset (the only way to reset the values is to restart the server) - num_of_reads number of individual read operations that were issued to the file; note that this is physical reads, not logical reads; logical reads would not be registered - num_of_bytes_read the number of bytes that were read, as opposed to the number of reads; the size of a read is not a constant value that can be calculated by the number of reads - io_stall_read_ms total time user processes waited for I/O; note that this number can be much greater than the sample_ms; if ten processes are trying to use the file simultaneously, but the disk is only able to serve one of them, then you might get nine seconds waiting over a ten-second time period - num_of_writes, num_of_bytes_written, io_stall_write_ms the same as the read values, but for writes - io_stall sum of io_stall_write_ms and io_stall_read_ms - size_on_disk_bytes the size of the file in bytes - file_handle the Windows file handle of the file.

What does the dmv sys.dm_db_column_store_row_group_physical_stats do?

Shows clustered columnstore index rowgroup-level information. Provides current rowgroup-level information about all of the columnstore indexes in the current database. Returns one row for each rowgroup in the current database.

What does the system view sys.dm_db_missing_index_details show?

Shows detailed information about missing indexes.

Tell about the DMV sys.dm_tran_locks

Shows information about currently active lock manager resources. This DMV sprovides insight into the current state of locking in a SQL Server instance, across all databases. It returns a row for every currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns provided offer information regarding both the resource on which the lock is being held (or has been requested), and the owner of the request. sys.dm_tran_locks provides only a snapshot of the state of the server at the point in time the query is executed. The resource-related columns identify the resource being locked, its type, and the database on which the locking is occurring. Columns: - resource_type target resource object for the lock, such as Table (OBJECT locks), Page (PAGE locks), Row (RID locks), or Key (KEY locks). Discussed in more detail shortly - resource_database_id ID of the database on which the locked resource resides. This column can be used to join to the dbid column in sys.sysdatabases, as well as to several other system views and DMVs. - resource_associated_entity_id depending upon the resource_type, this value is either: -the object_id of the target object, if resource_type = OBJECT -the object_id of the parent object (courtesy of sys.partitions), if the resource_type is a KEY, PAGE, or RID. -Returning the name of the object is possible via the OBJECT_ID() system function, by passing in both the object_id and the database_id (respectively). The remaining columns oer a means to determine the nature, status, and owner of the lock request, and to relate this locking information to that available from other DMVs, regarding the sessions or transactions with which the locks are associated. - request_mode type of lock that is being held, or has been requested, such as Shared (S), Update (U), Exclusive (X), Intent Exclusive (IX), and so on. Discussed in more detail shortly. - request_status status of the lock: GRANT indicates the lock has been taken CONVERT the request is in the process of being fulflled WAIT the resource is not locked, but is trying to lock the resource. - request_owner_type type of owner of the transaction: TRANSACTION CURSOR SESSION SHARED_TRANSACTION_WORKSPACE EXCLUSIVE_TRANSACTION_WORKSPACE. - request_session_id the session_id of the requestor. Exposing this column allows the DBA to join back to the information provided in any of the sys.dm_ exec_* DMVs as well as sys.sysprocesses (via a join to its spid column). - request_owner_id this column is only valid when the request_owner_type is TRANSACTION. In that case, the value is the transaction_id for the associated transaction. One fnal column that is very useful is the lock_owner_address, which is a binary address used internally to track the lock request. It is not interesting to the DBA in its own right, but in the fact that it can be used to join to the resource_address column in the sys.dm_os_waiting_tasks DMV, to relate locking information to tasks that are waiting for a resource to become available before proceeding (i.e. which are blocked). We'll discuss this in much more detail in the section entitled Investigating Blocking.

What does the system view sys.dm_db_missing_index_group_stats show?

Shows information about groups of missing indexes. This provides all the detail statistics regarding the size of the beneft that SQL Server would expect from the missing index, including how often it would have been used in scans and seeks, and how many compiled plans could use it. Its identifer column is group_handle, which joins to index_group_handle in sys. dm_db_missing_index_groups. As noted, for the time being there is only one index per group. It also returns the following statistical columns: - unique_compiles the number of plans that have been compiled that might have used the index - user_seeks the number of seek operations in user queries that might have used the index - user_scans the number of scan operations in user queries that might have used the index - last_user_seek the last time that a seek operation might have used the index - last_user_scan the last time that a scan operation might have used the index - avg_total_user_cost average cost saving for the queries that could have been helped by the index in the group - avg_user_impact the estimated percentage by which the average query cost would drop, for the queries that could use this index. For each of the user statistics columns there are equivalent system columns, which record when the index is used for a system operation, such as automatic statistics operations. The last_user_* columns are vital in helping you assess whether you really do need to add the suggested missing index. If this time isn't fairly recent, then it's likely that the query it would have helped was ad hoc, and not part of your normal workload, and so the beneft of creating the index is likely to be marginal, or even detrimental, if the table in question is updated frequently

Tell about the DMV sys.dm_tran_active_transactions

Shows information about instance-level transactions. stores transactional information relating to status, type, and state of each transaction currently initiated but not yet completed, on the SQL instance; it also provides information on distributed transactions, though some of the DTC-centric columns have been deprecated in SQL Server 2008 returns a list of all transactions that are active at the time the query is executed. This view operates at the scope of the entire SQL instance, meaning that results are returned for all databases on the instance. Since this view provides a point-in-time snapshot of currently active transactions, the results will change each time the query is executed, depending upon the state of the individual transactions. Identifcation columns returned by this view are transaction_id, which uniquely identifes a transaction across the entire SQL Server instance, and name, which is the name of the transaction

What does the system view sys.dm_db_missing_index_columns show?

Shows information about table columns that are missing an index.

Tell about the DMV sys.dm_db_missing_index_columns

Shows information about table columns that are missing an index. a DMF that accepts an index_handle parameter and returns a table providing details of columns that would comprise the suggested missing index This DMF accepts the index_handle as a parameter (retrieved from sys.dm_db_missing_index_details or sys.dm_db_ missing_index_group) and returns a table containing a record for each of the individual columns that would make up the identifed index. This makes it easier for tools to use the columns to build a CREATE INDEX statement. It returns only three columns, column_id, column_name and column_usage, the latter taking the selfexplanatory values of either EQUALITY, INEQUALITY or INCLUDE. There may be duplicate column_name values for the case where a column would be useful for both an equality and an inequality predicate. It's important to note that the columns in this list are not ordered in a way that reects the optimal column ordering for the index key, so you may have to do some additional tweaking to get the best result.

What does the system view sys.dm_db_missing_index_groups show?

Shows information about what missing indexes that are contained in a specific missing index group.

What does the dmv sys.dm_db_index_operation_stats do?

Shows low-level I/O, locking, latching, and access activity for each partition of a table in index.

Tell about the advanced DMV sys.dm_os_memory_cache_hash_tables

Shows one row for each active cache in SQL Server.

Tell about the Detailed scanning mode for the [sys].[dm_db_index_physical_stats] dmv

The DETAILED mode scans all pages and returns all statistics. It effectively calculates the fragmentation through a LIMITED mode scan and also calculates all remaining statistics by having to read every page at every level of the index. It is the most expensive and time consuming scanning mode.

Tell about the Limited scanning mode for [sys].[dm_db_index_physical_stats]

The LIMITED mode scans the level above the leaf level of an index and is consequently fast. It can, however, only return the logical fragmentation and the page count because it never scanned the leaf-level pages of the index. For a heap the page free space (PFS) and index allocation map (IAM) pages are examined, and the data pages of a heap scanned. This is the default scan mode.

Tell about the Sampled scanning mode for the [sys].[dm_db_index_physical_stats] dmv

The SAMPLED mode works differently, depending on the size of the table. A LIMITED mode scan is initially performed. If the leaf-level contains less than 100,000 pages, all leaf-level pages are scanned to calculate additional fragmentation. For the larger tables only 1% of the table is scanned by reading every 100th page.

What are ways to identify sessions that cause blocking?

Using Activity Monitor Querying any of the following DMVs: [sys].[dm_exec_requests] [sys].[dm_os_waiting_tasks] [sys].[dm_tran_locks] Executing the [sp_who], or the undocumented [sp_who2] system stored procedures. Using the blocked processed report. Confguring and consuming the blocked process threshold reports in the database engine.

Tell about the advanced DMV sys.dm_os_ring_buffers

The following SQL Server Operating Systemrelated dynamic management views are Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. You can use the undocumented Ring Buffer DMV to troubleshoot the below issues. Security Exceptions Exception raised at SQL Operating System level Connection Dropped By the Server System Resource Utilization Memory Pressure CLR Integration Scheduler State Extended Events Subsystems State

Tell about the column sys.dm_db_index_physical_stats.avg_fragmentation_in_percent

The percent of fragmentation, or out-of-order pages/extents. Ideally this value should be as close as possible to 0. This value is key to understanding what remediation action you should perform for a fragmented index.

What DMV would allow you to display the actual SQL text?

The sys.dm_exec_sql_text function

What does the sys.dm_tran_locks DMV do?

The sys.dm_tran_locks DMV provides you with information about existing locks and locks that have been requested but not yet granted in addition to details about the resource for which the lock is requested. You can use this DMV only to view information at the current point in time. It does not provide access to historical information about locks.

What does STATS in the BACKUP DATABASE command mean?

This controls at what percentage intervals the database engine should display a message indicating the progress of the backup operation. The default value is 10, which means you are notified whenever another 10 percent of the backup operation completes. It also shows up in the dmv sys.dm_exec_requests.percent_complete column

The Sampled scanning mode for the [sys].[dm_db_index_physical_stats] dmv performs a Limited scan first True or False

True

Tell about the DMV sys.dm_os_waiting_tasks

Wait statistics for currently executing requests that are experiencing resource waits. This DMV returns one row for each task that is waiting for a resource to become available before proceeding (i.e. is blocked). Armed with a session_ id for any tasks that are currently waiting, we can use the resource_address column in this DMV to join back to the lock_owner_address column in the sys.dm_tran_ locks DMV, and so relate the waiting tasks information to information on the locks that are being held. Initially, it might seem more logical to join on session_id rather than resource_address, but remember that the goal is to determine what resource contention is occurring; what resource is locked and therefore causing waits for other sessions that need to acquire locks on the object in contention. From here, we can join to other session/transaction-related views in order to arrive, ultimately, at a big picture overview of locks that may be causing blocking problems, the sessions and statements that caused those locks to be acquired and those that are blocked as a result. some important columns: waiting_task_address session_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id

What are some columns in the sys.dm_db_index_physical_stats dmv that you should pay attention to for fragmentation?

avg_fragmentation_in_percent avg_page_space_used_in_percent avg_fragment_size_in_pages forwarded_record_count fragment_count page_count

You have a big database, before taking the full backup you want to estimate the total time for backup. How would you do that?

You can check the backup history to see how long other backups take and then interpolate the time to your database size: SELECT TOP 100 backup_size, backup_start_date, backup_finish_date FROM msdb.dbo.backupset WHERE type = 'D' ORDER BY backup_set_id --- While a backup is in progress, you can execute the below query to check the progress. This will display the total elapsed time and estimated completion time returns milliseconds: SELECT command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / 60000.0 FROM sys.dm_exec_requests WHERE command like 'BACKUP%' --- It's not really possible to tell you how long it will take to backup a database of size X. It depends on a lot of factors, primarily the device you're backing up to. If your backing up to SSD, it's going to be massively quicker than to a USB 2.0 attached drive. It also depends on things like: Are you backing up locally or to a network share, etc? Do you use backup compression? If so, do your databases have any features enabled that would affect how effective compression is, e.g. TDE? How many volumes are your databases split over? How many files are you striping your backups over? What buffercount, maxtransfersize's are being used? Of that 1TB, how much of it is actual data? so on and so forth... I've got a 1TB database that backs up to disk in 9 minutes.

What is the DMV that is Returns information about all the waits by threads that executed for each session. This view returns the same information that is aggregat ed in the sys.dm_os_wait_status but for the current session and includes the session_id.

sys.dm_exec_session_wait_status

Suppose you issued a BACKUP DATABASE command with the STATS option, but you can't see the progresss.. How can you view the progress of the backup being performed?

You can use TSQL with: SELECT session_id, db_name(database_id) as database_name, start_time, command, percent_complete, estimated_completion_time FROM sys.dm_exec_requests WHERE command LIKE 'backup %';

How would you find blocked processes in SQL Server?

You can use: sp_who2 System Stored Procedure sys.dm_exec_requests DMV Sys.dm_os_waiting_tasks SQL Server Management Studio Activity Monitor SQL Server Management Studio Reports SQL Server Profiler

What stored proc can you use to determine the current state of encryption for all of the databases?

[sys].[dm_database_encryption_keys]

What are some important columns in sys.dm_exec_requests?

blocking_session_id command dop last_wait_type percent_complete status transaction_isolation_level wait_time wait_type

What are some important columns in sys.dm_os_waiting_tasks?

blocking_session_id resource_id wait_type wait_duration_ms

How can you find the text of the query executed by using DMOs? a. This info is provided in the sys.dm_exec_query_stats dynamic management view. B. By querying the sys.dm_exec_sql_text dynamic management function. c. The sys.dm_exec_query_plan dynamic management function returns the query text. D. You cannot find the query text through DMOs.

correct answer: b a. incorrect: There is no query text provided from the sys.dm_exec_query_stats DMO. B. correct: You can get the query text by querying the sys.dm_exec_sql_text dynamic management function. c. incorrect: There is no query text provided from the sys.dm_exec_query_plan DMO. D. incorrect: You can get the query text by querying the sys.dm_exec_sql_text dynamic management function.

Which DMO gives you information about index usage? a. sys.dm_exec_query_stats B. sys.dm_exec_query_text c. sys.dm_db_index_usage_stats D. sys.indexes

correct answer: c a. incorrect: The sys.dm_exec_query_stats DMO gives you statistics about queries, not indexes. B. incorrect: The sys.dm_exec_query_text DMO gives you the text of the batches and queries. c. correct: The sys.dm_db_index_usage_stats DMO gives you information about index usage. D. incorrect: sys.indexes is a catalog view, not a DMO.

how would you display active transactions?

select * from sys.dm_tran_active_transaction

How would you find transactions that have locks in SQL Server?

select * from sys.dm_tran_locks Old school : sp_lock << but it is now deprecated SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID()

How do you view what Audits have been setup?

select from these tables: sys.dm_audit_actions - Stores information on each audit action and each audit action group that you can use with SQL Server Audit sys.server_audits - Enables you to see information about each SQL Server audit con fgured on an instance sys.dm_server_audit_status - Enables you to see information about currently confg ured audits sys.server_audit_specifcations - Enables you to see information about server audits sys.server_audit_specifcations_details - Enables you to see information about actions that are audited at the server level sys.database_audit_specifcations - Enables you to see information about currently confgured database audit specifcations sys.database_audit_specifcations_details - Enables you to see information about actions that are audited at the database level fn_get_audit_fle - Enables you to query a file-based target for audit information SELECT audit_id, type_desc, on_failure_desc, queue_delay, is_state_enabled FROM sys.server_audits

What is the common column just to join all these 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

session_id

What are the DMO categories?

sys.dm_db_* sys.dm_io_* sys.dm_exec_* sys.dm_tran_* sys.dm_os_* DIETO

What is the dmv that Shows clustered columnstore index rowgroup-level information. ?

sys.dm_db_column_store_row_group_physical_stats

What is the DMV that Returns information about the space used for each file in the database.

sys.dm_db_file_space_usage

What is the Dynamic management function to check some basic information about the index?

sys.dm_db_index_ physical_stats

What is the dmv that Shows low-level I/O, locking, latching, and access activity for each partition of a table in index. ?

sys.dm_db_index_operation_stats

DMV to find how many pages are allocated for an object

sys.dm_db_index_physical_stats

What DMV should you query to determine the degree of fragmentation in a table or index?

sys.dm_db_index_physical_stats

What is the DMV that Returns size and fragmentation information about the data and indexes of the specifed table or view.

sys.dm_db_index_physical_stats

What is the dmv that Shows data and index size and fragmentation. ?

sys.dm_db_index_physical_stats

sys.dm_db_index_physical_stats is used to determine fragmentation of an index.. what DMV is used to determine fragmentation of a table?

sys.dm_db_index_physical_stats ! The same dmv sys.dm_db_index_physical_stats ( { database_id | NULL | 0 | DEFAULT }, { object_id | NULL | 0 | DEFAULT }, { index_id | NULL | 0 | -1 | DEFAULT }, { partition_number | NULL | 0 | DEFAULT }, { mode | NULL | DEFAULT } )

What is the DMV that Returns counts of the different types of index operations and the time each type of operation was last performed.

sys.dm_db_index_usage_stats

What is the dmv that Shows different types of index operations and the time each type was last performed. ?

sys.dm_db_index_usage_stats

What is the DMV that Shows information about table columns that are missing an index.

sys.dm_db_missing_index_columns

What is the dmv that Shows information about table columns that are missing an index?

sys.dm_db_missing_index_columns

What DMOs should you use to find 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

What is the DMV that Displays detailed information about missing indexes.

sys.dm_db_missing_index_details

What is the dmv that Shows detailed information about missing indexes?

sys.dm_db_missing_index_details

What is the dmv that Shows information about groups of missing indexes?

sys.dm_db_missing_index_group_stats

What is the dmv that Shows information about what missing indexes that are contained in a specific missing index group?

sys.dm_db_missing_index_groups

What is the DMV that Returns information about operations performed on the database.

sys.dm_db_operation_stats