Apress - SQL Server 2008 Query Performance Tuning Distilled (2009)02 | CHAPTER 2 SYSTEM PERFORMANCE ANALYSIS 19 Dynamic Management Views To get an immediate snapshot of a large amount of data that was formerly available only in Performance Monitor SQL Server now offers the same data internally through a set of dynamic management views DMVs and dynamic management functions DMFs . These are extremely useful mechanisms for capturing a snapshot of the current performance of your system. I ll introduce several of these throughout the book but I ll focus on a few that are the most important for monitoring performance and for establishing a baseline. The view displays the SQL Server counters within a query allowing you to apply the full strength ofT-SQL to the data immediately. For example this simple query will return the current value for Logins sec SELECT cntr_value FROM WHERE OBJECT_NAME MSSQL GF2OO8 General Statistics AND counter_name Logins sec This returns the value ofl5 for my server. For your server you ll need to substitute the appropriate server name in the OBJECT_NAME comparison. There are a large number of DMVs and DMFs that can be used to gather information about the server. Rather than cover them all I ll introduce one more that you will find yourself accessing on a regular basis . This DMV shows an aggregated view of the threads within SQL Server that are waiting on various resources collected since the last time SQL Server was started or the counters were reset. Identifying the types of waits that are occurring within your system is one of the easiest mechanisms to begin identifying the source of your bottlenecks. You can sort the data in various ways but for the first example I ll look at the waits that have the longest current count using this simple query SELECT TOP 10 FROM ORDER BY wait_time_ms DESC Figure 2-2 displays the output. wait_type waitingLtasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms 1 i .
