SQL Server 2005 Performance Dashboard Reports - Professional SQL Server 2005 Performance Tuning - page 472

significant amount of time to understand the environment and collect sets of metrics that will eventually
narrowitdowntoafewspecificitems.

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

CatalogViews
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 .

DynamicManagement 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 manage-
ment 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

To get a list of the top 25 most impactful SQL statements by total physical reads recently run on your
instance, run this:

SELECT TOP(25) qs.*,
(SELECT TOP 1
SUBSTRING(st.text,statement_start_offset / 2+1,
( (CASE WHEN statement_end_offset = -1