Best way of benchmarking INSERTs - all inclusive?

If I would like to benchmark how different table definitions affe开发者_如何学编程ct row insertion speed in SQL Server, I guess it's not sufficient to just time transaction from BEGIN to COMMIT: this only measures the time spend to append INSERTs to the (sequential) log. Right?

But the real I/O hit comes when the INSERTs are actually applied to the real table (a clustered index which might be slightly reorganized after the INSERTs). How can I measure the total time used, all inclusive? That is, the time for all the INSERTs (written to log) + the time used for updating the "real" data structures? Is it sufficient to perform a "CHECKPOINT" before stopping the timer?

Due to lack of response I will answer this myself.

As far as I can see in various documentation, I will reach all related disk activity induced by a query by issuing a CHECKPOINT. This will force-write all dirty pages to disk.

If nothing but the query to be measured is executed, the only dirty pages will be the ones touched by the query. The experiments performed seem to support this "theory".

SET STATISTICS TIME ON will give you running and CPU times in MS for each statement you run after setting it

edit: Using the query below you can find out exactly how many pages are dirty in the buffer pool at the time of execution as well as their size in MB and configured max/min memory on server and totals.

SELECT
ISNULL((CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END),'Total Pages') AS [Database Name],
SUM(CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END)  AS [Dirty Page Count],
SUM(CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END)  AS [Clean Page Count],
COUNT(*) * 8.0 / 1024.0 [Size in MB], a.value_in_use [Min Server Memory], 
b.value_in_use [Max Server Memory]
FROM sys.dm_os_buffer_descriptors
INNER JOIN sys.configurations a on a.configuration_id = 1543
INNER JOIN sys.configurations b on b.configuration_id = 1544
GROUP BY [database_id],a.value_in_use,b.value_in_use WITH CUBE
HAVING A.value_in_use IS NOT NULL AND B.value_in_use IS NOT NULL
ORDER BY 1;