SQL DBA Finger Tip Commands
Most of the SQL DBAs in the present world depend a lot on either Enterprise Manager or Management Studio for many of the common daily operations. However, the below table lists out some common commands that we use daily and if known can be issued with Query Analyzer or Query Editor to retrieve results instantaneously....
Command : sp_helpdb
This gives you information about all databases in the instance or specific information about one database.
Usage : sp_helpdb databasename
Command : fn_virtualfilestats
This command will show you the number of read and writes to a data file. Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.
Usage :SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)
SELECT * FROM :: fn_virtualfilestats(1, 1)
Command : fn_get_sql()
Returns the text of the SQL statement for the specified SQL handle. This is similar to using DBCC INPUTBUFFER, but this command will show you additional information. This can also be embedded in a process easier then using the DBCC command
Usage :DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle
Command : sp_lock
This command shows you all of the locks that the system is currently tracking This is similar to information you can see in Enterprise Manager.
Usage :sp_lock or sp_lock spid or sp_lock spid1, spid2
Command : sp_help
This command gives you information about the objects within a database. The command without an objectname will give you a list of all objects within the database.
Usage :sp_help or sp_help objectname
Command : sp_who2
Gives you process information similar to what you see when using Enterprise Manager.
Usage :sp_who2 or sp_who2 spid
Command : sp_helpindex
Gives you information about the indexes on a table as well as the columns used for the index.
Usage :sp_helpindex objectname
Command : sp_spaceused
This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.
Usage :sp_spaceused or sp_spaceused objectname
Command : DBCC CACHESTATS
Displays information about the objects currently in the buffer cache.
Usage :DBCC CACHESTATS
Command : DBCC CHECKDB
This will check the allocation of all pages in the database as well as check for any integrity issues.
Usage :DBCC CHECKDB
Command : DBCC CHECKTABLE
This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
Usage :DBCC CHECKTABLE (‘tableName’)
Command : 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.
Usage :DBCC DBREINDEX (tablename, indexname, fillfactor)
Command : DBCC PROCCACHE
This command will show you information about the procedure cache and how much is being used. Spotlight will also show you this same information.
Usage :DBCC PROCCACHE
Command : DBCC MEMORYSTATUS
Displays how the SQL Server buffer cache is divided up, including buffer activity.
Usage :DBCC MEMORYSTATUS
Command : DBCC SHOWCONTIG
This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
Usage :DBCC SHOWCONTIG or DBCC SHOWCONTIG WITH ALL_INDEXES or DBCC SHOWCONTIG tablename
Command : DBCC SHOW_STATISTICS
This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
Usage :DBCC SHOW_STATISTICS (tablename, indexname)
Command : 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.
Usage :DBCC SHRINKFILE (filename, size in MB) or DBCC SHRINKFILE (DataFile, 1000)
Command : DBCC SQLPERF
This command will show you much of the transaction logs are being used.
Usage :DBCC SQLPERF(LOGSPACE)
Command : DBCC TRACEON
This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
Usage :DBCC TRACEON(traceflag)
Command : DBCC TRACEOFF
This command turns off a trace flag.
Usage :DBCC TRACEOFF(traceflag)
Please note that incorrect or inappropriate use of this commands may lead to unexpected results...
Most of the SQL DBAs in the present world depend a lot on either Enterprise Manager or Management Studio for many of the common daily operations. However, the below table lists out some common commands that we use daily and if known can be issued with Query Analyzer or Query Editor to retrieve results instantaneously....
Command : sp_helpdb
This gives you information about all databases in the instance or specific information about one database.
Usage : sp_helpdb databasename
Command : fn_virtualfilestats
This command will show you the number of read and writes to a data file. Use sp_helpdb with the database name to see the logical file numbers for the data files and the database id.
Usage :SELECT * FROM :: fn_virtualfilestats(dabaseid, logicalfileid)
SELECT * FROM :: fn_virtualfilestats(1, 1)
Command : fn_get_sql()
Returns the text of the SQL statement for the specified SQL handle. This is similar to using DBCC INPUTBUFFER, but this command will show you additional information. This can also be embedded in a process easier then using the DBCC command
Usage :DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@Handle
Command : sp_lock
This command shows you all of the locks that the system is currently tracking This is similar to information you can see in Enterprise Manager.
Usage :sp_lock or sp_lock spid or sp_lock spid1, spid2
Command : sp_help
This command gives you information about the objects within a database. The command without an objectname will give you a list of all objects within the database.
Usage :sp_help or sp_help objectname
Command : sp_who2
Gives you process information similar to what you see when using Enterprise Manager.
Usage :sp_who2 or sp_who2 spid
Command : sp_helpindex
Gives you information about the indexes on a table as well as the columns used for the index.
Usage :sp_helpindex objectname
Command : sp_spaceused
This command shows you how much space has been allocated for the database (or if specified an object) and how much space is being used.
Usage :sp_spaceused or sp_spaceused objectname
Command : DBCC CACHESTATS
Displays information about the objects currently in the buffer cache.
Usage :DBCC CACHESTATS
Command : DBCC CHECKDB
This will check the allocation of all pages in the database as well as check for any integrity issues.
Usage :DBCC CHECKDB
Command : DBCC CHECKTABLE
This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.
Usage :DBCC CHECKTABLE (‘tableName’)
Command : 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.
Usage :DBCC DBREINDEX (tablename, indexname, fillfactor)
Command : DBCC PROCCACHE
This command will show you information about the procedure cache and how much is being used. Spotlight will also show you this same information.
Usage :DBCC PROCCACHE
Command : DBCC MEMORYSTATUS
Displays how the SQL Server buffer cache is divided up, including buffer activity.
Usage :DBCC MEMORYSTATUS
Command : DBCC SHOWCONTIG
This command gives you information about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
Usage :DBCC SHOWCONTIG or DBCC SHOWCONTIG WITH ALL_INDEXES or DBCC SHOWCONTIG tablename
Command : DBCC SHOW_STATISTICS
This will show how statistics are laid out for an index. You can see how distributed the data is and whether the index is really a good candidate or not.
Usage :DBCC SHOW_STATISTICS (tablename, indexname)
Command : 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.
Usage :DBCC SHRINKFILE (filename, size in MB) or DBCC SHRINKFILE (DataFile, 1000)
Command : DBCC SQLPERF
This command will show you much of the transaction logs are being used.
Usage :DBCC SQLPERF(LOGSPACE)
Command : DBCC TRACEON
This command will turn on a trace flag to capture events in the error log. Trace Flag 1204 captures Deadlock information.
Usage :DBCC TRACEON(traceflag)
Command : DBCC TRACEOFF
This command turns off a trace flag.
Usage :DBCC TRACEOFF(traceflag)
Please note that incorrect or inappropriate use of this commands may lead to unexpected results...