Sql Server Statement Output

在oracle中，我们要查看查询统计信息，我们可以设计autotrace，可以参考设置SQL*PLUS的AUTOTRACE，那么在sqlserver中如何设置呢？

在sqlserver中也有统计信息，主要有statistics time和statistics io，比如我们执行如下查询，

set statistics time on
set statistics io on

select * from Sales.Orders

set statistics time off
set statistics io off

在MESSAGE标签中我们可以查看到一下一下信息

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 13 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(830 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 169 ms.

其实SQL SERVER MANAGEMENT STUDIO(SSMS)有这样的功能，不需要显示标明，如下图所示：

ps：2012-8-27

除了上面的time和io以外，还有一个profile，这个在SSMS中好像没有设置，可以通过如下命令打开

SET STATISTICS PROFILE { ON | OFF }

主要作用是显示语句的配置文件信息。 STATISTICS PROFILE 对即席查询、视图和存储过程有效。