Monday, December 3, 2012

Measuring SQL Query execution time

It is not recommended to measure SQL execution time on a DB, wise guys believe that it is much more meaningful to run performance tests from an application, so the response time will also include a network delay, and SQL provider computation time. In my scenario, I do not have an access to an application, and there are many entry points for a one SQL Query. That is why it is optimized on a DB side. A query below displays performance metrics of a query, in my example I am interested with each query that includes 'VersionedFields' string.
select top 40 * from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt where qt.text like '%VersionedFields%' order by last_execution_time desc
'Elapsed time' is a most important metrics for me. Much more low level way to measure a query response time is to use STATISTICS
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON 
GO
SET STATISTICS TIME ON
GO

-- SQL Query goes here like, SELECT * FROM VersionedFields

DBCC DROPCLEANBUFFERS
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
Notes regarding DROPCLEANBUFFERS. I only run it on a test bed, not on a production.
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.
If one really wants to clear entire cache, a CHECKPOINT should be also used.
[CHECKPOINT] Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
I do not run checkpoint that often.

No comments: