SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Frequently used DMV's & DBCC commands.....


Frequently used DMV's & DBCC commands.....

Author
Message
Sgar...
Sgar...
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4199 Visits: 666
Hi Guys

I'm looking FOR DMVs & DBCC commands that day to day DBA needs.... which are more frequently used....

Please help I am out of time....

Sagar Sonawane
** Every DBA has his day!!Cool
GilaMonster
GilaMonster
SSC Guru
SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)

Group: General Forum Members
Points: 996066 Visits: 48920
I could list a hundred or so... (and a read through Books Online would give you the same)

What are you specifically looking for?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)

Group: General Forum Members
Points: 392168 Visits: 34708
Out of time for what? Is this about you learning or about an emergency at work, or are we filling in for an interview?

For a great start on DMVs, I'd recommend Tim Ford & Louis Davidson's book, Performance Tuning With Dynamic Management Views

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Arjun SreeVastsva
Arjun SreeVastsva
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7077 Visits: 1754
1.DBCC CHECKALLOC
DBCC CHECKALLOC checks page usage and allocation in the database. Use this command if allocation errors are found for the database. If you run DBCC CHECKDB, you do not need to run DBCC CHECKALLOC, as DBCC CHECKDB includes the same checks (and more) that DBCC CHECKALLOC performs.


2.DBCC CHECKCATALOG
This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.

3.DBCC CHECKCONSTRAINTS
DBCC CHECKCONSTRAINTS alerts you to any CHECK or constraint violations.
Use it if you suspect that there are rows in your tables that do not meet the constraint or CHECK constraint rules.

4.DBCC CHECKDB
A very important DBCC command, DBCC CHECKDB should run on your SQL Server instance on at least a weekly basis. Although each release of SQL Server reduces occurrences of integrity or allocation errors, they still do happen. DBCC CHECKDB includes the same checks as DBCC CHECKALLOC and DBCC CHECKTABLE. DBCC CHECKDB can be rough on concurrency, so be sure to run it at off-peak times.

5.DBCC CHECKTABLE
DBCC CHECKTABLE is almost identical to DBCC CHECKDB, except that it is performed at the table level, not the database level. DBCC CHECKTABLE verifies index and data page links, index sort order, page pointers, index pointers, data page integrity, and page offsets. DBCC CHECKTABLE uses schema locks by default, but can use the TABLOCK option to acquire a shared table lock. CHECKTABLE also performs object checking using parallelism by default (if on a multi-CPU system).

6.DBCC CHECKFILEGROUP
DBCC CHECKFILEGROUP works just like DBCC CHECKDB, only DBCC CHECKFILEGROUP checks the specified filegroup for allocation and structural issues. If you have a very large database (this term is relative, and higher end systems may be more apt at performing well with multi-GB or TB systems ) , running DBCC CHECKDB may be time-prohibitive.
If your database is divided into user defined filegroups, DBCC CHECKFILEGROUP will allow you to isolate your integrity checks, as well as stagger them over time.

7.DBCC CHECKIDENT
DBCC CHECKIDENT returns the current identity value for the specified table, and allows you to correct the identity value if necessary.

8.DBCC DBREINDEX
If your database allows modifications and has indexes, you should rebuild your indexes on a regular basis. The frequency of your index rebuilds depends on the level of database activity, and how quickly your database and indexes become fragmented. DBCC DBREINDEX allows you to rebuild one or all indexes for a table. Like DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, running DBREINDEX during peak activity times can significantly reduce concurrency.

9.DBCC INDEXDEFRAG
Microsoft introduced the excellent DBCC INDEXDEFRAG statement beginning with SQL Server 2000. This DBCC command, unlike DBCC DBREINDEX, does not hold long term locks on indexes. Use DBCC INDEXDEFRAG for indexes that are not very fragmented, otherwise the time this operation takes will be far longer then running DBCC DBREINDEX. In spite of it's ability to run during peak periods, DBCC INDEXDEFRAG has had limited effectiveness compared to DBCC DBREINDEX (or drop/create index).

10.DBCC INPUTBUFFER
The DBCC INPUTBUFFER command is used to view the last statement sent by the client connection to SQL Server. When calling this DBCC command, you designate the SPID to examine. (SPID is the process ID, which you can get from viewing current activity in Enterprise Manager or executing sp_who. )

