Post reply

Performance Monitoring

  • July 17, 2012 at 1:25 pm

    #259574

    Application team is doing performace testing and want to know the top SQL and their execution plans (whether indexes used or not)

    Waht exactly I need to configure to get those details?

  • July 17, 2012 at 2:26 pm

    #1513884

    Hi gmamata7,

    You didn't really say what the top queries are rated by (disk IO, cpu time, etc..)

    Here is a query I use to find cached plans with tablescans in them.

    I've added some ORDER BY clauses you can use to find the top queries for IO, CPU, most executed, or most re-used. Just uncomment the ORDER BY that applies to your situation.

    SELECT TOP 50

    Query_Text.text AS [SQL],

    Cached_Plan.cacheobjtype,

    Cached_Plan.objtype,

    DB_NAME(Query_Text.dbid) AS [DatabaseName],

    Cached_Plan.usecounts AS [Plan usage],

    Query_Plan.query_plan ,

    Cached_Plan.*

    FROM

    sys.dm_exec_cached_plans Cached_Plan

    CROSS APPLY

    sys.dm_exec_sql_text(Cached_Plan.plan_handle) Query_Text

    CROSS APPLY

    sys.dm_exec_query_plan(Cached_Plan.plan_handle) Query_Plan

    JOIN

    sys.dm_exec_query_stats Query_Stats

    ON

    Cached_Plan.Plan_Handle = Query_Stats.Plan_Handle

    WHERE 1 = 1

    -- Plan has a tablescan in it...

    --AND CAST(Query_Plan.query_plan AS NVARCHAR(MAX)) LIKE ('%Tablescan%')

    -- Plan has parallelism in it...

    --AND CAST(Query_Plan.query_plan AS NVARCHAR(MAX)) LIKE ('%parallel="1"%')

    ORDER BY

    -- Highest reuse...

    -- usecounts DESC

    -- Highest IO...

    -- (Query_Stats.total_logical_reads + Query_Stats.total_logical_writes) / Query_Stats.execution_count DESC

    -- Highest execution time...

    --Query_Stats.total_worker_time / Query_Stats.execution_count DESC

    -- Most executed...

    --Query_Stats.Execution_Count DESC

    I hope this helps.

  • July 17, 2012 at 2:42 pm

    #1513893

    gmamata7 (7/17/2012)

    Application team is doing performace testing and want to know the top SQL and their execution plans (whether indexes used or not)

    Waht exactly I need to configure to get those details?

    Not much, by default the reports in the server should be able to get what you're looking for.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 17, 2012 at 9:46 pm

    #1513983

    the performance test will be 1 hour and I want to gather performance statistics for that hour only ?

    SO do I need run some queries continuously or configure Profiler?

  • July 17, 2012 at 10:02 pm

    #1513986

    You can restart the server before the test and see the reports as Craig Farrell suggested after the test. This would ensure the reports are only showing the stats for the test. Apart, it would be great if you could configure the Perfmon counters also in the server.

  • July 18, 2012 at 6:57 am

    #1514184

    If restarting the server before the test is not an option, you could reset the plan cache using DBCC FREEPROCCACHE

  • July 24, 2012 at 3:04 pm

    #1517087

    I restarted the sql server service before starting the performance test and I noticed that Memory\Available MBytes

    is very high (12 GB out 16 GB, Max memory is configured to 12 GB). This is normal as we restarted the sql server sevice but it's giving wrong impression that there is issue memory.

    So I think we should not restart the sql server service before starting the performance test. Please provide your inputs

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply