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
Jerusalem, Israel (PRWEB) February 15, 2012
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.