March towards SQL Server : Day 29 – SQL DBA Interview Questions Answers – All About DBCC & DMVs


Dear readers,

 

Now its time to cover one of the most important topic for any Database Administrator out there. We owe our career as DBA to these small yet most powerful feature provided by Microsoft to check every minute details about our system  and these little keywords always come to rescue when anyone from application side and management throws toughest questions on us related to health, current state and performance of our servers. These small keywords are DMVs\DMFs and DBCC. Read below the today’s 31 interview questions to know more about these.

 

1)    What is the use of DBCC commands?
DBCC stands for database consistency checker. There are many DBCC command in SQL Server. We generally use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

2)    What are the DMV’s in SQL Server ?
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Dynamic management views and functions return internal, implementation-specific state data. Their schemas and the data they return may change in future releases of SQL Server. Therefore, dynamic management views and functions in future releases may not be compatible with the dynamic management views and functions in this release. For example, in future releases of SQL Server, Microsoft may augment the definition of any dynamic management view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM dynamic_management_view_name in production code because the number of columns returned might change and break your application.

3)     What is back process for the DBCC?

When you execute one of these DBCC commands, the Database Engine creates a database snapshot and brings it to a transactionally consistent state. The DBCC command then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.
Sometimes an internal database snapshot is not required or cannot be created. When this occurs, the DBCC command executes against the actual database. If the database is online, the DBCC command uses table-locking to ensure the consistency of the objects that it is checking. This behavior is the same as if the WITH TABLOCK option were specified.
An internal database snapshot is not created when a DBCC command is executed:
•    Against master, and the instance of SQL Server is running in single-user mode.
•    Against a database other than master, but the database has been put in single-user mode by using the ALTER DATABASE statement.
•    Against a read-only database.
•    Against a database that has been set in emergency mode by using the ALTER DATABASE statement.
•    Against tempdb. In this case, a database snapshot cannot be created because of internal restrictions.

4)    Can you explain DBCC CheckDB?

DBCC CHECKDB is a Algorithm  which at  backend checks that:

1. Object Integrity
2. Linkages for text, ntext, and image pages
3. Index and data pages are correctly linked.
4. Indexes are in their proper sort order.
5. Pointers are consistent.
6. The data on each page is reasonable (Allocation Checks).
7. Page offsets are reasonable.

5)    what is the exact use of DMVs?

DMVs can be used in the gathering of baseline information and for diagnosing performance problems. Few important dmvs are:
1. sys.dm_os_performance_counters
2. sys.dm_db_index_physical_stats
3. sys.dm_db_index_usage_stats
6)    Please explain DMV Categories?

Category Prefix
Common Language Runtime (CLR) Sys.dm_clr_*
Database Sys.dm_db_*
Indexing Sys.dm_db_index_*
Database Mirroring Sys.dm_db_mirroring_*
Execution Sys.dm_exec_*
Full-Text Search Sys.dm_fts_*
I/O Sys.dm_io_*
Query Notifications Sys.dm_qn_*
Replication Sys.dm_repl_*
Service Broker Sys.dm_broker_*
SQL Server Operating System Sys.dm_os_*
Transactions Sys.dm_tran_*
Change Data Capture Sys.dm_cdc_*
Object Sys.dm_sql_*
Resource Governor Sys.dm_resource_governor_*
SQL Server Extended Events Sys.dm_xe_*
Sys.dm_cryptographic_*
Security Sys.dm_provider_*
Sys.dm_audit_*

7)    What are all the SQL Server  Dynamic Management Views(DMV) and Dynamic management functions(DMF)  available in SQL Server?
Use below query to list out all available DMVs present in a SQL Installation :-
SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE ‘dm[_]%’
ORDER BY name

8)    How many types of DMVs are there?

There are two types of dynamic management views:
a.    Server-scoped DMV: Stored in Master Database
b.    Database-scoped DMV: Specific to each database

9)    Explain DBCC inputbuffer()?
DBCC INPUTBUFFER  returns the last sql statement  issued by a client. The command requires the SPID
DBCC INPUTBUFFER (SPID)

