比较存储过程性能 ex 和新版本 [英] Comparing stored procedure performance ex and new version

问题描述

我确实在 sp 上使用的表上创建了两个新索引.新结果表明，对于有问题的连接，扫描转换为搜索.我认为搜索比扫描操作更好.另一方面，时间与没有新索引的时间大致相同.

I did create two new indexes on the tables that are used on a the sp. The new results shows that on the part of problematic joins, the scans are converted to seek. I think seek is better rather than scan operations. On the other hand, the time takes more or less the same duration as it was without new indexes.

很明显，在将我的新版本 sp 投入生产之前，我如何才能感到满意.

So clearly, how can i get satisfied before putting my new version sp to production.

比如，改变sp的参数可以帮助我看看新版本是否比旧版本快还是什么?

For instance, changing parameters of sp can help me to see if the new version faster than old version or what else?

问候黑

推荐答案

要做的几件事:
1) 通过在每次测试运行后清除数据和执行计划缓存，确保公平地比较性能.您可以使用以下方法清除这些内容(建议仅在您的开发/测试环境中执行此操作):

A few things to do:
1) ensure you are comparing performance fairly by clearing the data and execution plan cache after each test run. You can clear these down using (recommend only doing this on your dev/test environment):

CHECKPOINT -- force dirty pages in the buffer to be written to disk
DBCC DROPCLEANBUFFERS -- clear the data cache
DBCC FREEPROCCACHE -- clear the execution plan cache

2) 运行 SQL Profiler 以记录每种情况的读/写/CPU/持续时间(有/没有索引).这将为您提供一系列可供比较的指标(即与 SSMS 中显示的时间相反).
要运行 SQL Profiler 跟踪，请在 Management Studio 中转到工具 -> SQL Server Profiler.出现提示时，指定要对其运行跟踪的数据库服务器.将出现跟踪属性"对话框 - 您应该能够单击运行"以开始运行默认跟踪.然后只需执行您的存储过程并看到它出现在 SQL Profiler 中 - 它会在旁边显示持续时间、读取次数等.

2) Run SQL Profiler to record the Reads/Writes/CPU/Duration for each situation (with/without the indexes). This will give you a range of metrics to compare on (i.e. as opposed to just the time shown in SSMS).
To run an SQL Profiler trace, in Management Studio go to Tools -> SQL Server Profiler. WHen prompted, specify the db server to run the trace against. A "Trace Properties" dialog will appear - you should just be able to click "Run" to start running a default trace. Then just execute your stored procedure and see it appear in SQL Profiler - it will show the Duration, number of reads etc alongside it.

3) 使用比您已有的数据量大得多的数据进行测试.如果您使用少量数据进行测试，那么仅凭持续时间通常很难看出差异.

3) Test with much larger volumes of data than you already have. If you test with small amounts of data, then the difference is often difficult to see on duration alone.

我最近博客此处关于如何公平地测试查询的不同变体的性能，其中更详细地介绍了我的做法.

I recently blogged here about how to fairly test the performance of different variants of a query, which goes into a bit more detail about how I do it.

这篇关于比较存储过程性能 ex 和新版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！