Brad M McGehee

Comments

1. Orlando (subscribed) said June 4, 2012, 2:05 pm:

   I use sys.dm_db_index_physical_stats to stay on top of index fragmentation. Granted, my index maintenance job is scheduled to run nightly so I do not use this DMF directly all that often but if it were dropped from the product I would need to find an alternative as soon as possible.

   As for DMVs I use directly I would have to go with sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_exec_sql_text. I use them most out of the many I use to get a quick look at who is doing what at any given moment.

2. Idris Chambas said June 5, 2012, 4:38 am:

   I use sys.dm_os_waiting_tasks to get all waiting tasks that could be long enough to cause concern or unrelated to blocking such as I/O and memory contention, by returning a far more granular level of information.
   When detailed lock-based blocking information is required, I join it to the sys.dm_tran_locks DMV to expose the smoking gun.

3. Phil said June 8, 2012, 4:39 am:

   I often use sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats to look at the state of the database. I record how often the indexes are scan and multiply that number by the number of pages to identify any indexes that are not performing as well as they could.These measures are loaded into the database on a nightly basis so that i can look at the history of each index to see if the increase of scans tie in with a deployment in some way, or an external event such as marketing.

4. Pombeiro (subscribed) said June 8, 2012, 9:19 am:

   I usually use sys.dm_db_index_physical_stats to now the health of my indexes, sys.dm_db_missing_index_* to see if there is any need to create a new index (not all records means a new index  ), sys.dm_exec_query_stats to check if there is any query taking more time to execute than normal, there are others DMV that I use and some of the DMV that I wrote I use in conjunction with other to get the bigger picture of my system.

5. Robert said June 19, 2012, 12:52 pm:

   I am very fond of sys.dm_db_index_physical_stats, sys_dm_db_index_usage_stats, sys.dm_os_performance_counters, sys.dm_os_memory_objects, os_threads, os_wait_stats……

   the list could go on and on. It all depends on what I am trying to do, i guess.

6. Brad said June 20, 2012, 5:33 pm:

   The DMVs I use most are when looking for slow performing queries. The dm_exec_query_stats returns good information on cached query plans. I can select the TOP 20 while joining with dm_exec_sql_text and dm_exec_query_plan to get the SQL statements executed and any graphical or XML query plans associated.

7. Francesco Quaratino said June 26, 2012, 4:41 am:

   My favorite ones are sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats.
   However, instead of using DMVs directly I’d rather query a collection of scripts that simplify their usage, which is called SYS2 DMVs. It’s available on codeplex (http://sys2dmvs.codeplex.com/) and has been written by a SQL Server MVP named Davide Mauri.

8. LadyRuna said June 26, 2012, 9:07 am:

   I frequently use sys.dm_os_performance_counters to check the health of the servers because I can report on those items without firing up SQL profiler (I can also have it pull the data at a specific time by setting up a SQL Agent job that pulls a snapshot of the counters).

9. Svetlana said June 26, 2012, 10:52 am:

   I use the following DMVs the most often:
   sys.dm_db_index_physical_stats
   sys.dm_db_index_usage_stats
   sys.dm_db_missing_index_groups
   sys.dm_db_missing_index_group_stats
   sys.dm_db_missing_index_detailsI use them to find missing indexes, unused indexes, update full-factor based on index usage, find the best candidates for the compression.

10. Ranga said June 26, 2012, 11:12 am:

    I have a script that joins sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_query_plan. I can provide a slow performing stored proc as a filter and see which sql statement(s) is causing the slowness. There are so many different ways you can filter this query. I think it is from here: http://www.sqlmag.com/Article/ArticleID/97761/sql_server_97761.html
    My other favourite ones are sys.dm_os_wait_stats, sys.dm_io_virtual_file_stats these two to find what is causing the performance problems. And the last one is sys.dm_exec_requests – For some tasks you can tell how much percent is complete, like a backup.

11. Thomas Vong said June 26, 2012, 7:34 pm:

    The sys.dm_exec_requests & sys.dm_exec_sql_text are the most often used by us, cos to trouble shooting many difference problem we need to know what is going on during that moment and in normal the DMV result could help us forcus on the right direction to fix the problem.

12. Illiyaz Syed said June 27, 2012, 8:39 am:

    I use many DMVs in my day to day task however I like the below ones and wanted to be handy:

    • sys.dm_exec_requests
    • sys.dm_exec_sql_text
    • sys.dm_os_waiting_tasks
    • sys.dm_io_pending_io_requests
    • sys.dm_db_index_physical_stats

13. John Pertell (subscribed) said June 28, 2012, 7:56 am:

    It kind of depends what I’m looking at. It it’s current performance I usually start with sys.dm_exec_requests then go to sys.dm_exec_sql_text and sys.dm_exec_query_plan for more information. I also use sys.dm_db_index_physical_stats and sys.dm_db_index_usage_stats quite a bit.

14. bradmcgehee said July 2, 2012, 11:34 am:

    Thanks for everyone who participated in the June 2012 Question of the Month. The randomly selected winner this month is LadyRuna, who won a $50 Amazon.com gift certificate and a copy of SQL Monitor 3.1. Don’t forget to enter the July 2012 Question of the Month.