I have a large table (more than 10 millions records). this table is heavily used for search in the application. So, I had to create indexes on the table. However ,I experience a slow performance when a record is inserted or updated in the table. This is most likely because of the re-calculation of indexes.
我有一张大桌子(超过1000万条记录)。此表大量用于在应用程序中搜索。所以,我不得不在桌面上创建索引。但是,在表中插入或更新记录时,我的性能会降低。这很可能是因为重新计算了索引。
Is there a way to improve this.
有没有办法改善这一点。
Thanks in advance
提前致谢
5 个解决方案
#1
9
You could try reducing the number of page splits (in your indexes) by reducing the fill factor on your indexes. By default, the fill factor is 0 (same as 100 percent). So, when you rebuild your indexes, the pages are completely filled. This works great for tables that are not modified (insert/update/delete). However, when data is modified, the indexes need to change. With a Fill Factor of 0, you are guaranteed to get page splits.
您可以通过减少索引的填充因子来尝试减少页面拆分(在索引中)。默认情况下,填充因子为0(与100%相同)。因此,当您重建索引时,页面将完全填满。这适用于未修改的表(插入/更新/删除)。但是,在修改数据时,索引需要更改。如果填充因子为0,则可以保证页面拆分。
By modifying the fill factor, you should get better performance on inserts and updates because the page won't ALWAYS have to split. I recommend you rebuild your indexes with a Fill Factor = 90. This will leave 10% of the page empty, which would cause less page splits and therefore less I/O. It's possible that 90 is not the optimal value to use, so there may be some 'trial and error' involved here.
通过修改填充因子,您应该在插入和更新时获得更好的性能,因为页面不会总是必须拆分。我建议您使用填充因子= 90重建索引。这将使页面的10%为空,这将导致页面拆分更少,因此I / O更少。 90可能不是最佳使用价值,因此这里可能涉及一些“试验和错误”。
By using a different value for fill factor, your select queries may become slightly slower, but with a 90% fill factor, you probably won't notice it too much.
通过使用不同的填充因子值,您的选择查询可能会稍微变慢,但如果填充因子为90%,您可能不会注意到它太多。
#2
2
There are a number of solutions you could choose
您可以选择多种解决方案
a) You could partition the table
a)您可以对表进行分区
b) Consider performing updates in batch at offpeak hours (like at night)
b)考虑在非高峰时段(如夜间)批量更新
c) Since engineering is a balancing act of trade-offs, you would have to choose which is more important (Select or Update/insert/delete) and which operation is more important. Assuming you don't need the results in real time for an "insert", you can use Sql server service broker for those operations to perform the "less important" operation asynchronously
c)由于工程是权衡的平衡行为,您必须选择哪个更重要(选择或更新/插入/删除)以及哪个操作更重要。假设您不需要实时获得“插入”的结果,您可以使用Sql server服务代理来执行这些操作以异步执行“不太重要”的操作
http://msdn.microsoft.com/en-us/library/ms166043(SQL.90).aspx
Thanks -RVZ
#3
2
We'd need to see your indexes, but likely yes.
我们需要查看您的索引,但可能是。
Some things to keep in mind are that you don't want to just put an index on every column, and you don't generally want just one column per index.
要记住的一些事情是,您不希望仅在每个列上放置索引,并且通常不希望每个索引只有一列。
The best thing to do is if you can log some actual searches, track what users are actually searching for, and create indexes targeted at those specific types of searches.
最好的办法是,如果您可以记录一些实际搜索,跟踪用户实际搜索的内容,并创建针对这些特定类型搜索的索引。
#4
0
This is a classic engineering trade-off... you can make the shovel lighter or stronger, never both... (until a breakthrough in material science. )
这是一个经典的工程权衡...你可以使铲子更轻或更坚固,从不两者......(直到材料科学的突破。)
More index mean more DML maintenance, means faster queries.
更多索引意味着更多的DML维护,意味着更快的查询。
Fewer indexes mean less DML maintenance, means slower queries.
较少的索引意味着较少的DML维护,意味着查询速度较慢。
It could be possible that some of your indexes are redundant and could be combined.
有些索引可能是多余的,可以合并。
Besides what Joel wrote, you also need to define the SLA for DML. Is it ok that it's slower, you noticed that it slowed down but does it really matter vs. the query improvement you've achieved... IOW, is it ok to have a light shovel that weak?
除了Joel写的内容之外,您还需要为DML定义SLA。可以慢一点,你注意到它放慢了速度但是它确实比你已经实现的查询改进真的很重要...... IOW,是否可以使用轻型铲子?
#5
0
If you have a clustered index that is not on an identity numeric field, rearranging the pages can slow you down. If this is the case for you see if you can improve speed by making this a non-clustered index (faster than no index but tends to be a bit slower than the clustered index, so your selects may slow down but inserts improve)
如果您的聚簇索引不在标识数字字段上,则重新排列页面可能会降低您的速度。如果是这种情况,您可以看看是否可以通过使其成为非聚集索引来提高速度(比没有索引更快但往往比聚簇索引慢一点,因此您的选择可能会减慢但插入改进)
I have found users more willing to tolerate a slightly slower insert or update to a slower select. That is a general rule, but if it becomes unacceptably slow (or worse times out) well no one can deal with that well.
我发现用户更愿意容忍稍慢的插入或更新到较慢的选择。这是一般规则,但如果它变得无法接受缓慢(或更糟糕的时候),那么没有人可以很好地处理这个问题。
#1
9
You could try reducing the number of page splits (in your indexes) by reducing the fill factor on your indexes. By default, the fill factor is 0 (same as 100 percent). So, when you rebuild your indexes, the pages are completely filled. This works great for tables that are not modified (insert/update/delete). However, when data is modified, the indexes need to change. With a Fill Factor of 0, you are guaranteed to get page splits.
您可以通过减少索引的填充因子来尝试减少页面拆分(在索引中)。默认情况下,填充因子为0(与100%相同)。因此,当您重建索引时,页面将完全填满。这适用于未修改的表(插入/更新/删除)。但是,在修改数据时,索引需要更改。如果填充因子为0,则可以保证页面拆分。
By modifying the fill factor, you should get better performance on inserts and updates because the page won't ALWAYS have to split. I recommend you rebuild your indexes with a Fill Factor = 90. This will leave 10% of the page empty, which would cause less page splits and therefore less I/O. It's possible that 90 is not the optimal value to use, so there may be some 'trial and error' involved here.
通过修改填充因子,您应该在插入和更新时获得更好的性能,因为页面不会总是必须拆分。我建议您使用填充因子= 90重建索引。这将使页面的10%为空,这将导致页面拆分更少,因此I / O更少。 90可能不是最佳使用价值,因此这里可能涉及一些“试验和错误”。
By using a different value for fill factor, your select queries may become slightly slower, but with a 90% fill factor, you probably won't notice it too much.
通过使用不同的填充因子值,您的选择查询可能会稍微变慢,但如果填充因子为90%,您可能不会注意到它太多。
#2
2
There are a number of solutions you could choose
您可以选择多种解决方案
a) You could partition the table
a)您可以对表进行分区
b) Consider performing updates in batch at offpeak hours (like at night)
b)考虑在非高峰时段(如夜间)批量更新
c) Since engineering is a balancing act of trade-offs, you would have to choose which is more important (Select or Update/insert/delete) and which operation is more important. Assuming you don't need the results in real time for an "insert", you can use Sql server service broker for those operations to perform the "less important" operation asynchronously
c)由于工程是权衡的平衡行为,您必须选择哪个更重要(选择或更新/插入/删除)以及哪个操作更重要。假设您不需要实时获得“插入”的结果,您可以使用Sql server服务代理来执行这些操作以异步执行“不太重要”的操作
http://msdn.microsoft.com/en-us/library/ms166043(SQL.90).aspx
Thanks -RVZ
#3
2
We'd need to see your indexes, but likely yes.
我们需要查看您的索引,但可能是。
Some things to keep in mind are that you don't want to just put an index on every column, and you don't generally want just one column per index.
要记住的一些事情是,您不希望仅在每个列上放置索引,并且通常不希望每个索引只有一列。
The best thing to do is if you can log some actual searches, track what users are actually searching for, and create indexes targeted at those specific types of searches.
最好的办法是,如果您可以记录一些实际搜索,跟踪用户实际搜索的内容,并创建针对这些特定类型搜索的索引。
#4
0
This is a classic engineering trade-off... you can make the shovel lighter or stronger, never both... (until a breakthrough in material science. )
这是一个经典的工程权衡...你可以使铲子更轻或更坚固,从不两者......(直到材料科学的突破。)
More index mean more DML maintenance, means faster queries.
更多索引意味着更多的DML维护,意味着更快的查询。
Fewer indexes mean less DML maintenance, means slower queries.
较少的索引意味着较少的DML维护,意味着查询速度较慢。
It could be possible that some of your indexes are redundant and could be combined.
有些索引可能是多余的,可以合并。
Besides what Joel wrote, you also need to define the SLA for DML. Is it ok that it's slower, you noticed that it slowed down but does it really matter vs. the query improvement you've achieved... IOW, is it ok to have a light shovel that weak?
除了Joel写的内容之外,您还需要为DML定义SLA。可以慢一点,你注意到它放慢了速度但是它确实比你已经实现的查询改进真的很重要...... IOW,是否可以使用轻型铲子?
#5
0
If you have a clustered index that is not on an identity numeric field, rearranging the pages can slow you down. If this is the case for you see if you can improve speed by making this a non-clustered index (faster than no index but tends to be a bit slower than the clustered index, so your selects may slow down but inserts improve)
如果您的聚簇索引不在标识数字字段上,则重新排列页面可能会降低您的速度。如果是这种情况,您可以看看是否可以通过使其成为非聚集索引来提高速度(比没有索引更快但往往比聚簇索引慢一点,因此您的选择可能会减慢但插入改进)
I have found users more willing to tolerate a slightly slower insert or update to a slower select. That is a general rule, but if it becomes unacceptably slow (or worse times out) well no one can deal with that well.
我发现用户更愿意容忍稍慢的插入或更新到较慢的选择。这是一般规则,但如果它变得无法接受缓慢(或更糟糕的时候),那么没有人可以很好地处理这个问题。