November 3, 2008 at 8:15 am
What's the best test methodology for comparing the performance of multiple, logically equivalent versions of a stored procedure? Browsing through the internet, it seems that you should use the following:
1. SET STATISTICS TIME ON - CPU time measurement
2. SET STATISTICS IO ON - compare scan counts, logical/physical reads
When I run the stored procedures multiple times with the same parameters, it seems that version A outperforms version B some of the time (i.e., 700 ms vs. 500 ms CPU time for first run; 200 ms. vs. 400 ms CPU time for second run). Additionally, changing the input parameters (i.e., report_date = '7/6/2008' versus '7/20/2008') causes "inconsistencies" in time and I/O results (i.e., scan count = 300 vs. 500 for 7/6/2008; scan count = 700 vs. 600 for 7/20/2008).
Is there a better methodology for testing performance? Or should I just go through all possible iterations and choose the one with the best "average"?
November 3, 2008 at 9:16 am
You should also be looking at the execution plans to see how they're using/not using indexes. If you're getting widely disparate behaviors on one proc based on different data, you need to make sure you've got up to date statistics on the tables being referenced and you need to check the execution plan, you might be seeing different plans. I'd suggest flushing the cache between each test so that you're seeing compile times and actual reads vs. reads from memory and cache.
Other than that, you've pretty much got it. Check the number of scans and I/O and the execution time. Find the one that is the best in all three most of the time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 3, 2008 at 11:33 am
Is there a threshold for CPU/elapsed time and IO costs where two stored procedures are close enough to be considered "equivalent"? Especially with time-based measurements, multiple executions of the same stored procedure with the same input parameters don't produce identical results.
November 3, 2008 at 12:27 pm
The margin of error is, I believe, +-3ms. So anything within that range is not worth talking about. Other than that, it depends on the situation. I've had two versions of a procedure that were about 15ms apart. I wouldn't normally worry about that, but in this case, the procedure was getting called about a thousand times a minute, so squeezing an extra 15ms out of it made a huge difference.
As to yours, you just need to understand whether or not the performance difference from different parameters is simply a cause of different results being returned (because moving 1 row takes longer than moving 100) or because you're getting poor performance and different execution plans for different parameter sets. The second option is a concern, the first is the cost of doing business.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 3, 2008 at 9:09 pm
jlp3630 (11/3/2008)
What's the best test methodology for comparing the performance of multiple, logically equivalent versions of a stored procedure? Browsing through the internet, it seems that you should use the following:1. SET STATISTICS TIME ON - CPU time measurement
2. SET STATISTICS IO ON - compare scan counts, logical/physical reads
When I run the stored procedures multiple times with the same parameters, it seems that version A outperforms version B some of the time (i.e., 700 ms vs. 500 ms CPU time for first run; 200 ms. vs. 400 ms CPU time for second run). Additionally, changing the input parameters (i.e., report_date = '7/6/2008' versus '7/20/2008') causes "inconsistencies" in time and I/O results (i.e., scan count = 300 vs. 500 for 7/6/2008; scan count = 700 vs. 600 for 7/20/2008).
Is there a better methodology for testing performance? Or should I just go through all possible iterations and choose the one with the best "average"?
The first run is always at a disadvantage compared to additional runs because of cache... the 2nd+ runs have cached data. Lookup DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. And, then don't do either on a production box or you may slow down the whole thing for a period.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2008 at 10:45 am
I have 2 versions of a stored procedure with I/O and time statistics below. I used DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE before running these on my local computer.
The first one takes up more CPU time and I/O (tbl_pa_product_label_sets has 322 more logical reads, tbl_pa_products has 119 more logical reads), but less elapsed time than the second one. Additionally, the first stored procedure execution plan subtree cost is 33.8019 versus 8.87153 for the second one.
Is the second stored procedure better, or is it really a toss up?
Stored Procedure #1:
Table 'tbl_pa_classification_labels'. Scan count 2, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_label_sets'. Scan count 2, logical reads 139, physical reads 6, read-ahead reads 233, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_batch_information'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_product_details'. Scan count 373, logical reads 3902, physical reads 1, read-ahead reads 160, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_product_label_sets'. Scan count 381, logical reads 2444, physical reads 2, read-ahead reads 88, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_products'. Scan count 390, logical reads 14386, physical reads 69, read-ahead reads 3344, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_brands'. Scan count 65, logical reads 260, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 330 ms, elapsed time = 2124 ms.
Stored Procedure #2:
Table 'tbl_pa_product_details'. Scan count 373, logical reads 3589, physical reads 1, read-ahead reads 160, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_product_label_sets'. Scan count 381, logical reads 2122, physical reads 4, read-ahead reads 72, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_products'. Scan count 390, logical reads 14505, physical reads 79, read-ahead reads 3264, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_brands'. Scan count 65, logical reads 260, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_batch_information'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_label_sets'. Scan count 2, logical reads 139, physical reads 6, read-ahead reads 233, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_pa_classification_labels'. Scan count 2, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 4447 ms.
November 5, 2008 at 5:37 pm
I'd be tempted to use the one that uses the fewest resources... but lemme ask... are you returning any output from the procs to the screen?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2008 at 3:57 am
It's clearly not a wash, one of the procedures is doing something more effeciently than the other, but the return times don't seem indicative of the resource cost. Can you post the execution plans?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 6, 2008 at 8:09 am
Jeff Moden (11/5/2008)
I'd be tempted to use the one that uses the fewest resources... but lemme ask... are you returning any output from the procs to the screen?
During my test runs, I was returning the same output to the screen.
Here was the structure of my test:
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
[Run query]
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
[Run query]
November 6, 2008 at 8:20 am
Grant Fritchey (11/6/2008)
It's clearly not a wash, one of the procedures is doing something more effeciently than the other, but the return times don't seem indicative of the resource cost. Can you post the execution plans?
I attached both stored procedures, IO and CPU statistics, and the text version of the execution plans. The differences between the stored procedures are as follows:
1. Stored Procedure 2 - contains hardcoded values
2. Stored Procedure 1 - uses functions when checking pls.pk_fk_label_set_id IN () and p.fk_brand_id IN () instead of "writing out" the text
Please let me know if you need anything else.
November 7, 2008 at 5:40 am
Ah, cool. I'd say sacrifice the CPU for speed. Go with option 1. I like the use of single statement functions and the lack of hardcoded values. If had to make a choice between memory, disk or cpu to be limited by, I'd pick CPU because you can almost always buy a bigger machine. You can't always get more memory or increase disk I/O.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply