August 8, 2011 at 11:39 pm
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:
August 9, 2011 at 3:15 am
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
August 9, 2011 at 5:16 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2011 at 5:20 am
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.
August 9, 2011 at 5:45 am
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
August 9, 2011 at 6:06 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2011 at 6:17 am
Subjective Adapts (8/9/2011)
2.DBCC CHECKCATALOGThis 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
August 9, 2011 at 6:17 am
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.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
August 9, 2011 at 8:48 am
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)
May 6, 2018 at 10:18 pm
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
May 8, 2018 at 8:30 am
What an ominous post! I hope you are able to solve whatever issue is facing you.
These links have a bunch of queries from Glenn Berry that might help.
https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-may-2018/
DMV Query Archives
https://www.sqlskills.com/blogs/glenn/category/dmv-queries/
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply