c# - sql server 24/7 插入删除选择性能

Question

我正在构建一个 C# 应用程序，它使用 Bulkinsert 每秒插入 2000 条记录。

数据库版本为2008 R2

应用程序调用一个 SP，该 SP 使用 TOP (10000) 删除超过 2 小时的记录。这是在每次插入后执行的。

最终用户使用日期范围和 2 到 10 个参数 ID 选择要在图表中查看的记录。

由于应用程序将 24/7 全天候运行，没有停机时间，我担心性能问题。

分区不是一个选项，因为客户没有企业版。

• 聚簇索引定义好吗？
• 由于在表的一端插入行并在另一端删除行这一事实，是否有必要实现任何索引重建/重建索引以提高性能？
• 关于更新统计信息，它在 2008 R2 中仍然是一个问题吗？
• 我使用 OPTION (RECOMPILE) 来避免在选择中使用过时的查询计划，这是一个好方法吗？
• 是否有任何可以加快 SELECT 速度的表格提示？
• 关于锁定策略有什么建议吗？

除了上述情况外，我还有 3 个表格，它们在不同的时间范围内以相同的方式工作。一个每 20 秒插入一次并删除超过 1 周的行，另一个每分钟插入一次并删除超过六周的行，最后一个每 5 分钟插入一次并删除超过 3 年的行。

CREATE TABLE [dbo].[BufferShort](
    [DateTime] [datetime2](2) NOT NULL,
    [ParameterId] [int] NOT NULL,
    [BufferStateId] [smallint] NOT NULL,
    [Value] [real] NOT NULL,
 CONSTRAINT [PK_BufferShort] PRIMARY KEY CLUSTERED 
(
    [DateTime] ASC,
    [ParameterId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER PROCEDURE [dbo].[DeleteFromBufferShort]
@DateTime DateTime,
@BufferSizeInHours int

AS
BEGIN
    DELETE TOP (10000) 
    FROM BufferShort
    FROM BufferStates 
    WHERE BufferShort.BufferStateId = BufferStates.BufferStateId 
    AND BufferShort.[DateTime] < @DateTime
    AND (BufferStates.BufferStateType = 'A' OR BufferStates.Deleted = 'True')

    RETURN 0
END


ALTER PROCEDURE [dbo].[SelectFromBufferShortWithParameterList]
@DateTimeFrom Datetime2(2),
@DateTimeTo Datetime2(2),
@ParameterList varchar(max)

AS
BEGIN
SET NOCOUNT ON;

-- Split ParameterList into a temporary table
SELECT * INTO #TempTable FROM dbo.splitString(@ParameterList, ',');

SELECT *
FROM BufferShort Datapoints
JOIN Parameters P ON P.ParameterId = Datapoints.ParameterId
JOIN #TempTable TT ON TT.Token = P.ElementReference
WHERE Datapoints.[DateTime] BETWEEN @DateTimeFrom AND @DateTimeTo
ORDER BY [DateTime]
OPTION (RECOMPILE)

RETURN 0
END

Answer 1

这是典型的因小失大的案例。您每天要插入 1.5 亿条记录，而且您没有使用 Enterprise。

不使用聚簇索引的主要原因是机器无法跟上插入的行数。否则，您应该始终使用聚簇索引。是否使用聚簇索引的决定通常在认为每个表都应该有聚簇索引的人和认为可能有 1% 或 2% 的表不应该有聚簇索引的人之间争论不休。 (我没有时间参与关于这个的“宗教”类型的辩论 - 只是研究网络。)除非表上的插入失败，否则我总是使用聚集索引。

我不会使用 STATISTICS_NORECOMPUTE 子句。如果插入失败，我只会将其关闭。请参阅 Kimberly Tripp(MVP 和真正的 SQL Server 专家)的文章，网址为 http://sqlmag.com/blog/statisticsnorecompute-when-would-anyone-want-use-it .

我也不会使用 OPTION (RECOMPILE)，除非您发现查询在实际查询计划中没有使用正确的索引(或连接类型)。如果您的查询每分钟/秒执行多次，这会对您的机器性能产生不必要的影响。