SQL SERVER 过程性能不一致 [英] SQL SERVER Procedure Inconsistent Performance

问题描述

我正在处理一个 SQL 作业，它涉及 5 个 proc、几个 while 循环以及大量的插入和更新.

I am working on a SQL Job which involves 5 procs, a few while loops and a lot of Inserts and Updates.

此作业处理大约 75000 条记录.

This job processes around 75000 records.

现在，该作业可以正常处理 10000/20000 条记录，速度约为 500/min.在大约 20000 条记录之后，执行就终止了.它每 30 分钟加载大约 3000 条记录并保持相同的速度.

Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at same speed.

我怀疑是网络，但不确定.这些类型的查询很难通过 SQL 性能监视器进行分析.不太确定从哪里开始.

I was suspecting network, but don't know for sure. These kind of queries are difficult to analyze through SQL Performance Monitor. Not very sure where to start.

此外，在其中一个 proc 中有一个游标，它只针对很少的记录执行.

Also, there is a single cursor in one of the procs, which executes for very few records.

关于如何在全尺寸数据集上加快此过程的任何建议?

Any suggestions on how to speed this process up on the full-size data set?

推荐答案

我会检查您的更新是否在事务中.如果是，它可以解释为什么它在经过一定数量的修改"数据后死亡.您可以检查您的tempdb"有多大作为指标.

I would check if your updates are within a transaction. If they are, it could explain why it dies after a certain amount of "modified" data. You might check how large your "tempdb" gets as an indicator.

我也看到过这样的情况，在长时间运行的事务期间，数据库会在同时有其他用途"时死亡，同样是因为使用了事务性和不正确的隔离级别.

Also I have seen cases when during long-running transactions the database would die when there are other "usages" at the same time, again because of transactionality and improper isolation levels used.

如果您可以将您的工作拆分为独立的非重叠块，您可能想要这样做:例如按日期、根"对象的 ID 范围等分块进行工作.

If you can split your job into independent non-overlaping chunks, you might want to do it: like doing the job in chunks by dates, ID ranges of "root" objects etc.

这篇关于SQL SERVER 过程性能不一致的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！