I have been using SQL Server for over 20 years and have been working on the focused area of SQL Server Performance Tuning for over 12 years. In recent time I have been very busy doing Comprehensive Database Performance Health Check, and the client, I enjoy working the most is the one who has used in the past Database Engine Tuning Advisor.
Do not get me wrong that Database Engine Tuning Advisor is not a bad tool if you are in the year 2000. However, as the time has progressed, SQL Server has also advanced quite a bit and now the SQL Server Performance Tuning World is way above and beyond just creating indexing and statistics.
If you have used Database Engine Tuning Advisor (DETA or as commonly known as DTA) in the past, you must be aware that it most of the time only gives following two suggestions: 1) Create Index 2) Create Statistics.
This particular nature of DETA or DTA in fact actually have created a mess for quite a many different SQL Server users. I have seen quite a few organizations where overuse of DTA has ended up a table with 100s of indexes and eventually it has become impossible to use those tables.
In one of the recent project, where the customer complained about dead slow performance, after careful observation and analysis, I removed over 100 indexes and statistics which started with the prefix _DTA and we were able to see an instant performance improvement for SQL Server.
I personally prefer that there are no more than 5 indexes on a single table. If you have to create a new index, I strongly suggest you drop one index and keep the total number of indexes per table to 5. Here is the script which you can use to identify missing index or remove unused indexes.
Though, many may disagree. I suggest that you say no to Database Engine Tuning Advisor and adopt DMVs which gives metadata of the data.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I wouldn’t consider myself a follower of yours but so many of my questions end up answered on pages of your blog. Thank you for writing these and please continue.
Thanks for insight as always …. Huge Fan of your blog
why DTA suggesting extra statistics.
Generally if we create any index automatically statistics should be created for that particular index. But why again DTA suggesting exta statistics
As I said it the blog post, it is not the best tool.
If not the DTA, then what do you recommend? The suggestion to “adopt DMVs which gives metadata of the data” is quite vague. Do you have a blog post about how one goes about optimizing in this manner?
Yes, there are missing indexes and unused indexes script on the blog. You can use them.