Monitor using Database Consistency Checker

Yes, Microsoft SQL Server can still be monitored using DBCC, the database consistency checker. DBCC is a built-in tool that can be used to check the integrity of SQL Server databases and objects. It can also be used to repair certain types of corruption. To monitor SQL Server using DBCC, you can use the following steps:

1. Start SQL Server Management Studio (SSMS).
2. Connect to the SQL Server instance that you want to monitor.
3. In the Object Explorer pane, expand the Databases node and select the database that you want to monitor.
4. Right-click the database and select New > Query.
5. In the query window, type the following command:
   

   DBCC CHECKDB (database_name)
   

6. Press F5 to execute the command.
   DBCC will check the integrity of the database and report any errors that it finds. If DBCC finds any corruption, it may be able to repair it automatically. However, in some cases, you may need to manually repair the corruption.

You can also use DBCC to monitor the performance of SQL Server. To do this, you can use the following steps:

DBCC INPUTBUFFER

6. Press F5 to execute the command.
DBCC will display the current input buffer, which contains information about the SQL queries that are currently being executed. You can use this information to identify queries that are causing performance problems.
Limitations of DBCC
DBCC is a powerful tool for monitoring SQL Server databases, but it has a few limitations:

• DBCC can be resource-intensive, so it is important to use it carefully.
• DBCC cannot detect all types of corruption.
• DBCC cannot repair all types of corruption.

Recommendations If you are serious about monitoring SQL Server performance and health, I recommend using a dedicated monitoring tool such as SolarWinds Database Performance Analyzer (DPA) or Paessler PRTG Network Monitor. These tools provide a more comprehensive view of SQL Server performance and can help you to identify and resolve performance problems quickly and easily.
However, if you are on a tight budget or only need to perform basic SQL Server monitoring, DBCC can be a useful tool. Just be sure to use it carefully and to be aware of its limitations.

SQL Server has a command called database consistency checker (dbcc) that can be used to monitor SQL Server's performance. While this dbcc can be used to monitor databases, it can also monitor and configure SQL Server. The next three lessons will show you six different ways to use dbcc to monitor SQL Server:

1. dbcc memusage: monitor memory buffers
2. dbcc proccache: montior the procedure cache
3. dbcc buffer: monitor data cache
4. dbcc perform: monitor overall system performance
5. dbcc output buffer: monitor data sent back to clients
6. dbcc input buffer: monitor data sent from clients

We will begin by looking at dbcc memusage and dbcc proccache.

Microsoft’s documentation says that dbcc memusage is not supported and no longer exists in SQL Server 7.0. This tool is still there, but for those of you familiar with SQL Server 6.x, its functionality has been greatly reduced.
dbcc memusage [(‘BUFFER’)]
The dbcc memusage command will display the number of memory buffers (8k block) used by a table or index. It will only display the information for the 20 tables or indexes that are taking up the most memory. Below you’ll see a sample output of the dbcc memusage command.

dbid   objectid    indexid buffers     

------ ----------- ------- ----------- 

1      36          0       8

1      2           255     5

1      3           0       5

1      3           2       4

1      99          0       4

2      99          0       4

The column headings refer to the following information:

1. dbid: The identifier of the database that the object is from
2. objectid: The object identifier of the table
3. indexid: The index identifier of the table; if it is a zero then the space is used for data
4. buffers: The number of buffers in use

The dbcc proccache command is used to monitor SQL Server’s procedure cache.
dbcc proccache Procedure cache: The amount of memory that SQL Server reserves for storing stored procedures.
View the code below

num proc buffs	num proc buffs used	num proc buffs active	proc cache size	proc cache used	proc cache active
40	40	13	48	48	21