Monitoring Your Servers : Data Collection vs. Performance Advisor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Aaron Bertrand » Monitoring Your Servers : Data Collection vs....

Monitoring Your Servers : Data Collection vs. Performance Advisor

Rate Topic

Display Mode

Topic Options

Author

Message

Aaron Bertrand

Posted Wednesday, March 18, 2009 11:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 21, 2006 3:11 PM
Points: 2, Visits: 1

Comments posted to this topic are about the item Monitoring Your Servers : Data Collection vs. Performance Advisor

Post #679155

JJ B

Posted Thursday, March 19, 2009 8:55 AM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 3:08 PM
Points: 255, Visits: 2,407

Great article! I thought you did a great job providing the right amount of detail, explanations and pictures. And I liked that you gave a straight up opinion. Thanks.

Post #679506

nicklaz

Posted Tuesday, July 07, 2009 8:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 03, 2009 12:33 PM
Points: 4, Visits: 25

would you recommend performance monitor over idera diagnostic manager or the quest performance tools?

Post #748570

MVDBA

Posted Tuesday, July 07, 2009 8:29 AM

SSCrazy

Group: General Forum Members
Last Login: Monday, April 29, 2013 10:15 AM
Points: 2,261, Visits: 758

they all have pros and con's -

idera sql diagnostic manager tends to be a little flaky and actually consumes a fair amount of CPU from the sql box with the aggressive queries it runs. - but easy to set up and cheap

data collection can give you a massive amount of data and expect 5% cpu usage (or so my colleague informs me) - but free

MVDBA

Post #748592

nicklaz

Posted Friday, July 10, 2009 6:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 03, 2009 12:33 PM
Points: 4, Visits: 25

a couple of my co-workers tried to set up sql sentry but said that it was not intuitive. do you have any experience with the quest performance monitoring tools? how do they compare?

Post #751011

Marios Philippopoulos

Posted Friday, March 26, 2010 3:46 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Thank you for the article.

I have been struggling to figure out what each of the metrics means in the SQL Sentry Performance Advisor dashboard, and I must admit I am a little lost.

Let's take for example the "Page Reads/Writes" graph (History mode) in the SQL SERVER MEMORY section.

I take it these are Reads/Writes from disk(?), which would indicate memory pressure? Is this correct?

I wish there was some documentation on how to best make use of this tool to detect performance issues.

What I would find really useful would be a chart listing the system monitor counters used for each graph.

Anyone have any suggestions?

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #891144

Greg Gonzalez

Posted Wednesday, May 19, 2010 2:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 2:58 PM
Points: 8, Visits: 179

Marios,
I just ran across your post. I know you've had some SQL Sentry training recently so hopefully these and any other questions have already been answered, but I wanted to go ahead and respond just in case.

You are correct, the SQL Server Memory Page Reads/Writes chart shows any time SQL Server reads or writes buffer data to/from disk. On the left you'll note the Windows Read/Write Faults chart with similar palette shows pretty much the same thing, but for Windows. You generally want both of these charts to be as close to zero as possible since it means data is staying in cache and not being continously paged to/from disk because of insufficient memory.

There are some times SQL Server paging will be above zero and it's ok, including:
- checkpoints
- tempdb activity (depends on type and level of activity)
- index rebuilds/defrags
- backups

However, if you see continuously high SQL Server paging when these activities are NOT happening, and it correlates with low page life expectancy (<300 seconds), lazy writes, and/or high disk latency (all of which are also shown in this area on the dashboard), then yes, more than likely you have some memory pressure. To determine which queries may be causing churn, simply highlight any range where you see high paging, then select Jump To->Top SQL, then sort the grid descending by reads or writes. In "Show Totals" mode it will strip any parameters and provide aggregates over the range. Focus on the queries with the highest IOs first -- take a look at the plans, and look for scans, sorts, or other potentially IO-heavy operations. (In our next release, 5.6, you'll be able to do this directly from the Top SQL tab!)

We will be publishing more about how to interpret the metrics we show in the very near future... keep any eye on the SQL Sentry documentation, as well as my blog, http://greg.blogs.sqlsentry.net . In the meantime, I highly recommend checking out the SQL Server Waits & Queues whitepaper as it covers many of them:
http://msdn.microsoft.com/en-us/library/cc966413.aspx?ppud=4

Greg Gonzalez
President, SQL Sentry

Post #924649

Marios Philippopoulos

Posted Wednesday, May 19, 2010 2:35 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:10 AM
Points: 1,824, Visits: 3,477

Thank you for all the information;
I will certainly keep an eye out for upcoming news and documentation of the SQL Sentry Performance Advisor and will be visiting your blog.

__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation

Post #924673

« Prev Topic | Next Topic »

Permissions