I have a table with 281,433 records in it, ranging from March 2010 to the current date (Sept 2014). It's a transaction table which consists of records that determine stock which is currently in and out of the warehouse.
我有一张包含281,433条记录的表格，从2010年3月到当前日期（2014年9月）。它是一个交易表，由确定当前进出仓库的库存的记录组成。
When making picks from the warehouse, the system needs to look over every transaction from a particular customer that was ever made (based on the AccountListID field, which determines the customer, a customer might on average have about 300 records in the table). This happens 2-3 times per request from the particular .NET application when a picking run is done.
从仓库中挑选时，系统需要查看来自特定客户的每笔交易（根据确定客户的AccountListID字段，客户平均可能在表中有大约300条记录）。在完成拣配运行时，每个特定.NET应用程序的请求会发生2-3次。
There are times when the database seemingly locks out. Some requests complete no bother, within about 3 seconds. Others hang for 'up to 4 minutes' according to the end users.
有时候数据库似乎已经锁定了。有些请求在3秒内完成。根据最终用户的说法，其他人则需要“最多4分钟”。
My guess is with 4-5 requests at the same time all looking at this one transaction table things are getting locked up.
我的猜测是同时有4-5个请求，所有查看这个事务表的事情都被锁定了。
I'm thinking about partitioning this table so that the primary transaction table only contains record from the last 2 years. The end user has agreed that any records past this date are unnecessary.
我正在考虑对此表进行分区，以便主事务表仅包含过去2年的记录。最终用户同意不需要在此日期之后的任何记录。
But I can't just delete them, they're used elsewhere in the system. I have indexes already in place and they make a massive difference (going from >30 seconds to <2, on the accountlistid field). It seems partitioning is the next step.
但我不能删除它们，它们在系统的其他地方使用。我已经建立了索引并且它们产生了巨大的差异（在accountlistid字段上从> 30秒到<2）。看来分区是下一步。
1) Am I going down the right route as a solution to my 'locking' problem? 2) When moving a set of records (e.g. records where the field DateTimeCheckedIn is more than 2 years old) is this a manual process or does partitioning automatically do this?
1）我是否采取正确的方式解决我的“锁定”问题？ 2）当移动一组记录（例如，字段DateTimeCheckedIn超过2年的记录）这是一个手动过程还是分区自动执行此操作？
2 个解决方案
#1
1
I don't think partitioning will be necessary here. You can probably fix this with a well-placed index: I'm thinking a single index covering (in order) company, part number, and quantity. Or, if it's an old server, possibly just add ram. Finally, since this is reading a lot of older data for transactions, where individual transactions themselves are likely never (or at most very rarely) updated once written, you might do better with a READ UNCOMMITTED isolation level for this query.
我不认为在这里需要分区。你可以用一个位置很好的索引来解决这个问题：我正在考虑一个单一的索引（按顺序）覆盖公司，部件号和数量。或者，如果它是旧服务器，可能只需添加ram。最后，由于这是为事务读取大量旧数据，其中单个事务本身可能永远（或者至多很少）一旦写入更新，您可能会在此查询的READ UNCOMMITTED隔离级别上做得更好。
#2
2
Partitioning shouldn't be necessary on a table with fewer than 300,000 rows, unless each record is really big. If a record is occupying more than 4k bytes, then you have 300,000 pages (2,400,000,000 bytes) and that is getting larger.
除非每条记录都非常大，否则在少于300,000行的表上不需要进行分区。如果一条记录占用超过4k字节，那么你有300,000页（2,400,000,000字节），并且它会越来越大。
Indexes are usually the solution for something like this. Taking more than a second to return 300 records in an indexed database seems like a long time (unless the records are really big and the network overhead adds to the time). Your table and index should both fit into memory. Check your memory configuration.
索引通常是这样的解决方案。花费一秒多的时间在索引数据库中返回300条记录似乎很长一段时间（除非记录非常大并且网络开销增加了时间）。您的表和索引都应该适合内存。检查内存配置。
The next question is about the application code. If it uses cursors, then these might be the culprit by locking rows under certain circumstances. For read-only cursors, "FAST_FORWARD" or "FORWARD READ_ONLY" should be fast. It is possible that if the application code is locking all the historical records, then you might get contention. After all, this would occur when two records (for different) customers are on the same data page. The solution is to not lock the historical records as you read them. Or, to avoid using cursors all together.
下一个问题是关于应用程序代码。如果它使用游标，那么这些可能是在某些情况下锁定行的罪魁祸首。对于只读游标，“FAST_FORWARD”或“FORWARD READ_ONLY”应该很快。如果应用程序代码锁定了所有历史记录，则可能会出现争用。毕竟，当两个记录（针对不同的）客户位于同一数据页面上时，会发生这种情况。解决方案是在读取历史记录时不锁定历史记录。或者，避免一起使用游标。
#1
1
I don't think partitioning will be necessary here. You can probably fix this with a well-placed index: I'm thinking a single index covering (in order) company, part number, and quantity. Or, if it's an old server, possibly just add ram. Finally, since this is reading a lot of older data for transactions, where individual transactions themselves are likely never (or at most very rarely) updated once written, you might do better with a READ UNCOMMITTED isolation level for this query.
我不认为在这里需要分区。你可以用一个位置很好的索引来解决这个问题：我正在考虑一个单一的索引（按顺序）覆盖公司，部件号和数量。或者，如果它是旧服务器，可能只需添加ram。最后，由于这是为事务读取大量旧数据，其中单个事务本身可能永远（或者至多很少）一旦写入更新，您可能会在此查询的READ UNCOMMITTED隔离级别上做得更好。
#2
2
Partitioning shouldn't be necessary on a table with fewer than 300,000 rows, unless each record is really big. If a record is occupying more than 4k bytes, then you have 300,000 pages (2,400,000,000 bytes) and that is getting larger.
除非每条记录都非常大，否则在少于300,000行的表上不需要进行分区。如果一条记录占用超过4k字节，那么你有300,000页（2,400,000,000字节），并且它会越来越大。
Indexes are usually the solution for something like this. Taking more than a second to return 300 records in an indexed database seems like a long time (unless the records are really big and the network overhead adds to the time). Your table and index should both fit into memory. Check your memory configuration.
索引通常是这样的解决方案。花费一秒多的时间在索引数据库中返回300条记录似乎很长一段时间（除非记录非常大并且网络开销增加了时间）。您的表和索引都应该适合内存。检查内存配置。
The next question is about the application code. If it uses cursors, then these might be the culprit by locking rows under certain circumstances. For read-only cursors, "FAST_FORWARD" or "FORWARD READ_ONLY" should be fast. It is possible that if the application code is locking all the historical records, then you might get contention. After all, this would occur when two records (for different) customers are on the same data page. The solution is to not lock the historical records as you read them. Or, to avoid using cursors all together.
下一个问题是关于应用程序代码。如果它使用游标，那么这些可能是在某些情况下锁定行的罪魁祸首。对于只读游标，“FAST_FORWARD”或“FORWARD READ_ONLY”应该很快。如果应用程序代码锁定了所有历史记录，则可能会出现争用。毕竟，当两个记录（针对不同的）客户位于同一数据页面上时，会发生这种情况。解决方案是在读取历史记录时不锁定历史记录。或者，避免一起使用游标。