分析SQL语句性能3种方法分享

发布时间：2020-12-12 09:26:30 所属栏目：MsSql教程来源：网络整理

导读：第一种方法：代码如下:Minimsdn.com为您提供的代码: -- Turn ON [Display IO Info when execute SQL] SET STATISTICS IO ON -- Turn OFF [Display IO Info when execute SQL] SET STATISTICS IO OFF Link: http://msdn.microsoft.com/zh-cn/library/ms18436

第一种方法：

代码如下:Minimsdn.com为您提供的代码:
-- Turn ON [Display IO Info when execute SQL]
SET STATISTICS IO ON
-- Turn OFF [Display IO Info when execute SQL]
SET STATISTICS IO OFF

Link: http://msdn.microsoft.com/zh-cn/library/ms184361.aspx

第二种方法

:
代码如下:MINIMSDN.com为您提供的代码:
--Turn ON [Display detail info and the request for resources]
SET SHOWPLAN_ALL ON
-- Turn OFF [Display detail info and the request for resources]
SET SHOWPLAN_ALL OFF

Link: http://msdn.microsoft.com/zh-cn/library/ms187735

第三种方法:

Links: http://msdn.microsoft.com/zh-cn/library/ff650689.aspx ; http://msdn.microsoft.com/zh-cn/library/aa175244(v=SQL.80).aspx
Demo For three kinds of Method:
For SQL Script:
代码如下:select * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

Its Execution plan: (

)

Its IO info: (

)

- - You can try one table with 100/10000/1000000 rows but create/don't create Clustered/NONCLUSTERED Index.

Its Detail info Etc.: (

)

For SQL Script:

代码如下:select top 100 * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC

Its Execution plan: (

)

Its IO info: (

)

Its Detail info Etc.: (

)

For SQL Script:

代码如下:select top 100 * from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC
order by StagingOMC.COrgTPName

Its Execution plan: (

)

Its IO info: (

)

Its Detail info Etc.: (

)

For SQL Script:

代码如下:select top 100 StagingOMC.COrgTPName,COUNT(CorgID) from dbEBMSStaging.dbo.MSSalesTxlatOrganizationMaster_Corg StagingOMC
group by StagingOMC.COrgTPName
order by StagingOMC.COrgTPName

Its Execution plan: (

)

Its IO info: (

)

v

Its Detail info Etc.: (

)

- - By these three kinds of methods,you can try to check those words in the internet web are right or wrong about how to improve SQL Script performance.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!