Post reply

Need help using Database Engine Tuning Advisor

  • December 18, 2015 at 10:43 am

    #307124

    Hi,

    I've some list of SQL Server Profiler - trace data file as follow

    I knew, how to Add - 1 Trace Data into Workload.

    My question is, how can I Add Trace Data more than 1 into Workload?

    As a result, I can Start Analysis

    Please help

  • December 21, 2015 at 5:01 am

    #1847064

    If you select the first file then I believe the Tuning adviser will automatically use the additional files

    File

    Specify a .sql script or trace file for a workload. Specify the path and filename in the associated text box. Database Engine Tuning Advisor assumes that the workload trace file is a rollover file. For more information about rollover files, see Limiting Trace File and Table Sizes.

    https://technet.microsoft.com/en-us/library/ms178494(v=sql.105).aspx

  • December 21, 2015 at 5:02 am

    #1847065

    Never used DTA.

    All I have ever done is drop indexes created by users who used the DTA,(both physical and hypothetical indexes en masse)

    When looking at results, always always remember the DTA and DMV's dont suggest Clustered indexes.

    What you can do is play back all traces through profiler on a clean system.

    There are 2 ways to move forward from here.

    One is to look at DM_exec_procedure_stats and look at the top 10 worst procs by total logical reads.

    Then look at why those procs are hitting the DB hard.

    You want there to be a missing index of low cardinality on each proc.

    If you execute the expensive proc and you see no missing index, then you need to ask why the engine cannot benefit from an index.

    Then fix the proc until it asks for a good index. Google Sargability.

    The other way is to just look at the missing indexes here.

    PRINT 'Missing Indexes: '

    PRINT 'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

    PRINT 'be seen if the index was created. This is a unitless number, and has meaning only relative '

    PRINT 'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '

    PRINT 'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

    PRINT ''

    PRINT '-- Missing Indexes --'

    SELECT statement,mid.equality_columns,mid.inequality_columns,

    migs.unique_compiles compiles,migs.user_seeks Seeks, CONVERT (decimal (28,0), migs.avg_total_user_cost/100 * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS saving,

    migs.avg_total_user_cost cost,migs.avg_user_impact impact,migs.last_user_seek LastSeek,

    'CREATE INDEX misIND'

    + ' ON ' + mid.statement

    + ' (' + ISNULL (mid.equality_columns,'')

    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement

    FROM sys.dm_db_missing_index_groups mig

    INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle

    INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

    ORDER BY

    1,2,6 desc

    --1 desc

    PRINT ''

    GO

    Take note that the suggestions do not suggest how to cluster a table so you might get many recommendations for many non clustered indexes with the same or similar key.

    Just a recommendation.

    Only looking at indexing is an inferior way to tune the system and won't help you if your worst procs are written in such a way that they don't ask for or use indexes.

    Also, once again with feeling, always always remember the DTA and DMV's dont suggest Clustered indexes.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply