Geek Answers Handbook

SQL Server profiling, how should I do this?

So, I used SQL Profiler and I know how I can see what my LINQ queries are doing behind the scenes. We are now in the process of identifying queries that may take longer and need to be optimized or indexed.

So, now, when I look through my LINQ queries in the profiler working nearby, there is a lot of other data and queries that I don’t need. One way or another, a profiler or other tools can sort the queries in the order of the greatest time ... so I will work on optimizing it. I want to run my application, and then somehow see in the profiler the worst requests in the batch.

Can someone guide me or guide me to something more useful while trying to do profiling with sql server 2005. Any ideas or suggestions or best ways about profiling are also welcome. Thank.

+5
source share
3 answers

Here is a DMV request that will list requests with some details about processor time. Once you define a query, run it with the help Include Actual Execution Planto see the flow of queries and where you might need indexing.

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 article about the discovery of performance problems.

+3
source

" " , - :

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 
ORDER BY 3 DESC

. http://msdn.microsoft.com/en-us/library/ms189741%28SQL.90%29.aspx

+2

Profiler. " " " ". " " "". " " . , , 5 , 5000.

.

+1

More articles:


All Articles