Understanding SQL Server 2008 Performance Measurement Improvements

1. Understanding SQL Server 2008 Performance Measurement Improvements Jeffry A. Schwartz August 19, 2009 Webinar -�SQLRx� jeffrys@isi85.com

2. 2 Overview SQL Server 2000 provided ~11 PerfMon objects and counters Access to some system tables A few table-valued functions SQL Server 2005 introduced numerous long-awaited performance measurement metrics Compared to SQL 2000, SQL 2005 Approximately tripled SQL-specific PerfMon objects and counters Created numerous views and functions (~80 Dynamic Management Views and Functions) that provide very useful and previously unavailable performance information SQL 2008 Added four SQL-specific PerfMon objects and counters Added ~40 Dynamic Management Views and Functions

3. 3 Overview Sheer number of performance views and new metrics can be dizzying New metrics documented in Books Online Numerous articles and books However, much of the literature simply defines the DMVs and metrics, so knowing where to begin is extremely difficult http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

4. 4 Overview Performance analyst must understand Which PerfMon objects and DMVs are pertinent to a particular problem How to interpret numerous metrics provided by new objects and views Most valuable views and metrics

5. 5 Emphasis of Presentation Highlight most useful of the newly available SQL Server-specific PerfMon objects and counters DMVs How to convert the numeric DMV identifiers into understandable text Properly use and interpret most important SQL Server-specific PerfMon objects and counters DMV metrics

6. 6 SQL Server PerfMon Objects Many more useful metrics, e.g., Wait statistics Tempdb (General Statistics) Cursors Transactions .Net Data Provider for SQL Server Deprecated Features (2008)

7. 7 Wait Statistics Object Subset of this information was previously available only via complex queries from several disparate sources Small overlap with Locks object Instances where Counters would normally be Most useful instances Cumulative wait time (ms) Average wait time (ms)

8. 8 Wait Statistics Object Cumulative wait time (ms) instance extremely useful Unfortunately, still only counts values within the last second Sampling every 15-30 seconds usually provides an adequate representation Use sys.dm_os_wait_stats to determine Total wait times by specific type of wait without sampling problems Discussed later in presentation

9. 9 Wait Statistics Object Summarizes very useful counters including Lock waits (total for all lock resources) Log buffer waits Log write waits Memory grant queue waits Network IO waits Non-Page latch waits Page IO latch waits Page latch waits Thread-safe memory object waits Transaction ownership waits

10. 10 Lockable PerfMon Resources

11. 11 Deprecated Features Object Simplifies detection of deprecated feature usage since last SQL Server instance restart Examples of deprecated features/counters '::' function calling syntax Data types: text ntext or image DATABASEPROPERTY DATABASEPROPERTYEX('IsFullTextEnabled') DBCC DBREINDEX DBCC INDEXDEFRAG DBCC SHOWCONTIG NOLOCK or READUNCOMMITTED in UPDATE or DELETE sp_adduser sysindexes sysobjects Use Deprecation Announcement & Deprecation Final Support trace events to determine exact causes

12. 12 Critical General Views Required for converting numeric DMV identifiers into understandable text Many were available under 2000 with slightly different names 2000 sysobjects -> 2005 sys.objects See �Mapping System Tables to System Views (Transact-SQL)� topic in BOL for further info sys.databases Lists all databases and their IDs so proper associations can be made sys.partitions Only way to decode HOBT IDs returned by lock-specific information, e.g. blocked process records sys.configurations Provides information regarding OS and SQL Server configurations sys.dm_os_sys_info Provides information regarding system (can be sampled once or repeatedly) Useful for determining whether hyperthreading is active and accessible memory

13. sys.dm_os_sys_info Useful Columns No parameters required Number of logical CPUs on the system Ratio of number of logical or physical cores exposed by one physical processor package (hyperthread_ratio) Amount of physical memory available Amount of virtual memory available to the process in user mode. This can be used to determine whether SQL Server was started by using a 3-GB switch Date and time SQL Server last started

