I have a table with ~2 million records in and I need to add a new nonclustered index to a uniqueidentifier to improve query performance.
我有一个包含大约200万条记录的表,我需要在uniqueidentifier中添加一个新的非聚簇索引,以提高查询性能。
Will adding a nonclustered index lock the table or otherwise degrade performance significantly while it's being applied?
添加非聚集索引是否会锁定表格,否则会在应用时显着降低性能?
There's lots of information out there about the benefits/pitfalls of indexing, but I can't find anything that tells me that happens during an indexing operation
有很多关于索引的好处/缺陷的信息,但我找不到任何告诉我在索引操作期间发生的事情
I'm running SQL Server 2008 R2 (on Windows Server 2008 if that's important)
我正在运行SQL Server 2008 R2(在Windows Server 2008上,如果这很重要)
EDIT: It's the Enterprise Edition
编辑:这是企业版
3 个解决方案
#1
10
In Enterprise Edition, you gain the ability to do online index operations. It looks something like this:
在Enterprise Edition中,您可以进行在线索引操作。它看起来像这样:
create index MyIDX on MyTable (MyColumn) with (online = on)
Note that the operation does still take some locks during the process (at the beginning and end, IIRC), but doesn't lock the table for the duration of the index creation. If you're concerned, fire up an extended events session in a non-production environment and trace what locks are created and how long they exist for while creating your index.
请注意,在此过程中(在开始和结束时,IIRC),操作仍会执行某些锁定,但在创建索引期间不会锁定表。如果您担心,请在非生产环境中启动扩展事件会话,并在创建索引时跟踪创建的锁以及它们存在的时间。
Update: The documentation has a pretty good exposition about what locks are held when for both online and offline operations.
更新:该文档非常好地阐述了在线和离线操作时锁定的内容。
#2
17
For those of us not running "Expensive Edition" (Enterprise), the answer is thus:
对于我们这些没有运行“昂贵版”(企业版)的人来说,答案是这样的:
An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
创建非聚簇索引的脱机索引操作会获取表上的共享(S)锁。这可以防止更新基础表,但允许读取操作,例如SELECT语句。
So basically it renders the target table "read only" while the index is built. This may or may not be a problem for your overlaying applications -- check with your dev teams and users!
所以基本上它在构建索引时呈现目标表“只读”。对于您的覆盖应用程序,这可能是也可能不是问题 - 请与您的开发团队和用户核实!
PS: The question of whether or not, or why, to apply such an index, is an entirely different conversation. The SQL community and its legion of professional bloggers & SMEs are your friends.
PS:应用这样一个索引是否或为什么的问题是一个完全不同的对话。 SQL社区及其专业博主和中小企业团队是您的朋友。
#3
0
Depends (on a lot of things) as a rule of thumb adding the index ill improve selects and degrade inserts. A uniqueidentifier is basically a size fixed random phrase and thanks to it that indexes ill can get fragmented fast.
取决于(在很多事情上)作为经验法则添加索引,改善选择和降低插入。 uniqueidentifier基本上是一个大小固定的随机短语,并且由于它,索引病态可以快速分段。
For inserts it ill get a exclusive lock at row level (that's ok since that row is being "build") and soft locks at page level or table level (i'm not 100% sure about it, try the documentation but you got the idea).
对于插入,它会在行级别获得一个独占锁定(这是正确的,因为该行正在“构建”)和页面级别或表级别的软锁(我不是100%肯定它,尝试文档,但你得到了理念)。
That soft locks are not a issue depending on your isolation level (and hints applied to the selects). It can be a issue if you use a very restritive isolation level (that locks ill be honored to the letter)
软锁不是问题,具体取决于您的隔离级别(以及应用于选择的提示)。如果你使用一个非常具有限制性的隔离级别(这可以锁定这封信,可能是一个问题)
Anyway I suggest you to hit the books and do some acid tests. Its a very complex topic and depends a lot on your particular scenario.
无论如何,我建议你打书,做一些酸测试。它是一个非常复杂的主题,很大程度上取决于您的特定情况。
#1
10
In Enterprise Edition, you gain the ability to do online index operations. It looks something like this:
在Enterprise Edition中,您可以进行在线索引操作。它看起来像这样:
create index MyIDX on MyTable (MyColumn) with (online = on)
Note that the operation does still take some locks during the process (at the beginning and end, IIRC), but doesn't lock the table for the duration of the index creation. If you're concerned, fire up an extended events session in a non-production environment and trace what locks are created and how long they exist for while creating your index.
请注意,在此过程中(在开始和结束时,IIRC),操作仍会执行某些锁定,但在创建索引期间不会锁定表。如果您担心,请在非生产环境中启动扩展事件会话,并在创建索引时跟踪创建的锁以及它们存在的时间。
Update: The documentation has a pretty good exposition about what locks are held when for both online and offline operations.
更新:该文档非常好地阐述了在线和离线操作时锁定的内容。
#2
17
For those of us not running "Expensive Edition" (Enterprise), the answer is thus:
对于我们这些没有运行“昂贵版”(企业版)的人来说,答案是这样的:
An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.
创建非聚簇索引的脱机索引操作会获取表上的共享(S)锁。这可以防止更新基础表,但允许读取操作,例如SELECT语句。
So basically it renders the target table "read only" while the index is built. This may or may not be a problem for your overlaying applications -- check with your dev teams and users!
所以基本上它在构建索引时呈现目标表“只读”。对于您的覆盖应用程序,这可能是也可能不是问题 - 请与您的开发团队和用户核实!
PS: The question of whether or not, or why, to apply such an index, is an entirely different conversation. The SQL community and its legion of professional bloggers & SMEs are your friends.
PS:应用这样一个索引是否或为什么的问题是一个完全不同的对话。 SQL社区及其专业博主和中小企业团队是您的朋友。
#3
0
Depends (on a lot of things) as a rule of thumb adding the index ill improve selects and degrade inserts. A uniqueidentifier is basically a size fixed random phrase and thanks to it that indexes ill can get fragmented fast.
取决于(在很多事情上)作为经验法则添加索引,改善选择和降低插入。 uniqueidentifier基本上是一个大小固定的随机短语,并且由于它,索引病态可以快速分段。
For inserts it ill get a exclusive lock at row level (that's ok since that row is being "build") and soft locks at page level or table level (i'm not 100% sure about it, try the documentation but you got the idea).
对于插入,它会在行级别获得一个独占锁定(这是正确的,因为该行正在“构建”)和页面级别或表级别的软锁(我不是100%肯定它,尝试文档,但你得到了理念)。
That soft locks are not a issue depending on your isolation level (and hints applied to the selects). It can be a issue if you use a very restritive isolation level (that locks ill be honored to the letter)
软锁不是问题,具体取决于您的隔离级别(以及应用于选择的提示)。如果你使用一个非常具有限制性的隔离级别(这可以锁定这封信,可能是一个问题)
Anyway I suggest you to hit the books and do some acid tests. Its a very complex topic and depends a lot on your particular scenario.
无论如何,我建议你打书,做一些酸测试。它是一个非常复杂的主题,很大程度上取决于您的特定情况。