SQL server query optimization tool: Statistics of SQL statement execution time

1. Use SET STATISTICS TIME ON
-- First clear the cache
Dbcc dropcleanbuffers;
Dbcc freeproccache;

-- Run
Set statistics time on;

SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo. Orders
WHERE orderdate> = '123'
AND orderdate <'20140901 ';

Set statistics time off;
GO

You will get a similar message:

SQL Server Analysis and Compilation Time:
CPU time = 15 ms, occupied time = 33 Ms.

SQL Server execution time:
CPU time = 0 ms, occupied time = 6 ms.

The output shows the pure CPU time and actual time consumed to analyze and compile the query, and the time consumed to execute the query.

The problem is: set statistics time on; the number of SQL statements between SET STATISTICS TIME OFF and the number of times it will be output. The reading is quite costly.

2. Use getdate ()
Declare @ d datetime

Set @ d = getdate ()

--------------------------------------------------------
--/**
Declare @ year int

Set @ year = 2000

While @ year <2065

Begin

Exec sp_reset @ year, 1

Set @ year = @ year + 1

End

--------------------------------------------------------

Select [statement execution time (MS)] = datediff (MS, @ d, getdate ())
--*/

Store the GETDATE value in the variable before executing the query, and extract the value stored in the variable after the query. The difference between the two dates is the time when the batch processing is run.

3. Use SQL SERVER Profiler

SQL server Profiler can monitor the SQL queries that are running in the database. If you want to obtain the query run, monitor the SP: StmtCompleted and SQL: BatchCompleted events. The Duration column indicates the execution time.

Advantage: Real-time Monitoring.

4. Use sys. dm_exec_query_stats and sys. dm_exec_ SQL _text.

Sys. dm_exec_query_stats: return the aggregate performance statistics of the cache query plan. Each query statement in the cache plan corresponds to a row in this view, and the row survival is associated with the plan itself. When a plan is deleted from the cache, the corresponding row is also deleted from the view.

Sys. dm_exec_ SQL _text: return the SQL batch processing text identified by the specified SQL _handle. This table value function replaces the system function fn_get_ SQL.

SELECT total_worker_time/1000 n' total CPU time ms'
, Total_elapsed_time/1000 n' total consumed time ms'
, (Total_elapsed_time/execution_count)/1000 N 'average time Ms'
, SUBSTRING (st. text, (qs. statement_start_offset/2) + 1,
(CASE statement_end_offset
WHEN-1 then datalength (st. text)
ELSE qs. statement_end_offset END
-Qs. statement_start_offset)/2) + 1) n'execution statement'
FROM sys. dm_exec_query_stats AS qs
Cross apply sys. dm_exec_ SQL _text (qs. SQL _handle) st

Advantage: You can obtain the SQL query history and the time they spent separately. This is useful in historical analysis.