EXERCISE 13.2MONITOR DEADLOCKSTHIS EXERCISE WILL DEMONSTRATE HOW TO EN...

4. Double-click the log file.

Alternately, run sp_readerrorlog from a query window to read the

contents of the active error log.

Guide to the DYNAMIC

Management Views (DMVs)

DMVs are almost certainly the single most useful tool for troubleshooting and performance tuning for SQL Server databases. DMVs and DYNAMIC Management Functions (DMFs) provide Administrators with a simple yet powerful insight into the workings of SQL Server and hardware resources (disk, memory, CPU). There were 89 DMVs introduced to SQL Server 2005 and 47 new DMVs released with SQL Server 2008. These can be grouped in two broad types: Server Scoped (require VIEW SERVER STATE permission on the server)

Database Scoped (require VIEW DATABASE STATE permission on the database)DMVs can be categorized as follows (there are others that don’t fit into these categories):Common Language RuntimeCryptographicDatabase MirroringExecutionExtended EventsFilestreamFull-Text SearchIndexI/OQuery NotificationsReplicationService BrokerSQL Server Operating SystemTransactionAs mentioned in the Profiler section earlier in this chapter, there’s a SQL trace running continuously in the background and cycling itself while SQL Server is running. This trace gathers the data used by the DMVs; you should be aware of those that provide snapshot information and others that provide data cumulatively since the last service restart. The trace data is not persisted anywhere within SQL Server, although this is possible (covered in the Performance Data collection, later).DMVs provide information that often could be reached only by querying meta-data or system tables. SQL Server administrators often like a real-time view of current server activity, and they might use the Activity Monitor within Management Studio, or if they have a background with SQL Server 2000 or earlier. They’ll probably use SP_WHO or SP_WHO2—both provide session level activity view. There are, how-ever, a couple of DMVs in Table 13.4 that provide this information plus much more.Table 13.4

Using DMVs to View Current Activity within SQL Server

DYNAMIC Management View

Purpose

sys.dm_exec_requests

Provides information about a request

currently executed by SQL Server

Sys.dm_exec_sessions

An overview of all current sessions (SPIDs)

within SQL Server

It’s easy to SELECT all data within a DMV, however there are great opportuni-ties to write useful queries to interrogate DMVs. One such example is sys.dm_db_index_physcial_stats. This DMV shows the level of index fragmentation. In previous versions this was available from the DBCC SHOWCONTIG command, however this was very intensive for Disk IO, and the results were cumbersome and difficult to manipulate without significant effort. The following example shows a query that categorizes index fragmentation as High (more than 30%), Medium (less than 30%), and Low (less than 5%), ordering the results by greatest fragmentation first since this is where we should pay most attention:

SELECT

OBJECT_NAME(indstat.object_id, indstat.database_id) AS obj_name,

QUOTENAME(sysind.name) [index_name],

CASE

WHEN avg_fragmentation_in_percent < 5 THEN 'LOW'

WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM'

ELSE 'HIGH'

END as frag_level,

indstat.*

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')

AS indstat

INNER JOIN sys.indexes sysind ON indstat.object_id = sysind.object_id AND

indstat.index_id = sysind.index_id

ORDER BY avg_fragmentation_in_percent DESC

The output of the sys.dm_db_index_physcial_stats can be used as the input to an index maintenance job. In this scenario it is possible to build a SQL Server Agent job that takes action based on the output of the sys.dm_db_index_physcial_stats, such as an index reorganize (for indexes with low or medium fragmentation) or rebuilding indexes (for those indexes with heavy fragmentation).Extending the idea of good indexing maintenance, SQL Server can also suggest indexes that would help improve query performance. This information is provided by a group of DMVs, the most useful of which is sys.dm_db_missing_index_details. Using the output from this DMV, we can generate the CREATE INDEX statement to add the new index and improve performance! However, there numerous limitations of the missing indexes feature; for example, it doesn’t consider the cost of maintaining an index and it doesn’t specify an order for columns to be used in the index.There are DMVs such as sys.dm_os_∗ that reflect aspects of the SQL Server Operating System and can be useful barometers for understanding more about SQL Server Internals, system memory consumption, requirements, and the like. The follow-ing query uses the sys.dm_exec_query_stats to provide the top 10 queries consuming most CPU:

SELECT TOP 10

SUM(qrystat.total_worker_time) AS Total_CPU_Time,

SUM(qrystat.execution_count) AS Number_of_Executions,

COUNT(*) as Number_of_Statements,

qrystat.plan_handle

FROM

sys.dm_exec_query_stats qrystat

GROUP BY qrystat.plan_handle

ORDER BY sum(qrystat.total_worker_time) DESC