查询基准测试
序言
我对使用本机 SQL 聚合函数和 SSIS 中提供的聚合之间的性能差异有一个假设（数据库将压垮 SSIS）。我想开发一个实证过程来测试这一点。
对于初始测试，我唯一关心的是执行速度。后续迭代将涵盖 CPU 和内存成本。
为了实现这一点，我构建了一个小表（100M 行）并希望捕获时间差异。我计划通过比较 OnPreExecute 和 OnPostExecute 的时间来在 SSIS 包中完成这一切。
第一个数据流将采用 SELECT * FROM TABLE 的形式，而后者将是 SELECT Aggregate(col1) FROM TABLE
vs 
类似的问题，但不是我正在寻找的
tl;dr;
为了使评估尽可能均匀，我是否应该
- 在步骤之间刷新缓存
- 通过预先执行查询来预热缓存
- ，
如果决定刷新，则为dbcc freeproccache 足够了还是我需要dbcc dropcleanbuffers
Preamble
I have a hypothesis about the performance difference between using the native SQL aggregate functions and the aggregates supplied in SSIS (database will crush SSIS). I'd like to develop an empirical process for testing that.
For initial testing, my sole concern is execution speed. Later iterations will cover cpu and memory costs.
To accomplish this, I've built out a small table (100M rows) and want to capture the difference in timings. I'm planning on doing it all within an SSIS package by comparing the timings of OnPreExecute and OnPostExecute.
The first data flow will take the form of SELECT * FROM TABLE while the latter would be SELECT Aggregate(col1) FROM TABLE
vs
Similar questions but not what I was looking for
tl;dr;
To make the evaluations as even as possible, should I
- flush the cache between steps
- warm the cache by executing the queries beforehand
- something else
If the decision is to flush, is dbcc freeproccache sufficient or do I need to dbcc dropcleanbuffers
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为您确实想在运行之间清除页面缓存。
如果是我，我会运行类似的命令：
FREEPROCCACHE仅清除计划缓存，与数据页无关。CHECKPOINT将把所有脏页刷新到磁盘。DROPCLEANBUFFERS将清除数据页的缓冲池。对于 SSMS 中运行的任何内容，我还会考虑使用 SET STATISTICS IO ON 和 SET_STATISTICS TIME ON。
I think you really want to clear the page cache between runs.
If it were me I would run something like:
The
FREEPROCCACHEonly clears the plan cache, and has nothing to do with data pages.The
CHECKPOINTwill flush all dirty pages to disk.The
DROPCLEANBUFFERSwill clear the buffer pool of data pages.I would also consider
SET STATISTICS IO ONandSET_STATISTICS TIME ONfor anything run in SSMS.