SQLServer 2005 -- Dynaic Management Views

The dynamic management views (DMVs) in SQL Server 2005 are designed to give you a window into what's going on inside SQL Server. They can provide information on what's currently happening inside the server as well as the objects it's storing. They are designed to be used instead of system tables and the various functions provided in SQL Server 2000. This article provides an introduction to DMVs and covers a few of the basic views and functions.

The Dynamic Management Views are actually composed of both views and table-valued functions. Some apply to the entire server and are stored in the master database. Others are specific to each database. All are stored in the sys schema. They all start with dm_ in the name. They have been broken up into twelve categories:

Common Language Runtime Related Dynamic Management Views

I/O Related Dynamic Management Views and Functions

Database Mirroring Related Dynamic Management Views

Query Notifications Related Dynamic Management Views

Database Related Dynamic Management Views

Replication Related Dynamic Management Views

Execution Related Dynamic Management Views and Functions

Service Broker Related Dynamic Management Views

Full-Text Search Related Dynamic Management Views

SQL Operating System Related Dynamic Management Views

Index Related Dynamic Management Views and Functions

Transaction Related Dynamic Management Views and Functions

SQL Server 2005 Performance Dashboard Reports

Free report from Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Overview
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.

Reporting Services is not required to be installed to use the Performance Dashboard Reports.

AAP Certification

AAP (Accredited ACH Professional) Certification Exam
Overview:
120 Multiple Choice Questions
Including 20 non-graded pilot questions
3 Hours to Complete


Test Makeup:
General payment overview 10%
Marketing & products 8%
Risk management 15%
Rules and regulations 24%
Operational process/flow 28%
Technical standards/formats 15%

Granularity of Locks

Hierarchical lockable units:

assume set of resources to be locked is organized in a hierarchy, e.g. (database, areas, files, records) as a simple hierarchy. Each node of hierarchy can be locked, and in doing so, one implicity locks all of that node's descendants. There is shared access (S, shared-read access) and exclusive access (X, exclusive read/write access).
In order to lock a subtree rooted at node R in S or X, one must prevent others from obtaining incompatible locks on any ancestor of R. A new mode, "intention mode" is introduced to do this - all ancestors of a node are tagged with intention mode before the node itself is locked. Summary of modes:
null (NL): no access
intention-share (IS): allows requestor to lock descendant nodes in S or IS mode. (does no implicit locking)
intention-exclusive (IX): allows requestor to explicitly lock descendants in X, S, SIX, IX, or IS mode. (does no implicit locking)
share (S): access to node and all descendants without setting further locks. (implicitly sets S locks on all descendants)
share and intention exclusive (SIX): implicitly locks all descendants of node in share mode and allows requestor to explicitly lock descendant nodes in X, SIX, or IX mode. (for finer grained locking)
exclusive (X): exclusive access to node and all descendants. (implicitly sets X locks on all descendants)

To request a node,
before requesting S or IS on node, all ancestor nodes must be held in IX or IS mode.
before requesting X, SIX, or IX on node, all ancestors must be held in SIX or IX mode.
locks should be released either at end of transaction (in any order), or in leaf to root order in the middle of a transaction.

Source from: http://swig.stanford.edu/pub/summaries/database/locks.html