locked
Dynamic management views are itself slow? RRS feed

  • Question

  • Are dynamic management views enhance locking / blocking/ latching etc?

    if not, the data is sometime not uptodate they presents?

    Regards,


    Manish

    Sunday, October 7, 2012 4:31 AM

Answers

  • Any other suggestion on this. should we deduce that Some DMVs are slow.

    can we update statisitcs or index on these DMVs too?


    Manish


    DMV's aren't tables, they are reading the in memory structures in SQL Server, so there are no statistics or indexes.  What specifically are you running that you consider to be slow?  Post the query/queries and we can probably better explain to you the reasons you see slow performance.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by LearnerSql Thursday, October 11, 2012 5:28 PM
    Tuesday, October 9, 2012 5:05 PM

All replies

  • Are dynamic management views enhance locking / blocking/ latching etc?

    if not, the data is sometime not uptodate they presents?

    Regards,


    Manish

    The DMV's in SQL Server are not guaranteed to be transactionally consistent in all cases.  For the most part the information provided is accurate for a point in time at which the query against the DMV's was run.  What problem do you think you are having with the DMV's?

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Sunday, October 7, 2012 5:11 AM
  • Here i meant to say as there are latches/Locks placed on DMVs in highly OLTP environment, it should be slow to return if DMVs also follow Isolation levels or they follow readpast hint.

    Manish

    Sunday, October 7, 2012 8:27 AM
  • Here i meant to say as there are latches/Locks placed on DMVs in highly OLTP environment, it should be slow to return if DMVs also follow Isolation levels or they follow readpast hint.

    Manish


    Not from anything I've ever experienced.  If it is slow to return it is usually because you are pulling large amounts of data, like dumping buffer descriptors or querying plan cache.  I usually run those types of queries in read uncommitted isolation just in case, but I've never been able to prove that was beneficial regardless of how big the workload I was looking at actually was.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Proposed as answer by Rama Udaya Sunday, October 7, 2012 4:15 PM
    Sunday, October 7, 2012 9:48 AM
  • yes you will be feel of slowness because Dont know how you are invoking the DMVs either indivually  calling or joining with other DMVs & when you are running it...  usually you will not get the right result when you are running first time usually it requires to run twice but we need to use the DMVs with minimal cut of result by approaching the proper expected outcome results

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Sunday, October 7, 2012 3:16 PM
  • yes you will be feel of slowness because Dont know how you are invoking the DMVs either indivually  calling or joining with other DMVs & when you are running it...  usually you will not get the right result when you are running first time usually it requires to run twice but we need to use the DMVs with minimal cut of result by approaching the proper expected outcome results

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Kindly provide an example that shows it being incorrect on first execution and correct the second time around.  The only cases where I have seen this actually occur are edge case systems with abnormal workloads and it tends to only be around one specific portion of the DMV's.  Even with the fact that transactional consistency is not guaranteed, the DMV's are the BEST way of gathering information about SQL Server and they have the LEAST amount of performance impact for doing so. 

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Sunday, October 7, 2012 3:21 PM
  • Thats correct Jon, I observed during the load... I will keep taking the outcome when I encounter the issue.. Iam thinking might be the Modifications might also includes(ex- for an quick Data modifications).

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Sunday, October 7, 2012 3:26 PM
  • Mansih,

    Performance impact depends on the DMV you use and how you use. I can give you more detail if you are looking for specific DMV.

    Use SQLOS DMV's little carefully. This DMV's enumerate critical structures and classes in scheduler and they need locks/Spinlocks/latches in this critical SOS structures and classes to be thread safe.
    So running this DMV's continously will lock the critical structures of SOS and affect performance.But that doesnt mean 'dont use this DMV'. Use it with care, dont use in loop.

    @Rama : You have mentioned "usually you will not get the right result when you are running first time usually it requires to run twice" This is really intresting . Can you kindly provide the name of DMV which require two runs?

    Thank you,

    Karthick P.K |My Facebook Page |My Site| Blog Space |Twitter

    www.Mssqlwiki.com

    Please click theMark as answerbutton if this reply solves your problem


    Sunday, October 7, 2012 4:23 PM
  • while using to identify Disk bottlenecks to see pending I/O
    sys.dm_io_pending_io_requests  by joining the sys.master_files along with sys.dm_io_virtual_file_stats(I used few weeks back ).

    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Sunday, October 7, 2012 4:52 PM
  • Any other suggestion on this. should we deduce that Some DMVs are slow.

    can we update statisitcs or index on these DMVs too?


    Manish


    • Edited by LearnerSql Monday, October 8, 2012 6:17 PM
    Monday, October 8, 2012 6:14 PM
  • Any other suggestion on this. should we deduce that Some DMVs are slow.

    can we update statisitcs or index on these DMVs too?


    Manish


    DMV's aren't tables, they are reading the in memory structures in SQL Server, so there are no statistics or indexes.  What specifically are you running that you consider to be slow?  Post the query/queries and we can probably better explain to you the reasons you see slow performance.

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by LearnerSql Thursday, October 11, 2012 5:28 PM
    Tuesday, October 9, 2012 5:05 PM
  • Any other suggestion on this. should we deduce that Some DMVs are slow.

    >>This not correct DMVs plays important role incase of an any type of an issue that occurs it may be perofrmance or anything releated to securities & others.. In my case It is totally different as said above.

    can we update statisitcs or index on these DMVs too?

    >>No you cannot ...

    Also see the Jon response as well...for the same.


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Tuesday, October 9, 2012 7:05 PM