This project is read-only.

Analysis scripts fail

Dec 14, 2016 at 3:37 PM
My SQL Nexus version installation almost works. I can get reports, but not the ReadTraces reports. When I open a ReadTrace report I get the following error:
System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'ReadTrace.spReporter_BatchAggScaleFactor'.
at System.Data.SqlClient.SqlConnection.OnError ...
c:\jacklidocs3\cprsql\main\src\sqlnexus_pass\CodePlexerHelper\SQLNexus.Codeplex\sqlnexus\fmNexus.cs:line 1360
Error Number:2812,State:62,Class:16
And the following is an excerpt from the sqlnexus.000.log file, this is the first error of a long list of errors. All errors are about objects not being available in the database. However, on inspection the objects named are available in the database.
SQLNexus Information: 0 : [sqlnexus.fmImport.RunScript] Executing PerfStatsAnalysis.sql...
SQLNexus Information: 0 : [sqlnexus.fmImport.RunScript] db name = sqlnexus
SQLNexus Error: 0 : [sqlnexus.fmImport.RunScript] Changed database context to 'sqlnexus'.
Current database: sqlnexus
Changed database context to 'sqlnexus'.
Current database: sqlnexus
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'tbl_REQUESTS'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean >breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject >stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand >cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, >TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, >Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
1 >completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NexusInterfaces.CSql.ExecuteBatches(String[] batches) in >c:\jacklidocs3\cprsql\main\src\sqlnexus_pass\CodePlexHelper\SQLNexus.Codeplex\NexusInterf>aces\csql.cs:line 103
Error Number:208,State:1,Class:16
Many of the errors are logged twice, but not all. The errors are either "Cannot find the object" or "Invalide object name". There are also errors "Cannot insert duplicate key row in object 'dbo.tbl_Reports' with unique index 'IX_tbl_Reports_ReportName'" and "Violation of UNQUE KEY constraint", to name a few. The log file is 115724 lines long.

Since this question is asked before on June 11, 2015 with the previous version of SQL Nexus I don't think I am alone with this error, but given that many people seem to successfully use the tool I also think this must be something in the configuration. So, what am I doing wrong or what goes wrong? Any pointer is appreciated.
Dec 19, 2016 at 2:16 PM
Edited Dec 19, 2016 at 8:38 PM
These seem to be two separate issues: 1) the store procedure ReadTrace.spReporter_BatchAggScaleFactor does not exist, 2) PerfStatAnalysis.sql fails because all kinds of objects already exist.
Since without the second one, you will never get to the first one, I started with the second one. Even though I selected the option Drop Database... this error occurs. It does not seem that the database is actually dropped, I am not even sure that the tables are truncated. So, after inserting a gazillion if exists object_id(...) drop ... this script completes successfully (whether it really works, I don't know yet.)
The first seems to indicate that the ReadTrace tool never worked. On closer inspection this may be true because I don't have any trc files in my output. I thought I had selected for tracefile output in psdiag configuration, so where is that output?
I looked into the output folder and found SERVERNAME__ProfilerTraces_Startup.OUT which referred to an (old) trace file. I added that file to the output folder and did the import in sqlnexus again. Now there is an TRC import error. The ReadTrace log now says that the trace file does not contain any valid data points.
Indeed the traces have never been made. I now looked through the logs in the internal folder of the PSDiag output folder. The log files are riddled with errors. That, while PSDiag just happily said everything was honky dory. The log files show that the trace file could not be created due to permission errors, the counters could not be installed, etc. etc. etc. It even contains errors about filever.exe an executable from W2000 and W2003; I am using W2008R2!
Dec 19, 2016 at 9:35 PM
It turns out it is not caused by configuration or installation: user error. Thanks to someone within Microsoft, who was able to show how to use the tools within ten minutes.

PSSDiag configuration manager creates a that must be copied to the server under test. This means that you should not use pssdiag.cmd that is in the Build folder. The Build folder is an intermediate folder, it is not the end result. When you use pssdiag.cmd from the file, then all information is properly collected and the error when opening the readtrace report and the script errors found in the log files will not happen.

To use PSSDiag, do as follows.
  1. Run PSSDiag Configuration Manager
  2. Configure as needed and click Save
  3. Do not look in or at the Build folder
  4. Open the Customer folder
  5. Copy the file to the server where the data must be collected.
  6. Unpack the file
  7. Run the unpacked pssdiag.cmd
  8. Run any load or whatever against your server
  9. Once the load is finished, in the pssdiag consile press CTRL+C on the keyboard
  10. Once everything stopped, copy the output folder to the computer where you want to use SQLNexus to do the analysis
Marked as answer by albert_z on 12/19/2016 at 1:35 PM