As a seasoned SQL Server user, I have witnessed the evolution of SQL Server over the past 20 years. One area that has particularly caught my attention is SQL Server Performance Tuning. For the past 12 years, I have been focused on optimizing database performance, and during this time, I have come across various tools and techniques.

One tool that many SQL Server users have relied on in the past is the Database Engine Tuning Advisor (DETA or DTA). However, as technology has advanced, so has SQL Server, and the world of SQL Server Performance Tuning has expanded far beyond just creating indexes and statistics.

If you have used DETA in the past, you may be familiar with its limitations. Most of the time, DETA only suggests two actions: creating indexes and creating statistics. While these suggestions may have been sufficient in the year 2000, they often fall short in today’s complex SQL Server environments.

Unfortunately, the nature of DETA has led to some organizations overusing the tool, resulting in tables with hundreds of indexes. This excessive indexing can lead to performance degradation and make it nearly impossible to effectively use those tables.

In a recent project, a customer complained about slow performance. After careful analysis, I discovered that over 100 indexes and statistics, all starting with the prefix “_DTA,” were unnecessary. By removing these unused indexes, we were able to achieve an instant performance improvement for their SQL Server.

Based on my experience, I strongly believe that a table should not have more than five indexes. If you need to create a new index, I recommend dropping an existing one to maintain a maximum of five indexes per table. This approach ensures that indexes are carefully chosen and optimized for performance.

Instead of relying solely on DETA, I encourage SQL Server users to explore the power of Dynamic Management Views (DMVs). DMVs provide valuable metadata about the data and can be used to identify missing indexes or remove unused indexes. By leveraging DMVs, you can gain deeper insights into your database’s performance and make informed decisions.

While some may disagree with my perspective on DETA, I believe that embracing newer tools and techniques, such as DMVs, is essential for staying ahead in the ever-evolving world of SQL Server Performance Tuning.

Remember, optimizing SQL Server performance is not just about creating indexes and statistics. It requires a holistic approach that considers various factors, including query optimization, database design, and server configuration.

So, the next time you encounter performance issues in your SQL Server environment, think beyond DETA and explore the vast possibilities offered by DMVs. Your database and applications will thank you for it!