Analyze Enterprise Vault Dtraces using PowerShell-SQL tool.

This is for Enterprise Vault Engineers, however slight modification on this tool can help guys those are working on other technologies as well. The most difficult work for a support engineer is to analyze performance issue, where thousands of traces collected over time. In past, I developed a tool (combination of Power Shell & SQL script) that can upload Enterprise Vault dtrace to SQL server then assist on most common use cases such as finding out delay in specific function, searching a given keyword OR extracting entire thread.

You can either download executable file OR use PowerShell script on SQL 2016 server. Download link

Prerequisite

  • Microsoft SQL 2016 installed on Windows 2012 R2.
  • This works with windows authentication so logged in user must have DB creator OR equivalent permission on SQL instance.
  • The tool can be executed on server where SQL server binary is installed. For example, you can run it on SQL1 and connect to SQL2. Both servers should have SQL installed.
  • Copy latest dtrace_analyser exe file to SQL server.
  • Upload & Analyse, both are different operation. For example, you can upload dtrace today and analyse it tomorrow.

Upload D-trace

Double click on dtrace_analyser.exe, a windows form and console (command prompt) will open. Windows form enable user to upload /analyze the data. Console shows the status of operation it executing in background. During upload/analyze, form will free and all options will be disable till execution completes.

Just place all the dtrace in a single directory then supply name of that directory.

First.jpg

Click on Upload Traces, copy dtrace folder location OR click on Browse to select the location.

If the Database (DtraceReview) and Table (DtraceContent) already exists in SQL then it will skip the creation of database/table but truncate existing set of upload dtrace for consistency and accuracy during analysis.

Upload speed depends on resources. Approximately 10-15 seconds will take to upload single 100 MB dtrace file when SQL server have 12 GB RAM & 8 CPU.

Analyze Traces

Click on Find Delay option, by default the location of output HTML file will be C:\temp\dtrace-YYYYMMDDMMSS.html, you can change the location by clicking on Browse option or just type the location in text bar.

second.jpg

This tool automatically review each thread and prepare HTML report. If the numbers of lines in thread is huge, form may go to Not Responding mode, leave it running (will fix in next version) however actual work can be seen from console window.

Third.jpg

Once processing finish, HTML file will automatically open. Delay in seconds can be seen in last column. Any thread where delay is more than 2 second will only be visible in HTML output.

Fourth.jpg

Please note, this tool isn’t intelligent to understand the lines written for d-trace logging. Few function are expected to have delay such as one below. Engineers should understand the limitation because some function completes when their sub function completes.

Fifth.jpg

All process id, process name and thread id can be seen in HTML report.

Six.jpg

Last section of HTML will show events captured across all d-trace files.

Seven.jpg

Use Search Keyword option to search specific function, exception OR line. By default output will be located in C:\temp\search_output_YYYYMMDDHHMMSS.txt. You can change location and file name by clicking on Browse OR manually type the location.

Eight.jpg

Use Extract thread option to download all lines of specific thread to text file. By default output will save in text file located in C:\temp\Dtrace_thread_ThreadID.txt. You can change location and file name by clicking on Browse OR manually type the location.

Ninth.jpg