·
2 min read

Optional Additional Installation Script for Blocking Analysis with DynamicsPerf v2.0

In relation to the analysis scripts that come with DynamicsPerf (Performance Analyzer) v2.0, (based on the new extended events being used in place of the SQL profiler trace which featured in v1.20), i.e.:

…

—            BLOCKING_EVENTS

— Blocking events sorted by TIME desc

—————————————————————-

SELECT TOP 100 *

FROM (SELECT event_data.value(‘(event/@name)[1]’, ‘varchar(50)’) AS EVENT_NAME,

DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), event_data.value(‘(/event/@timestamp)[1]’, ‘datetime2’)) AS END_TIME,

…

As I discovered from experience, if you are dealing with large volumes of blocking events, you may find this time consuming as the blocked process report for each event needs to be drilled into.

So I wrote a SQL script for my own purposes, which installs the following additional objects on DynamicsPerf to facilitate faster analysis and decided to share in case it helps others:

  • Table: BLOCKED_PROCESS_INFO_EXTENDED_SETUP – stores the date/time the stored procedure below last ran. This is used to insert a delta of any new blocking data next time it runs.
  • Table: BLOCKED_PROCESS_INFO_EXTENDED – stores the result of the stored procedure below.
  • Stored procedure: SP_POPULATE_BLOCKED_PROCESS_INFO_EXTENDED – queries the extended events (XEL) files and shreds the xml from the blocked process reports and stores the result in the above table. So the first time it runs, it will take just as long, however once the table is populated, the result can be quickly reused.
  • I have also altered the stored procedure SP_PURGEBLOCKS to enable the above BLOCKED_PROCESS_INFO_EXTENDED to be purged as well.

It should be run after completing all of the usual deployment/installation steps covered in the Performance Analyser 2.0 deployment guide.

There is then an analysis script to use with those new objects.

You can find these two scripts attached, with a few caveats:

  1. These scripts are my own, so not part of the official DynamicsPerf package and comes with the usual disclaimer, below. They are based on v2.0 RCO and the features for blocking analysis with v2.0 may well change over time, meaning the scripts here may also be subject to change or could become obsolete at a later date. Therefore, it’s particularly important in this case to understand how they work.
  2. These scripts are designed for use on a separate non production environment by copying the DynamicsPerf over from Production, i.e. using backup/restore for the database and copying the *.xel files from the default location of C:\SQLTRACE, then storing them locally in non-production also under C:\SQLTRACE. Some scripts can take time to run depending on what information has been captured.
  3. As with anything it should be tested on a non-production environment first, however as per point 2, it should not be necessary to run this script on Production at all.

**** Disclaimer****

Microsoft provides programming examples for illustration only, without warranty either expressed or implied,
including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This blog post assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.

Blocked_Process_Info_Extended_Scripts

GlenTurnbull

Related posts