14. 14 Database-Specific Views Query for each database separately sys.objects Lists all database objects such as tables, views, stored procedures, etc. sys.indexes Lists all indices and their associated table IDs Does not provide row counts as sysindexes does sys.filegroups Lists all file groups and their IDs sys.database_files Lists all physical database files and their IDs

15. 15 Dynamic Management Views and Functions �Designed to give you a window into what's going on inside SQL Server� Two types DMV - Pure view, i.e., no parameters required DMF - Table-valued function, i.e., parameters required Parameters usually specify database, table, index, partition, etc. Provide significant amount of information regarding System Databases Internal workings of SQL Server Performance

16. 16 Dynamic Management Views and Functions Some were possible with complex queries on 2000 When possible Very clumsy to use Required temp tables Required significant understanding of the underlying tables Most DMVs are very simple to use Some still require joins with other DMVs and DMFs Some may lessen need for using SQL Trace

17. 17 Dynamic Management Views and Functions Contain values since last SQL Server instance restart, unless manually reset Perfect for periodic or intermittent sampling Therefore, must compute differences between adjacent or distant samples for same database, file, table, index, etc. Many DMVs can be reset manually using command similar to DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

18. 18 Dynamic Management Views and Functions � General Guidelines Most performance DMVs begin with sys.dm_ Must reconcile numeric IDs with static views that contain textual names Values accumulated from last SQL Server instance restart Sample rates can range from once per minute to a few times per day Must calculate differences between individual sample records Be sure to diff records with same database ID, object ID, and index ID

19. Dynamic Management Views and Functions � General Usage Scenario Method 1 Capture before monitored activities begin and store results into a SQL table or a spreadsheet Execute workload (whether natural production or test) Capture again and compare value differences Load into spreadsheets for further analysis, if necessary Method 2 Capture every <n> minutes and store results in a flat file After workload and capture process complete, load data into SQL tables Use SQL to compare incremental value differences and store results Extract interval data to spreadsheets for further analysis

20. Dynamic Management Views and Functions � Usage Warnings Microsoft assures us these views DO NOT BLOCK (single exception cited below) as usage of older system tables could However, be careful of execution frequency when cross apply or several joins involved because resource consumption could be high Beware of the following two sys.dm_db_index_physical_stats, which examines the data within the physical files and provides extremely valuable data, can block depending upon the option used, but not nearly as badly as DBCC SHOWCONTIG sys.dm_tran_locks can consume large amounts of CPU and can generate huge output when # of locks large Use only if absolutely necessary

21. 21 Dynamic Management Views and Functions Category List

22. 22 Execution-Related Dynamic Management Views and Functions Most commonly used ones sys.dm_exec_query_stats sys.dm_exec_requests sys.dm_exec_sessions sys.dm_exec_sql_text Other less commonly used ones sys.dm_exec_cached_plans sys.dm_exec_connections sys.dm_exec_cursors

23. 23 sys.dm_exec_query_stats DMV Returns aggregate performance statistics for cached and completed query plans Top <n> can be returned for any category Execution count, CPU time, CLR time, and elapsed time Physical and logical reads, Logical writes Activity Monitor on 2008 appears to use this DMV primarily Currently active long-running queries NOT shown via this view View contains one row per query plan Lifetime of a row is tied to the plan itself Can reduce dependence upon SQL statement tracing, but creation_time, last_execution_time, and accumulated values MUST be accounted for

24. 24 sys.dm_exec_query_stats Columns �Starting and ending positions of the query�� �within the text of its batch or persisted object� Pointer to plan, which can be passed to the sys.dm_exec_sql_text DMF Times at which plan was compiled and last executed # of times plan executed since last compilation Total, minimum, maximum, and last amounts consumed by executions since compilation CPU, CLR, and elapsed times, all in microseconds Physical and logical reads, Logical writes

