May 08 – 09 2012, Kongresshaus Berchtesgaden 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 About me IT Experience Principal Architect & Consultant – D Bi A Solutions : Europe ([email protected]) 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 www.packtpub.com www.sqlserver-qa.net @sqlmaster 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. 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 Performance Monitoring Action Monitor Tuning Life Cycle Analysis Collect 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 2012 Login :: User Now let us cover commonly used by DBAs.. Best of the bunch. 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 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 Sys.dm_os_wait_stats Resources Network: async network io (SQL2008+) & networkio (SQL2000). Gets accumulated data…on resources. 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! 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 Sys.dm_exec_query_stats Gets cached query plans When used with sys.dm_exec_sql_text sql_handle is 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. 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 Cardinalities.. From To On/Apply Relationship sys.dm_exec_sessions sys.dm_exec_requests session_id One-to-zero or one-tomany sys.dm_exec_sessions sys.dm_exec_connectio session_id ns One-to-zero or one-tomany sys.dm_exec_sessions sys.dm_tran_session_tr session_id ansactions One-to-zero or one-tomany sys.dm_exec_sessions sys.dm_exec_cursors(se session_id CROSS APPLY One-to-zero or one-tossion_id | 0) OUTER APPLY many sys.dm_exec_sessions sys.dm_db_session_spa session_id ce_usage One-to-one 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 sysprocesses catalog view as sys.dm_exec_sessions, sys.dm_exec_sessions and sys.dm_exec_requests are best ones. 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. 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. sys.dm_db_index_physical_stats What to look for… avg_fragmentation_in_pct for 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 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. 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 sys.dm_db_index_usage_stats Finds index access information Eliminate built-in indexes OBJECTPROPERTY([object_id], 'IsMsShipped') = 0 What to look That index hasn’t been used for: at all! 0 or no values: • user_lookups, • user_seeks, Ouch, index • user_scans (read operations) hurts Value > seeks and scans • user_updates 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 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 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); SYSMON Counters Replaces sys.sysperfinfo catalog 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 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/databaseadministration/brads-sure-dba-checklist/ http://www.sqlserver-qa.net knowledge sharing network. www.packtpub.com www.sqlserver-qa.net @sqlmaster End slide if you need one Any Questions?