search

microsoft / SqlNexus

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
MIT License
337 stars 95 forks source link

Error running reports #348

Closed micky3102 closed 2 months ago

micky3102 commented 2 months ago

I've downloaded and extracted SQLNexus_7.24.02.18. I used the PowerShell script SetupSQLNexusPrereq.ps1 to check and install the prerequisites. When I run SQLDiag on the database host with both ProfilerCollector enabled="true" and ProfilerCollector enabled="false", import the files (import is successful with no errors), trying to use report SQL Perf Main / Perfmon Summary I get an error about a missing database object. When I inspect the database sure enough, it's not there. I'm just wondering if I missed a step with the set-up or is this a bug? I can't seem to find too many hits about "Invalid object name 'dbo.tbl_ServerProperties'". In the following error stack after tbl_ServerProperties it mentions ... A data source instance has not been supplied for the data source DataSet_CPU. The default database sqlnexus was created and I can see a lot of other objects, any help much appreciated.

SQL Nexus - Report Issues.docx

Thanks

Michael

PiJoCoder commented 2 months ago

Hello @micky3102. Thanks for your interest in SQL Nexus. The problem you describe points to a missing table: dbo.tbl_ServerProperties as you pointed out. And the logs for that table are gathered if you use SQL LogScout https://github.com/microsoft/SQL_LogScout/releases/tag/v6.24.02.18 (using Basic and many other scenarios) or PSSDIAG (which calls SQLDiag is not in active development but still used). The idea is that SQL Nexus needs this table at a minimum as a reference to many reports.

The needed data comes from the --ServerProperty-- rowset

In SQL_LogScout this is the file https://github.com/microsoft/SQL_LogScout/blob/ac483ee181d2840f4f84917140dabc3b53dd0552/SQL%20LogScout/Bin/MiscDiagInfo.sql#L145

In PSSDIAG this is the file https://github.com/microsoft/DiagManager/blob/21f07c4a1c6936b9211352d3653086c3ee5a3c28/DiagManager/CustomDiagnostics/SQL%20Base/MiscPssdiagInfo.sql#L131

Bottom line, you need the output that will be imported in that table.

So best option is to collect the logs on your SQL host by using SQL LogScout and then the tbl_ServerProperties table will be created for you. If you need performance logs gathered (Xevent in this case), you can use either the GeneralPerf or DetailedPerf scenario. There's a LightPerf scenario also, but it doesn't gather Xevent. Sorry long answer, hope it helps.