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: 1. Start SSMS. 2. Connect to the SQL Server instance that you want to monitor. 3. In the Object Explorer pane, expand the **Server Objects** node and select **Management**. 4. Right-click **Management** and select **New** > **Query**. 5. In the query window, type the following command: ```sql 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

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.

The dbcc proccache command provides high-level information about the procedure cache. Unlike previous versions of SQL Server, you can not use SQL to find out how much space is used by a procedure. Click the View Code button to see the output of the dbcc proccache command.
The columns in the output refer to the following information:

1. num proc buffs: Maximum number of stored procedures that could fit in the procedure cache
2. num proc buffs used: Actual number of stored procedures in cache
3. num proc buffs active: Number of stored procedures in cache that are executing
4. proc cache size: Total size of the procedure cache
5. proc cache used: Number of procedure cache buffers allocated to stored procedures
6. proc cache active:-Number of procedure cache buffers holding stored procedures that are currently executing

In the next lesson, you will continue your exploration of the dbcc command and learn how to use it to monitor SQL Server’s data cache.