I was reading Ian Stirk's Uncover Hidden Data to Optimize Application Performance article on MSDN Magazine, and now I am wondering if table indexes creation could be automated, as Google AppEngine does for its BigTable.
我在MSDN杂志上阅读Ian Stirk的Uncover Hidden Data to Optimize Application Performance文章,现在我想知道表索引创建是否可以自动化,就像Google AppEngine为其BigTable所做的那样。
There is any tool or Sql Server feature that automates table index creation?
有任何工具或Sql Server功能可以自动创建表索引吗?
4 个解决方案
#1
I'm glad you enjoyed my article!
我很高兴你喜欢我的文章!
In my new book about SQL Server performance via DMVs (www.manning.com/stirk), in chapter 10, you'll see a script that allows you to automatically created indexes... there's also a script to remove unused indexes... you can see how these could be used together.
在我通过DMV(www.manning.com/stirk)关于SQL Server性能的新书中,在第10章中,您将看到一个允许您自动创建索引的脚本......还有一个脚本可以删除未使用的索引。你可以看到它们如何一起使用。
Thanks Ian
#2
No, as far as I know, there's no feature in SQL Server that enables automatic table index creation.
不,据我所知,SQL Server中没有启用自动表索引创建的功能。
I wouldn't think it to be a good idea, either, because getting the right indexes in place will depend on a multitude of factors, hardly any of which can be really truly automated.
我也不认为这是一个好主意,因为获得正确的索引将取决于多种因素,几乎没有任何因素可以真正实现自动化。
Sure - you can place a primary key on any column called "ID" - until you run into a case where you need a primary key on something else....
当然 - 您可以将主键放在任何名为“ID”的列上 - 直到遇到需要其他主键的情况....
Sure, it makes sense to index foreign key columns in the child table - but sometimes, the added overhead for INSERTs can more than offset the gains of having the index.
当然,在子表中索引外键列是有意义的 - 但有时,INSERT的额外开销可以抵消索引的增益。
Getting the right indices in place is just way too dependant on your actual usage and a lot of dynamic, usage parameters (and design decisions on your part, too) so I'd be surprised if any solution would really work all that well...
获得正确的索引过于依赖于您的实际使用情况和大量动态的使用参数(以及您的设计决策),所以如果任何解决方案能够真正发挥作用,我会感到惊讶。 。
Marc
#3
I am not aware of any tools, and the best way to create indexes is to actually check the queries and their execution plans manually. I don't think that an automated tool will ever be as good as a few good DBA's analyzing the data together with the Profiler.
我不知道任何工具,创建索引的最佳方法是手动检查查询及其执行计划。我不认为自动化工具会像一些优秀的DBA一起分析数据与Profiler一样好。
But, if you feel like giving a shot yourself, I recommend that you start looking at the performance views in the SQL Server.
但是,如果您想自己尝试一下,我建议您开始查看SQL Server中的性能视图。
Start with the function sys.dm_db_missing_index_columns, that should give you a hint of which columns that could benefit from being indexed.
从函数sys.dm_db_missing_index_columns开始,该函数应该提供一些可以从索引中受益的列的提示。
sys.dm_db_index_usage_stats could show you which indexes are useless, or could be optimized as well.
sys.dm_db_index_usage_stats可以显示哪些索引无用,或者也可以进行优化。
sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_query_stats and sys.dm_exec_sql_text should show you queries performed and how they perform, and together with information from the other tables, you could probably find out which ones that need more work.
sys.dm_exec_cached_plans,sys.dm_exec_query_plan,sys.dm_exec_query_stats和sys.dm_exec_sql_text应该显示执行的查询及其执行方式,并且与其他表中的信息一起,您可能会找出哪些需要更多工作。
Actually, I vaguely recall some wizard that can help you to analyze performance in the Profiler, probably not automatically, but it might be possible to put that in a Maintenance plan.
实际上,我依旧回忆起一些可以帮助您分析Profiler中的性能的向导,可能不会自动分析,但可能会将其放入维护计划中。
#4
I know it's an old thread, but I thought someone may find this interesting:
我知道这是一个老线程,但我认为有人可能会觉得这很有趣:
http://blogs.msdn.com/b/queryoptteam/archive/2006/06/01/613516.aspx
Even though it sounds awesome I heed caution - indexes can cripple a system as much as they can make it fly - only use if you know what you are doing!
即使它听起来很棒我也要注意 - 索引可以使系统瘫痪,因为它们可以让它飞起来 - 只有在你知道自己在做什么的情况下才能使用!
#1
I'm glad you enjoyed my article!
我很高兴你喜欢我的文章!
In my new book about SQL Server performance via DMVs (www.manning.com/stirk), in chapter 10, you'll see a script that allows you to automatically created indexes... there's also a script to remove unused indexes... you can see how these could be used together.
在我通过DMV(www.manning.com/stirk)关于SQL Server性能的新书中,在第10章中,您将看到一个允许您自动创建索引的脚本......还有一个脚本可以删除未使用的索引。你可以看到它们如何一起使用。
Thanks Ian
#2
No, as far as I know, there's no feature in SQL Server that enables automatic table index creation.
不,据我所知,SQL Server中没有启用自动表索引创建的功能。
I wouldn't think it to be a good idea, either, because getting the right indexes in place will depend on a multitude of factors, hardly any of which can be really truly automated.
我也不认为这是一个好主意,因为获得正确的索引将取决于多种因素,几乎没有任何因素可以真正实现自动化。
Sure - you can place a primary key on any column called "ID" - until you run into a case where you need a primary key on something else....
当然 - 您可以将主键放在任何名为“ID”的列上 - 直到遇到需要其他主键的情况....
Sure, it makes sense to index foreign key columns in the child table - but sometimes, the added overhead for INSERTs can more than offset the gains of having the index.
当然,在子表中索引外键列是有意义的 - 但有时,INSERT的额外开销可以抵消索引的增益。
Getting the right indices in place is just way too dependant on your actual usage and a lot of dynamic, usage parameters (and design decisions on your part, too) so I'd be surprised if any solution would really work all that well...
获得正确的索引过于依赖于您的实际使用情况和大量动态的使用参数(以及您的设计决策),所以如果任何解决方案能够真正发挥作用,我会感到惊讶。 。
Marc
#3
I am not aware of any tools, and the best way to create indexes is to actually check the queries and their execution plans manually. I don't think that an automated tool will ever be as good as a few good DBA's analyzing the data together with the Profiler.
我不知道任何工具,创建索引的最佳方法是手动检查查询及其执行计划。我不认为自动化工具会像一些优秀的DBA一起分析数据与Profiler一样好。
But, if you feel like giving a shot yourself, I recommend that you start looking at the performance views in the SQL Server.
但是,如果您想自己尝试一下,我建议您开始查看SQL Server中的性能视图。
Start with the function sys.dm_db_missing_index_columns, that should give you a hint of which columns that could benefit from being indexed.
从函数sys.dm_db_missing_index_columns开始,该函数应该提供一些可以从索引中受益的列的提示。
sys.dm_db_index_usage_stats could show you which indexes are useless, or could be optimized as well.
sys.dm_db_index_usage_stats可以显示哪些索引无用,或者也可以进行优化。
sys.dm_exec_cached_plans, sys.dm_exec_query_plan, sys.dm_exec_query_stats and sys.dm_exec_sql_text should show you queries performed and how they perform, and together with information from the other tables, you could probably find out which ones that need more work.
sys.dm_exec_cached_plans,sys.dm_exec_query_plan,sys.dm_exec_query_stats和sys.dm_exec_sql_text应该显示执行的查询及其执行方式,并且与其他表中的信息一起,您可能会找出哪些需要更多工作。
Actually, I vaguely recall some wizard that can help you to analyze performance in the Profiler, probably not automatically, but it might be possible to put that in a Maintenance plan.
实际上,我依旧回忆起一些可以帮助您分析Profiler中的性能的向导,可能不会自动分析,但可能会将其放入维护计划中。
#4
I know it's an old thread, but I thought someone may find this interesting:
我知道这是一个老线程,但我认为有人可能会觉得这很有趣:
http://blogs.msdn.com/b/queryoptteam/archive/2006/06/01/613516.aspx
Even though it sounds awesome I heed caution - indexes can cripple a system as much as they can make it fly - only use if you know what you are doing!
即使它听起来很棒我也要注意 - 索引可以使系统瘫痪,因为它们可以让它飞起来 - 只有在你知道自己在做什么的情况下才能使用!