MarketView Database Tuning Best Practice

1. MarketView Database Tuning Best Practice StarCite Engineering May 13, 2009

2. Agenda • Identify the hot-spot • Long Running DML • Inefficient Indexes • More dig into (tempdb, disk usage, etc) • Improve the Long Running DML • Improve the Inefficient indexes • TempDB improvement • Production Data • Some hot topics • Further topics

3. Identify the hot-spot - Long Running DML • Powerful DMV & DMF Dynamic Management View & Dynamic Management Function

4. Long Running DML Identify top 10 query by CPU cost

5. Long Running DML Identify top 10 query by elapsed time

6. Long Running DML MKV tools can identify to specific table SELECT TOP 50 qs.total_worker_time/qs.execution_count as [Avg CPU Time], qt.text as query_text, qt.dbid, qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt Where qt.text like '%table_name%' ORDER BY [Avg CPU Time] DESC

7. Inefficient indexes Identify the missing Index

8. Inefficient indexes Identify the most costly unuage index

9. Dig into • TempDB • Data file fragmentation • Log file fragmentation • Disk usage

10. TempDB • TempDB Provide it a best environment! Temp Store Procedure Temp Table Put it on faster disk(raid5). Provide separated disk for tempdb, and avoid the working files sit with os files. When you have multiple CPUs may increase tempdb file numbers can help. Increase tempdb initial size it depends? Table variables Row version Internal Table Cursor

11. TempDB (Space Used By)

12. TempDB (Space Used By) Total Space Usage Select SUM (user_object_reserved_page_count)*8 as user_objects_kb, SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, SUM (version_store_reserved_page_count)*8 as version_store_kb, SUM (unallocated_extent_page_count)*8 as freespace_kb From sys.dm_db_file_space_usage Where database_id = 2 Here is one sample output (with space in KBs). user_objets_kb internal_objects_kb version_store_kb freespace_kb ---------------- -------------------- ------------------ ------------ 8736 128 64 448

13. TempDB Some of the common issues with tempdb are as follows: • Running out of storage space in tempdb. • Queries that run slowly due to the I/O bottleneck in tempdb. • Excessive DDL operations leading to a bottleneck in the system tables. • Allocation contention.

14. TempDB look for multiple counters to cross check the validity of your findings. • PhysicalDisk Object: Avg. Disk Queue Lengthrepresents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck. • Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number Less than 10 ms - very good Between 10 - 20 ms - okay Between 20 - 50 ms - slow, needs attention Greater than 50 ms – Serious I/O bottleneck • Avg. Disk Sec/Writeis the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet. • Physical Disk: %Disk Timeis the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck. • Avg. Disk Reads/Secis the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity. • Avg. Disk Writes/Secis the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

15. TempDB When you have identified an I/O bottleneck, you can address it by doing one or more of the following: • Check the memory configuration of SQL Server. If SQL Server has been configured with insufficient memory, it will incur more I/O overhead. • Increase I/O bandwidth. * Add more physical drives to the current disk arrays and/or replace your current disks with faster drives. This helps to boost both read and write access times. But don't add more drives to the array than your I/O controller can support. * Add faster or additional I/O controllers. Consider adding more cache (if possible) to your current controllers. • Examine execution plans and see which plans lead to more I/O being consume. It is possible that a better plan (for example, index) can minimize I/O. If there are missing indexes, you may want to run Database Engine Tuning Advisor to find missing indexes

16. TempDB 1. The following DMV query can be used to find which batches/requests are generating the most I/O. select top 5 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc 2. You can also identify I/O bottlenecks by examining the latch waits. These latch waits account for the physical I/O waits when a page is accessed for reading or writing and the page is not available in the buffer pool. Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type

17. TempDB • Put it on faster disk(raid5). • Provide seperated disk for tempdb • Increase tempdb file number. A suggestion number is 1 processer 1 file • Increase tempdb initial size

