在选择日志表的聚集索引时需要建议 [英] Need advice on selecting a clustered index for a logging table
问题描述
我使用SQL Server2005.我的Web应用程序使用一个简单的日志记录表来跟踪用户活动和访问的URL. 表格设计非常简单
I use SQL Server 2005. I have a simple logging table that my web application uses to track user activities and urls visited. The table design is very simple
ID(身份值)
LogDate(日期时间)
LogDate (datetime),
活动(nvarchar(200))，
Activity (nvarchar(200)),
网址(nvarchar(1000))
Url (nvarchar(1000))
我们主要在此表中插入. 有时，如果我们要调查特定用户在某个日期范围内的活动，我们将对此表执行一些查询. 该表当前具有一个标识列作为其主键.这也是它的聚集索引.
We mainly do Inserts into this table. Once in a while, our perform some queries against this table if we want to investigate a particular user's activities over a date period. The table currently has an identity column as its primary key. This is also its clustered index.
我想知道将它的聚集索引更改为LogDate列是否对我更好. LogDate列存储活动的日期/时间，并且可以有重复项，但是由于我们总是要插入表中，因此新记录应始终位于表的末尾，因此SQL Server没有理由必须进行重新组织或进行可能会影响插入效果的页面拆分.将LogDate列作为聚簇索引也应该有助于提高搜索性能.
I'm wondering if it is better for me to change its clustered index into the LogDate column. The LogDate column stores the date/time of the activity and can have duplicates, but since we are always inserting into the table, new records should be always at the end of the table, so there is no reason for SQL Server to have to regorganise or do page splits that would impact Insert performance. Having the LogDate column as the clustered index should also help search performance.
请让我知道我的推理是否正确.谢谢！
Please let me know if my reasoning is correct. Thank you!
推荐答案
Yes, your reasoning is correct, provided the rate of inserts is much less than the granularity of datetime (3.33ms)
SQL Server 2008具有新的数据类型DATETIME2，具有更高的精度(100纳秒).
SQL Server 2008 has a new datatype, DATETIME2, with higher precision (100 nanoseconds).
如果您留有合理数量的可用空间(FILLFACTOR在80-90之间)，并定期(每周一次)重建索引，那么一切都会很好.
If you leave a reasonable amount of free space (FILLFACTOR between 80-90), and rebuild the index regularly (once a week) all should be well.
这篇关于在选择日志表的聚集索引时需要建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
