What is the use of DBCC commands?
DBCC stands for Database Console Commands, a few of us also call it database consistency checker. There are many DBCC commands in SQL Server. We generally use these commands to check the consistency of the databases, i.e., maintenance, validation task & status checks, Informational, and Miscellaneous.
What is the 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.
Can you explain DBCC CHECKDB?
DBCC CHECKDB is an Algorithm which at backend checks that:
- Object Integrity
- Linkages for text, ntext, and image pages
- Index and data pages are correctly linked.
- Indexes are in their proper sorting order.
- Pointers are consistent.
- The data on each page is reasonable (Allocation Checks).
- Page offsets are reasonable.
What are the DMVs 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.
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:
- sys.dm_os_performance_counters
- sys.dm_db_index_physical_stats
- sys.dm_db_index_usage_stats
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_* |
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 -- or -- SELECT name ,type ,type_desc FROM sys.system_objects WHERE name LIKE 'dm[_]%' ORDER BY name
Reff.
How many types of DMVs are there?
There are two types of dynamic management views:
- Server-scoped DMV: Stored in Master Database
- Database-scoped DMV: Specific to each database
Explain DBCC inputbuffer()?
DBCC INPUTBUFFER returns the last SQL statement issued by a client. The command requires SPID. DBCC INPUTBUFFER (SPID)
List a 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
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
- Processor
- Waiter list – threads waiting for resources. Use Sys.dm_os_waiting_tasks to view resource waits for the resources
- 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
- RUNNING – thread is executing on the server
- SUSPENDED – thread is waiting for resources to become available.
- RUNNABLE – the thread is waiting to execute on the processor
Why does RUNNING transition to SUSPENDED?
Thread is executing and if waiting for a resource moves to SUSPENDED into the waiter list
Why does SUSPENDED transition into RUNNABLE?
The resource is now available and moves to the bottom of the RUNNABLE queue.
Why does RUNNABLE transition into RUNNING?
Top spid at head of RUNNABLE queue moves to processor
List 5 important 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
What is the use of DBCC DBREINDEX?
This command will reindex your table. If the index name is left out, then all indexes are rebuilt. If the fill factor is set to 0 then this will use the original fill factor when the table was created.
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.
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.
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.
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 used DBCC commands along with DBCC CHECKDB, DBCC SHRINKFILE, DBCC SQLPERF (logspace) etc.
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 corruption 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.
What is the effect of DBCC CHECKDB and DBCC DBREINDEX on the Transaction log?
DBCC DBREINDEX is an offline operation 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.
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
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.
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.
DMVs are in-memory structures and are any way’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.
Which DMV will 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
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
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
Name few databases 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
Which DMVs are useful to gather information about database mirroring
- sys.dm_db_mirroring_connections
- sys.dm_db_mirroring_auto_page_repair
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
For more you may visit: https://www.sqldbahub.com/p/dbcc.html