18. Disk Term • Hard Drive: HDD or disk, non-volatile storage device which stores digitally encoded data on rapidly rotating platters with magnetic surfaces. • Platter: Two or more per HDD. • Cylinder: All tracks which can be accessed by the heads while the access arms are stationary. • Track: Concentric rings on a platter. • Sector • 1. Wedge-shaped sections of a platter, classically 64 sectors per track. • 2. Bits which lie at the intersection of a track & a sector, usually 512 bytes. (This is a standard definition & one we will use most.) • File Allocation Unit: Some integral number of 512 byte sectors which are treated as a unit by the OS.

19. Disk Issue Implicit • Output of diskpar (Windows 2000 Resource Kit) (Above Windows 2000 should use diskpart.exe) C:\>diskpar -i 0 ---- Drive 0 Geometry Infomation ---- Cylinders = 12161 TracksPerCylinder = 255 SectorsPerTrack = 63 BytesPerSector = 512 DiskSize = 100027630080 (Bytes) = 95393 (MB) ---- Drive Partition 0 Infomation ---- StatringOffset = 32256 PartitionLength = 49319424 HiddenSectors = 63 PartitionNumber = 1 PartitionType = de • By default, for years Windows instantiated 63 hidden sectors in all new partitions. • These hidden sectors contain the master boot record (MBR). • Note the typos: • “StatringOffset” instead of “StartingOffset”. • “Infomation” instead of “Information”

20. Disk Issue Implicit

21. Disk Issue Implicit (File Allocation Unit) • fsutil fsinfo ntfsinfo c: • Output for default NTFS format C:\>fsutil fsinfo ntfsinfo c: NTFS Volume Serial Number : 0x3a16ff9d16ff5879 Version : 3.1 Number Sectors : 0x000000000a2397ff Total Clusters : 0x00000000014472ff Free Clusters : 0x000000000025b76a Total Reserved : 0x00000000000051f0 Bytes Per Sector : 512 Bytes Per Cluster : 4096 Bytes Per FileRecord Segment : 1024 Clusters Per FileRecord Segment : 0 Mft Valid Data Length : 0x0000000007c90000 ...

22. Disk Issue Implicit (File Allocation Unit) Extent 64KB Data Page 8KB …… So we need change the file allocation unit to 64KB that could reduce I/O significantly.

23. Disk file fragmentation • Two types of disk Reading : • sequential reading (Index seek) • Random reading(Index scan) • Tuning: • Increase initial data file size • Set correct data file increasing size • Schedulable clean up file fragmentation File A in DataBase A File A in DataBase A Unused space File B in DataBase B File B in DataBase B

24. Log file fragmentation • More log file cause data fragmentation • Small size log file. • Less log file (VLF). A suggestion number is 5 • Schedule backup log file.

25. Improve the Long Running DML

26. Improve the Long Running DML Select dbuser1_.id as id190_, … … From DB_SITE_USERS dbsiteuser0_, DB_USERS dbuser1_, SITE_MODULES sitemodule2_, DB_WORKSHOPS dbworkgrou3_, DB_USER_ROLES dbuserrole4_, DB_ROLE_MODULES dbrolemodu5_, DB_ROLES dbrole6_, WORKGROUP_RESOURCES workgroupr7_, RESOURCES resource8_, RESOURCE_ITEMS resourceit9_ Where dbsiteuser0_.SITE_ID=35 AND dbsiteuser0_.DELETED_FLAG=0 AND dbuser1_.USER_ID=dbsiteuser0_.USER_ID AND dbuser1_.DELETED_FLAG=0 AND sitemodule2_.SITE_ID=35 AND sitemodule2_.MODULE_ID=17 AND sitemodule2_.DELETED_FLAG=0 AND dbworkgrou3_.SITE_ID=35 AND dbworkgrou3_.DELETED_FLAG=0 AND dbrole6_.SITE_ID=35 AND dbrole6_.ROLE_ID=dbrolemodu5_.ROLE_ID AND ( dbrole6_.NAME in ( 'RFP EscalatiON','BrAND Manager','Executive Management' ) ) AND dbrole6_.DELETED_FLAG=0 AND dbuserrole4_.WORKGROUP_ID=dbworkgrou3_.WORKGROUP_ID AND dbuserrole4_.SITE_USER_ID=dbsiteuser0_.SITE_USER_ID AND dbuserrole4_.DELETED_FLAG=0 AND dbrolemodu5_.ROLE_ID=dbuserrole4_.ROLE_ID AND dbrolemodu5_.ACTIONS % 8-4>=0 AND dbrolemodu5_.DELETED_FLAG=0 AND workgroupr7_.WORKGROUP_ID=dbuserrole4_.WORKGROUP_ID AND workgroupr7_.DELETED_FLAG=0 AND resourceit9_.RESOURCE_ID=workgroupr7_.RESOURCE_ID AND resourceit9_.DELETED_FLAG=0 AND resource8_.id=resourceit9_.RESOURCE_ID AND resource8_.DELETED_FLAG=0 AND ((resourceit9_.RESOURCE_ITEM_XREF_ID=1381 or resourceit9_.ALL_RESOURCE_ITEM_XREFS=1 ) AND resourceit9_.RESOURCE_ITEM_TYPE_ID=1 or ( resourceit9_.RESOURCE_ITEM_XREF_ID in (90) or resourceit9_.ALL_RESOURCE_ITEM_XREFS=1) AND resourceit9_.RESOURCE_ITEM_TYPE_ID=2 )

27. Improve the Long Running DML • SELECT u.user_name as userName, u.email as email • FROM RESOURCE_ITEMS as ri with(nolock) • JOIN WORKGROUP_RESOURCES as wr with(nolock) • ON ri.deleted_flag=0 AND wr.deleted_flag = 0 • AND ri.resource_item_type_id IN( 1,2) • AND ri.resource_item_xref_id IN(1381, 90) • AND ri.site_id=35 • AND ri.resource_id = wr.resource_id • JOIN DB_USER_ROLES ur with (nolock) • ON ur.workgroup_id = wr.workgroup_id AND ur.deleted_flag = 0 • JOIN DB_ROLES r with (nolock) • ON r.role_id = ur.role_id • JOIN DB_ROLE_MODULES rm with (nolock) • ON r.deleted_flag = 0 AND rm.deleted_flag =0 AND r.role_id = rm.role_id • AND r.role_id in ('35\18','35\81','35\82') AND r.site_id = 35 • AND (rm.MODULE_ID=17 or all_modules = 1) AND (rm.ACTIONS % 8-4>=0) • JOIN DB_SITE_USERS su ON su.site_user_id = ur.site_user_id AND su.deleted_flag = 0 • JOIN DB_USERS u ON u.user_id = su.user_id AND u.deleted_flag = 0

28. Improve the Long Running DML How about the index?

29. Improve the Long Running DML

30. Improve the Long Running DML • How can we improve the long running reading query? • Restrict the size of input. The leftmost table selection is most important factor for the performance of query. • Reduce the size of output. Only read you need read data from the output tables. • Try you best not involve the String, Date comparison or like. If you must, please make sure correct use and avoid the index scan happen. • Read the query plan generated by sql server. For small or medium size query try you best use “INNER LOOP” or “HASH MATCH”. For large size query try you best use “MERGE JOIN”. • If customer of your system can accept some level data inconsistency, please put with(nolock) table hint that will reduce read lock contention. If customer of your system cannot accept data inconsistency and your sql server version is 2005, you also can try SNAPSHOT ISOLATION level (VERSION DATA) but that will come without free (each query will occupy more memories than normal other isolation level). • Join type need be carefully considered. Avoid “OUTTER JOIN” and “RIGHT JOIN”. • Performance killer “Table Spooler”, “Stream Aggregation”. Count(*) will impose the “Table Spooler” and “Stream Aggregation”, try you best use other way for implementation. Holy Grail Algorithm • MSDN will be your best assistance.

31. Improve the Long Running DML • How can we improve the long running create, update or delete query? • Restrict the size of data you want to change in the table. Try you best control data changes within the row level, otherwise sql server may escalate the lock level from row level to table level or schema level. With(rowlock) table hint is not 100 percent true. • Large data change need to be batch mode (within one transaction). Multiple transactions data changes will impose contention and even further dead lock. • Change only your need change data. Because that possibly will not reflect changes to some NONE-CLUSTER indexes. • Avoid concurrent long running changing transactions. If really need that, separate them into small transactions (NESTED TRANSACTION).

