Post reply

DTA Plan Analysis

  • November 11, 2015 at 7:06 am

    #306426

    Hi,

    With the dta utility I can specify a test server for workload analysis (I cant in the GUI and I know this is documented), and with the GUI I can specify the plan cache for the workload.

    I want to be able to do both of these but I cant see how to specify to tune the plan cache via the dta.exe utility?

    Does anyone know how this can be done?

  • November 11, 2015 at 8:17 am

    #1839123

    mikefle (11/11/2015)

    Hi,

    With the dta utility I can specify a test server for workload analysis (I cant in the GUI and I know this is documented), and with the GUI I can specify the plan cache for the workload.

    I want to be able to do both of these but I cant see how to specify to tune the plan cache via the dta.exe utility?

    Does anyone know how this can be done?

    Not sure what you meant by 'tune the plan cache via the dta.exe utility', i am assuming you meant to tune a query and have optimal execution plan. Are you just trying to look at the plan and find pain points?

  • November 11, 2015 at 8:26 am

    #1839130

    Hi, from 2012, as well as 'File' and 'Table' there is a new 'Plan Cache' option, (which will take the top 1000 queries from the plan cache based on execution time.

    In the dta, either by command line, or input file, I cant see how to specify this, the options are 'File' 'Database' or 'Event string' , which are the same options listed in 2008 R2 BoL

    https://msdn.microsoft.com/en-GB/library/ms173513(v=sql.105).aspx

  • November 11, 2015 at 8:28 am

    #1839131

    OK, you got me thinking about this; I have scripted the input file from the GUI (export definition), and I got this;

    <?xml version="1.0" encoding="utf-16"?>

    <DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/dta">

    <DTAInput>

    <Server>

    <Name>SQLTEST12</Name>

    <Database>

    <Name />

    </Database>

    </Server>

    <Workload>

    [highlight=#ffff11] <ServerCache />[/highlight]

    </Workload>

    <TuningOptions>

    <TuningLogTable />

    <FeatureSet>IDX</FeatureSet>

    <Partitioning>NONE</Partitioning>

    <KeepExisting>ALL</KeepExisting>

    <OnlineIndexOperation>OFF</OnlineIndexOperation>

    </TuningOptions>

    </DTAInput>

    </DTAXML>

    This wasn't documented, Ill give it a go.

  • November 11, 2015 at 9:09 am

    #1839147

    Thats worked.

Viewing 5 posts - 1 through 4 (of 4 total)

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