SQL Server DMVs - Best of the Bunch. Troubleshoot Your Data Platform Like a Pro

1. May 08 – 09 2012, KongresshausBerchtesgaden SQL Server DMVs- Best of the Bunch. Troubleshoot Your Data Platform Like a Pro @sqlmaster Satya SK Jayanty Director & Principal Architect D BI A Solutions

2. About me • IT Experience • Principal Architect & Consultant – D Bi A Solutions : Europe (consulting@dbiasolutions.co.uk) • Been in the IT field over 20+ years (using SQL Server ver.4.2 onwards) • Publications • Author: Microsoft SQL Server 2008 R2 Administration cookbook – Packt Publishers (May 2011) • Co-author for MVP Deep Dives Volume II – Manning Publications (October 2011) • Community Contributions • SQL Server MVP since 2006 • Founder (SQLMaster) & blogs at www.sqlserver-qa.net(SQL Server Knowledge Sharing Network) • Contributing Editor & Moderator - www.sql-server-performance.com[SSP] • Quiz Master & Blogger: www.beyondrelational.com & www.sqlservergeeks.com • Active participation in assorted forums such as SSP, SQL Server Central, MSDN, SQL Server magazine, dbforums etc. www.sqlserver-qa.net@sqlmaster

3. www.packtpub.com www.sqlserver-qa.net @sqlmaster

4. Agenda…. • None…! • Nearly 300 DMVs to cover, not possible with 1 hour presentation. • Highlight most useful ones on the Data platform. • DBA… Developers & Architects with relevant privileges. • Bound on permission levels. • Playing through some code…. • Sharing on what I use on in my regular Consulting engagements.

5. Where to start? • SQL Server gets blame for every problem! • Query running slow, connection timeout, unresponsive? • Remember SP_WHO and SP_WHO2? • Do you need third party tools? • Think Performance/Monitoring…. Think DMVs • SQL Server 2005 onwards… better way ahead • Most of the SQL Server Management Studio actions • DMV’s tour – long way to go • Implement recommended practices

6. Tuning Life Cycle

7. Just a beginning… • DMO – Dynamic Management Objects? • …..no Distributed Management Objects! • DMV – Dynamic Management Views • DMF – Dynamic Management Functions Permissions, if not SA • VIEW SERVER STATE :: VIEW DATABASE STATE • Login :: User • Now let us cover commonly used by DBAs.. Best of the bunch. 2012

8. How do you pick best of the bunch? Divide and Rule! • Pick important problems in your data platform! • Divide problem into 4: CPU, IO, Network & Memory • Additionally query statistics to fine tune • DMVs to offer helping hand on each of them. • Sessions: Transactions • Fragmentation: Index statistics, missing indexes • Statistics: Waits, Query, Index usage

9. Sys.dm_os_wait_stats • Wait stats – waiting for resource. • To watch sum(time) and max(time) • SQL2008 R2 consist 294 & now SQL2012 = 359 • Wait values to watch • LOGMGR_QUEUE • DBMIRROR_WORKER_QUEUE • ONDEMAND_TASK_QUEUE

10. Sys.dm_os_wait_stats • Resources • Network: async network io (SQL2008+) & networkio (SQL2000). • Gets accumulated data…on resources. Application unable to process data quickly enough! If "async network io" are high, review the code and see large result sets are getting transmitted.

11. Still waiting… Sys.dm_os_wait_stats • CXPACKET & SOS_SCHEDULER_YIELD • CPU is suffering • Adding CPUs – rather reduce CPU intensive queries • PAGEIOLATCH_* • Hard disks & Disk IO is a problem • Watch for other IO intensive processes on Windows • Locking waits look for LCK_M_* • Indicates transaction contention • See whether non-SQL Server applications grabbing resources • So about wait tasks, now let’s see waiting tasks!

12. Sys.dm_os_waiting_tasks • Good to get query specific performance problems • Good one to capture blocking on instance • Capture user sessions on query specific • Join with dm_exec_sql_text gives sql_handle to get handful statistics on problem

13. Sys.dm_exec_query_stats • Gets cached query plans • When used with sys.dm_exec_sql_textsql_handleis best to obtain statistics • To find if query is CPU bound then look for total_worker time • Best one to differentiate number of writes and reads on database level • SQL2012 – find queries returning large number of rows • total_rows, min_rows, max_rows and last_rows aggregate row count columns.

14. Sys.dm_exec_sessions • Session information – server scope view • Extra information common criteria compliance enabled, logon statistics displayed: • last_successful_logon • last_unsuccessful_logon • unsuccessful_logons • What’s new SQL2012: open_transaction_count • removing the last of the reasons you ever needed to use: • select * from sys.sysprocesses

