Post reply

SQL Query Performance

  • July 31, 2012 at 7:05 am

    #392862

    Hi All

    I want to investigate Query Performance on my SQL Instance

    I've been looking at the sys.dm_exec_query_stats DMV

    How up to date is this DMV? Will it show me the longest run queries on my instance since SQL was started?

    Thanks

  • July 31, 2012 at 9:06 am

    #1519777

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 31, 2012 at 10:25 am

    #1519837

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    Thank You

    So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

    Where would I start for a "SQL has been slow erratically"

    Thanks

  • July 31, 2012 at 10:28 am

    #1519841

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

    Am I on the right track here?

    Thanks

  • July 31, 2012 at 11:21 am

    #1519866

    SQLSACT (7/31/2012)

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

    Am I on the right track here?

    Thanks

    Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 31, 2012 at 11:25 am

    #1519869

    SQLSACT (7/31/2012)

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    Thank You

    So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

    Where would I start for a "SQL has been slow erratically"

    Thanks

    Actually, if "SQL Server is slow NOW" I'd look first at sys.dm_exec_requests and then combine that, as needed with sys.dm_exec_query_stats. Again, depending on the volatility of your cache, this is a good place to look. It's just potentially not good because the queries do leave cache. For detailed, specific views, capture every call with extended events. Just be ready to deal with large amounts of data.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 31, 2012 at 11:34 am

    #1519871

    when the cached plans are flushed off the cache next time the query is being executed the plan should be generated again which consumes CPU clocks and could have negative impact on SQL Server performance and if the memory allocated to the plan cache is not big enough the plans are flushed off more quickly

    Pooyan

  • July 31, 2012 at 11:49 pm

    #1520077

    Grant Fritchey (7/31/2012)

    SQLSACT (7/31/2012)

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

    Am I on the right track here?

    Thanks

    Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.

    Thanks

    Besides checking the oldest date,

    How can I check if plans are never being reused?

    Is there some sort of standard as to how long a plan stays in cache? 1 day, 2 days etc..

    Thank you

  • July 31, 2012 at 11:53 pm

    #1520078

    Grant Fritchey (7/31/2012)

    SQLSACT (7/31/2012)

    Grant Fritchey (7/31/2012)

    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    Thank You

    So I would use that DMV as part of troubleshooting for a "SQL is slow NOW" situation

    Where would I start for a "SQL has been slow erratically"

    Thanks

    Actually, if "SQL Server is slow NOW" I'd look first at sys.dm_exec_requests and then combine that, as needed with sys.dm_exec_query_stats. Again, depending on the volatility of your cache, this is a good place to look. It's just potentially not good because the queries do leave cache. For detailed, specific views, capture every call with extended events. Just be ready to deal with large amounts of data.

    Thanks

    I've been doing some reading on the topic of performance, I've taken little pieces from different sections.

    I've put this together for a "SQL is Slow Now" situation, am I on the right track here?

    select ER.session_id,ES.login_time,ER.Command, ER.start_time,ER.blocking_session_id

    ,ER.wait_resource

    ,ER.wait_type

    ,ER.wait_time

    ,ER.reads

    ,ER.writes

    ,DB_NAME(ER.database_id)

    from sys.dm_exec_requests ER

    inner join sys.dm_exec_sessions ES

    on ER.session_id = ES.session_id

    inner join sys.dm_os_waiting_tasks WT

    on ES.session_id = WT.session_id

    where ES.is_user_process <> 0

    Thanks

  • August 1, 2012 at 1:20 am

    #1520107

    By checking the usecount column in sys.dm_exec_cached_plans you can look for plns with value of 1 .duration of a plan staying in the cache depends on the memory available to sql server and frequency of the plan being used. Plans that are not used for a while if there is a need to free up some space for new plans, are flushed off.

    Pooyan

  • August 1, 2012 at 1:28 am

    #1520109

    SQLSACT (7/31/2012)

    I've put this together for a "SQL is Slow Now" situation, am I on the right track here?

    Two things:

    - Firstly cross apply to sys.dm_sql_text for the SQL statement

    - Just google for sys.dm_exec_requests and you'll find lots of scripts that others have written that you can use. No need to write from scratch again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 1, 2012 at 6:09 am

    #1520202

    GilaMonster (8/1/2012)

    SQLSACT (7/31/2012)

    I've put this together for a "SQL is Slow Now" situation, am I on the right track here?

    Two things:

    - Firstly cross apply to sys.dm_sql_text for the SQL statement

    - Just google for sys.dm_exec_requests and you'll find lots of scripts that others have written that you can use. No need to write from scratch again.

    Thank You

    I've taken pieces of scripts that I found and put this together:

    Am I on track here?

    Does this script completely ignore Stored Procedures?

    SELECT TOP 10

    SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

    ( ( CASE statement_end_offset

    WHEN -1 THEN DATALENGTH(t.text)

    ELSE s.statement_end_offset

    END - s.statement_start_offset ) / 2 ) + 1)

    AS statement_text,

    execution_count ,

    statement_start_offset AS stmt_start_offset ,

    sql_handle ,

    plan_handle ,

    total_logical_reads / execution_count AS avg_logical_reads ,

    total_logical_writes / execution_count AS avg_logical_writes ,

    total_physical_reads / execution_count AS avg_physical_reads ,

    t.text,

    qp.query_plan

    FROM sys.dm_exec_query_stats AS s

    CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

    CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp

    ORDER BY avg_physical_reads DESC

    Thanks

  • August 1, 2012 at 7:35 am

    #1520256

    Looks ok. No, it doesn't ignore procedures. Procedures show up in both query stats and proc stats. You can just use proc stats to narrow down what you're querying against if you need it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 1, 2012 at 8:13 am

    #1520295

    Grant Fritchey (8/1/2012)

    Looks ok. No, it doesn't ignore procedures. Procedures show up in both query stats and proc stats. You can just use proc stats to narrow down what you're querying against if you need it.

    Thanks!

    So my query will give me info on Queries whether that Query is from a Procedure or if it's a straight query?

    Thanks

  • August 1, 2012 at 8:16 am

    #1520298

    Yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 17 total)

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