1 / 25

Interpreting DMV’s & practical uses

Interpreting DMV’s & practical uses. Jannie Muller mullerjannie.wordpress.com. Making sense of – Dynamic Manage Views. And other practical DBA stuff!. Troubleshooting Timeline “Wh ere do we start ?!”. “ sys.configurations VS. sp_configure .”. sys.configurations Degree of parallelism

cecily
Download Presentation

Interpreting DMV’s & practical uses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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!

More Related