How to do a simple SELECT/UPDATE/DELETE performance test?

Question

I have a table which might be causing excessive delays during insertion of new records. I'd like to run a test against the database, measuring how long it takes to select, insert, and delete from the table.

Are there any built-in tools to do this against an SQL Server database table?

Answer 1

Simple down to 3ms accuracy:

SELECT GETDATE();
-- run query
SELECT GETDATE();

On SQL Server 2008 and above you can use SYSDATETIME() which has much greater accuracy.

You can also set a variable equal to the current time, run your query, and use DATEDIFF to determine the delta in whatever granularity makes sense (however this won't work across batches).

Other options:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

You can also look at the execution plans to determine where a specific query might have a bottleneck. I might recommend the free Plan Explorer for this (disclaimer: I work for SQL Sentry). If you generate actual plans from within the tool, it will show you actual runtime metrics for each query (everything shown above, without the work).