My previous blog talks about sending automated alert (VizAlerts) for slow render workbooks, this blog will show you how to  enforce timeout or other government rules for slow render workbooks.

Problem statement: Slow workbook alert  is important and absolutely necessary. However sometimes the alerts and email renders are not good enough – what if some workbook owners do not take any actions?  What server governance team can do about?

Solution #1: Reduce vizqlserver.querylimit

• vizqlserver.querylimit: Covers specifically the queries executed to return views or populate extracts. The default value is 1800 seconds (or 30 minutes). This can be reduced to 180 seconds (for example).
• What it means? Any queries more than this limit will receive the this  error
• How to change this setting?

• tsm configuration set -k vizqlserver.querylimit -v 300
• tsm pending-changes apply

Solution #2: Remove workbook permission

The solution #1 (Reduce vizqlserver.querylimit) should be good enough for most of governance process to enforce render time.  It is  very simple and effective. Limitations of solution #1  are

• It is for whole server and is not site specific
• It has no exception at all, no matter which workbook

If you want to have more flexibility, you can create custom solution. How to do it?  After a lot of research and discussion with many others, the best custom solution is to create your own selection criteria for slow render workbooks, then remove those workbook permissions. What I did is as followings:

1. Find out the render time pattern of 1% slowest workbooks (based on the data on my server, those 1% slowest workbook took about 20% server VizQL CPU ands memory
2. Find out what the weekly avg render time for those slowest 1% workbooks (it was about 30 seconds for my server after avg due to caches, etc)
3. Now you have the selection criteria of identifying slowest workbooks that you wanted to take actions on
4. Use my previous blog  to send  automated alert (VizAlerts) for slow render workbooks
5. I will recommend to send 3 warnings before the enforced actions – it means that the slowest workbook have to be slow and met selection criteria for 4 weeks in the row before enforcement actions
6. At 4th week, remove the workbook’s permissions and send auto email to let workbook owner know that the workbook’s permissions are removed and the email should include all permissions being removed – this email can be achieved by another VizAldert and permission query and deletion can be done using REST API :
   • Query workbook permission API GET /api/api-version/sites/site-id/workbooks/workbook-id/permissions
   • Delete workbook permissions API

     DELETE /api/api-version/sites/site-id/workbooks/workbook-id/permissions/groups/group-id/capability-name/capability-mode  and DELETE /api/api-version/sites/site-id/workbooks/workbook-id/permissions/users/user-id/capability-name/capability-mode

Conclusions:

1. You should reduce vizqlserver.querylimit from default 1800 seconds to somewhere 180-300 seconds
2. Next step is to implement slow workbook VizAlert warning
3. Depends on your organization culture, if VizAlert alone does not change publisher’s behavior, you can take enforcement actions to remove workbook permissions automatically  after a few warning.  Why remove permission is the best approach:

• It stops end user access the workbook so no more slow render that impacts everyone else on the server
• It still allows workbook owners to work on the perf tune which is what you wanted
• It gives workbook owner flexibility to re-grant workbook access right away to meet business continuity for whatever reasons
• Any updates of the workbook will re-set the 4 week clock – it means that it buys another 4 week time for owner to improve the workbook