15. Cardinalities..

16. Sys.dm_exec_requests • Similar to SP_WHO2 and active requests are resulted • No need DBCC INPUTBUFFER (still lives in SQL2012) • Good to get blocking chain • Find backup & restore operation look into percent_complete column • No need to look into sysprocessescatalog view as • sys.dm_exec_sessions, sys.dm_exec_sessions and sys.dm_exec_requests are best ones.

17. Index usage statistics • Identify the used and unused indexes • How to track about all indexes and heaps on the database? • Best to run number of times (busy and calm) in benchmarking the performance. • …mix and match with missing indexes.

18. sys.dm_db_index_physical_stats • Remember DBCC SHOWCONTIG … forget about it! • Returns fragmentation statistics . • Helpful to obtain how often indexes need to be rebuilt based on how frequently they become fragmented. • Tables with frequent insert/update/delete operations. • Best to run number of times (busy and calm) in benchmarking the performance • …mix and match with missing indexes.

19. sys.dm_db_index_physical_stats • What to look for… • avg_fragmentation_in_pctfor logical for indexes and extent fragmentation for heaps. • Shows information index_id=0, HEAP & index_id=1, Clustered Index • Based on the fragmentation level schedule the REBUILD & REORG operations. • ALTER INDEX … REORGANIZE • ALTER INDEX … REBUILD • ALTER INDEX … REBUILD ALL Good to use SORT_IN_TEMPDB & improve the contiguity of index extents. OFF by default

20. Still on… • To use: Select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(‘dbo.person'),NULL,NULL,NULL) • Scan depth used: • LIMITED – faster and default • SAMPLED – 1% of leaf pages • DETAILED – heavy IO operation • Statistics are fine, how about obtain missing indexes information.

21. Missing Indexes feature • To start with: • sys.dm_db_missing_index_columns • sys.dm_db_missing_index_group_stats • sys.dm_db_missing_index_groups • sys.dm_db_missing_index_details • Good with INCLUDE columns -- suggestions • Only non-clustered indexes (no spatial indexes) • Statistics are retained until index is rebuilt or service is restarted. • Use it wisely…. Don’t over use

22. sys.dm_db_index_usage_stats • Finds index access information • Eliminate built-in indexes - OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 • What to look for: • 0 or no values: • user_lookups, • user_seeks, • user_scans(read operations) • Value > seeks and scans • user_updates That index hasn’t been used at all! • Ouch, index hurts

23. Other handy ones… • Lock pages enabled • sys.dm_os_process_memory • Long running SQLCLR queries (type=E_TYPE_USER) • sys.dm_os_workers & sys.dm_clr_tasks • FT catalogs population • sys.dm_fts_active_catalogs & sys.dm_fts_index_population • AUTO page repair history • sys.dm_db_mirroring_auto_page_repair

24. What’s new and enhanced …SQL2012 • sys.dm_server_memory_dumps • Memory dump information • sys.dm_exec_query_stats • 4 new columns to troubleshoot long running queries • sys.dm_server_services • Use to report status information about services • sys.dm_server_registry • Returns 1 row per registry key

25. What else…. • sys.dm_os_windows_info • Returns operating system information • sys.dm_os_volume_stats •  operating system volume (directory) on which the specified databases and files are stored  • Operating system volume (directory) • sys.dm_os_volume_stats mix with sys.database_files to obtain total space & available space for DB files: SELECT database_id, f.file_id, volume_mount_point, total_bytes, available_bytes FROM sys.database_files AS f CROSS APPLY sys.dm_os_volume_stats(DB_ID(f.name), f.file_id);

26. SYSMON Counters Replaces sys.sysperfinfocatalog view. • If the installation instance of SQL Server fails to display the performance counters of the Windows operating system, • sys.os_performance_counters • Over 250+ “SQL Server:” specific counters (SQL2008 R2 & SQL2012) • Good to get PERFMON counters information (instantly) • VIEW SERVER STATE is necessary • Find deprecated features used in code • [sqlinstance]:Deprecated Features object

27. Words of advice…. Make sure you perform B&B • Baseline & Benchmarking • Systems resource usage • Capacity planning • Read the free e-book, How to Become an Exceptional DBA • DBA checklist • http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/ • http://www.sqlserver-qa.net knowledge sharing network.

28. www.packtpub.com www.sqlserver-qa.net @sqlmaster

29. End slide if you need oneAny Questions?