Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Your Servers : Data Collection vs. Performance Advisor


Monitoring Your Servers : Data Collection vs. Performance Advisor

Author
Message
Aaron Bertrand
Aaron Bertrand
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Comments posted to this topic are about the item Monitoring Your Servers : Data Collection vs. Performance Advisor
JJ B
JJ B
Old Hand
Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)Old Hand (327 reputation)

Group: General Forum Members
Points: 327 Visits: 2846
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
nicklaz
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 25
would you recommend performance monitor over idera diagnostic manager or the quest performance tools?
MVDBA
MVDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 860
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
nicklaz
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
Marios Philippopoulos
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Greg Gonzalez
Greg Gonzalez
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 207
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
Marios Philippopoulos
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 3740
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
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search