The Upgrade Process
The upgrade to v4 was very straightforward. While it didn't initially give any initial indication that it had discovered that v3 was installed, it did carry over all of my settings and at the end of the process it warned that a downgrade was not possible due to the changes that the upgrade made to the structure of the repository database.
The upgrade was quick and painless. My only wish here would be for it to check (or indicate) that it found another version already installed. This would improve confidence level through the install process (I ended up going back and forth a couple times between the install and the current version to verify folder path and configured ports).
New Features
Within moments of launching the new version, I immediately noticed two new features.
- DBs list shortened to first 5 databases in SQL Instance view
- When you select an instance of SQL, SQL Monitor v4 only lists the first 5 databases on that server. I have several servers with 500-2000 databases and by the time SQL Monitor v3 listed them all, it's time to refresh the page (making the SQL Instance page nearly useless as it was constantly refreshing/loading). This feature greatly improves loading speed of the main page for SQL Instance monitoring.
- The issue still exists if you expand all the DBs on the server, but you can just click the Pause button (or rewind time 1 minute) to suspend the page refreshes. Once you've drilled into the specific database you're interested in, you can click the Return to present button to resume real-time monitoring (and page refreshes).
- SQL Waits Monitoring
- Top 10 SQL Server wait stats (sorted by Wait time, Waiting tasks, Average Wait time, or Signal wait time) with a brief description of each wait state.
- Each wait state breaks out the top 10 queries that generated those waits (sortable by Execution count, Duration, CPU time, Physical or Logical reads, or Logical writes)
- This is the best new feature of SQL Monitor v4, and this is what Daniel and I spent so much time discussing.
- Just knowing the query resource utilization only reveals part of the story. By monitoring the wait stats, you can better focus your tuning efforts and have a much clearer understanding of what SQL Server is doing.
Existing features that I love
SQL Monitor does a great job in multiple areas:- Analysis
- Easy access the performance graphs, just click on a metric on the main page to drill into the analysis graph
- Baselines (Regions View) - Awesome for tracking problems that occur regularly or comparing performance over multiple time periods
- Overlay additional stats (you can stack multiple metrics on the same graph to help pinpoint trouble areas)
- Alerts
- Many default alerts are active out of the box to give you a good idea of what your SQL Server is doing that might not be best practice
- Deadlocks appear here and you can drill into the detail of the deadlock participants.
Wish list
SQL Monitor does a great job monitoring many aspects of SQL Server (and the underlying OS), but there are a few new features I'd love to see added to make the product even more robust:
- Charts for SQL waits and query resource usage
- You can currently view charts for server resource utilization (OS and SQL resources) with baselines on the Analysis tab, but charts for wait types and query waits/resources would also be beneficial.
- Charts should be clickable to drill into detail for a specific wait type, resource, or query.
- Charts help to reveal queries that are more bursty in nature. I would be more likely to tune a poorly performing query that completely pegs system resources for a couple minutes than one that consumes the same amount of resources over a larger timeframe.
- # Blocked sessions (clickable to see the blocking tree - including query detail for all queries involved)
- Additional Deadlock detail (graphical deadlock graph would be great, but I did find that the text is available under Alerts -> {specific alert} -> Output)
- Additional Memory stats monitored
- Free list stalls/sec
- Lazy writes/sec
- Page Life Expectancy only reveals so much information and it's value decreases greatly when running on multiple NUMA nodes (PLE is an average across all NUMA nodes. If one NUMA node is crushed, but the others are fine, PLE will not reveal the a true measure of memory pressure). These stats are additional ways to measure memory utilization/pressure on an instance of SQL.
- Signal wait time % (total Signal Wait time/Total Wait Time)
- This is the percentage of time that SQL Server is waiting on the CPU to be ready to process a query (10% = low, 15% = medium, 20% = high and anything over 20% indicates high CPU resource contention)
- This is available as a custom metric (instructions here: http://sqlmonitormetrics.red-gate.com/cpu-signal-wait-time/), but I think this is important enough to include by default
In conclusion, SQL Monitor is maturing into a robust product and the v4 upgrade brings it one step closer to being the best SQL monitoring tool on the planet.
No comments:
Post a Comment