查看Sql Server语句执行效率，时间的方法

最新推荐文章于 2024-01-25 08:58:13 发布

lglgsy456

最新推荐文章于 2024-01-25 08:58:13 发布

阅读量3k

点赞数

本文链接：https://blog.csdn.net/lglgsy456/article/details/38511069

版权

This tutorial proposes 3 ways in order for you to get the Execution time of SQL Query or Stored Procedures are called or submitted to your SQL Server.

They will give you durations in microseconds and base on the execution time, you may have a deeper understand and will do some optimization for your database structure/indexing to make it runs better.

1. Using SQL Server Profiler

I think it’s a easiest way for you to trace/track Which Stored Procedures Or SQL Commands Are Running On SQL Server and how long it takes for each of SQL Query/ Stored Procedure execution.

SQL Server Profiler Execution Time

As you see, all commands are in TextData column and all Execution time for each are in Duration column respectively.

2. Using SQL Script with @StartTime and @EndTime parameters

The script should be run on SQL Server Management Studio Query.

 
        USE AdventureWorksDW; 
       
        GO  
       
        DECLARE 
         @StartTime datetime,@EndTime datetime 
       
        SELECT 
         @StartTime=GETDATE() 
       
        SELECT 
         *  
        FROM 
         DimCustomer  
        where 
         Gender =  
        'M' 
       
        SELECT 
         @EndTime=GETDATE() 
       
        SELECT 
         DATEDIFF(ms,@StartTime,@EndTime)  
        AS 
         [Duration  
        in 
         microseconds]

Just replace your own SQL statements with line 2, after execute the statement, it will show the Duration in microseconds in another result panel.

Get Execution Time With Start Time And End Time

3. Using SQL Script with SET STATISTICS TIME (Transact-SQL)

It displays the number of milliseconds required to parse, compile, and execute each statement.

Run this SQL script on your SQL Query:

 
        USE AdventureWorksDW; 
       
        GO  
       
        SET 
         STATISTICS 
         TIME 
         ON 
       
        GO 
       
        SELECT 
         *  
        FROM 
         DimCustomer  
        where 
         Gender =  
        'M' 
       
        Go 
       
        SET 
         STATISTICS 
         TIME 
         OFF 
        ; 
       
        GO

And below is the result set:

 
        SQL Server parse and compile time:  
       
        CPU time = 0 ms, elapsed time = 1 ms. 
       
        SQL Server parse and compile time:  
       
        CPU time = 0 ms, elapsed time = 1 ms. 
       
        (9351 row(s) affected) 
       
        SQL Server Execution Times: 
       
        CPU time = 63 ms,  elapsed time = 479 ms. 
       
        SQL Server parse and compile time:  
       
        CPU time = 0 ms, elapsed time = 1 ms.

Get Execution Time With SET STATISTICS TIME

That’s all! Feel free to contribute your own solution by submitting your comments as you’re always be welcome.

- See more at: http://4rapiddev.com/sql-server/capture-and-display-execution-time-of-sql-query-in-sql-server/#sthash.OgMz1Qq4.dpuf

lglgsy456

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
查看Sql Server语句执行效率，时间的方法

This tutorial proposes 3 ways in order for you to get the Execution time of SQL Query or Stored Procedures are called or submitted to your SQL Server.They will give you durations in microseconds a
复制链接

扫一扫