Important System Tables

Question 1

What is the system table that you can use to see how much an index is being used?

Answer 1

SYS.DM_DB_INDEX_USAGE_STATS

Question 2

What is the system table that has Index statistics?

Answer 2

SYS.DM_DB_INDEX_PHYSICAL_STATS

PARAMETERS:
database_id (OR NULL)
, object_id (OR NULL)
, index_id (OR NULL)
, partition_number (OR NULL)
, mode (OR NULL)

Question 3

What is the System DM view that shows system locking?

Answer 3

sys.dm_tran_locks

Question 4

What is the system DM that shows what queries a user is running and the wait_type that is occuring?

Answer 4

sys.dm_exec_requests

Question 5

What is the system DM view that shows CPU usage?

Answer 5

sys.dm_os_schedulers

Question 6

What is the system DM view that shows the Buffer Pool / data cache?

Answer 6

sys.dm_os_buffer_descriptors

Question 7

What is the DM view you would use for Memory Issues?

Answer 7

sys.sysperfinfo

Question 8

What is the system view that gives you current permissions for any given user?

Answer 8

sys.server_permissions

Question 9

What is the dynamic management view to see database-level memory consumers?

Answer 9

sys.dm_xtp_system_memory_consumers

Question 10

Which dynamic management view shows you SQL Operating System threads under the current SQL Server Process?

Answer 10

sys.dm_os_threads

Question 11

Which dynamic management view shows information about the environment (IE CPI Count, physical memory and virtual memory)?

Answer 11

sys.dm_os_sys_info

Question 12

Which view shows information about the full-text and sematic indexes in each table?

Answer 12

sys.dm_db_fts_index_physical_stats

Question 13

Which view shows edition-specific features that are enabled in the current database but are not supported on all SQL server versions?

Answer 13

sys.dm_db_persisted_sku_features

Question 14

What system view shows query statistics?

Answer 14

sys.dm_exec_query_stats

Question 15

What system view shows query plans for executing queries?

Answer 15

sys.dm_exec_query_plan

Question 16

What dynamic management view shows information about actual wait statistics in connection to a particular session?

Answer 16

sys.dm_exec_session_wait_stats

Question 17

What dynamic management view shows the impact that an index group would have if it was created?

Answer 17

sys.dm_db_missing_index_group_stats

Question 18

What dynamic management view shows column information about missing indexes but does not return statistics?

Answer 18

sys.dm_db_missing_index_columns

Question 19

What dynamic management view shows information about missing indexes such as column information and the statement used to create the missing index?

Answer 19

sys.dm_db_missing_index_details

Question 20

What dynamic management view returns information about indexes that are missing in a specific index group, except for spatial indexes?

Answer 20

sys.dm_db_missing_index_groups