SQL Server Profiling how should I go about it?

Question

Hi all,

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.

Answer 1

You can start by altering the trace properties when you start the Profiler. Select the Events Selection tab and click on the Column Filters button. In the Edit Filter dialog, select Duration. Then click on Greater than or equal and enter a value. The value is in milliseconds, so if you want to see queries with duration longer than 5 seconds, enter 5000.

You might look at other filters here as well and configure the duration value as needed.

Answer 2

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.

Answer 3

for a "quick an dirty" list of worst queries, you could just use something like this:

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

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