workaround for SQLNexus fail to import SQL 2016 trc file (SQLNexus ReadTrace匯入sql 2016 trc失敗)

workaround for SQLNexus fail to import SQL 2016 trc file
(SQLNexus ReadTrace匯入sql 2016 trc失敗)

Problem:

Readtrace (SQL Profiler TRC Files) Import failed.

Analysis:

from ReadTrace.log

07/23/18 00:32:50.485 [0X00001364] The major version number (13) in the trace file header is not a supported file version.
07/23/18 00:32:50.485 [0X00001364] ERROR: Read of file header for file C:\Temp\20180712110000\SQLSERVER-1_SQLDIAG__sp_trace.trc failed with operating system error 0x8007000D (The data is invalid)
07/23/18 00:32:50.519 [0X00001364] *** ERROR: Attempt to initialize trace file reader failed with operating system error 0x8007000D (The data is invalid)
07/23/18 00:32:50.519 [0X00001364] Reads completed - Global Error Status 0xfffffffe
07/23/18 00:32:50.520 [0X00001364] Signaling worker threads to complete final actions.

Possible Cause原因:

目前的版本RML ReadTrace無法匯入SQL Server 2016 trc

Workaround:

執行以下Powershell，將F:\Temp\20180707192503目錄下的trc檔變更版本號碼，改成舊版SQL Server

# 目前的版本RML ReadTrace無法匯入SQL Server 2016 trc
# Workaround: Change the version in trc file

Write-Output "Change the version in trc file"
Get-Date

# The version information we want to write: 0x0A = 10 = SQLServer 2008
[Byte[]] $versionData = 0x0A
# The offset of the version information in the file
$offset = 390

$OutputPath = "F:\Temp\20180707192503"
Get-ChildItem $OutputPath -Filter *.trc| 
Foreach-Object {
 $trc = "$OutputPath\$_"
 [System.IO.FileMode] $open = [System.IO.FileMode]::OpenOrCreate
 $stream = New-Object System.IO.FileStream -ArgumentList $trc, $open
 $stream.Seek($offset, [System.IO.SeekOrigin]::Begin);
 $stream.Write($versionData, 0, $versionData.Length);
 $stream.Close()

Write-Output "Change Done $trc"
}

SQL Nexus and RML Utilities (ReadTrace) – SQL Server效能分析工具

SQL Nexus and RML Utilities (ReadTrace) – SQL Server效能分析工具

update 2022/3/16

軟體下載與安裝方法

---

官方步驟

Prerequisites to run SQL Nexus

1. .NET framework 4.6.2. Windows 10 anniversary update has version already.
2. Download and install SQLSysClrTypes
3. Download and install ReportViewer control (ReportViewer.msi)
4. Download and install RML Utilities (RMLSetup_AMD64.msi)
5. An instance of SQL Server (2012 or above) to connect to and process data
6. Optional: PowerBI Desktop

Automate Prerequisites Installation

