SQLServer Database Engine Tuning Advisor
Here below it's explained how to setup and run such powerful tool.
1.Open SQLServer Management Studio and select Tools→SQL Server Profiler
2.In the General tab, select a file to save to:
3.In the Events Selection tab, set cursors and TSQL events:
4.Then launch Mechworks PDM (standalone client)
5.click RUN on the SQL Server Profiler and start to exercize it using Standalone Client ( searches, child Tree, expand, etc ) – never work for more then 2-3 minutes – so you must focus your tests on specific areas ( example: expand the node and all the children of 33191-01-PRJ )
6.The profile starts collecting data:
7.when the PDM function has finished, press the STOP red button in the SQL Profiler toolbar:
8.from SQLServer Management Studio start Tools→Database Engine Tuning Advisor...
9.assign the Workload File, the Database for workload analysis and the databases and tables to tune:
10.click the Start Analysis button:
11.The analysis will run and produce the reports:
12.at the end the report will show the suggested indexes to be added to the database – in this case, exercizing the WhiteWater database with a DBWAlone 64-bit, with the expand the node and all the children of 33191-01-PRJ, I got the following report:
13.Then teste the recommendations by applying them:
14.verify the indexes have been created in the database:
15.Finally test the system several times to have a satisfying setting.
Please note that too many indexes on the parent-child table may SLOW DOWN the SAVE process.