32. Improve the Long Running DML MKV performance bottleneck due to the long running transactions. Application transaction enlist Db transaction Update table A Table A enlist Sql server 2005 1 enlist * Update table B Db transaction Table B 1 * Db transaction Update table C Table C FINE-GRAINED TRANSACTION or COARSE-GARINED TRANSACTION ?

33. Improve the Inefficient indexes • Identify the slowness reason from indexes perspective • Identify the slowness reason from contention perspective • Reduce the contention of update by unused indexes • Apply the trace configuration for diagnostic purpose if deadlock found. • Group review & Performance testing.

34. Dynamic Management Views and Functions Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. • Index Related Dynamic Management Views and Functions • sys.dm_db_index_operational_stats • sys.dm_db_index_usage_stats • sys.dm_db_missing_index_details • sys.dm_db_missing_index_groups • sys.dm_db_index_physical_stats • sys.dm_db_missing_index_columns • sys.dm_db_missing_index_group_stats

35. About the Missing Indexes Feature - Indexes perspective The missing indexes feature uses dynamic management objects and Showplan to provide information about missing indexes that could enhance SQL Server query performance. • Dynamic Management Objects • sys.dm_db_missing_index_group_stats • sys.dm_db_missing_index_groups • sys.dm_db_missing_index_details • sys.dm_db_missing_index_columns Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes. Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group. Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index. Returns information about the database table columns that are missing an index.

36. Examples - Indexes perspective • Find the 10 missing indexes with the highest anticipated improvement for user queries select top 10 d.*, s.avg_total_user_cost, s.avg_user_impact, s.last_user_seek, s.unique_compiles from sys.dm_db_missing_index_group_stats s, sys.dm_db_missing_index_groups g, sys.dm_db_missing_index_details d where s.group_handle = g.index_group_handle and d.index_handle = g.index_handle order by s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans) desc

37. Examples - Indexes perspective • Suggested index columns & usagesSELECT mig.*, statement AS table_name, column_id, column_name, column_usage FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle ORDER BY mig.index_group_handle, mig.index_handle, column_id;

38. Indexes perspective - Improve • The result set of sys.dm_db_missing_index_details returns this information in the equality_columns, inequality_columns, and included_columns columns. • The result set returned by sys.dm_db_missing_index_columns returns this information in its column_usage column. • Create Index by Using Missing Index Information • List the equality columns first (leftmost in the column list). • List the inequality columns after the equality columns (to the right of equality columns listed). • List the include columns in the INCLUDE clause of the CREATE INDEX statement. • To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first.

39. Limitations of the Missing Indexes Feature • It is not intended to fine tune an indexing configuration. • It cannot gather statistics for more than 500 missing index groups. • It does not specify an order for columns to be used in an index. • For queries involving only inequality predicates, it returns less accurate cost information. • It reports only include columns for some queries, so index key columns must be manually selected. • It returns only raw information about columns on which indexes might be missing. • It does not suggest filtered indexes. • It can return different costs for the same missing index group that appears multiple times in XML Showplans. • It does not consider trivial query plans.

40. Index Perspective – Balance (1) declare @dbid int select @dbid = db_id() select 'object' = object_name(object_id),index_id ,'user reads' = user_seeks + user_scans + user_lookups ,'system reads' = system_seeks + system_scans + system_lookups ,'user writes' = user_updates ,'system writes' = system_updates from sys.dm_db_index_usage_stats where objectproperty(object_id,'IsUserTable') = 1 and database_id = @dbid order by 'user reads' desc

