I am happy to announce SQLWATCH 2.0 beta 4 which includes the following changes:
Table of Contents
New XE Sessions
[new] #68 – New Extended event session (SQLWATCH_waits) to capture wait stats over 1 second. By default this session is disabled. There is no requirement to run it. If you chose not to, SQLWATCH will use the default system_health session to get waits details. The difference is that the default session logs waits over 15 seconds and it takes more time to parse the results due to its size. The SQLWATCH session has been optimised for reading and offloading into table. This session feeds into the Wait Stats tab.
[new] #70 – New Extended event session to capture blocking chains (SQLWATCH_blockers). By default, this session is disabled. Note, similarly to the waits session, there is no requirement to run it. system_health session will also capture blocking chains as long as the blocking chain monitor is enabled. Similarly to the waits session, this has been optimised for frequent reads, is smaller and thus requires fewer resources to parse the output. This session feeds the blocking tab.
Additional session to capture long running queries (SQLWATCH_long_queries). This session is also disabled by default and there is currently no dashboard to produce output.
Feed servers from text file
[new] #73 – Power BI dashboard now accepts a list of servers in a text file. By default it will look for C:\temp\list.txt but you can change it by setting a different path in the parameter. A sample file is included in the project.
WhoIsActive tab is back
You may have noticed the WhoIsActive (http://whoisactive.com) tab was removed in beta 3, however, after some suggestions from the community this is now back and improved. There is a new agent job dedicated to WhoIsActive collection. This, and the additional XE sessions give a lot of flexibility around what data is being collected and at what intervals.
Improved timeline shows tasks split by status allowing quick identification of “what gets stuck” and when. Amber colour identifies task that are waiting to be run but SQL does not have resources, likely CPU whilst reds are those that were running but are now stopped waiting for more resources.
New Q&A section
You can now leverage Power BI’s Q&A functionality and ask questions about data without having to modify the dashboard. You can launch Q&A from each tab by clicking Q&A icon in the bottom left corner.
New Index statistics and usage analysis
Historical Index usage statistics and histogram allowing you to track performance and usefulness of your indexes.
Improved Wait Stats dashboard
Wait stats tab now includes:
- Top queries
- Bubble chart showing correlation between number of executions and wait type’s time. This is helpful to understand which waits are causing bottlenecks at the current workload.
- Details table is now an expandable matrix.
Generic Performance Counters chart is back
The generic performance counters chart is now ported from version 1.x and contains a number of tweaks. If you added any custom performance counters to monitor they will appear here.
[new] – added configurable timeout to each PowerBI query
[new] – aligned job naming standard
[new] – improved agent job creation process during installation (this is internal improvement transparent to the end-user)
[fixed] #67 – Agent jobs are now being installed under the context of the account running the install. It is a good practice to run agent jobs under a dedicated service account that does not expire, you will have to change it manually to the preferred account after jobs have been deployed according to your preferences.
[fixed] #72 – batch requests /sec line colour consistency in the activity chart
[fixed] #74 – hardcoded reference to SQLWATCH database in fn_get_missing_indexes
[fixed] #75 – Collation conflict is fixed and SQLWATCH can be now installed on a server with any collation without the extra steps described here: https://sqlwatch.io/docs/known-issues-and-limitations/collation-conflict/
Number of other visual tweaks
More consistent naming standard across the Power BI dashboard. For example, ServerName is now sql_instance. This is also in line with dbatools.io.
To get started, download the beta 4 version, deploy or update SQLWATCH database and place list.txt in C:\temp or specify different folder in the Report parameter.
Here’s a short clip showing how to get started with PowerBI and the list.txt source file once the database has been upgraded:
In Power Query this error often manifest itself as either generic “Formula.Firewall” or with more meaningful message:
As a work around please set PBI to ignore privacy: