The `DBCC PERFMON` command in SQL Server was an undocumented feature used to display I/O activity, cache activity, locking activity, and process waiting. However, it is no longer supported in current versions of SQL Server. Instead, Microsoft recommends using the Windows Performance Monitor (PerfMon) to monitor SQL Server performance metrics. PerfMon provides a comprehensive set of performance counters that allow you to monitor various aspects of SQL Server, including:
For the following list of 4 elements, put the elements in a HTML unordered list .
I/O Activity: Counters such as Physical Disk: Disk Reads/sec and Disk Writes/sec help track disk I/O performance.
Cache Activity: The SQLServer:Buffer Manager object includes counters like Buffer cache hit ratio, indicating how often data pages are found in memory without requiring a disk read.
Locking Activity: The SQLServer:Locks object provides counters such as Number of Deadlocks/sec, which can help identify locking issues.
Process Waiting: The SQLServer:Wait Statistics object includes counters that show the amount of time SQL Server processes spend waiting for resources.
To use PerfMon for monitoring SQL Server:
Open Performance Monitor (perfmon.exe) on your Windows server.
Click the green plus sign (+) to add counters.
In the "Add Counters" dialog, expand the SQLServer objects to select the specific counters you wish to monitor.
Add the selected counters to your monitoring session.
By utilizing PerfMon, you can effectively monitor and analyze SQL Server performance metrics, replacing the deprecated `DBCC PERFMON` command.
dbcc command Legacy
It's not quite accurate to say the entire `DBCC` command is deprecated, but many of its subcommands are. Microsoft is gradually replacing specific `DBCC` statements with more modern alternatives, primarily because those alternatives offer better functionality, performance, and maintainability.
Here's a breakdown:
Deprecated `DBCC` Commands:
DBCC DBREINDEX: Use ALTER INDEX instead. It provides more flexibility and options for online rebuilding.
DBCC SHOWCONTIG: Use sys.dm_db_index_physical_stats for more detailed and accurate information about index fragmentation.
DBCC SHRINKDATABASE: Generally discouraged for regular use due to performance impacts and potential for increased fragmentation. Consider alternatives like filegroup shrinking or data partitioning.
`DBCC` Commands Still Relevant:
Many `DBCC` commands remain valuable tools for database administrators:
DBCC CHECKDB: Crucial for checking the integrity of your database.
DBCC CHECKTABLE: Checks the integrity of a specific table.
DBCC PAGE: Allows inspection of individual data pages (used for advanced troubleshooting).
DBCC TRACESTATUS: Displays information about trace flags.
DBCC INPUTBUFFER: Shows the last statement sent from a client.
Why the Shift Away from Some `DBCC` Commands?**
Improved Alternatives: Newer commands like ALTER INDEX offer more control and features (e.g., online index operations).
Performance: Some DBCC commands can be resource-intensive.
Maintainability: Dedicated commands for specific tasks are often easier to understand and maintain.
Important Notes:
Always check the latest SQL Server documentation for the most up-to-date deprecation information.
When possible, transition to the recommended alternatives for better performance and future compatibility.
Some DBCC commands are intended for advanced troubleshooting and should be used cautiously.
In summary, while some `DBCC` subcommands are being phased out, the `DBCC` command itself remains an important part of SQL Server. Just be aware of the specific commands that have been deprecated and use their recommended replacements.
The outstanding I/O section lists the number of disk reads and writes that are currently being performed on SQL Server’s behalf.
Statistic Value
--------------------------- --------
Cache Hit Ratio 97.31707
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 50.0
(7 row(s) affected)
The cache information section provides an overview of the data cache usage.
Statistic Value
------------------------------ ------
Network Reads 79.0
Network Writes 152.0
Command Queue Length 0.0
Max Command Queue Length 0.0
Worker Threads 0.0
Max Worker Threads 0.0
Network Threads 0.0
Max Network Threads 0.0
(8 row(s) affected)
The thread information section contains network information and thread information.
Statistic Value
------------------------------- -----
RA Pages Found in Cache 0.0
RA Pages Placed in Cache 0.0
RA PhysicalI/O 0.0
Used Slots 0.0
(4 row(s) affected)
read ahead section
The read ahead section contains information about read ahead access ( where SQL Server reads data pages before it has been requested).
The spinlock section, for the various item types on which SQL Server places internal locks, keeps track of the number of retries for a lock (spin) and the number of requests that need to retry (collisions).
This table represents the memory address, its octal representation, and its ASCII translation for the bottom half of the `Output Buffer`.
The memory address that the information is stored in,
The octal representation of the output buffer, 3. The ASCII representation of the buffer
Click the link above to see the output for this command.
If the connection’s last command did not return the 512 bytes, the buffer will contain information from the previous command.
dbcc inputbuffer
The command dbcc outputbuffer returns the last output buffer, whereas the command dbcc inputbuffer
will return the last buffer sent from a client to dbccinput-buffer-output .
The syntax of dbcc inputbuffer is as follows:
dbcc inputbuffer (spid)
Output from dbccinput buffer in SQL-Server
Extracted Text:
Columns:
EventType: Language Event
Parameters: 0
EventInfo:select convert(numeric(10,2), -1.222)
Description:
The event type describes what the buffer did.
A Language Event indicates that SQL was executed against the local computer.
Parameters: The number of parameters in a Remote Procedure Call (RPC) or 0 for a Language Event.
EventInfo: Displays the first 255 characters of the event.
Relevant Features:
EventType: The type of action being performed. In this case, it's a Language Event, which refers to SQL commands executed locally.
Parameters: This value (0) signifies that no parameters are associated with the event. For RPCs, this would indicate the number of parameters passed.
EventInfo: Captures and displays the SQL query or command being executed, limited to the first 255 characters. Here, it shows the SQL command: select convert(numeric(10,2), -1.222), which converts a value to a numeric type with two decimal places.
Purpose: This structure is commonly used in debugging or auditing SQL Server events, allowing administrators to identify and analyze SQL queries or RPCs causing specific events.
What is DBCC INPUTBUFFER ?
It is a command used to identify the last statement executed by a particular SPID.
You would typically use this after running sp_who2
How to use
For example, I have just run run sp_who2 and identified that my lead blocker is SPID number 54 so I would run as follows:
DBCC INPUTBUFFER(54)
Here is a simple demonstration. In a new query window run thisview sourceprint?
1 BEGIN
2UPDATE Orders SET Amount = 500 WHERE OrderID = 1;
Now in a second query window run this
UPDATE Orders SET Amount = 200 WHERE OrderID = 1;
Now run sp_who2 to identify the lead blocker followed by DBCC INPUTBUFFER using the lead blocker SPID.
Here are my results with the column "EventInfo" providing the detail that I need on the T-SQL which is causing the problem.
It is possible to copy and paste the code into a query window for analysis.
The next lesson will cover the dbcc trace flags[1] that can be used to monitor SQL Servers behavior.
The following table contains a list of the dbcc commands presented in this module.
SQL Server dbcc Commands The following table contains a list of dbcc commands and their uses.
dbcc memusage
monitor memory buffers
dbcc proccache
monitor the procedure cache
dbcc buffer
monitor data cache
dbcc perform
monitor overall system performance
dbcc output buffer
monitor data sent back to clients
dbcc input buffer
monitor data sent from clients
DBCC (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.
Database Console Command statements are grouped into the following categories.
Command category
Perform
Maintenance
Maintenance tasks on a database, index, or filegroup.
Miscellaneous
Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
Informational
Tasks that gather and display various types of information.
Validation
Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
DBCC Internal Database Snapshot Usage
The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates.
This prevents blocking and concurrency problems when these commands are executed. For more information, see Database Snapshots (SQL Server).
[1]trace flags: A SQL Server dbcc option that changes the behavior of SQL Server.