You can use the following PowerShell file (https://github.com/microsoft/SqlNexus/blob/master/Setup-Related/SetupSQLNexusPrereq.ps1) to install the three required and one optional prerequisites for SQL Nexus

How to Download and Use

1. Download the SQLNexus_7.22.03.23_Signed.zip from Assets below. This is a zip file that contains Microsoft digitally-signed binaries
2. Extract in a path location of your choice
3. Go to the path and run sqlnexus.exe

安裝設定步驟

1.SQL Server Database Engine (官方步驟的第5點)

SQLNexus and RML需要SQL Server DB來儲存分析結果，可以使用SQL Server Express版本

Microsoft® SQL Server® 2019 Express

https://www.microsoft.com/en-us/Download/details.aspx?id=101064

2.Report Viewer 

不同版本SQL Nexus與RML Utilities，需要不同版本的Report Viewer

RML Utilities requires Report Viewer to run correctly and Report Viewer also has a few updates to be considered. 

Report Viewer	Download
2008	Report Viewer 2008系統需求.Net Framework 2.0 Microsoft Report Viewer 2008 SP1 Redistributable http://www.microsoft.com/download/en/details.aspx?id=3841 Security Update MS09-062 http://support.microsoft.com/kb/971119 Report Viewer Redistributable 2008 Service Pack 1 GDIPLUS.DLL Security Update https://www.microsoft.com/en-us/download/details.aspx?id=3203
2010	Microsoft Report Viewer 2010 SP1 Redistributable Package http://www.microsoft.com/download/en/details.aspx?id=6610 Update fixes several Report Viewer issues after you install Visual Studio 2010 Service Pack 1 (主要描述的就是要安裝下面那個連結) http://support.microsoft.com/kb/2549864  Update for Microsoft Visual Studio 2010 Service Pack 1 Report Viewer (KB2549864) https://www.microsoft.com/en-us/download/details.aspx?id=27231 Note If you have Microsoft Report Viewer 2010 SP1 Redistributable Package installed, install the file that is named "ReportViewer.exe." If you use Microsoft Visual Studio 2010 Service Pack 1, install the file that is named "VS10SP1-KB2549864-x86.exe."  遇到以下錯誤，需安裝Report Viewer 2010 SP1 Redistributable Package SQL Nexus的Reports有超連結，但是無法點選開啟報表，需安裝Report Viewer 2010 SP1 update(KB2549864)
2015 Version=12.x.x.x	MICROSOFT REPORT VIEWER 2015 RUNTIME (12.0.2402.15) https://www.microsoft.com/en-us/download/details.aspx?id=45496
Version=13.0.0.0	Microsoft® SQL Server® 2016 Service Pack 3 Feature Pack https://www.microsoft.com/en-us/download/details.aspx?id=103444 i. Download and install enu\x86\sqlsysclrtypes.msi  若x86與x64都下載了，在Windows 2016環境兩個版本都能安裝成功 Microsoft® System CLR Types for Microsoft SQL Server® 2016 SP3 The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server. Note: This component also requires Windows Installer 4.5 Filename: X86 and x64 Package (SQLSysClrTypes.msi) ii. Download and install enu\x86\reportviewer.msi 安裝完成後的版本資訊 為 13.3.6300.2 SQLNexus github提供的下載連結reportviewer版本是13.2.5026.0(比較舊)  必須先裝sqlsysclrtypes.msi，不然安裝ReportViewer時會出現以下錯誤 若sqlsysclrtypes.msi與reportviewer.msi沒有安裝正確，執行SQLNexus會出現以下錯誤訊息 If you run sqlnexus.exe, Error "Could not load file or assembly 'Microsoft.ReportViewer.Common, Version=13.0.0.0,'"

3. Install RML Utilities (ReadTrace)

Description of Replay Markup Language Utilities for SQL Server

https://docs.microsoft.com/en-us/troubleshoot/sql/tools/replay-markup-language-utility

Obtain the RML Utilities for SQL Server

After you install the Database Experimentation Assistant, you will find the RML tools (ReadTrace and OStress) present in the C:\Program Files (x86)\Microsoft Corporation\Database Experimentation Assistant\Dependencies\X64\ folder.

If you're using the RML tools together with the SQL Nexus tool, you can obtain ReadTrace and ostress from https://github.com/microsoft/SqlNexus/releases/tag/09.04.0097.

RML Utilities for SQL Server is available for download from the Microsoft Download Center.

如果沒有透過Database Experimentation Assistant或SQLNexus來取得RML Utilities，直接從Download center下載安裝時，只能安裝x86或x64其中一種版本，如果已經安裝x86版本，再安裝x64時則會跳出已經安裝過x86，所以無法繼續安裝

我的範例環境與安裝順序是

Windows Server 2016 Datacenter (Azure VM Image)

1.安裝SQL Server 2019 Developer Edition與SSMS

2.無須安裝 .Net framework 4.6.2  (ndp462-kb3151802-web.exe) 

若安裝會跳出 .NET Framework 4.6.2 or a later update is already installed on this computer.

3.安裝 Microsoft System CLR Types for SQL Server 2016 (SQLSysClrTypes.msi)

4.安裝 Microsoft Report Viewer for SQL Server 2016 (ReportViewer.msi)

5.安裝 RML Utilities for SQL Server (X64) (RMLSetup_AMD64.exe)

4.SQL Nexus
Microsoft SQL Nexus

https://github.com/microsoft/SqlNexus

https://github.com/Microsoft/SqlNexus/releases

Prerequisites to run SQL Nexus

1. .NET framework 4.6.2. Windows 10 anniversary update has version already.
2. Download and install SQLSysClrTypes
3. Download and install ReportViewer control (ReportViewer.msi)
4. Download and install RML Utilities (RMLSetup_AMD64.msi)
5. An instance of SQL Server (2012 or above) to connect to and process data
6. Optional: PowerBI Desktop

PS.RML Utilities for SQL Server安裝完成後在功能清單會出現以下項目

Automate Prerequisites Installation

You can use the following PowerShell file (https://github.com/microsoft/SqlNexus/blob/master/Setup-Related/SetupSQLNexusPrereq.ps1) to install the three required and one optional prerequisites for SQL Nexus

How to Use

1. Download the SQLNexus_v_7_21_07_07.EXE (this is a digitally signed self-extracting zip file by Microsoft)
2. Double-click on it to allow files to be extracted in a path location of your choice
3. You will now see a SQLNexus_v_7_21_03_26.zip file which you can extract into a subfolder, e.g. \SQLNexus_v_7_21_03_26
4. Go to the new subfolder and execute sqlnexus.exe

第一次啟動SQL Nexus，會提示要連接到哪一個SQL Server Instance，連進去後會建立一個空的sqlnexus資料庫，此時若在UI介面上不改選其他資料庫，匯入分析log時(例如PSSDiag output folder)就會匯入此資料庫。

5.分析方法

(1)事先建立好分析用的log DB或用SQL Nexus建立log DB

(2)匯入log

A.如果用PSSDiag收集log

啟動SQL Nexus後，先選擇要匯入的資料庫或點選New Database建立新資料庫

點選 Import 

選擇PSSDiag收集後的output folder，如果要覆蓋上次匯入到目前選擇的DB，則可以勾選Drop Current DB Before Importing

PS.此選項是在Import之前執行Drop Database語法，並且用預設Create Database語法重新建立DB，也就是說原始DB如果有設定多個File group與多個data file，重建後會只有一個file group與一個mdf檔

匯入進行中

  

會也跳出匯入的狀態與紀錄

PS. TRC檔很大，則需要恢復比較多時間來匯入

B.如果只有收集SQL Trace (trc檔)

使用SQL Nexus，選擇log DB，匯入trc檔

或

用命令列匯入
Cd C:\Program Files\Microsoft Corporation\RMLUtils
ReadTrace.exe -S"(local)" -E -d"ReadTraceDB20161020213501" -I"D:\TEMP\SQLTraceFiles\2016-10-20_21-35-01\TPECDB08_20161020212501.trc" -o"D:\TEMP\SQLTraceFiles\2016-10-20_21-35-01\output"

修改參數
-d後面的資料庫名稱
-l要匯入第一個trc檔
-o指定一個執行匯入過程的紀錄檔目錄

匯入Import完成後自動啟動Reporter.exe顯示報表

FAQ:

Import時若出現以下警告: You have captured both trace and xevent files. import will fail!. Please remove one of them before importing.

經測試Import可能會成功，但是SQLNexus report可能會出問題，然後SQL Perf Main頁面可能會變成空白，必須重選DB，觸發重新載入資料，SQL Perf Main頁面才會恢復正常

2.PerfMon Charts出現錯誤

Invalid object name 'pal_Counters'

原因:

SQLNexus_v_7_21_07_07 有bug

解決方法:

改下載SQLNexus_7.22.03.23_Signed.zip版本，此版PerfMon Charts改名為Perfmon Summary，即可正常顯示

3.Active Trace and XEvent Report出現以下錯誤

Invalid object name 'tbl_XEvents'

暫時還沒有找到解決方法

Reference:

Cumulative Update 1 to the RML Utilities for Microsoft SQL Server Released
https://blogs.msdn.microsoft.com/psssql/2008/11/12/cumulative-update-1-to-the-rml-utilities-for-microsoft-sql-server-released/

Microsoft SQL Nexus

https://github.com/Microsoft/SqlNexus/releases

Precision Performance FOR MICROSOFT SQL SERVER...
http://blogs.technet.com/b/patricg/archive/2008/01/11/precision-performance-for-microsoft-sql-server.aspx
PRB: RML Utilities - ReadTrace processing fails with "Attempt to use an invalid variant type
http://blogs.msdn.com/b/psssql/archive/2009/02/11/prb-rml-utilities-readtrace-processing-fails-with-attempt-to-use-an-invalid-variant-type.aspx
SQL Nexus and RML Utilities: A primer on the issues that I have dealt with last year
http://blogs.msdn.com/b/sqlserverfaq/archive/2011/02/07/sql-nexus-and-rml-utilities-a-primer-on-the-issues-that-i-have-dealt-with-last-year.aspx

SQL Server: Working with SQL Nexus

https://social.technet.microsoft.com/wiki/contents/articles/51981.sql-server-working-with-sql-nexus.aspx

What is ECMAScript ES (什麼是ECMAScript ES3,ES5)

What is ECMAScript ES
(什麼是ECMAScript ES3,ES5)

1995年 Netscape Navigator網景的布蘭登·艾克開發的一種腳本語言的標準化規範；最初命名為Mocha，後來改名為LiveScript，一年後改名為JavaScript (PS.Javascript跟Java完全無關)

ECMAScript 網景在1996將Javascript提交給ECMA International歐洲電腦製造商協會，進行標準化，確認出這版語言標準

ECMAScript 2

ECMAScript 3 (ES3) 1999年發布後就長達10年沒有變動，中間是個各大瀏覽器廠商進行自己的語言延伸

ECMAScript 4 由於關於語言的複雜性出現分歧，第4版本被放棄，其中的部分成為了第5版本及Harmony的基礎

ECMAScript 5 (ES5) 2009年，此時大部分開發者仍然寫的ES3風格的javascript

有一說法是2012年因為推動停止舊版IE支援，開發者才開始大量使用ES5
ES 5之前的協定稱為ES.Harmony後來又改為ECMAScript 2015（ES2015）


ECMAScript
https://en.wikipedia.org/wiki/ECMAScript

Use the Maintenance Plan Wizard (使用維護計畫精靈)

Use the Maintenance Plan Wizard
(使用維護計畫精靈)

1.使用SSMS連線到SQL Server，在SQL Server Instance>管理>維護計畫，按下右鍵，選擇[維護計畫精靈]

2.設定排程，建議在離峰時間執行，例如01:00，最少可以每周執行一次。

3.選擇需要定期執行的工作

如果您選擇了[重建索引]，就不需要勾選[更新統計資料]，因為REBUILD(重建索引)會一併更新統計資料

4.針對上一個步驟所選擇的項目，勾選您的資料庫

以下是[重建索引工作]

以下是[更新統計資料工作]

  

Reference:

Use the Maintenance Plan Wizard (使用維護計畫精靈)
https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/use-the-maintenance-plan-wizard?view=sql-server-2017

Frequently Asked Questions for PSSDiag Collection (PSSDiag收集常見問題)

Frequently Asked Questions for PSSDiag Collection
(PSSDiag收集常見問題)