Interpreting DMV’s & practical uses

1. Interpreting DMV’s & practical uses Jannie Muller mullerjannie.wordpress.com

2. wellington.sqlpass.org

3. Making sense of – Dynamic Manage Views And other practical DBA stuff!

4. Troubleshooting Timeline“Where do we start ?!” wellington.sqlpass.org

5. “sys.configurationsVS. sp_configure.” • sys.configurations • Degree of parallelism • Server memory • Query memory • Full text crawl bandwith • CPU affinity • Fillfactor

6. “sys.configurationsVS. sp_configure.” Hard affinity. No affinity.

7. “sys.dm_os_sys_info and sys.dm_os_memory_Clerks” • sys.dm_os_sys_info • Number of CPUs(cpu count) • Hyperthreading ratio • System Memory • Restart date • Affinity (scheduler count) • sys.dm_os_memory_clerks • NUMA Nodes

8. “sys.dm_os_buffer_descriptors” • sys.dm_os_buffer_descriptors • Row per item in the buffer pool. • Page types.

9. Troubleshooting Timeline“Where do we start ?!” wellington.sqlpass.org

10. Sp_who2 • sp_who2 ‘active’ • Active requests • Blocking • NUMA • Server start date • Long running processes • Log, mirror, jobs,broker • Gauge workload • Checkpoints • Status • Runnable\waiting • Fulltext • System threads

11. “sys.dm_os_wait_statsandsys.dm_os_waiting_tasks” • sys.dm_os_wait_stats • Historical • Overall wait types • Filter on types • Resource waits • Signal (CPU waits) • sys.dm_os_waiting_tasks • Current • Who is blocking • What is it blocking on • Activity • Bottlenecks select 100.*sum(signal_wait_time_ms)/sum(wait_time_ms), 100.*sum(wait_time_ms-signal_wait_time_ms)/sum(wait_time_ms) fromsys.dm_os_wait_stats -- resource & CPU waits.

12. “sys.os_wait_statsandsys.dm_waiting_tasks” • sys.dm_os_wait_stats • Historical • Overall wait types • Filter on types • Resource waits • Signal (CPU waits) • sys.dm_os_waiting_tasks • Current • Who is blocking • What is it blocking on • Activity • Bottlenecks select 100.*sum(signal_wait_time_ms)/sum(wait_time_ms), 100.*sum(wait_time_ms-signal_wait_time_ms)/sum(wait_time_ms) fromsys.dm_os_wait_stats -- resource & CPU waits.

13. “sys.dm_exec_requestsandsys.dm_exec_query_stats” • sys.dm_exec_query_stats • Historical • Logical \ Physical reads(IO) • Total worker time (CPU) • Execution Count • Text handle. • sys.dm_exec_requests • Current • Who is executing • What is it blocking on • Activity • Text Handle • read \ writes \ cpu • Percentage complete • Schedulers • Wait types

14. “sys.exec_requestsandsys.dm_exec_query_stats” • sys.dm_exec_query_stats • Historical • Logical \ Physical reads(IO) • Total worker time (CPU) • Execution Count • Text handle. • sys.dm_exec_requests • Current • Who is executing • What is it blocking on • Activity • Text Handle

15. “sys.dm_exec_query_stats” When it works, it works well… ..that issue on a Friday afternoon just before Beer’o clock.

16. Butterfly effect… Table details: 200mb, 600k rows. @ 200 executions per second, it makes a difference.

17. “sp_recompile” - The solution

18. Troubleshooting Timeline“Where do we start ?!” wellington.sqlpass.org

19. “sys.dm_io_virtual_file_stats” • Cumulative • read \ Write waits. • Data Vs. log file. SQLVS.Excel

20. “sys.dm_db_index_usage_stats , sys.dm_db_index_operational_stats , sys.dm_db_index_physical_stats” • sys.dm_db_index_usage_stats • Historical • Seek • Scan • Lookup • Update • Last used date • sys.dm_db_index_operational_stats • Waits or latches • Range or singleton lookup • sys.dm_db_index_physical_stats • Fragmentation • Size “You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots.” - BOL

21. “sys.dm_db_index_usage_stats , sys.dm_db_index_operational_stats , sys.dm_db_index_physical_stats” • sys.dm_db_index_usage_stats • Historical • Seek • Scan • Lookup • Update • Last used date • sys.dm_db_index_operational_stats • Waits or latches • Range or singleton lookup • sys.dm_db_index_physical_stats • Fragmentation • Size

22. “sys.dm_db_index_usage_stats , sys.dm_db_index_operational_stats , sys.dm_db_index_physical_stats” • sys.dm_db_index_usage_stats • Historical • Seek • Scan • Lookup • Update • Last used date • sys.dm_db_index_operational_stats • Waits or latches • Range or singleton lookup • sys.dm_db_index_physical_stats • Fragmentation • Size

23. “sys.dm_db_index_usage_stats , sys.dm_db_index_operational_stats , sys.dm_db_index_physical_stats” • sys.dm_db_index_usage_stats • Historical • Seek • Scan • Lookup • Update • Last used date • sys.dm_db_index_operational_stats • Waits or latches • Range or singleton lookup • sys.dm_db_index_physical_stats • Fragmentation • Size “The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable. All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. “ - BOL

24. Troubleshooting Timeline“Where do we start ?!”

25. Thanks! Questions? Please complete an evaluation form for this session …and thanks again to our awesome sponsors!