Post reply

SQL Performance Statistics

  • September 19, 2012 at 7:49 am

    #1539572

    SQLSACT (9/19/2012)

    Grant Fritchey (9/19/2012)

    SQLSACT (9/19/2012)

    Grant Fritchey (9/18/2012)

    Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.

    Thanks

    If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?

    Thanks

    It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.

    Thanks Grant

    A question reagarding performance in General, may be out of the scope of this thread

    How do I know if my plan cache is the root of a performance issue on my instance?

    At which point of troubleshooting do I say, ok, now let's look at the plan cache

    Is it usually on the top of the list of things to check when troubleshooting performance?

    Also, is Plan cache performance related more to memory or CPU?

    Thanks

    Plan cache is a memory problem primarily. But, if you're flushing stuff in & out of your cache all the time, it can be a CPU issue, but mainly because compiling is CPU intensive. First, I'd verify that you have a memory problem. Specifically that you are getting internal/VAS memory issues. Before you know that's happening, then I wouldn't sweat the cache. My most recent article on Simple-Talk shows one way to check for internal memory problems[/url].

    "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

  • September 19, 2012 at 8:01 am

    #1539579

    Grant Fritchey (9/19/2012)

    SQLSACT (9/19/2012)

    Grant Fritchey (9/19/2012)

    SQLSACT (9/19/2012)

    Grant Fritchey (9/18/2012)

    Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.

    Thanks

    If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?

    Thanks

    It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.

    Thanks Grant

    A question reagarding performance in General, may be out of the scope of this thread

    How do I know if my plan cache is the root of a performance issue on my instance?

    At which point of troubleshooting do I say, ok, now let's look at the plan cache

    Is it usually on the top of the list of things to check when troubleshooting performance?

    Also, is Plan cache performance related more to memory or CPU?

    Thanks

    Plan cache is a memory problem primarily. But, if you're flushing stuff in & out of your cache all the time, it can be a CPU issue, but mainly because compiling is CPU intensive. First, I'd verify that you have a memory problem. Specifically that you are getting internal/VAS memory issues. Before you know that's happening, then I wouldn't sweat the cache. My most recent article on Simple-Talk shows one way to check for internal memory problems[/url].

    Great Stuff - Thanks

  • September 19, 2012 at 3:32 pm

    #1539825

    SQLSACT (9/19/2012)

    pooyan_pdm (9/18/2012)

    SQLSACT (9/18/2012)

    anthony.green (9/18/2012)

    Basic formula is

    ((Cache Hit Ratio / Cache Hit Ratio Base) * 100)

    Thanks

    And that would give me an accurate % of plan-reuse in my instance?

    What is the Cache Hit Ratio Base based on?

    Thanks

    No.That's the percentage of times that a requested data page was in buffer.

    for plan reuse consider this:

    Initial Compilations = SQL Compilations/Sec – SQL Recompilation/Sec

    Plan Reuse = (Batch Req/sec - Initial Compilations) / Batch Req/sec

    No.That's the percentage of times that a requested data page was in buffer

    I think he was referring to the SQLServer:Plan Cache hit ratio

    How can I incorporate you suggestion into a script?

    Thanks

    DECLARE @Compilation BIGINT,@ReCompilation BIGINT,@BatchRequest BIGINT

    SELECT @Compilation=cntr_value from sys.dm_os_performance_counters WHERE counter_name ='SQL Compilations/sec'

    SELECT @ReCompilation=cntr_value from sys.dm_os_performance_counters WHERE counter_name ='SQL Re-Compilations/sec'

    SELECT @BatchRequest=cntr_value FROM sys.dm_os_performance_counters WHERE counter_name ='Batch Requests/sec'

    SELECT (@BatchRequest-(@Compilation-@ReCompilation))*100.00/@BatchRequest

    Pooyan

  • September 19, 2012 at 3:44 pm

    #1539836

    Chapter 1 http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

    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

  • September 19, 2012 at 11:56 pm

    #1539896

    Grant Fritchey (9/19/2012)

    SQLSACT (9/19/2012)

    Grant Fritchey (9/19/2012)

    SQLSACT (9/19/2012)

    Grant Fritchey (9/18/2012)

    Just remember that sys.dm_os_performance_counters is just querying against the Performance Monitor counters. They're as accurate or as inaccurate as anything else within Performance Monitor because they're both from the same source.

    Thanks

    If I query a per/second performance counter, is my result the same as what the performance counter would be at that time?

    Thanks

    It just depends on how that counter is being collected and displayed by SQL Server. You'd have to read the documentation for it to be sure. But they are absolutely the same source.

    Thanks Grant

    A question reagarding performance in General, may be out of the scope of this thread

    How do I know if my plan cache is the root of a performance issue on my instance?

    At which point of troubleshooting do I say, ok, now let's look at the plan cache

    Is it usually on the top of the list of things to check when troubleshooting performance?

    Also, is Plan cache performance related more to memory or CPU?

    Thanks

    Plan cache is a memory problem primarily. But, if you're flushing stuff in & out of your cache all the time, it can be a CPU issue, but mainly because compiling is CPU intensive. First, I'd verify that you have a memory problem. Specifically that you are getting internal/VAS memory issues. Before you know that's happening, then I wouldn't sweat the cache. My most recent article on Simple-Talk shows one way to check for internal memory problems[/url].

    I found this script on Kimberley Tripp's blog

    SELECT objtype AS [CacheType]

    , count_big(*) AS [Total Plans]

    , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

    , avg(usecounts) AS [Avg Use Count]

    , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

    , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

    FROM sys.dm_exec_cached_plans

    GROUP BY objtype

    ORDER BY [Total MBs - USE Count 1] DESC

    go

    On my instance I've got 19459 adhoc plans and 17172 have only been used once

    That's not good, right?

    Thanks

  • September 20, 2012 at 3:54 am

    #1539958

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    "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

  • September 20, 2012 at 4:03 am

    #1539963

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

  • September 20, 2012 at 4:41 am

    #1539990

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do 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

  • September 20, 2012 at 6:42 am

    #1540038

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do it.

    Thanks

    I'll wait until I'm a bit more clued up before implementation

    Thanks for your help

  • September 20, 2012 at 6:45 am

    #1540041

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do it.

    Thanks

    I'll wait until I'm a bit more clued up before implementation

    Thanks for your help

    Of course. When dealing with information from the internet, follow the lead of Ronald Reagan, trust but verify.

    "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

  • September 20, 2012 at 6:49 am

    #1540044

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do it.

    Thanks

    I'll wait until I'm a bit more clued up before implementation

    Thanks for your help

    Of course. When dealing with information from the internet, follow the lead of Ronald Reagan, trust but verify.

    I'm not that clued up on American Politics (I'm South African) so I'm not sure if that was sarcasm :w00t:

  • September 20, 2012 at 6:54 am

    #1540046

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do it.

    Thanks

    I'll wait until I'm a bit more clued up before implementation

    Thanks for your help

    Of course. When dealing with information from the internet, follow the lead of Ronald Reagan, trust but verify.

    I'm not that clued up on American Politics (I'm South African) so I'm not sure if that was sarcasm :w00t:

    Not stating political support of Reagan one way or another, but that approach was right at the time, and it applies to our situation. Trust certain resources on the internet (Paul Randal, Jonathan Kehayias, Ted Krueger, Jeff Moden, Gail Shaw...) but verify and validate that what they are saying is true and applicable to your situation. Not sarcasm per se, just using the quote because it's applicable.

    By the way, the other quote I frequently use is TANSTAAFL, There Ain't No Such Thing As A Free Lunch. That's from Heinlein's "The Moon is a Harsh Mistress" but it's so applicable to computing that I'm always talking about it. No free lunches and verification are my guiding principals.

    "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

  • September 20, 2012 at 7:00 am

    #1540048

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    SQLSACT (9/20/2012)

    Grant Fritchey (9/20/2012)

    19000 out of how many plans total? What kind of queries are being run against the system? I'm assuming these are not parameterized queries or stored procedures? If so, you might want to try using 'Optimize for Ad Hoc Workload'. It creates plan stubs instead of full plans for the first time a query is called.

    It fluctuates

    I've been checking the system periodically and it consistently shows that over 80% of the adhoc plans have only been used once

    The thing is that this instance is used by many applications so I'm not keen on making a server wide setting.

    Also, there are over 4000 plans of the prepared and proc Cache_type

    There is quite a bit of memory allocated to this instance and I haven't been seeing any signs of memory pressure. This just happened to be the first system I tested the script on, as part of my journey in understanding the plan cache and its effect o performance

    Thanks

    There's no down-side to optimize for ad hoc. I've tested it. I've seen others test it. It works. Paul Randal recommends turning it on for all servers (last time I checked, he changes his mind sometimes). That's one I'd just do. But based on the percentage of ad hoc queries, I'd absolutely do it.

    Thanks

    I'll wait until I'm a bit more clued up before implementation

    Thanks for your help

    Of course. When dealing with information from the internet, follow the lead of Ronald Reagan, trust but verify.

    I'm not that clued up on American Politics (I'm South African) so I'm not sure if that was sarcasm :w00t:

    Not stating political support of Reagan one way or another, but that approach was right at the time, and it applies to our situation. Trust certain resources on the internet (Paul Randal, Jonathan Kehayias, Ted Krueger, Jeff Moden, Gail Shaw...) but verify and validate that what they are saying is true and applicable to your situation. Not sarcasm per se, just using the quote because it's applicable.

    By the way, the other quote I frequently use is TANSTAAFL, There Ain't No Such Thing As A Free Lunch. That's from Heinlein's "The Moon is a Harsh Mistress" but it's so applicable to computing that I'm always talking about it. No free lunches and verification are my guiding principals.

    Great stuff, thanks

    I've been going throught the Troubleshooting SQL Server book by Jonathan Kehayias & Ted Krueger, helps a lot.

Viewing 13 posts - 16 through 27 (of 27 total)

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