Before entering to this article concept I am sharing one thing…A Few days ago I have given the Interview in one of the IT leading company and there interviewer asked to me many questions. There was one question was ”What is Database Engine Tuning Advisor (DTA) and its utility?” Ok. Now,
This DTA understand the structure of database and it’s also understand the internal functional and logical of the server. This tool automatic makes the valid and needful structure which includes Indexing, Indexed View and table partitioning and more(tuning on large query in one or more then one database).
In SQL Server you can perform the following tasks with DTA. (Very Important)
1. As we understand we can troubleshoot the performance of a specific problem query
2. Using DTA Tool we can perform the tuning operation on large set of queries across one or more databases
3. It’s used for optimal physical design changes on the database
4. Minimize and utilize the storage space of the database.
5. Using this we can save the Disk space.
6. Using it we can remove unwanted existing Index, View and merge the table partition and database partition.
7. DTA is not only gives the suggestion even its gives the method and script to perform the operation in the recommended object.
8. When we execute the analysis it return the Latest progress, recommendation and finally its return the Report. In the report we can find the following things-
Maximum tuning time 50 Minutes
Time taken for tuning 1 Minute
Estimated percentage improvement 0.00
Maximum space for recommendation (MB) 251
Space used currently (MB) 161
Space used by recommendation (MB) 115
Number of events in workload 30
Number of events tuned 30
Number of indexes recommended to be dropped 36
Number of indexes on views recommended to be dropped 2
There are many tasks that can do in the DTA.
Thanks For reading.......