SQL Server 分析我应该怎么做? [英] SQL Server Profiling how should I go about it?

问题描述

所以我使用了 SQL Profiler之前，我知道如何查看我的 LINQ 查询在后台执行的操作.现在，我们正在识别可能需要更多时间并需要优化或有一些索引的查询.

所以，现在当我在并排运行的分析器中查看我的 LINQ 查询时，有很多我不关心的其他数据和查询.无论如何，探查器或其他一些工具是否可以按最大时间的顺序对查询进行排序....以便我将对其进行优化.我想运行我的应用程序，然后以某种方式在分析器中查看最糟糕的查询.

任何人都可以指导或指导我尝试使用 sql server 2005 进行分析时更有用的东西.也欢迎任何有关分析的想法或建议或最佳方法.谢谢.

解决方案

这是一个关于 DMV 的查询，它会列出一些关于 CPU 时间的详细信息.查明查询后，在设置了 Include Actual Execution Plan 的情况下运行它，以查看查询流程以及您可能需要索引的位置.

选择highest_cpu_queries.plan_handle，highest_cpu_queries.total_worker_time，q.[文字]从(选择前 50 名qs.plan_handle,qs.total_worker_time从sys.dm_exec_query_stats qs按 qs.total_worker_time desc 排序)作为highest_cpu_queries交叉应用 sys.dm_exec_sql_text(plan_handle) 作为 q按highest_cpu_queries.total_worker_time desc排序

这是一篇关于查找性能问题的优秀文章.>

So I have used SQL Profiler before and I know how I can view what my LINQ queries are doing behind the scenes. Now, we are in the process of identifying queries that may take more time and need to be optimized or have some indexing.

So, now when I view my LINQ queries in the profiler running it side by side there is lot of other data and queries that I dont care. Is there anyway the profiler or some other tools could sort the queries in the order of the largest time....so that I will work on optimizing it. I want to run my application and then see somehow in the profiler the worst queries of the lot.

Can anyone guide or direct me towards something that is more useful trying to do profiling with sql server 2005. Also any ideas or suggestions or best ways about going about profiling are welcome. Thanks.

解决方案

Here is a query on a DMV that will list queries with some details on CPU time. Once you pinpoint the query run it with Include Actual Execution Plan set on to see the query flow and where you might need indexed.

select  
    highest_cpu_queries.plan_handle,  
    highest_cpu_queries.total_worker_time, 
    q.[text] 
from  
    (select top 50  
        qs.plan_handle,  
        qs.total_worker_time 
    from  
        sys.dm_exec_query_stats qs 
    order by qs.total_worker_time desc) as highest_cpu_queries 
    cross apply sys.dm_exec_sql_text(plan_handle) as q 
order by highest_cpu_queries.total_worker_time desc

Here is a good article on finding performance problems.

这篇关于SQL Server 分析我应该怎么做?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！