Monitoring Your Servers : Data Collection vs. Performance Advisor

Question

Monitoring Your Servers : Data Collection vs. Performance Advisor

Aaron Bertrand

Valued Member

Points: 58
More actions
March 18, 2009 at 11:07 pm

#201930

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

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

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

JJ B SSCertifiable Points: 5130 More actions · Answer 1

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.

nicklaz Grasshopper Points: 20 More actions · Answer 2

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

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

nicklaz Grasshopper Points: 20 More actions · Answer 4

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?

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

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?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Greg Gonzalez SSC Journeyman Points: 81 More actions · Answer 6

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]