SQLWATCH 2.0 beta 4

I am happy to announce SQLWATCH 2.0 beta 4 which includes the following changes:

New XE Sessions

SQLWATCH 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.

SQLWATCH Blockers

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.

SQLWATCH WhoIsActive

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.

SQLWATCH Q&A

New Index statistics and usage analysis

Historical Index usage statistics and histogram allowing you to track performance and usefulness of your indexes.

SQLWATCH_index_stats_beta4

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.

SQLWATCH wait stats beta 4

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.

SQLWATCH Perf Mon

Other changes

[new] – added configurable timeout to each PowerBI query

[new] – aligned job naming standard

SQLWATCH Jobs

[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.

Getting started

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.

You must update or deploy SQLWATCH database from VS Project or the included DACPAC. If you have version 1.3.x or if you have deployed SQLWATCH database using dbatools you have to upgrade manually to version 1.5 (beta4) from the VS project (build 1.5.7009.1537)

Here’s a short clip showing how to get started with PowerBI and the list.txt source file once the database has been upgraded:

 

If you encounter a “Formula.Firewall” error please disable Privacy in Options. This error is due to source data being pulled from many sources i.e. csv file with list of servers and the sql serve source. I am working on resolving this before the final release.

 

SQLWATCH Load error

In Power Query this error often manifest itself as either generic “Formula.Firewall” or with more meaningful message:

SQLWATCH Privacy error

 

As a work around please set PBI to ignore privacy:

SQLWATCH PBI Privacy

 

This site, like most websites, uses cookies. By continuing to use this website, you agree to their use.