Run Microsoft SQL Server Management Studio > Tools > SQL Server Profiler > Use the template = “Tuning” > click Run

Let the tracer run for awhile > click Stop when ready

Click File > Save as > Trace File > place the trace file onto the Desktop directory

 Click on Tools > ‘Database tuning advisor’ > Login as Sysadmin > click on the binocular icon within the Workload group to select the previously captured file

Open the trace file > select the database(s) to optimize > ‘Start Analysis’

If there were errors during analysis processing, increase the tuning options space; otherwise, review the Tuning session result > the ‘Recommendations’ tab should be displayed > wait for the analysis engine to complete > select All Recommendations > Actions > Save Recommendations > Name the script TuningRecommendations.sql > Click on Actions > Evaluate Recommendations

Input the name of new session as ‘Tuning Evaluation’ > click on Binocular Icon to locate the saved TuningRecommendations.sql file > set Database for workload analysis = master > click Start Analysis

Review the TuningRecommendations.sql file with Application Specialist(s) and/or DBA(s) prior to applying recommendations > make edits as necessary

Although the probability of having these metadata interfering with data is low, is worth noting that many of the recommended indexes and statistic objects need to be validated DEV/QA/STAGE prior to applying to PROD. These are the two types of objects that the Database Tuning Advisor (DTA) would recommend by default:

  1. Indexes: once created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data. Occasionally, DBAs would run this T-SQL, sys.dm_db_index_physical_stats to detect fragmentation. Reorganize and rebuild are the common user actions taken to maintain indexes. Here are some a quick rules of thumb regarding indexes:
    • avg_fragmentation_in_percent between 10% to 30% > REORGANIZE
    • avg_fragmentation_in_percent between 10% to 30% > REBUILD
  2. Statistics: this object type does not get updated automatically. Thus, DBAs should contemplate their effects on existing scheduled maintenance jobs (e.g. additional sequences to update statistics should be considered to keep these objects up-to-date)

When ready to apply DTA Recommendations in PROD, access the DTA GUI > Load the approved TuningRecommendations.sql file > put a check mark next to each desired recommendation > Actions > ‘Apply Recommendations’ > select ‘Apply Now’ > press OK when done

Another screenshot of a recommendation for ‘80% improvement’

Click ‘Close’ when done

A screenshot of a successful SQL tune-up

How To Reverse Changes:

It’s likely that only indexes and statistics would be recommended by the Database Tuning Advisor (DTA) wizard. Those objects would often have a prefix of ‘_dta_‘. Other times, custom indexes would follow the convention of ‘_tuning_index_‘, or the like. Therefore, it is relatively easy to locate and drop those objects to revert changes.

A) Scripted Process:

Copy the TuningRecommendations.sql file into a new file and edit it to drop named indexes and statistics similar to this sample before invoking T-SQL:

use [TESTDB001]
go

DROP INDEX _dta_index_TestTable_10_1954106002__K1_12_36_83_84 
ON [dbo].[TestTable];
DROP STATISTICS _dta_index_TestTable_10_1954106002__S1_12_36_83_84 
ON [dbo].[TestTable];

go

use [TESTDB100]
go

DROP INDEX [_dta_index_PM00300_c_7_944722418__K1_K2] ON [dbo].[PM00300]

-- truncated for brevity --

B) Manual Process:

Step 1: Find indexes and statistics with a preceding ‘_dta_‘ or ‘_tuning_index_‘ in their names

-- View indexes of a table
USE TEST_MSCRM
GO

EXEC sp_helpindex '[dbo].[TestTable]'
GO

/* Sample output:
_dta_index_TestTable_10_1954106002__K1_12_36_83_84 nonclustered located on PRIMARY SystemUserId
_dta_index_TestTable_10_1954106002__K1_12_53 nonclustered located on PRIMARY SystemUserId
cndx_PrimaryKey_SystemUser clustered, unique, primary key located on PRIMARY SystemUserId
fndx_for_cascaderelationship_calendar_system_users nonclustered located on PRIMARY CalendarId
fndx_for_cascaderelationship_site_system_users nonclustered located on PRIMARY SiteId
fndx_for_cascaderelationship_territory_system_users nonclustered located on PRIMARY TerritoryId
fndx_for_cascaderelationship_user_parent_user nonclustered located on PRIMARY ParentSystemUserId
fndx_Sync_VersionNumber nonclustered, unique located on PRIMARY VersionNumber
ndx_application_user nonclustered, unique located on PRIMARY ApplicationId
ndx_azure_active_directoryobjectid nonclustered, unique located on PRIMARY AzureActiveDirectoryObjectId
ndx_Cover nonclustered located on PRIMARY FullName, YomiFullName
ndx_firstname nonclustered located on PRIMARY FirstName
ndx_for_cascaderelationship_position_users nonclustered located on PRIMARY PositionId
ndx_for_cascaderelationship_queue_system_user nonclustered located on PRIMARY QueueId
ndx_for_cascaderelationship_systemuser_defaultmailbox_mailbox nonclustered located on PRIMARY DefaultMailbox
ndx_for_cascaderelationship_TransactionCurrency_SystemUser nonclustered located on PRIMARY TransactionCurrencyId
ndx_IdentityId nonclustered, unique located on PRIMARY IdentityId
ndx_internalemailaddress nonclustered located on PRIMARY InternalEMailAddress
ndx_isdisabledaccessmodetitle nonclustered located on PRIMARY IsDisabled, AccessMode, Title
ndx_IsLicensed nonclustered located on PRIMARY IsLicensed
ndx_IsSyncWithDirectory nonclustered located on PRIMARY IsSyncWithDirectory
ndx_lastname nonclustered located on PRIMARY LastName
ndx_Security nonclustered located on PRIMARY BusinessUnitId
UQ_TestTableActiveDirectoryGuid nonclustered, unique, unique key located on PRIMARY ActiveDirectoryGuid
*/

Step 2: Remove Indexes and Statistics

-- Removing indexes created by Tuning Advisor
USE TEST_MSCRM
GO

DROP INDEX _dta_index_TestTable_10_1954106002__K1_12_36_83_84 
ON [dbo].[TestTable];
DROP STATISTICS _dta_index_TestTable_10_1954106002__S1_12_36_83_84 
ON [dbo].[TestTable];
GO

Step 3: Repeat the search and removal processes (step 1 & 2)

An example app, such as Dynamics 365 (On Premise version), these tables are known to have indexes recommended by the Tuning Advisor:

[dbo].[AccountBase]
[dbo].[ActionCardBase]
[dbo].[ActivityPointerBase]
[dbo].[AsyncOperationBase]
[dbo].[ContactBase]
[dbo].[CustomerAddressBase]
[dbo].[InternalAddressBase]
[dbo].[PhoneCallBase]
[dbo].[PostBase]
[dbo].[SystemUserBase]
[dbo].[WorkflowBase]
Exceptions:
TITLE: DTAEngine
------------------------------

1% of consumed workload has syntax errors. Check tuning log for more information.

------------------------------
BUTTONS:

OK
------------------------------

When generating trace files via SQL Management Studio, this error was caused by ‘Showplan Statistics Profile’ option under the ‘Performance’ category on the Event Selection tab in SQL Server Profiler – be sure to uncheck that when generating a trace file to preempt this issue.

 

As it is often not recommended to apply all items from DTA, here is a sample of a refined set of Transactional SQL (T-SQL) that Application Specialists & DBAs have agreed upon in the past:

-- Optimize Dynamics Database
use [DYNAMICS]
go

CREATE CLUSTERED INDEX [_tuning_index_TemplateID] ON [dbo].[SY20000]
(
	[TemplateID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE CLUSTERED INDEX [_tuning_index_USERID_SectionID_MetricSequence] ON [dbo].[SY08100]
(
	[USERID] ASC,
	[SectionID] ASC,
	[MetricSequence] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_USERID_CmdParentDictID_CmdParentFormID] ON [dbo].[SY07110]
(
	[USERID] ASC,
	[CmdParentDictID] ASC,
	[CmdParentFormID] ASC,
	[CmdParentCmdID] ASC,
	[CmdSequence] ASC,
	[CmdDictID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [INBR]
go

CREATE CLUSTERED INDEX [_tuning_index_DUEDATE_CURTRXAM] ON [dbo].[RM20101]
(
	[DUEDATE] ASC,
	[CURTRXAM] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_DUEDATE_CURTRXAM] ON [dbo].[RM30101]
(
	[DUEDATE] ASC,
	[CURTRXAM] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_CRLMTTYP] ON [dbo].[RM00101]
(
	[CRLMTTYP] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_CUSTNMBR] ON [dbo].[RM00103]
(
	[CUSTNMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_CUSTNMBR_AGPERAMT_1_AGPERAMT_2] ON [dbo].[RM00103]
(
	[CUSTNMBR] ASC,
	[AGPERAMT_1] ASC,
	[AGPERAMT_2] ASC,
	[AGPERAMT_3] ASC,
	[AGPERAMT_4] ASC,
	[AGPERAMT_5] ASC,
	[AGPERAMT_6] ASC,
	[AGPERAMT_7] ASC
)
INCLUDE([TNSFCLIF],[NONSFLIF],[CUSTBLNC],[LASTAGED],[FRSTINDT],[LSTNSFCD],[LPYMTAMT],[LASTPYDT],[LSTTRXDT],[LSTTRXAM],[LSTFCHAM],[UPFCHYTD],[AVDTPLYR],[AVDTPLIF],[AVGDTPYR],[NUMADTPL],[NUMADTPY],[NUMADTPR],[TDTKNYTD],[TDTKNLYR],[TDTKNLTD],[TDISAYTD],[RETAINAG],[TNSFCYTD],[NONSFYTD],[UNPSTDSA],[UNPSTDCA],[UNPSTOSA],[UNPSTOCA],[NCSCHPMT],[TTLSLYTD],[TTLSLLTD],[TTLSLLYR],[TCOSTYTD],[TCOSTLTD],[TCOSTLYR],[TCSHRYTD],[TCSHRLTD],[TCSHRLYR],[TFNCHYTD],[TFNCHLTD],[TFNCHLYR],[FNCHCYTD],[FNCHLYRC],[TBDDTYTD],[TBDDTLYR],[TBDDTLTD],[TWVFCYTD],[TWVFCLTD],[TWVFCLYR],[TWROFYTD],[TWROFLTD],[TWROFLYR],[TTLINYTD],[TTLINLTD],[TTLINLYR],[TTLFCYTD],[TTLFCLTD],[TTLFCLYR],[WROFSLIF],[WROFSLYR],[WROFSYTD],[HIBALLYR],[HIBALYTD],[HIBALLTD],[LASTSTDT],[LSTSTAMT],[DEPRECV],[ONORDAMT],[TTLRTYTD],[TTLRTLTD],[TTLRTLYR],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_NOTEINDX_RevisionNumber] ON [dbo].[CN30100]
(
	[NOTEINDX] ASC,
	[RevisionNumber] DESC
)
INCLUDE([CUSTNMBR],[CPRCSTNM],[DATE1],[Contact_Date],[TIME1],[Contact_Time],[RevisionDate],[RevisionTime],[CN_Group_Note],[Caller_ID_String],[Action_Promised],[ActionType],[Action_Date],[Action_Assigned_To],[Action_Completed],[Action_Completed_Date],[Action_Completed_Time],[Amount_Promised],[USERID],[Note_Display_String],[CNTCPRSN],[ADRSCODE],[USERDEF1],[USERDEF2],[USRDAT01],[PRIORT],[NOTECAT],[NoteStatus],[Action_Cancelled_By],[Action_Cancelled_Date],[MODIFDT],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE NONCLUSTERED INDEX [_tuning_index_CUSTNMBR_Contact_Date_Contact_Time] ON [dbo].[CN00100]
(
	[CUSTNMBR] ASC,
	[Contact_Date] DESC,
	[Contact_Time] DESC,
	[NOTEINDX] ASC
)
INCLUDE([CPRCSTNM],[DATE1],[TIME1],[RevisionNumber],[CN_Group_Note],[Caller_ID_String],[Action_Promised],[ActionType],[Action_Date],[Action_Assigned_To],[Action_Completed],[ACTCMDSP],[Action_Completed_Date],[Action_Completed_Time],[Amount_Promised],[Amount_Received],[USERID],[Note_Display_String],[CNTCPRSN],[ADRSCODE],[USERDEF1],[USERDEF2],[USRDAT01],[PRIORT],[NOTECAT],[NoteStatus],[Action_Cancelled_By],[Action_Cancelled_Date],[MODIFDT],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_NOTEINDX_RevisionNumber_RMDTYPAL] ON [dbo].[CN30200]
(
	[NOTEINDX] ASC,
	[RevisionNumber] DESC,
	[RMDTYPAL] ASC,
	[DOCNUMBR] ASC
)
INCLUDE([CUSTNMBR],[CPRCSTNM],[RevisionDate],[RevisionTime],[USERID],[ActionType],[ActionAmount],[CURTRXAM],[CURNCYID],[CURRNIDX],[DEX_ROW_ID]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [ManagementReporter]
go

CREATE NONCLUSTERED INDEX [_tuning_index_StartBoundary_IsEnabled_Id] ON [Scheduling].[Trigger]
(
	[StartBoundary] ASC,
	[IsEnabled] ASC,
	[Id] ASC
)
INCLUDE([Type],[UnitOfMeasure],[EndBoundary],[RecurrenceLimit],[Interval],[DaysOfWeek],[DayOfMonth],[WeekOfMonth],[MonthOfYear],[OnlyWeekday],[TimeZoneId],[RunImmediately],[Version]) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [OFFIC]
go

CREATE CLUSTERED INDEX [_tuning_index_ACTINDX] ON [dbo].[GL30000]
(
	[ACTINDX] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

SET ANSI_PADDING ON
go

CREATE CLUSTERED INDEX [_tuning_index_ACTNUMBR_1_ACTNUMBR_4_ACCTTYPE] ON [dbo].[GL00100]
(
	[ACTNUMBR_1] ASC,
	[ACTNUMBR_4] ASC,
	[ACCTTYPE] ASC,
	[ACTNUMBR_2] ASC,
	[ACTNUMBR_3] ASC,
	[ACTNUMBR_5] ASC,
	[ACTNUMBR_6] ASC,
	[ACTNUMBR_7] ASC,
	[ACTNUMBR_8] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

use [SC]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

-- Optimize DB Related to Dynamics
use [DF]
go

CREATE CLUSTERED INDEX [_tuning_index_CUSTNMBR] ON [dbo].[RM00101]
(
	[CUSTNMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE CLUSTERED INDEX [_tuning_index_VENDORID_ADRSCODE] ON [dbo].[PM00300]
(
	[VENDORID] ASC,
	[ADRSCODE] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_tuning_index_CURTRXAM_RMDTYPAL_DOCNUMBR] ON [dbo].[RM20101]
(
	[CURTRXAM] ASC,
	[RMDTYPAL] ASC,
	[DOCNUMBR] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
Categories: