查看Sql Server语句执行效率,时间的方法
来源:互联网 发布:淘宝买欧式家具靠谱吗 编辑:程序博客网 时间:2024/05/21 08:02
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.
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.
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.
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- 查看Sql Server语句执行效率,时间的方法
- sql server 查看执行效率低下的语句
- 查看sql语句执行效率
- 查看SQL语句执行效率
- sql server中如何查看执行效率不高的语句
- sql server中如何查看执行效率不高的语句
- sql server中如何查看执行效率不高的语句
- 优化sql语句执行效率的方法
- Explain命令查看MySQL的SQL语句执行效率
- MSSQL Server中通过查看SQL语句执行所用的时间
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- MySQL查看SQL语句执行效率
- Postgresql查看SQL语句执行效率
- hdu 3236 Gift Hunting 二维01背包
- hibernate 批量删除方法
- poj3253 优先队列
- 枣庄看内分泌失调到哪所最好
- android中常用的文本加密方式总结
- 查看Sql Server语句执行效率,时间的方法
- Vim中Python自动补全
- class文件的动态加载
- N阶矩阵及其对角线之和
- poj_1135_最短路径&枚举
- oracle sql书写规范
- 数据结构学习之单链表
- STL vector用法介绍
- uva 10791 Minimum Sum LCM ( 唯一分解定理 )