CODEWITHSUNDEEP
sql-serverbenchmarking
billinkc
billinkc

Reputation: 61269

Query benchmarking

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

SSIS Aggregate vs Database Aggregate

Similar questions but not what I was looking for

tl;dr;

To make the evaluations as even as possible, should I

If the decision is to flush, is dbcc freeproccache sufficient or do I need to dbcc dropcleanbuffers

Upvotes: 3

Views: 1743

Answers (1)

JNK
JNK

Reputation: 65217

I think you really want to clear the page cache between runs.

If it were me I would run something like:

DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

The FREEPROCCACHE only clears the plan cache, and has nothing to do with data pages.

The CHECKPOINT will flush all dirty pages to disk.

The DROPCLEANBUFFERS will clear the buffer pool of data pages.

I would also consider SET STATISTICS IO ON and SET_STATISTICS TIME ON for anything run in SSMS.

Upvotes: 5

Related Questions