IMP DMVs, SPs & Functions that Every DBA should know
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
1) Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
2) Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
If a user has been granted VIEW SERVER STATE permissions but denied VIEW DATABASE STATE permission, the user can see server-level information, but not database-level information.
All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema.
For example, to query the dm_os_wait_stats dynamic management view, run the following query:
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats;
Dynamic management views and functions have been organized into the following categories.
Also, Make a note of the below object types in SQL.
|
Type |
Type_desc |
|
C |
CHECK_CONSTRAINT |
|
D |
DEFAULT_CONSTRAINT |
|
F |
FOREIGN_KEY_CONSTRAINT |
|
FN |
SQL_SCALAR_FUNCTION |
|
IF |
SQL_INLINE_TABLE_VALUED_FUNCTION |
|
IT |
INTERNAL_TABLE |
|
P |
SQL_STORED_PROCEDURE |
|
PK |
PRIMARY_KEY_CONSTRAINT |
|
S |
SYSTEM_TABLE |
|
SQ |
SERVICE_QUEUE |
|
TF |
SQL_TABLE_VALUED_FUNCTION |
|
U |
USER_TABLE |
|
UQ |
UNIQUE_CONSTRAINT |
|
V |
VIEW |
In this series of blogs, we will try to cover or understand mostly used DMVs, SPs, and Functions that every DBA should know.
Ref: Dynamic Management Views (Transact-SQL) - SQL Server | Microsoft Docs
Comments
Post a Comment