25. 25 sys.dm_exec_query_stats Example Returns top 20 query statements that performed most logical reads select top 20 [sql_handle], creation_time, execution_count, last_execution_time, total_worker_time, total_physical_reads, last_physical_reads, max_physical_reads, total_logical_reads, last_logical_reads, max_logical_reads, total_logical_writes, last_logical_writes, max_logical_writes, total_clr_time, last_clr_time, max_clr_time, total_elapsed_time, last_elapsed_time, max_elapsed_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as statement_text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY total_physical_reads DESC Note how other DMVs and DMFs are used

26. 26 sys.dm_exec_requests Columns Timestamp when request arrived Status of the request Identifies current type of command being processed, e.g., select, insert, update, etc. Plan handles and offsets similar to sys.dm_exec_query_stats If blocked, ID of session blocking request If request currently (or previously) blocked, returns wait type and wait time (ms) # of transactions and result sets open for request CPU, total elapsed times in (ms) Physical and logical reads, Logical writes # of rows that have been returned to the client by this request

27. 27 sys.dm_exec_requests Example Returns batches that contain top 20 currently active query statements that consumed the most CPU select top 20 * from sys.dm_exec_requests ExecReq OUTER APPLY sys.dm_exec_sql_text (ExecReq.sql_handle) ExecSQLText where (session_id > 50 and session_id <> @@spid) and command not like 'WAITFOR%' order by cpu_time desc

28. 28 Index Related Dynamic Management Views and Functions sys.dm_db_index_usage_stats Returns information regarding query code usage of tables and indices, e.g., inserts, updates, deletes, random accesses, and sequential accesses sys.dm_db_index_operational_stats �Returns current low-level I/O, locking, latching, and access method activity for each partition of a table or index in the database� Most commonly useful metric tracks actual forwarded record fetches sys.dm_db_index_physical_stats Execute infrequently because physical files are interrogated and causes some blocking to occur Replaces DBCC SHOWCONTIG

29. 29 sys.dm_db_index_usage_stats DMV Returns One row per index Number of seeks, scans, lookups, and updates for user and system queries Seeks ? random Scans ? sequential Time of last seek, scan, lookup, and update for user and system queries System category Maintenance, e.g., statistics updates User category Insert, update, delete, and select operations Reports user statements, not record counts One insert statement can result in millions of rows being inserted View will report one, not millions Helps determine index and table usage patterns Particularly useful for identifying indices that are never used by queries

30. sys.dm_db_index_usage_stats Example List Rarely-Used Indices For Specific DB With Resolved Names declare @dbid int = db_id() select objectname=object_name(inxusage.object_id), inxusage.object_id, indexname=sysinx.name, sysinx.index_id, user_seeks, user_scans, user_lookups, user_updates from sys.dm_db_index_usage_stats inxusage, sys.indexes sysinx where database_id = @dbid and objectproperty(inxusage.object_id,'IsUserTable') = 1 and sysinx.object_id = inxusage.object_id and sysinx.index_id = inxusage.index_id order by (user_seeks + user_scans + user_lookups + user_updates) asc

31. 31 sys.dm_db_index_operational_ stats DMF One row per index Helpful for Determining how indices are used Identifying contention areas Four parameters required { database_id | NULL | 0 | DEFAULT } (use db_id() for current db) { object_id | NULL | 0 | DEFAULT } { index_id | NULL | -1 | DEFAULT } { partition_number | NULL | 0 | DEFAULT } Use NULL parameters to obtain information for all databases and files select * from sys.dm_db_index_operational_stats (NULL, NULL, NULL, NULL)

32. 32 sys.dm_db_index_operational_ stats Columns Pt 1 Cumulative counts Range and table scans started on index or heap Single row retrievals from index or heap Rows that were fetched through forwarding record Row locks requested Page locks requested Cumulative counts and elapsed times database engine Waited on row lock Waited on page lock Waited because of latch contention Waited on I/O page latch

