none
问一个Execution plan and optimizer的问题 RRS feed

  • 问题

  • 如果我有两段逻辑相同的SQL,我是从哪个角度分析哪个语句性能更佳。如果是ORACLE执行计划会有一个综合的COST来判断哪个成本高和低,但是在sqlserver的Execution plan里没有一个最终cost的标准,有几个指标例如:EstimatedTotalSubtreeCost、EstimateCPU、EstimateIO、cached plan size等等。

    如果打开SET STATISTICS TIME ON、SET STATISTICS IO ON、SET STATISTICS PROFILE ON参数可以查看SQL Server 执行时间,这个准吗,但是经过我的测试EstimatedTotalSubtreeCost=6 的SQL Server 执行时间居然比EstimatedTotalSubtreeCost=7的执行时间慢?

    2019年12月5日 6:32

答案

  • 清除了执行计划以后,进行比较,这样比较准确

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    2019年12月9日 9:33

全部回复

  • You can put both statements in single query window in ssms and view execution plan, sql will tell you cost of each statement.
    2019年12月5日 18:09
  • Hi database_noob,

     

    时间是准确的,请你以这种格式测试:

       Set statistics time on

       (query)

       Set statistics time off

     

    SQL Server Execution Times: 运行查询的时间实际耗费时间;

    SQL Server parse and compile time:编译查询成本的时间;

    CPU Time:此步骤的CPU时间;

    Elapsed Time: 此步骤的总时间成本;

     

    并且你是否在每次执行时,清除了执行计划?

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    2019年12月6日 9:32
  • Hi database_noob,

     

    时间是准确的,请你以这种格式测试:

       Set statistics time on

       (query)

       Set statistics time off

     

    SQL Server Execution Times: 运行查询的时间实际耗费时间;

    SQL Server parse and compile time:编译查询成本的时间;

    CPU Time:此步骤的CPU时间;

    Elapsed Time: 此步骤的总时间成本;

     

    并且你是否在每次执行时,清除了执行计划?

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

          并没有清除执行计划,我是执行多次看的结果,相当于全部都读缓存里的执行计划
    2019年12月9日 1:43
  • 清除了执行计划以后,进行比较,这样比较准确

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    2019年12月9日 9:33