11.DBCC OPENTRAN
DBCC OPENTRAN is a Transact-SQL command that is used to view the oldest running transaction for the selected database. The DBCC command is very useful for troubleshooting orphaned connections (connections still open on the database but disconnected from the application or client), and identification of transactions missing a COMMIT or ROLLBACK. This command also returns the oldest distributed and undistributed replicated transactions, if any exist within the database. If there are no active transactions, no data will be returned. If you are having issues with your transaction log not truncating inactive portions, DBCC OPENTRAN can show if an open transaction may be causing it.

12.DBCC PROCCACHE
You may not use this too frequently, however it is an interesting DBCC command to execute periodically, particularly when you suspect you have memory issues. DBCC PROCCACHE provides information about the size and usage of the SQL Server procedure cache.

13.DBCC SHOWCONTIG
The DBCC SHOWCONTIG command reveals the level of fragmentation for a specific table and its indices. This DBCC command is critical to determining if your table or index has internal or external fragmentation. Internal fragmentation concerns how full an 8K page is.
When a page is underutilized, more I/O operations may be necessary to fulfill a query request than if the page was full, or almost full.
External fragmentation concerns how contiguous the extents are. There are eight 8K pages per extent, making each extent 64K. Several extents can make up the data of a table or index. If the extents are not physically close to each other, and are not in order, performance could diminish.

14.DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency. Also remember that you cannot shrink a database past the target percentage specified, shrink smaller than the model database, shrink a file past the original file creation size, or shrink a file size used in an ALTER DATABASE statement.

15.DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).

16. DBCC TRACEOFF, TRACEON, TRACESTATUS
Trace flags are used within SQL Server to temporarily enable or disable specific SQL Server instance characteristics. Traces are enabled using the DBCC TRACEON command, and disabled using DBCC TRACEOFF. DBCC TRACESTATUS is used to displays the status of trace flags. You'll most often see TRACEON used in conjunction with deadlock logging (providing more verbose error information).

17.DBCC USEROPTIONS
Execute DBCC USEROPTIONS to see what user options are in effect for your specific user connection. This can be helpful if you are trying to determine if you current user options are inconsistent with the database options.
Lowell
Lowell
SSC Guru
SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)SSC Guru (320K reputation)

Group: General Forum Members
Points: 320266 Visits: 42528
on the other hand, I know of DBA's who have never used the DMV's at all.

Subjective Adapts covered the DBCC commands pretty well.

I dunno, would you say that a DBA doesn't use something until there is a problem that requires it?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)SSC Guru (392K reputation)

Group: General Forum Members
Points: 392168 Visits: 34708
Lowell (8/9/2011)
on the other hand, I know of DBA's who have never used the DMV's at all.

Subjective Adapts covered the DBCC commands pretty well.

I dunno, would you say that a DBA doesn't use something until there is a problem that requires it?


I agree with that. I've been lucky and never had any major corruption issues (so far, knock wood, turn 3 times widdershins & spit), so I'm almost completely unfamiliar with some of those DBCC commands. I've read through them on several occasions (just in case), but not having used them, I plead ignorance to their appropriate application.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)SSC Guru (996K reputation)

Group: General Forum Members
Points: 996066 Visits: 48920
Subjective Adapts (8/9/2011)
2.DBCC CHECKCATALOG
This command checks for consistency in and between system tables. This command is not executed within the DBCC CHECKDB command, so running this command weekly is recommended.


CheckDB most certainly does run CheckCatalog.

8.DBCC DBREINDEX


Deprecated, should not be used. Replacement is ALTER INDEX ... REBUILD

9.DBCC INDEXDEFRAG

Deprecated, should not be used. Replacement is ALTER INDEX ... REORGANISE

13.DBCC SHOWCONTIG

Deprecated, should not be used. Replacement is sys.dm_db_index_physical_stats

14.DBCC SHRINKDATABASE
DBCC SHRINKDATABASE shrinks the data and log files in your database.
Avoid executing this command during busy periods in production, as it has a negative impact on I/O and user concurrency.

15.DBCC SHRINKFILE
DBCC SHRINKFILE allows you to shrink the size of individual data and log files. (Use sp_helpfile to gather database file ids and sizes).


Avoid ever executing them (not just in busy periods) as they are generally not necessary and in most cases the database will just regrow. In addition, shrinking data files causes severe fragmentation.


