Expert Cautions About Hidden Dangers of Index Creation for SQL Server Performance

Share Article

Microsoft SQL Server MVP Ami Levin uses real-life examples to demonstrate how the creation of new indexes can degrade SQL Server performance

Every database administrator has witnessed first-hand how a carefully crafted index can reduce query duration significantly. While this technique is extremely effective, it is not risk-free

In a newly published article, titled “The Hidden Menace of CREATE INDEX”, SQL Server MVP Ami Levin highlights a commonly overlooked risk of this standard performance improvement technique.

Adding indexes to a table to improve data access paths is one of the most effective ways to optimize SQL Server performance. As Levin explains: “Every database administrator has witnessed first-hand how a carefully crafted index can reduce query duration significantly. While this technique is extremely effective, it is not risk-free.”

In the article, Levin provides a real-life case where the addition of a promising index had a devastating effect on database performance. The new index which was created and tested for one query caused the SQL Server query optimizer to change its execution plan for a different query as well. However, due to a skewed estimation by the query optimizer, this resulted in bringing the production database to a near halt.

“This example,” says Levin, “highlights the importance of very careful testing and benchmarking of all performance-related changes, prior to their production deployment.”

About Ami Levin

Ami Levin is a Microsoft SQL Server MVP, with over 20 years of experience in the IT industry. For the past 12 years he has been consulting, teaching and speaking on SQL Server worldwide. He manages the Israeli SQL Server user group, moderates the MSDN local support forum, and is a regular speaker at SQL Server conferences worldwide. Ami’s areas of expertise are data modeling, database design, T-SQL and performance tuning. Ami also blogs and publishes regularly on http://www.sql-server-tuning.com, and serves as the CTO of DBSophic.

###

Share article on social media or email:

View article via:

Pdf Print

Contact Author

Inbal Barit
DBSophic
+972-2-5610716
Email >