Post reply

running DMV's for Performance issue

  • July 4, 2012 at 7:47 am

    #271978

    Is there a way to find out performance of SQL 2008 R2 server for a particular database without running traces.

    Does running DMV's cause any performce impact?

    Thanks in adavance.

  • July 4, 2012 at 7:56 am

    #1508782

    extended events.

    Here is a great blog post by Jonathan Kehayias to get you started.

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • July 4, 2012 at 8:26 am

    #1508792

    Not all DMV's cause performance issue as this data is collected by the sqlserver only overhead is to query them.

    Some DMV's data is not collected by sql server but is collected on demand and thus these might cause issue e.g. dm_db_index_phsyical_stats.If you run this dmv try to run for a specific table or say index do not run it for whole database if your system is already performing bad.

    You can start with DMV's to look at finding the worst performing queries and then based on the output you can decide what actions do you need.

    try the following DMV's sys.dm_exec_query_stats and dm_exec_procedure_stats. Try below to find worst performing queries.. This will give you details based on the server level. To get the details on the database level

    you have to join with extra dmv sys.dm_exec_plan_attributes which will take the plan_handle as input.

    http://www.sqlservercentral.com/scripts/Finding+Top+N+worst+performaing+queries/91698/

    DMV's contains the data since last server restart so if the server has just been started then it might not give you a perfect picture.Also, if the statements/procs are recompiled or uses with recompile or recompile option then you wont have stats and plan details for these statements or procedure..

    But DMV's are a good starting point and see if you can figure out something. As mentoned in previous statement you can use extended events but for that you need to know what kind of data you want to collect.

    You can also try the following link for troubleshooting performance..

    http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/

    This one is good if you want to use trace.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 4, 2012 at 8:41 am

    #1508799

    You can look at the DMVs, the problem with the sys.dm_exec_query_stats and sys.dm_exec_procedure_stats DMVs is that they do not show data since SQL was last started, they show data for queries/procedures whose plans are still in cache. So if the cache gets cleared, plans get aged out, removed or invalidated and recompiled you will get an incomplete picture. There may even be queries whose plans never get cached.

    If that is 'good enough', by all means use the DMVs, but if you want a complete picture of what is running on a server you need to either use SQL TRace or Extended Events.

    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

  • July 4, 2012 at 8:53 am

    #1508806

    Thanks, adding events will have any impact on server perfromance at all?

  • July 4, 2012 at 8:55 am

    #1508807

    Any form of monitoring adds load. Unless you pull huge numbers of events or very large actions, extended events are very light weight. Trace is also a fairly low overhead if you use a server-side trace and keep the events down.

    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

  • July 4, 2012 at 9:12 am

    #1508817

    if i get it right ( from your other post).. you want to monitor few procs. Then I would suggest that try extended events ( or even traces) and cpature only for the SPID which is executing the proc. That way this will have minimal impcat on perf performance and will provide you valuable information..

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • July 4, 2012 at 9:16 am

    #1508818

    Thank you all for the vaulable inputs.

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

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