In addition, when you are quoting someone else's work, cite it and link to it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GregoryF
GregoryF
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6078 Visits: 885
The DBV's offer a wealth of insight, more than is available to discuss here. I suggest you take some time.

Here is a complete list of DMVs, you would do well to start looking into them.

http://social.msdn.microsoft.com/Forums/en/searchserverinstallconfigandadminstration/thread/2221d017-8ae1-4940-9895-1b0ad2e357b8

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
Diyas
Diyas
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4597 Visits: 491
DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.
The DBCC Commands broadly falls into four categories:
Maintenance
Informational
Validation
Miscellaneous
Maintenance Commands
Performs maintenance tasks on a database, index, or filegroup.


1. CLEANTABLE - Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE ('AdventureWorks','Person.Contact',0)2. DBREINDEX - Builds one or more indexes for the table in the specified database.
(Will be removed in the future version, use ALTER INDEX instead) USE AdventureWorks


DBCC DBREINDEX ('Person.Contact','PK_Contact_ContactID',80)3.
DROPCLEANBUFFERS - Removes all clean buffers from buffer pool.


DBCC DROPCLEANBUFFERS4. FREEPROCCACHE - Removes all elements from the procedure cache


DBCC FREEPROCCACHE5. INDEXDEFRAG - Defragments indexes of the specified table or view.


DBCC INDEXDEFRAG ('AdventureWorks', 'Person.Address', PK_Address_AddressID)6. SHRINKDATABASE - Shrinks the size of the data and log files in the specified database


DBCC SHRINKDATABASE ('AdventureWorks', 10)7. SHRINKFILE - Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file

to other files in the same filegroup, allowing the file to be removed from the database.
USE AdventureWorks;
- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (AdventureWorks_Log, 1)8. UPDATEUSAGE - Reports and corrects pages and row count inaccuracies in the catalog views.

DBCC UPDATEUSAGE (AdventureWorks)Informational Commands Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION - is maintained for backward compatibility. It runs but returns no data.

DBCC CONCURRENCYVIOLATION2. INPUTBUFFER - Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (52)3. OPENTRAN - Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.

DBCC OPENTRAN;4. OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (52)5. PROCCACHE - Displays information in a table format about the procedure cache.

DBCC PROCCACHE6. SHOW_STATISTICS - Displays the current distribution statistics for the specified target on the specified table USE AdventureWorks

DBCC SHOW_STATISTICS ('Person.Address', AK_Address_rowguid)7. SHOWCONTIG
- Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks

DBCC SHOWCONTIG ('HumanResources.Employee');8. SQLPERF - Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

DBCC SQLPERF(LOGSPACE)9. TRACESTATUS - Displays the status of trace flags.

DBCC TRACESTATUS(-1)10. USEROPTIONS - Returns the SET options active
(set) for the current connection.

DBCC USEROPTIONSValidation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC - Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (AdventureWorks)2. CHECKCATALOG - Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (AdventureWorks)3. CHECKCONSTRAINTS - Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS4. CHECKDB - Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (AdventureWorks)5. CHECKFILEGROUP - Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks

DBCC CHECKFILEGROUP6. CHECKIDENT - Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;

DBCC CHECKIDENT ('HumanResources.Employee')7. CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;

DBCC CHECKTABLE ('HumanResources.Employee')Miscellaneous Commands Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) - Unloads the specified extended stored procedure DLL from memory.

DBCC xp_sample (FREE)2. TRACEOFF - Disables the specified trace flags.

DBCC TRACEOFF (3205)3. HELP - Returns syntax information for the specified DBCC command.
- List all the DBCC commands

DBCC HELP ('?')
- Show the Syntax for a given DBCC commnad

DBCC HELP ('checkcatalog')4. TRACEON - Enables the specified trace flags.

DBCC TRACEON (3205)
Arun Benjamin
Arun Benjamin
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1432 Visits: 297
Most used DMVs

sys.dm_exec_requests
sys.dm_exec_sessions

sys.dm_os_performance_counters

sys.dm_os_sys_info
sys.dm_os_sys_memory

sys.dm_db_missing_index_columns
sys.dm_db_missing_index_details

Most used DMFs

sys.dm_db_index_physical_stats
sys.dm_db_stats_properties
sys._dm_exec_sql_text
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search