Performance Monitoring with Dynamic Management Views

  • GreyBeard

    Mr or Mrs. 500

    Points: 535

    Comments posted to this topic are about the item Performance Monitoring with Dynamic Management Views

  • ianstirk

    Ten Centuries

    Points: 1310

    Hi,

    Very nice article!

    Readers can discover a lot more about improving SQL performance via DMVs in this recently published book "SQL Server DMVs in Action" www.manning.com/stirk. It contains more than 100 scripts to identify problems, and offers a wide range of solutions.

    Chapters 1 and 3 can be downloaded for free. Chapter 1 includes scripts for:

    A simple monitor

    Finding your slowest queries

    Find your missing indexes

    Identifying what SQL is running now

    Quickly find a cached plan

    Thanks

    Ian

  • Dirk.Hondong

    Ten Centuries

    Points: 1024

    Hi Ron,

    I tried to take a look into the paper you´ve mentioned (SQL Server Instance Health Monitoring Tool) but it seems that the pdf is broken. It says the data type is incorrectly formatted.

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • chris.puncher

    SSC-Addicted

    Points: 433

    Good article, although a second one on what the figures mean in the real world would be useful.

    BTW the link to the first chapter of the book works fine, but is a bit slow.

    Chris

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks for the article - good stuff.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Dirk.Hondong

    Ten Centuries

    Points: 1024

    Hi Ian,

    thanks for mentoning the book. It´s now on my wish list

    The sample chapters look promising.

    Regards

    Dirk

    --
    May you never suffer the sentiment of spending a day without any purpose.
    @DirkHondong on Twitter

  • przemyslaw.dusza

    Valued Member

    Points: 59

    I tried to take a look into the paper you´ve mentioned (SQL Server Instance Health Monitoring Tool) but it seems that the pdf is broken. It says the data type is incorrectly formatted.

    yep, pdf is broken 🙁

  • SQLOzzie

    Ten Centuries

    Points: 1373

    Everything downloaded fine for me - nice article.

    Doug

  • SQLOzzie

    Ten Centuries

    Points: 1373

    Ron,

    Where can you download the script manager tool you wrote - that looks like a real neat utility I could use to categorize my scripts.

    Thanks,

    Doug

  • przemyslaw.dusza

    Valued Member

    Points: 59

    Hi

    I was able to download the file, but I can't open it using acrobat reader, it says that file is not a PDF file...

    http://rjssqlservernotes.files.wordpress.com/2011/10/sqlserverinstancehealthmonitor.pdf

    BR

    P

  • lrobbins

    SSC Enthusiast

    Points: 111

    Ron,

    Thanks for taking the time to write an article using a real world example of why DMVs can be much more powerful than their Performance Monitor counterpart.

    Would you say that best use case of DMVs over performance monitor & SQL Profiler would be that the recorded data can be better used as a central repository for various SQL instances (like in your 50 geographical location) case?

    I typically use just performance monitor to do these things, and I'm not too sure of the best use cases for using DMVs over them, other than preference.

    Lastly, the counters you provided, do you recommend to monitor these 24x7 or just in re-active cases or scheduled pro-active cases (e.g., on the 1st day of every month for 24 hours).

  • Pyman

    SSChampion

    Points: 13830

    Thanks much for the article Ron. For the perfmon counters where can I get the threshold values.

    “If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams

  • lrobbins

    SSC Enthusiast

    Points: 111

    Sapen,

    I recommend using this for that:

    SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server)

    Chapter 2 specifically.

    One key thing is that the thresholds are best determined by comparing to a baseline you create. The baseline being what metrics look like under normal operating conditions (when things are going smooth).

  • Datamate Technology

    Old Hand

    Points: 399

    Truly well written article. I personally was researching plans to setup something similar and your article has just made that job so much easier for me. 🙂

  • Slevin

    Mr or Mrs. 500

    Points: 574

    Agreed, I have tried this link in both Firefox and IE and receive the same error:

    "File does not begin with '%PDF-".

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

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