10)    List few DMVs for space usage related information ?
sys.dm_db_file_space_usage –  Lists space usage information for each file in the database. Reports on unallocated extent page count.
sys.dm_db_session_space_usage – Broken down by each session. Lists the number of pages allocated and deallocated
sys.dm_db_task_space_usage – Broken down by each task. Lists page allocation and deallocation activity

11)    While viewing activity on SQL Server , for example, sp_who2 – the status column displays different states – RUNNABLE – SUSPENDED – RUNNING.   Could you explain the difference?
Some background information on the SQL Schedulers , will make understanding the RUNNABLE – SUSPENDED – RUNNING  model clearer.
Schedulers are made up of three parts . A thread cycles though these three parts
1) Processor
2) Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources
3) Runnable – thread has all the resources and waiting for the processor. Explore runnable status with the  sys.dm_os_schedulers and sys.dm_exec_requests  DMVs
This leads us into the RUNNABLE – SUSPENDED – RUNNING
1)      RUNNING – thread is executing on the server
2)      SUSPENDED – thread is waiting for resources to become available.
3)      RUNNABLE – the thread is waiting to execute on the processor

12)    Why does RUNNING transition to SUSPENDED ?
Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list
13)    Why does SUSPENDED  transition into RUNNABLE?
The resource is now available and moves to the bottom of the RUNNABLE queue.

14)    Why does RUNNABLE transition into RUNNING?
Top spid at head of RUNNABLE queue moves to processor

15)    List 5 inportant DMVs for Index analysis.
sys.dm_db_index_usage_stats :- Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.
sys.dm_db_missing_index_details :- Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.
sys.dm_db_missing_index_columns :- Returns information about database table columns that are missing an index, excluding spatial indexes.
sys.dm_exec_query_stats :- Performance statistics for cached plans. The information is only available while the plan remains in the cache.
sys.dm_db_index_operational_stats :- Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete

16)    What is use of DBCC DBREINDEX?
This command will reindex your table. If the indexname is left out then all indexes are rebuilt. If the fillfactor is set to 0 then this will use the original fillfactor when the table was created.

17)    Which DBCC command is used to shrink database files?
DBCC SHRINKFILE :- This will allow you to shrink one of the database files. This is equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.

18)    Which DBCC command is used to store the Procedure cache related information?
DBCC PROCCACHE – This command will show you information about the procedure cache and how much is being used.

19)    Explain DBCC TRACEON & DBCC TRACEOFF?
DBCC TRACEON – This command will turn on a trace flag to capture events in the error log. DBCC TRACEOFF – This command turns off a trace flag.

20)    How can you check if any transaction is running on a database or not?

We can use DBCC OPENTRAN to check any running transaction on the database. It is one of the most commonly used DBCC command along with DBCC CHECKDB, DBCC SHRINKFILE, DBCC SQLPERF(logspace) etc.

21)    Can anyone predict how long DBCC Checkdb will run on any database?
As far as estimating how long DBCC CHECKDB will take to run on a given database, it’s very difficult to tell because there are so many variables involved. The following are some factors that affect DBCC CHECKDB’s run time:
•    The size of the database. This one’s not so obvious—it’s not the size of the database that matters, it’s the amount of data that’s in it.
•    The load on the system. DBCC CHECKDB is extremely resource hungry—I like to say it’s the most resource-intensive operation you can run on SQL Server. Therefore, if the server is already heavily loaded, DBCC CHECKDB will be competing for resources and will take a lot longer to run.
•    The capabilities of the system. If the database being consistency checked is very large and structurally complicated, but the server and/or I/O subsystem are heavily underpowered, this will have a knock-on effect on the ability of the server to provide the resources DBCC CHECKDB needs, slowing it down.
•    The options specified. If the WITH PHYSICAL_ONLY option is specified, the amount of processing that DBCC CHECKDB does is drastically cut down, which usually leads to a significant reduction in run time.
•    The complexity of the database schema. The more features that you use in the database, the more structures there are to be consistency checked, so DBCC CHECKDB will take longer to run.
•    The corruptions that are found. Some corruptions require deeper reprocessing of data to figure out exactly where the corruption is. This can lead to a much longer run time for DBCC CHECKDB.
•    The tempdb configuration. DBCC CHECKDB uses a lot of memory to store intermediate consistency checking data, and that storage usually spills out to the tempdb database. If tempdb isn’t configured well, it can be a bottleneck for DBCC CHECKDB and slow it down.
As you can see, there are too many factors involved to be able to make a good guess. The best way to know how long DBCC CHECKDB will take is to run it.

