Lesson 7 | Monitoring with the database consistency checker |
Objective | Monitor SQL Server with dbccs. |
Monitor using database consistency checker
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:
- dbcc memusage: monitor memory buffers
- dbcc proccache: montior 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
We will begin by looking at
dbcc memusage
and
dbcc proccache
.
dbcc memusage
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:
- dbid: The identifier of the database that the object is from
- objectid: The object identifier of the table
- indexid: The index identifier of the table; if it is a zero then the space is used for data
- buffers: The number of buffers in use
dbcc proccache
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 |
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:
- num proc buffs: Maximum number of stored procedures that could fit in the procedure cache
- num proc buffs used: Actual number of stored procedures in cache
- num proc buffs active: Number of stored procedures in cache that are executing
- proc cache size: Total size of the procedure cache
- proc cache used: Number of procedure cache buffers allocated to stored procedures
- 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.