DTA2:DTA Practice

DTA relies on query Optimizer to analyse workload and provides tuning recommendation. DTA has a limited ability to improve the performance of query statements, which is related to the tuning recommendation provided by DTA, which is mainly divided into three parts: index,indexed View and Partition. Tuning recommendation. However, the tuning recommendation provided by DTA is still very useful and can be used as the direction of performance tuning exploration.

Optimizing query performance can be difficult without a full understanding the database structure and the queries that is Run against the database. The Database Engine Tuning Advisor can make this task easier by analyzing the current query plan cache or by analyzing a W ORKLOAD of Transact-SQL queries that you create and recommending an appropriate physical design. For further advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of DIF Ferent Physical design alternatives. The DTA can provide the following information.

• Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.

• Recommend aligned or non-aligned partitions for databases referenced in a workload.

• Recommend indexed views for databases referenced in a workload.

• Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance In the workload.

• Recommend ways to tune the database for a small set of problem queries.

• Customize the recommendation by specifying advanced options such as disk space constraints.

• Provide reports that summarize the effects of implementing the recommendations for a given workload.

One, Open DTA GUI

From the Tools menu in SSMs, open the Database Engine Tuning Advisor. Illustration reference Msdn,dta is mainly divided into two pane.

1,left Pane is a session Monitor with detail info at the bottom of the tuning session in the selected state.

The left pane contains the Session Monitor, which lists all tuning sessions that has been performed on this Microsoft SQL Server instance. In the bottom sections of this pane, the details of the selected tuning session is displayed.

2,right pane initial state, only two tab,general and Tuning Options, set workload and fine-tune the Tuning.

The right pane contains the Generaland theTuning OptionsTabs. This is where you can define your Database Engine Tuning session. In the Generaltab, you type the name of the Your tuning session, specify the workload file or table to use, and select the databases and TA Bles want to tune in this session. A workload is a set of Transact-SQL statements that execute against a database or databases, and want to tune. Database Engine Tuning Advisor uses trace files, trace tables, Transact-SQL scripts, or XML files as workload input when t uning databases. On theTuning Optionstab, you can select the physical database design structures (indexes or indexed views) and the partitioning strategy You want the Database Engine Tuning Advisor to consider during it analysis. On this tab, you also can specify the maximum time, the Database Engine Tuning Advisor takes to tune a workload. By default, Database Engine Tuning Advisor would tune a workload for one hour.

SCRIPT1: creating instance Data

 UseDb_studyGoCreate Tabledbo.dt_a (IDint Identity  not NULL, Createddatekeyint constraintDf_dt_a_createddatekeydefault(cast(Convert(Char(8),getdate(), the) as int)), CreatedDatedatetime  not NULL constraintDf_dt_a_createddatedefault(getdate()));GoCreate TableDbo.dt_b (IDint Identity  not NULL, Createddatekeyint constraintDf_dt_b_createddatekeydefault(cast(Convert(Char(8),getdate(), the) as int)), CreatedDatedatetime  not NULL constraintDf_dt_b_createddatedefault(getdate()));GoInsert  intodbo.dt_adefault ValuesGo  -Insert  intoDbo.dt_bdefault ValuesGo  +

SCRIPT2: Write the following TSQL script in SSMS and save it as a. sql file with a file path of: D:\ Dt_join.sql.

Before DTA analysis, first of all, two tables did not aggregate index, if you want to create index, then create clustered index is preferred. Second, the ON clause is the filter for index and must be the index Key. Finally, the Select clause show appears in the field that has Createddatey, which is not used as a filter condition, but is returned as a query result, if the index created is nonclustered index, The Createddatey field is more appropriate as an include column, which reduces the size of the index key of the non-leaf level.

 Use Db_study Go Select  as  as Id_a,b.createddatekey  from Dbo.dt_b b  Left Join dbo.dt_a a      on b.ID=a.idGo

Second, set the workload in the General Tab

1, set session name to Dta_ 2016-01-24 2:42:04 PM

2,workload type selection file,file path is: D:\ dt_join.sql
3,database for workload analysis is the initial db,workload of the DTA connection to SQL Server using the use database_name switch DB, where Dt_join.sql has switched to Db_st using the USE statement Udy.

4,select database and tables to tune

Select the DB and tables that need to be optimized, this time the task is to optimize the dt_join.sql, so I choose Db_study and all the tables in that DB as the Optimization object.

5,save Tuning Log

Tick, DTA will store the tuning Log in the dbo.dta_tuninglog of msdb.

Third, set tuning options in Tuning Options tab

1,limit Tuning Time

When DTA analyzes the query performance of workload, it consumes system resources, should avoid performance tuning when the system is busy, tick Limit tuning time, and set stop at to stop the DTA setting at certain times. The default setting is DTA after one hours stop

2,physical Design Structures (PDS) to use in database

Allows DTA to evaluate the performance of creating a PDS (index or indexed view), and if a new PDS is created to improve performance, then DTA will return the tuning recommendation containing the PDS created.

3,partitioning strategy to employ

Allows DTA to evaluate how much performance is created partition, and if a new partition is created to improve performance, then DTA returns the tuning recommendation that contains the creation partition.

4,physical Design Structures (PDS) to keep in database

Allows DTA to evaluate the performance of removing the PDS (index or indexed view), and if deleting an already existing PDS can improve performance, then DTA will return the tuning recommendation that contains the deleted PDS.

In general, the advanced tuning options use the default values.

1,Define Max space for recommendations (MB)

Set maximum space to create PDS consumption

2,Include plan cache events from all databases

Specify that plan caches events from all databases is analyzed.

3,Max. Columns per index

Specify the maximum number of columns to include in any index. The default is 1023.

4,online Index Recommendations

Specifies whether to create an index online, divided into three levels: offline, create index,online as online as possible.

Four, Progress

1, in tool bar, click Start ANALYSIS,DTA to begin analysis workload

2,progress Tab

When start analysis, more than one progress Tab, able to view the progress of DTA analytics

At the end of the analysis, you can see that tuning log has a row of records, with two more tab:recommendations and reports.

Tuning log Reason field: Statement references only small tables, the statement refers to the small table, what is the DTA to the Small table query disdain tuning?

Five, view recommendations and reports Tab

Recommendations Tab shows estimated improvement is 0%,

Reports tab shows estimated percentage improvement is 0.00

DTA does not optimize a small table, so I insert more data and data columns into the table, making the table larger

Six, add wide columns and insert more rows of data.

--EnlareAlter Tabledbo.dt_aAddCol_1Char( +) not NULLconstraintDf__dt_a_col_1default(N'aaaaaaaaaaaaaaaaaaaaaaaaaaaa');GoAlter Tabledbo.dt_aAddCol_2Char( +) not NULLconstraintDf__dt_a_col_2default(N'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');GoInsert  intodbo.dt_adefault ValuesGo 10000--EnlareAlter TableDbo.dt_bAddCol_1Char( +) not NULLconstraintDf__dt_b_col_1default(N'aaaaaaaaaaaaaaaaaaaaaaaaaaaa');GoAlter TableDbo.dt_bAddCol_2Char( +) not NULLconstraintDf__dt_b_col_2default(N'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');GoInsert  intoDbo.dt_bdefault ValuesGo 10000

Seven, clone session

In the left pane, select the previous session, click Clone Session, copy the session configuration, and rename

Eight, Progress Tab

Tuning Log, no record

Nine, recommendations Tab

Estimated improvement:96% DTA estimates the performance increase is 96%, very good.

DTA does not provide partition recommendations.

DTA provides two index recommendations.

Click Definition to pop up SQL Script Preview, which is the definition of index recommendation.

Here is the script to create index, similar to the estimated index structure. DTA recommends creating nonclustered index, and without data compression (data_compression), a large amount of space using fixed-length column is idle, from a performance-optimized point of view, poorly.

CREATE nonclustered INDEX [_dta_index_dt_a_7_1490104349__k1]  on [dbo].[dt_a](    [ID] ASC) with(sort_in_tempdb= OFF, drop_existing= OFF, ONLINE= OFF)  on [PRIMARY]CREATE nonclustered INDEX [_dta_index_dt_b_7_1538104520__k1_2]  on [dbo].[Dt_b](    [ID] ASC) INCLUDE ([Createddatekey])  with(sort_in_tempdb= OFF, drop_existing= OFF, ONLINE= OFF)  on [PRIMARY]

Ten, Reports Tab
Tuning Summary Display Summary of information for DTA analysis workload

Tuning Reports provides 15 Reports that are useful for reporting the performance tuning information of DTA to workload.

Reference Documentation:

Https://msdn.microsoft.com/en-us/library/ms167345.aspx

https://msdn.microsoft.com/en-us/library/ms186232 (v=sql.110). aspx

DTA2:DTA Practice