33. 33 sys.dm_db_index_operational_ stats Columns Pt 2 Cumulative counts Leaf-level insert, delete, update, delayed delete operations Above leaf-level insert, delete, update, delayed delete operations Zeroes ? heap operations Leaf-level page allocations in the index or heap For an index, page allocation corresponds to a page split Page allocations caused by page splits above leaf level Page compression attempt and success counts (2008)

34. 34 sys.dm_db_index_operational_ stats Usage Pt 1 Reports actual operation counts, not user statements One insert statement can result in millions of rows being inserted View will report millions, not one To analyze a common access pattern to the table or index partition leaf_insert_count leaf_delete_count leaf_update_count leaf_ghost_count range_scan_count singleton_lookup_count

35. 35 sys.dm_db_index_operational_ stats Usage Pt 2 To identify latching and locking contention page_latch_wait_count and page_latch_wait_in_ms Indicate whether latch contention exists on index or heap, and significance of contention row_lock_count and page_lock_count Indicate how many times the Database Engine tried to acquire row and page locks row_lock_wait_in_ms and page_lock_wait_in_ms Indicate whether lock contention exists on index or heap, and significance of contention To analyze statistics of physical I/Os on an index or heap partition page_io_latch_wait_count and page_io_latch_wait_in_ms Indicate how many physical I/Os were issued to bring index or heap pages into memory and how much waiting was involved

36. sys.dm_db_index_operational_ stats Time Series Graph

37. sys.dm_db_index_physical_stats DMF Five parameters required { database_id| NULL | 0 | DEFAULT } (use db_id() for current db) { object_id| NULL | 0 | DEFAULT } { index_id| NULL | -1 | DEFAULT } { partition_number| NULL | 0 | DEFAULT } { LIMITED | SAMPLED | DETAILED | NULL | DEFAULT } LIMITED mainly provides fragmentation information Strict locking scheme not used for LIMITED or SAMPLED, so DETAILED mode primary cause of any blocking DETAILED provides much useful information Record count, forwarded record count, min-max-avg record lengths

38. sys.dm_db_index_physical_stats Use NULL parameters to obtain information for all databases and files select * from sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'DETAILED') DETAILED mode Used instead of SAMPLED when index or heap has < 10,000 pages Provides information about non-leaf levels

39. sys.dm_db_index_physical_stats Columns Index_type_desc Index type Index_id, index_level 0 ? heap Index_depth # of index levels Avg_fragmentation_in_percent Logical fragmentation for indexes or extent fragmentation for heaps Indices with values > 30 are candidates for rebuild/reorganization Fragment_count Number of fragments in the leaf level Avg_fragment_size_in_pages Average number of pages per fragment in the leaf level Larger is better Forwarded_record_count # of heap records having forward pointers to another data location

40. 40 I/O Related Dynamic Management Views and Functions sys.dm_io_pending_io_requests sys.dm_io_virtual_file_stats

41. 41 sys.dm_io_virtual_file_stats DMF Returns I/O statistics for data and log files, one row per file Use NULL parameters to obtain information for all databases and files select * from sys.dm_io_virtual_file_stats (NULL, NULL) Equivalent to select * from ::fn_virtualfilestats(-1,-1) on SQL 2000

42. 42 sys.dm_io_virtual_file_stats Columns ID of database ID of file Number of milliseconds since SQL Server instance started (useful for detecting restarts) Number of reads and writes issued against this file Total number of bytes read from and written to this file Total time, in milliseconds, users waited for I/O completions overall, as well as reads and writes specifically Number of disk bytes used by this file

43. sys.dm_io_virtual_file_stats Time Series Graph

44. 44 SQL Server Operating System-Related Dynamic Management Views Abundant views Most frequently used views sys.dm_os_wait_stats sys.dm_os_waiting_tasks sys.dm_os_latch_stats

45. 45 sys.dm_os_wait_stats DMV Useful for diagnosing performance issues with SQL Server Specific queries and batches Expands upon PerfMon Wait Statistics object Returns information about waits encountered by threads in execution 226 possible wait types Several of these are innocuous because they are accumulated as part of normal SQL Server operation Very important to eliminate normal wait types from consideration

46. sys.dm_os_wait_stats Very useful to categorize and group individual wait types to simplify understanding and analysis, e.g., Combine LOGMGR, IMPPROV_IOWAIT, IO_AUDIT_MUTEX, IO_COMPLETION, DISKIO_SUSPEND, IO_AUDIT_MUTEX, IO_COMPLETION, ASYNC_DISKPOOL_LOCK, ASYNC_IO_COMPLETION, REQUEST_DISPENSER_PAUSE, PAGEIOLATCH_DT, PAGEIOLATCH_EX, PAGEIOLATCH_KP, PAGEIOLATCH_NL, PAGEIOLATCH_SH, PAGEIOLATCH_UP into I/O Combine PAGELATCH_DT, PAGELATCH_EX, PAGELATCH_KP, PAGELATCH_NL, PAGELATCH_SH, PAGELATCH_UP into Latches

47. sys.dm_os_wait_stats Time Series Graph

48. 48 sys.dm_os_waiting_tasks DMV Reports information about currently waiting tasks Can be used to construct blocker/waiter lists Important Fields Total wait time (ms) for wait type Name of wait type Task currently holding this resource Blocking task session ID Description of resource being consumed

49. 49 sys.dm_os_latch_stats DMV SQL Server uses numerous kinds of latches (118 documented as of 2008 SP1) Latch times can be helpful in verifying I/O subsystem performance Large latch wait times often indicate poor I/O performance View returns information regarding all latch types Very useful to categorize and group individual wait types to simplify understanding, e.g., BUFFER_POOL_GROW and BUFFER into Buffer ALLOC_CACHE_MANAGER, ALLOC_CREATE_FREESPACE_CACHE, ALLOC_CREATE_RINGBUF, ALLOC_EXTENT_CACHE, and ALLOC_FREESPACE_CACHE into Allocation

50. 50 Transaction Related Dynamic Management Views and Functions Numerous views sys.dm_tran_locks is useful, but can distort system depending upon # of locks being held

51. 51 sys.dm_tran_locks DMV Each row represents currently active request to lock manager for lock that has been granted or is waiting to be granted Result set columns are divided into two main groups � resource and request Resource group describes resource on which the lock request is being made Resource type same as lockable resource Request group describes the lock request

52. 52 Conclusions SQL Server 2008 provides a wealth of performance information Numerous Dynamic Views are essential for uncovering and resolving performance bottlenecks Dynamic Views and Functions are Easy to use Well documented Low overhead with very few exceptions

53. Topics Covered in Future Sessions More in-depth usage and interpretation of DMVs and DMFs Table and index usage and contention detection Missing and unused index detection and analysis Excessive index maintenance for OLTP Procedure cache use and reuse Memory and procedure cache consumption issues Blocking and waiting tasks and statements Query plans with lowest reuse Most frequently recompiled statements Your suggestions�

54. 54 New sys.dm_ DMVs and DMFs in SQL 2008

55. Next Steps Let us analyze your WORST performing SQL Server: Email us from the Contact Us Link at www.sqlrx.com or email Dan Hooper at dhooper@isi85.com. SQLRx will perform a collection of your native SQL and Windows performance data. SQLRx will return a �Lite� Analysis with Recommendations. SQLRx available to assist you with: Optimizing Hardware and SQL Server Environments. Migrations and Upgrades (read about ReplayableTraceTM at www.sqlrx.com). Don�t migrate without using this valuable tool.  Attend next in series and email suggested performance topics of interest to jeffrys@isi85.com. Please take a moment to complete the Survey immediately following today�s presentation.