mkv_db_tuning.ppt - scm0329-ag by yaofenjin


									MarketView Database Tuning Best Practice

                       StarCite Engineering
                                May 13, 2009
                     CONFIDENTIAL   9-May-13   1

• 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

                                                 CONFIDENTIAL   9-May-13   2
  Identify the hot-spot - Long Running DML

• Powerful DMV & DMF
 Dynamic Management View & Dynamic Management Function

                                             CONFIDENTIAL   9-May-13   3
Long Running DML
    Identify top 10 query by CPU cost

                                        CONFIDENTIAL   9-May-13   4
Long Running DML
     Identify top 10 query by elapsed time

                                       CONFIDENTIAL   9-May-13   5
Long Running DML
     MKV tools can identify to specific table

 qs.total_worker_time/qs.execution_count as [Avg CPU Time],
 qt.text as query_text,
 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%'

                                                              CONFIDENTIAL   9-May-13   6
Inefficient indexes
        Identify the missing Index

                                     CONFIDENTIAL   9-May-13   7
Inefficient indexes
        Identify the most costly unuage index

                                        CONFIDENTIAL   9-May-13   8
  Dig into

• TempDB
• Data file fragmentation
• Log file fragmentation
• Disk usage

                            CONFIDENTIAL   9-May-13   9

Provide it a best environment!
        Temp Table                 Temp Store

Put it on faster disk(raid5).
                                             Table variables
   Row version
                        • TempDB
Provide separated disk for tempdb, and avoid
 the working files sit with os files.
       Internal Table
When you have multiple CPUs may increase

 tempdb file numbers can help.

Increase tempdb initial size it depends?
                                                 CONFIDENTIAL   9-May-13   10