22)    What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX is an offline operation is used to rebuild the indexes of a table dynamically. This operation requires enough space in the data files. If the space is not enough DBCC DBREINDEX may be unable to rebuild the indexes.
DBCC CHECKDB is used to produce a consistent view of the data by performing a physical consistency check on indexed views, validating integrity of the indexes, objects etc. in earlier versions of SQL, this required locking. Newer versions involve reading the transaction log of the oldest active transaction. REDO and UNDO of the transactions affect the volatile changes to available free space.

23)   How can DMVs help with performance tuning?

  • Helps to find out the queries that are causing memory or CPU pressure on your system
  • Helps to investigate caching, and query plan reuse
  • Helps to identify index usage patterns
  • Helps to track fragmentation in clustered indexes and heaps
  • Gives full details on blocking and blocked transactions

24)    What permission does a user need to access the DMV’s
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions (e.g OS, IO, Threads, tasks etc). These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions (e.g Index, Tables, partition, file etc). These require VIEW DATABASE STATE permission on the database.

25)    How are DMV’s and DMF’s changing the memory consumptions of SQL Server? consider the dm_exec_* which store the results of the current workload.
DMV’s are in-memory structures and are anyway’s used by SQL Server internally. It is with SQL Server 2005 that we started exposing them in an official manner rather than doing bit-manipulations with some DBCC commands. Hence there is nothing to be worried about the load or memory consumptions. It is not as alarming as you think.

26)    Which DMV give me query plan or I will use old method to find query plan?
Below DMVs can be used to provide query plan related information :-
sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_query_plan

27)    Name some Security related DMVs\DMFs.
sys.dm_audit_actions
sys.dm_audit_class_type_map
sys.dm_cryptographic_provider_properties
sys.dm_database_encryption_keys
sys.dm_server_audit_status

28)    Mention some SQL OS related DMVs\DMFs.
sys.dm_os_buffer_descriptors
sys.dm_os_child_instances
sys.dm_os_cluster_nodes
sys.dm_os_hosts
sys.dm_os_nodes
sys.dm_os_memory_pools
sys.dm_os_performance_counters
sys.dm_os_process_memory
sys.dm_os_schedulers
sys.dm_os_memory_objects
sys.dm_os_workers

29)    Name few database related DMVs :-

sys.dm_db_file_space_usage
sys.dm_db_partition_stats
sys.dm_db_session_space_usage
sys.dm_db_task_space_usage

30)    Which DMVs are useful to gather information about database mirroring :-

sys.dm_db_mirroring_connections
sys.dm_db_mirroring_auto_page_repair

31)    What are the most important DMVs\DMFs from a DBA perspective.

Execution Related
•    sys.dm_exec_connections
•    sys.dm_exec_sessions
•    sys.dm_exec_requests
•    sys.dm_exec_cached_plans
•    sys.dm_exec_query_plans
•    sys.dm_exec_sql_text
•    sys.dm_exec_query_stats
Index Related
•    sys.dm_db_index_physical_stats
•    sys.dm_db_index_usage_stats
SQL Server Operating System
•    sys.dm_os_performance_counters
•    sys.dm_os_schedulers
•    sys.dm_os_nodes
•    sys.dm_os_waiting_tasks
•    sys.dm_os_wait_stats
I/O Related
•    sys.dm_io_virtual_file_stats

 

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information  which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.