SQL Server“试运行”模式？加载数据缓冲区，无需持有锁或更改数据
我将对 SQL Server 数据库运行一些查询，然后进行删除。理想情况下，所有这些都发生在事务内（即原子事务）。
但实际上，由于数据早已从缓冲区中清除，SQL Server 将必须执行大量物理 IO 才能完成事务处理的 T-SQL。这可能是一个问题，因为如果整个批处理的运行时间超过 30 秒，那么用户将遇到超时问题。
我注意到，如果我分段运行我的select，每次运行越来越多的最终 SQL，让 SQL Server 用越来越多的所需数据填充缓冲区。例如：
第一次运行：
BEGIN TRANSACTION
SELECT ... WHERE ...
ROLLBACK
第二次运行：
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
ROLLBACK
...
第n次运行：
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
...
SELECT ... WHERE ...
ROLLBACK
当我到达最后一次运行时< /strong>：
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
...
SELECT ... WHERE ...
DELETE FROM ... WHERE ...
COMMIT
整个批次运行速度很快，因为缓冲区已预先填充。
SQL Server 是否有一种模式（即SET NOEXEC ON）会导致SQL Server 不执行任何实际的数据修改，不采取任何锁定，而是用所需的数据填充缓冲区？例如
SET NOEXEC ON
EXECUTE ThatThingYouDo
SET NOEXEC OFF
EXECUTE ThatThingYouDo
或
SET DRYRUN ON
EXECUTE ThatThingYouDo
SET DRYRUN OFF
EXECUTE ThatThingYouDo
i'm going to running some queries against an SQL Server database, followed by a delete. Ideally all this happens inside a transaction (i.e. atomic).
But practically, because the data has long since been purged from the buffers, SQL Server will have to perform a lot of physical IO in order to complete the transacted T-SQL. This can be a problem, because if the entire batch takes longer than 30 seconds to run, then users will experience timeout problems.
i noticed that if i run my selects in pieces, each time running more and more of the final SQL, letting SQL Server fill the buffers with more and more of the required data. e.g.:
First run:
BEGIN TRANSACTION
SELECT ... WHERE ...
ROLLBACK
Second run:
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
ROLLBACK
...
n-th run:
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
...
SELECT ... WHERE ...
ROLLBACK
And by the time i reach the final run:
BEGIN TRANSACTION
SELECT ... WHERE ...
SELECT ... WHERE ...
...
SELECT ... WHERE ...
DELETE FROM ... WHERE ...
COMMIT
The entire batch runs fast, since the buffers are pre-filled.
Is there a mode of SQL Server (i.e. SET NOEXEC ON) that would cause SQL Server to not perform any actual data modifications, not take any locks, but fill the buffers with needed data? e.g.
SET NOEXEC ON
EXECUTE ThatThingYouDo
SET NOEXEC OFF
EXECUTE ThatThingYouDo
or
SET DRYRUN ON
EXECUTE ThatThingYouDo
SET DRYRUN OFF
EXECUTE ThatThingYouDo
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
编号
假设您对插入的行执行了 INSERT 操作，然后进行了 UPDATE 操作。您将永远无法模拟 UPDATE，因为插入的行不存在。现在，在这种情况下，为什么你的选择是在事务中？默认情况下（即除非您使用 HOLDLOCK 或类似的），您不会在事务持续时间内锁定行。
如果您认为缓冲池（又名数据缓存）已“满”，那么您需要更多 RAM 或其他升级/扩展。
No.
Let's say you have INSERT followed by UPDATE on the inserted rows. You'll never be able to emulate the UPDATE because the inserted rows don't exist. Now, in this case, why are your selects in a transaction? By default (i.e. unless you use HOLDLOCK or similar), you are not locking the rows for the duration of the transaction.
If you think the buffer pool (a.k.a. the data cache) is "full," then you need more RAM or some other upgrade/scale up.
我发现，每当你尝试做一些非常不正常的事情来解决问题时，你的基本设计很可能就是问题所在。这是极不正常的。
也许您可以提供有关 DELETE 的更多信息（表大小、活动、索引、要删除的行、运行的其他进程等），这需要很长时间，并且将有一个传统的解决方案，使用索引或锁定等来解决它。
I have found that whenever you try to do something extremely out of the normal to solve a problem, that you basic design is most likely the problem. This is extremely out of the normal.
Perhaps you could provide more info on the DELETE (table size, activity, index, rows to delete, other processes running, etc) that is taking so long and there will be a conventional solution, using an index or locking , etc to address it.