No One is Perfect

The DMV’s; newly introduced in SQL Server 2005 gives database administrator information about the current state of the SQL Server machine. These values will help the administrator to diagnose problems and tune the server for optimal performance. DMV’s are designed to be used instead of system tables and various other functions provided in SQL Server 2000.

Two types of dynamic management views:-

Server-scoped DMV: Stored in Master Database
Database-scoped DMV: Specific to each database

Permission to Execute DMV [Security]
To query a Server scoped DMV the database user must have SELECT privilege on VIEW SERVER STATE and for database scoped DMV the user must have SELECT privilege on VIEW DATABASE STATE.

GRANT VIEW SERVER STATE to <Login >
GRANT VIEW DATABASE STATE to <User >

If you want to deny a user permission to query certain DMV’s you can use the DENY command to restrict access to specific DMV.

This command prints out the contents of the DBTABLE structure.

Syntax:

DBCC DBTABLE ({dbid|dbname})

where
dbid|dbname - database name or database ID

This is the example:

DBCC TRACEON (3604)
DBCC DBTABLE (master)

The DBTABLE structure has an output parameter called dbt_open.
This parameter keeps track of how many users are in the database.

Print DBINFO structure for specified database.

Syntax:

DBCC DBINFO [( dbname )]

where
dbname - is the database name.

This is the example:

DBCC TRACEON (3604)
DBCC DBINFO (master)

This command can be used to dump out bytes from a specific address.

Syntax:

dbcc bytes ( startaddress, length )

where
startaddress - starting address to dump
length - number of bytes to dump

This is the example:

DBCC TRACEON (3604)
dbcc bytes (10000000, 100)

This command can be used to print buffer headers and pages from the
buffer cache.

Syntax:

dbcc buffer ([dbid|dbname] [,objid|objname] [,nbufs], [printopt])

where
dbid|dbname - database id|database name.
objid|objname - object id|object name
nbufs - number of buffers to examine
printopt - print option
0 - print out only the buffer header and page header
(default)
1 - print out each row separately and the offset table
2 - print out each row as a whole and the offset table

This is the example:

DBCC TRACEON (3604)
dbcc buffer(master,'sysobjects')