SQLNexus and RML Utilities (ReadTrace) - SQL Server效能分析工具
SQLNexus and RML Utilities (ReadTrace) - SQL Server效能分析工具
軟體下載與安裝方法
1.SQL Server Database Engine
SQLNexus and RML需要SQL Server DB來儲存分析結果(可以使用SQL Server Express)
Microsoft SQL Server 2016 Service Pack 2 Express
https://www.microsoft.com/en-us/download/details.aspx?id=56840
2.Report Viewer
不同版本SQL Nexus，需要不同版本的Report Viewer
RML Utilities requires Report Viewer to run correctly and Report Viewer also has a few updates to be considered.
| Report Viewer | ||
| 2008 | Report Viewer 2008系統需求.Net Framework 2.0Microsoft Report Viewer 2008 SP1 Redistributable https://www.microsoft.com/download/en/details.aspx?id=3841Security Update MS09-062https://support.microsoft.com/kb/971119Report Viewer Redistributable 2008 Service Pack 1 GDIPLUS.DLL Security Updatehttps://www.microsoft.com/en-us/download/details.aspx?id=3203 | |
| 2010 | Microsoft Report Viewer 2010 SP1 Redistributable Package https://www.microsoft.com/download/en/details.aspx?id=6610Update fixes several Report Viewer issues after you install Visual Studio 2010 Service Pack 1 (主要描述的就是要安裝下面那個連結) https://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=27231Note 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) |
| 2015Version=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 | SQL Server 2016 Feature Packhttps://www.microsoft.com/en-us/download/details.aspx?id=52676i. Download and install enu\x86\sqlsysclrtypes.msiii. Download and install enu\x86\reportviewer.msi | 若沒有安裝正確會出現以下錯誤訊息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 the Replay Markup Language (RML) Utilities for SQL Server
https://support.microsoft.com/kb/944837
How to obtain the RML Utilities for SQL Server
The following files are available for download from the Microsoft Download Center:
The RML Utilities for SQL Server, x86 version
Download the RMLSetup_X86.msi package now.
The RML Utilities for SQL Server, x64 version
Download the RMLSetup_AMD64.msi package now.
只能安裝x86或x64其中一種版本，如果已經安裝x86版本，再安裝x64時則會跳出已經安裝過x86，所以無法繼續安裝
以Windows 2016, SQL 2016 Express為例，我安裝RMLSetup_AMD64.exe的x64版本
4.Install SQL Nexus
Microsoft SQL Nexus
https://github.com/Microsoft/SqlNexus/releases
Installation Steps
1.Download the latest binary files from Releases
2.Unzip the file to a local directory
3.Run sqlnexus.exe to start SQL Nexus
Note: We don't recommend this tool to point to your production SQL Server.
第一次啟動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，選擇log DB，匯入PSSDiag收集後的output folder
選擇PSSDiag output folder，如果要覆蓋上次匯入到目前選擇的DB，則可以勾選Drop Current DB Before Importing
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顯示報表
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...
https://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
https://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
https://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