Professional SQL Server® 2005 Performance Tuning

13.1. Supportability

To improve supportability in SQL Server 2005, a set of features was introduced to help you understand what is going on inside your instance:

• Catalog views

• Dynamic management views (DMVs) and functions

• Default trace

13.1.1. Catalog Views

Catalog views are a set of views that return SQL Server catalog information. The catalog views are essentially read-only versions of the system tables that were available in SQL Server 2000.

For more information on catalog views, visit http://msdn2.microsoft.com/en-us/library/ms174365.aspx.

13.1.2. Dynamic Management Views and Functions

Many DBAs complained that when a problem would arise with SQL Server 2000, they were unable to see what was going on inside their instance. With SQL Server 2005, dynamic management views and functions were introduced to expose the data experts' need to identify problems.

In order for SQL Server to run, it needs to keep track of a massive amount of information. It manages connections, sessions, requests, memory allocations, index usage statistics, and so on. Much of this information could be potentially useful in tracking down a performance problem. Dynamic management views (DMVs) expose these internal data structures as virtualized tables. You can use T-SQL to query the DMVs. Here are a few examples:

To get a list of user sessions on your system, run this:

SELECT * FROM sys.dm_exec_sessions
WHERE is_user_process = 1

To get a list of current requests on your system, run this:

SELECT * FROM sys.dm_exec_requests ...