如何学习SQL Server索引调优？ [英] How can I learn SQL Server index tuning?

问题描述

在编写tsql查询时学习索引调优的最佳实用方法是什么？我有VS2008 SQL Express。有人可以请我提供示例等吗？我已经找到了在线文章，理论上它们很棒，但我仍然没有看到现实生活中的索引调整。那里有很容易创建的小例子吗？

What is the best practical way of learning index tuning while writing tsql queries? I have VS2008 SQL Express. Could someone please provide me examples, etc? I have already found online articles and they are great in theory, but I still fail to see index tuning in real life action. Are there small easy to create examples out there?

推荐答案

要调整索引，您往往需要包含大量数据的大型表，这么简单的小例子并不容易。

To tune indexes, you tend to need large tables with lots of data, so small simple examples aren't easy to come by.

我的经验是使用SQL 2000工具。查询分析器，显示执行计划并查看所使用的索引和连接的类型。很难在这里描述。

My experience is with the SQL 2000 tools. Query Analyser, showing the Execution Plan and looking at the types of index and joins used. Very hard to describe it here.

我可以推荐一本关于这个主题的好书，特别是第9章。

I can recommend a good book on the subject, particularly Chapter 9.

http://www.amazon.com/Professional-Server-Performance-Tuning-Programmer/dp/ 0470176393

我不鼓励您使用自动索引调整工具，直到您了解如何自己手动完成。我认为重要的是它建议添加一个索引，你有能力理智地检查建议，并自己决定它是否是一个好的选择。通常它会建议您添加一个包含许多列的覆盖索引，以加快您要求分析的单个查询，但是当您查看针对该表的所有查询时，这可能会对您的数据库产生负面影响。

I would discourage you from using the automated Index Tuning tools until you understand how to do it yourself manually. I think it's important when it recommends adding an index that you have the ability to sanity-check the recommendation and decide for yourself whether it is a good option. Often it will recommend you add a "covering" index with many columns in order to speed up a single query you've asked to be analysed, but this may have adverse effects on your database overall when you look at all queries against that table.

这篇关于如何学习SQL Server索引调优？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！