We are using a single table for Auditing in a SQL Server 2008 DB.
我们在SQL Server 2008数据库中使用单个表进行审计。
The single table architecture works nicely, is simple to query, accommodates schema changes.
单表架构运行良好,查询简单,适应架构更改。
But it is a major bottleneck for the entire DB. All INSERTS and UPDATES have to go through the audit table.
但它是整个数据库的主要瓶颈。所有INSERTS和UPDATES都必须通过审计表。
We already use NOLOCK HINT for SELECT statements.
我们已经将NOLOCK HINT用于SELECT语句。
Since there are no UPDATEs to this table, is there any suggestions for improving the throughput of INSERT statements?
由于此表没有UPDATE,是否有任何改进INSERT语句吞吐量的建议?
3 个解决方案
#1
4
Make sure you have a INT (or BIGINT) IDENTITY primary clustered index on the table! And preferably no other indices (if possible) - those would slow down inserts.
确保表上有INT(或BIGINT)IDENTITY主聚簇索引!并且最好没有其他指数(如果可能的话) - 那些会降低插入速度。
It's a common misconception that since the table only needs INSERTs and hardly any reads, you should "save" yourself the trouble of a primary, clustered key.
这是一个常见的误解,因为表只需要INSERT而几乎不需要任何读取,所以你应该“省去”主要的群集密钥的麻烦。
As the Goddess of SQL Server Indexing, Kimberly Tripp, explains in her excellent blog post The Clustered Index Debate continues:
作为SQL Server Indexing的女神,Kimberly Tripp在她出色的博客文章“集群指数辩论”中继续说道:
Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing.
与堆相比,嵌入在集群表中更快(但仅在“右”聚簇表中)。这里的主要问题是IAM / PFS中用于确定堆中插入位置的查找比群集表(其中插入位置已知,由群集密钥定义)慢。插入到定义了顺序(CL)的表中以及该顺序不断增加的位置时,插入更快。
So a right clustered index can speed up your inserts, and right here means static (never changes), unique, as small as possible (INT or BIGINT) and preferably ever-increasing (no page splits and therefore no performance penalty).
因此,正确的聚簇索引可以加速插入,这里就意味着静态(永不改变),唯一,尽可能小(INT或BIGINT),最好是不断增加(没有页面拆分,因此没有性能损失)。
Also if your table is only ever getting inserts and no updates / deletes, you should make sure to use a 100% FILLFACTOR on the clustered index to fully fill up those SQL server pages.
此外,如果您的表只进行插入而没有更新/删除,则应确保在聚簇索引上使用100%FILLFACTOR来完全填充这些SQL服务器页面。
Marc
#2
2
The only recommendations I would make are:
我要做的唯一建议是:
- ensure you are writing non-string values as much as possible
- encapsulate your writes to the audit via stored procedures
- grab any other data from stored procedure calls or,
- consider using a separate just-for-this-purpose View in your audit sproc. Ensure its joins are as minimal as possible. This view would be for lookups for PK for audit messages, etc. when trying to locate the FK for your string data.
- not a recommendation, but a fact: less indexes mean faster inserts + slower selects.
- consider archiving away 'old' audit rows to another table. Keep the audit table as small as you can. Move those older audit rows to another table. For reporting/querying, create a view that will
joinorunionthe 'live' and 'older' audits.
确保尽可能多地编写非字符串值
通过存储过程封装您对审计的写入
从存储过程调用中获取任何其他数据,或者
考虑在审计sproc中使用单独的for-for-purpose View。确保其连接尽可能小。在尝试查找字符串数据的FK时,此视图将用于查找审计消息的PK等。
不是推荐,而是一个事实:更少的索引意味着更快的插入+更慢的选择。
考虑将“旧的”审计行归档到另一个表。保持审计表尽可能小。将那些较旧的审计行移动到另一个表。对于报告/查询,创建一个将加入或联合“实时”和“较旧”审核的视图。
#3
0
If you are only appending to the audit tables and run reports that are going to always end up performing table scans, consider removing any indexes on the table.
如果您只是附加到审计表并运行将始终最终执行表扫描的报表,请考虑删除表上的任何索引。
#1
4
Make sure you have a INT (or BIGINT) IDENTITY primary clustered index on the table! And preferably no other indices (if possible) - those would slow down inserts.
确保表上有INT(或BIGINT)IDENTITY主聚簇索引!并且最好没有其他指数(如果可能的话) - 那些会降低插入速度。
It's a common misconception that since the table only needs INSERTs and hardly any reads, you should "save" yourself the trouble of a primary, clustered key.
这是一个常见的误解,因为表只需要INSERT而几乎不需要任何读取,所以你应该“省去”主要的群集密钥的麻烦。
As the Goddess of SQL Server Indexing, Kimberly Tripp, explains in her excellent blog post The Clustered Index Debate continues:
作为SQL Server Indexing的女神,Kimberly Tripp在她出色的博客文章“集群指数辩论”中继续说道:
Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing.
与堆相比,嵌入在集群表中更快(但仅在“右”聚簇表中)。这里的主要问题是IAM / PFS中用于确定堆中插入位置的查找比群集表(其中插入位置已知,由群集密钥定义)慢。插入到定义了顺序(CL)的表中以及该顺序不断增加的位置时,插入更快。
So a right clustered index can speed up your inserts, and right here means static (never changes), unique, as small as possible (INT or BIGINT) and preferably ever-increasing (no page splits and therefore no performance penalty).
因此,正确的聚簇索引可以加速插入,这里就意味着静态(永不改变),唯一,尽可能小(INT或BIGINT),最好是不断增加(没有页面拆分,因此没有性能损失)。
Also if your table is only ever getting inserts and no updates / deletes, you should make sure to use a 100% FILLFACTOR on the clustered index to fully fill up those SQL server pages.
此外,如果您的表只进行插入而没有更新/删除,则应确保在聚簇索引上使用100%FILLFACTOR来完全填充这些SQL服务器页面。
Marc
#2
2
The only recommendations I would make are:
我要做的唯一建议是:
- ensure you are writing non-string values as much as possible
- encapsulate your writes to the audit via stored procedures
- grab any other data from stored procedure calls or,
- consider using a separate just-for-this-purpose View in your audit sproc. Ensure its joins are as minimal as possible. This view would be for lookups for PK for audit messages, etc. when trying to locate the FK for your string data.
- not a recommendation, but a fact: less indexes mean faster inserts + slower selects.
- consider archiving away 'old' audit rows to another table. Keep the audit table as small as you can. Move those older audit rows to another table. For reporting/querying, create a view that will
joinorunionthe 'live' and 'older' audits.
确保尽可能多地编写非字符串值
通过存储过程封装您对审计的写入
从存储过程调用中获取任何其他数据,或者
考虑在审计sproc中使用单独的for-for-purpose View。确保其连接尽可能小。在尝试查找字符串数据的FK时,此视图将用于查找审计消息的PK等。
不是推荐,而是一个事实:更少的索引意味着更快的插入+更慢的选择。
考虑将“旧的”审计行归档到另一个表。保持审计表尽可能小。将那些较旧的审计行移动到另一个表。对于报告/查询,创建一个将加入或联合“实时”和“较旧”审核的视图。
#3
0
If you are only appending to the audit tables and run reports that are going to always end up performing table scans, consider removing any indexes on the table.
如果您只是附加到审计表并运行将始终最终执行表扫描的报表,请考虑删除表上的任何索引。