TempDB (Space Used By)
 User objects      These are explicitly created by user sessions and are tracked in system catalog. They
                      include the following:
                    Table and index.
                    Global temporary table (##t1) and index.
                    Local temporary table (#t1) and index.
                    Session scoped.
                    Stored procedure scoped in which it was created.
                    Table variable (@t1).
                    Session scoped.
                    Stored procedure scoped in which it was created.

Internal objects   These are statement scoped objects that are created and destroyed by SQL Server to
                      process queries. These are not tracked in the system catalog. They include the
                    Work file (hash join)
                    Sort run
                    Work table (cursor, spool and temporary large object data type (LOB) storage)
                   As an optimization, when a work table is dropped, one IAM page and an extent is saved
                      to be used with a new work table.
                   There are two exceptions; the temporary LOB storage is batch scoped and cursor
                      worktable is session scoped.

 Version Store     This is used for storing row versions. MARS, online index, triggers and snapshot-based
                           isolation levels are based on row versioning. This is new in SQL Server 2005.

  Free Space                     This represents the disk space that is available in tempdb.

                                                                          CONFIDENTIAL         9-May-13     11
TempDB (Space Used By)
 User objects


                              Total Space Usage
Internal objects

                      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).
 Version Store     user_objets_kb internal_objects_kb      version_store_kb      freespace_kb
                   ---------------- --------------------    ------------------    ------------
                   8736              128                     64                   448
  Free Space

                                                                      CONFIDENTIAL         9-May-13   12
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.

                                                      CONFIDENTIAL   9-May-13   13
look for multiple counters to cross check the validity of your findings.
•    PhysicalDisk Object: Avg. Disk Queue Length represents 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/Write is 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 Time is 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/Sec is 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/Sec is 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.

                                                                                              CONFIDENTIAL            9-May-13   14
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

                                                                     CONFIDENTIAL     9-May-13     15
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

                                                                            CONFIDENTIAL       9-May-13      16
1. Put it on faster disk(raid5).
2. Provide seperated disk for tempdb
3. Increase tempdb file number. A suggestion number is 1 processer 1 file
4. Increase tempdb initial size

                                                    CONFIDENTIAL   9-May-13   17
    Disk Term
•   Hard Drive: HDD or disk, non-volatile storage device which stores
    digitally encoded data on rapidly rotating platters with magnetic
•   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.

                                                              CONFIDENTIAL   9-May-13    18
    Disk Issue Implicit
•   Output of diskpar (Windows 2000 Resource Kit) (Above Windows 2000 should use
             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”

                                                                            CONFIDENTIAL       9-May-13   19
Disk Issue Implicit

                      CONFIDENTIAL   9-May-13   20
   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

                                                                  CONFIDENTIAL   9-May-13   21
      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.

                                                                                 CONFIDENTIAL   9-May-13   22
     Disk file fragmentation

                File A in                File A in        Unused space
                DataBase A               DataBase A

•    Two types of disk Reading :
1.   sequential reading (Index seek)
2.   Random reading(Index scan)
                      File B in                  File B in
                     DataBase B                  DataBase B

•    Tuning:
1.   Increase initial data file size
2.   Set correct data file increasing size
3.   Schedulable clean up file fragmentation

                                                                         CONFIDENTIAL   9-May-13   23
   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.

                                                  CONFIDENTIAL   9-May-13   24
Improve the Long Running DML

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

                                                                                   CONFIDENTIAL           9-May-13   26
     Improve the Long Running DML
•   SELECT u.user_name as userName, 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

                                                                                   CONFIDENTIAL   9-May-13   27
Improve the Long Running DML

                   How about the

                                   CONFIDENTIAL   9-May-13   28
Improve the Long Running DML

                               CONFIDENTIAL   9-May-13   29
     Improve the Long Running DML
•    How can we improve the long running reading query?
1.   Restrict the size of input. The leftmost table selection is most important factor for the
     performance of query.
2.   Reduce the size of output. Only read you need read data from the output tables.
3.   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.
4.   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
5.   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).
6.   Join type need be carefully considered. Avoid “OUTTER JOIN” and “RIGHT JOIN”.
7.   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
8.   MSDN will be your best assistance.

                                                                        CONFIDENTIAL     9-May-13   30
    Improve the Long Running DML
•   How can we improve the long running create, update or delete query?
1. 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.
2. Large data change need to be batch mode (within one transaction).
   Multiple transactions data changes will impose contention and even
   further dead lock.
3. Change only your need change data. Because that possibly will not
   reflect changes to some NONE-CLUSTER indexes.
4. Avoid concurrent long running changing transactions. If really need that,
   separate them into small transactions (NESTED TRANSACTION).

                                                       CONFIDENTIAL   9-May-13   31
       Improve the Long Running DML
   MKV performance bottleneck due to the long running transactions.

Application    enlist
                           Db transaction       Update table A

                 enlist                                                Table A
                              Sql server 2005

              enlist                            Update table B              *
                          Db transaction                               Table B
                          Db transaction        Update table C         Table C


                                                                 CONFIDENTIAL    9-May-13   32
  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.

                                                      CONFIDENTIAL   9-May-13   33
  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

                                                        CONFIDENTIAL   9-May-13   34
  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                          detailed information
                                                     Returns summary about a
    – sys.dm_db_missing_index_columns               specific missing index; for
                                                    information aboutmissing
                                                    the database of missing
                                                    about a grouptable columns
                                                    index groups, foran the name
                                                    that are it returns index.
                                                    example,such as the group
                                                    indexes,missing example,
                                                    the performancethe table of
                                                    and identifier of
                                                    identifier and the identifiers
                                                    where the indexes that are
                                                    all missingindex is could be
                                                    improvements that missing,
                                                    and the columns and column
                                                    contained implementing
                                                    gained by in that group. a
                                                    types that should make up
                                                    specific group of missing
                                                    the missing index.

                                                        CONFIDENTIAL        9-May-13   35
   Examples - Indexes perspective

• Find the 10 missing indexes with the highest anticipated improvement for user
     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

                                                                          CONFIDENTIAL    9-May-13   36
  Examples - Indexes perspective

• Suggested index columns & usages
   SELECT 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 =
    ORDER BY mig.index_group_handle, mig.index_handle, column_id;

                                                                    CONFIDENTIAL   9-May-13   37
  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
    – To determine an effective order for the equality columns, order them
      based on their selectivity; that is, list the most selective columns first.

                                                              CONFIDENTIAL     9-May-13   38
   Limitations of the Missing Indexes
• 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
• 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.

                                                                     CONFIDENTIAL    9-May-13   39
   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

                                                         CONFIDENTIAL   9-May-13   40
    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

                                                                                       CONFIDENTIAL   9-May-13   41
  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)

                                                                  CONFIDENTIAL       9-May-13   42
  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),, i.index_id,partition_number,
    [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
    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

                                                                           CONFIDENTIAL     9-May-13   43
 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)
   qs.total_worker_time/qs.execution_count as [Avg CPU Time],qs.execution_count,
   (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

                                                                      CONFIDENTIAL   9-May-13   44
  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),
    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

                                                                         CONFIDENTIAL       9-May-13   45
  Contention perspective – Deadlock
