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.