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

THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) /
2+1)) AS sql_statement
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY total_physical_reads DESC

sys.dm_exec_sql_text is a dynamic management function. It is a table-valued function that accepts one
parameter, sql_handle. By using the CROSS APPLY operator, you can call this TVF with the sql_handle
from each row of sys.dm_exec_query_stats.

These are just a few quick examples of how you can use three DMVs. As of SQL Server 2005 Service
Pack 2, there are over 80 dynamic management views and functions. All of the DMVs follow this naming
convention: sys.dm_ + < descriptive name > . Thus, if you want to find

all DMVs, you can query the catalog view sys.all_objects and filter on this information.

+ < short group identifier > + _

To obtain a comprehensive list of all of the dynamic management views and functions, run this:

SELECT * FROM sys.all_objects
WHERE
[schema_id] = schema_id('sys') AND
[name] LIKE N'dm_%' AND
[is_ms_shipped] = 1
ORDER BY name ASC

For more information on dynamic management views and functions, visit http://msdn2.microsoft
.com/en-us/library/ms188754.aspx .

TheDefault Trace
Many performance problems can be associated with improper configuration, poor schema design, and
bad application architecture. It is common to have a solution that is working perfectly fine and then
out of nowhere performance problems arise. These seemingly spontaneous performance problems are
not random — there are underlying reasons for them. Many times they are caused by human error. For
example, a DBA might change an sp_configure value or drop an index and not fully understand the
ramifications of that action. Another possible reason could be that a scheduled script could fail part
way through, and without proper error handling, it will leave your server in an inconsistent state. The
unfortunate part is that you probably will not realize there is a problem until one of your customers
complains about a business application days later. At this point, the symptoms may be far removed
from the underlying problem with no easy-to-follow paper trail. Unless you have a proactive monitoring
solution, you will have little idea what changed or where to begin your investigation.

Time and time again, SQL Server customer support services helps customers who have not instrumented
any type of proactive monitoring. SQL Server customer support services has developed best practices
and know what data would be useful. So to help future customers, the SQL Server product team has
leveraged customer support services's expertise and created a lightweight proactive monitoring solution:
the default trace. You can think of the default trace as a flight recorder .Itisamechanismtorecordpoten-
tially important events so that you can reference them should there be a problem in the future. Unlike the
DMVs, the data found in the default trace is persistent across server restarts. This means that if your SQL
Server instance is restarted or stopped, all of the useful event information is retained and still available.