• 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);

                                                             CONFIDENTIAL    9-May-13   46
   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.

                                                      CONFIDENTIAL   9-May-13   47
  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,
    alter database tempdb modify file(name=templog,

                                                      CONFIDENTIAL   9-May-13   48
  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.'
     growth AS 'GrowthValue',
     'GrowthIncrement' =
      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.'
    FROM tempdb.sys.database_files;

                                                                CONFIDENTIAL   9-May-13   49
  TempDB improvement
• Update the size
    alter database tempdb modify file(name=tempdev,

                                                      CONFIDENTIAL   9-May-13   50
   TempDB improvement
• Number of data files
     – More data files for tempdb will reduce contention
     – Recommend: number of processors in system.
     alter database tempdb add file(name=tempdev2,
• Separate data file and log file
     – Tempdb does not need backups and it can well be in simple recovery
       model. This is the recommendation and also the default.

                                                           CONFIDENTIAL   9-May-13   51
   TempDB improvement
• Practice - Location
     – Move C partition -> D partition
• Result
           Page Response time (improvement in percent)
                                        Move from    Move from    Move from     Move from
                                          C: -> D:     C: -> D:     C: -> D:      C: -> D:
                                            with         with         with          with
                                           default      800M         320M           64M
                          No Change         size         size         size          size

              Average        1.813051     1.434593         FAIL        1.701       1.64125

              Maximum        5.983458     5.408847         FAIL      6.83278      6.060153

             90 Percent      2.774966     2.236746         FAIL      2.48322      2.350949

                                                                               CONFIDENTIAL   9-May-13   52
TempDB improvement (Resource

                               CONFIDENTIAL   9-May-13   53
Production Data

                  CONFIDENTIAL   9-May-13   54
   Production Data
Date        Release     Total Action   < 3s total     Average
            Number      Numbers        action numbers Percent
                                                      Page <3s
5/15/2009   MKV 8.8     106,429        103,174             96.9%

5/13/2009   MKV 8.7.1   81,018         75,300              93%

4/20/2009   MKV 8.7     81,672         75,325              92.2%

                                                CONFIDENTIAL     9-May-13   55
   Production Data
Date        Release     Total Action   < 3s total     Average
            Number      Numbers        action numbers Percent
                                                      Page <3s
5/15/2009   MKV 8.8     106,429        103,174             96.9%

5/13/2009   MKV 8.7.1   81,018         75,300              93%

4/20/2009   MKV 8.7     81,672         75,325              92.2%

                                                CONFIDENTIAL     9-May-13   56
  Some Hot Topics


                                  CONFIDENTIAL   9-May-13   57
Some Hot Topics (NUMA

                        CONFIDENTIAL   9-May-13   58
Some Hot Topics (NUMA)

                         CONFIDENTIAL   9-May-13   59
Some Hot Topics (NUMA)

                         CONFIDENTIAL   9-May-13   60
Some Hot Topics (NUMA)

                         CONFIDENTIAL   9-May-13   61
Some Hot Topics (NUMA)

                         CONFIDENTIAL   9-May-13   62
Some Hot Topics (NUMA)

                         CONFIDENTIAL   9-May-13   63
Some Hot Topics (CLUSTERED

                             CONFIDENTIAL   9-May-13   64
Some Hot Topics (CLUSTERED

                             CONFIDENTIAL   9-May-13   65
Some Hot Topics (CLUSTERED

                             CONFIDENTIAL   9-May-13   66
Some Hot Topics (CLUSTERED

                             CONFIDENTIAL   9-May-13   67
  Some Hot Topics (CLUSTERED

• Microsoft JDBC support configure the failover partner information

dbs/mss/server = tstsapdb1;FailoverPartner=tstsapdb2;Database=TST

                                                     CONFIDENTIAL     9-May-13   68
  Further topics

1. Disk tuning.
2. Sql server high availability and data replication
3. Sql server full text search
4. TempDB further tuning

                                                       CONFIDENTIAL   9-May-13   69
CONFIDENTIAL   9-May-13   70

To top