Issue when loading the Memory Error Script

@asavioli thank you for reporting this issue and for sharing the raw data file with me offline (coming from a PSSDIAG collection). I was able to reproduce the issue and then examined the SQLNexus.000.log. Here is a snippet that led me to the problem:

SQLNexus Information: 0 : First time encoutering row identifier '-- sys.dm_os_sys_info'
    DateTime=2021-04-20T18:10:44.5427504Z
SQLNexus Information: 0 : First time encoutering row identifier '-- sys.dm_os_memory_objects (total memory by type, >1MB)'
    DateTime=2021-04-20T18:10:44.5583785Z
SQLNexus Information: 0 : First time encoutering row identifier '-- memory_workingset_trimming'
    DateTime=2021-04-20T18:10:44.5780094Z
SQLNexus Information: 0 : Createtable command IF OBJECT_ID ('tbl_workingset_trimming') IS NULL CREATE TABLE [tbl_workingset_trimming] () 

    DateTime=2021-04-20T18:10:44.5780094Z
SQLNexus Information: 0 : RowsetImportEngine Error: An unexpected error has occurred: 

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near ')'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at RowsetImportEngine.TextRowsetImporter.CreateTable()
ClientConnectionId:4e080b08-3c54-4e60-8038-1bc1d120f040
Error Number:102,State:1,Class:15

The issue is clearly happening when SQL Nexus was about to import a data set based on the tag/row identifier '-- memory_workingset_trimming' and is failing on CreateTable() call.

SQL Nexus works by finding one of these tags and right below the tag, it looks for a set of headings underlined with -------- BELOW them. It uses the headings to define the CREATE TABLE statement. For example, if you have something like this


-- customer table
FirstName      LastName      Phone
-------------- -------------- -----------

SQL Nexus will create a table titled "tbl_customer_table" (if TextRowset does not contain a metadata definition) and it will contain columns: FirstName, LastName, Phone. BTW, the count of hyphens determines the length of the column.

In your case, the raw data output you shared with me contained this:


Msg 8115, Level 16, State 2, Server DBPSQLSHRD01\OPT, Procedure sp_mem_stats_general, Line 74
Arithmetic overflow error converting expression to data type int.
-- memory_workingset_trimming

-- sys.dm_os_ring_buffers (RING_BUFFER_RESOURCE_MONITOR and RING_BUFFER_MEMORY_BROKER)

As you can see there is an error logged in the file that caused the -- memory_workingset_trimming output not to be generated. Yes, the tag is there, but the potential column headings and the hyphens below them are missing. This causes the CREATE TABLE statement to contain zero columns and to look like this:

CREATE TABLE [tbl_workingset_trimming] ()

This of course leads to the SQL Server error "Incorrect syntax near ')'. " Try to run the above command in SSMS and you will get the same.

So the issue is with the raw data that SQL Nexus is trying to import. Potentially, there could be a better way to handle the exception here, but if we try to devise a strategy for custom exception handling for every possible exception, first it would be a large effort and second it would possibly slow us down in discovering the underlying issue.

We can file an issue with PSSDIAG for this to be examined further and prevent the arithmetic overflow.

microsoft / SqlNexus

Issue when loading the Memory Error Script #112