41. Index Perspective – Balance (2) declare @dbid int select 'object'=object_name(o.object_id), o.index_id , 'usage_reads'=user_seeks + user_scans + user_lookups , 'operational_reads'=range_scan_count + singleton_lookup_count , range_scan_count, singleton_lookup_count, 'usage writes' = user_updates , 'operational_leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count , leaf_insert_count,leaf_update_count,leaf_delete_count , 'operational_leaf_page_splits' = leaf_allocation_count , 'operational_nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count , 'operational_nonleaf_page_splits' = nonleaf_allocation_count from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) o ,sys.dm_db_index_usage_stats u where objectproperty(o.object_id,'IsUserTable') = 1 and u.object_id = o.object_id and u.index_id = o.index_id order by operational_reads desc, operational_leaf_writes, operational_nonleaf_writes

42. Index Perspective – Statistics • Slow down the performance: INSERTs, UPDATEs, and DELETEs • Whether or not the indexes will be used, decided by Query Optimizer. • Selectivity: the percentage of rows in a table that are returned by a query • Index statistics – Used by Query Optimizer for selectivity info • Density (Conceptually reverse to selectivity) • DBCC SHOW_STATISTICS (table_name, index_name)

43. Contention perspective - List Indexes with the Most Contention • sys.dm_db_index_operational_stats • sys.indexes USE G3ProdMKV_MarketView declare @dbid int select top 10 dbid=database_id, objectname=object_name(s.object_id),indexname=i.name, i.index_id,partition_number, row_lock_count,row_lock_wait_count,row_lock_wait_in_ms, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)), [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2)) from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s, sys.indexes i where objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id order by row_lock_wait_count desc

44. Contention perspective - Highest Average CPU Time and Highest Execution Counts The following sample scripts lists the top 10 statements by average CPU time. • sys.dm_exec_query_stats • sys.dm_exec_sql_text(sql_handle) SELECT TOP 10 qs.total_worker_time/qs.execution_count as [Avg CPU Time],qs.execution_count, SUBSTRING(qt.text,qs.statement_start_offset/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text, qt.dbid, dbname=db_name(qt.dbid), qt.objectid FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY [Avg CPU Time] DESC

45. Contention perspective - Contention of update by unused indexes • sys.indexes • sys.objects • sys.dm_db_index_usage_stats select top 10 object_name(i.object_id), i.name from sys.indexes i, sys.objects o where i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id and database_id = db_id()) and o.type = 'U' and o.object_id = i.object_id order by object_name(i.object_id) asc

46. Contention perspective – Deadlock diagnostic • Enables the specified trace flags. • Syntax • DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]trace# - Is the number of the trace flag to turn on.n - Is a placeholder that indicates multiple trace flags can be specified.-1 - Switches on the specified trace flags globally.WITH NO_INFOMSGS - Suppresses all informational messages. • Examples • DBCC TRACEON (1222); • DBCC TRACEON (1222, -1); • DBCC TRACEON (1222, 3206, -1);

47. Impact by indexes • Whenever you add one index into the table. Should view the impact to the INSERT, UPDATE and DELETE by the index you added . Set statistics time on Set statistics io on Updated sql before/after Set statistics time off Set statistics io off If updated sql slow than before, you should know the table read/write ratio. If read most, it should be acceptable. But if write most, it definitely impose bad impact overall.

48. TempDB improvement • Tempdb is often overlooked when finding out problems for performance issues with SQLServer • Location Separate tempdb from the user databases and SQL Server binaries and place the tempdb on faster disk array. alter database tempdb modify file(name=tempdev, filename='D:\sqldata\tempdb.mdf') alter database tempdb modify file(name=templog, filename='D:\sqldata\templog.ldf')

49. TempDB improvement • DB Size • Default: 8 MB • Query the current size SELECT name AS FileName, size*1.0/128 AS FileSizeinMB, CASE max_size WHEN 0 THEN 'Autogrowth is off.' WHEN -1 THEN 'Autogrowth is on.' ELSE 'Log file will grow to a maximum size of 2 TB.' END, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'Size is fixed and will not grow.' WHEN growth > 0 AND is_percent_growth = 0 THEN 'Growth value is in 8-KB pages.' ELSE 'Growth value is a percentage.' END FROM tempdb.sys.database_files;

50. TempDB improvement • Update the size alter database tempdb modify file(name=tempdev, filename='D:\sqldata\tempdb.mdf', size=XXXMB)