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
356 stars 101 forks source link

Perfmon CPU and Memory Reports failing with date time cast issue #83

Closed sureshpalla closed 4 years ago

sureshpalla commented 4 years ago

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

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.SqlDataReader.TryHasMoreRows(Boolean& moreRows)

at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)

at System.Data.SqlClient.SqlDataReader.Read()

at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)

at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)

at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)

at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at sqlnexus.fmNexus.FixupDataSources(String filename, String reportname, ReportDataSourceCollection datasources, ReportParameterInfoCollection paramc) in C:\Users\jackli\Documents\GitHub\Sqlnexus\sqlnexus\fmNexus.cs:line 1362

ClientConnectionId:fd4216c6-6c33-4c41-9997-72d1ff2a2dd5

Error Number:241,State:1,Class:16

OK

Microsoft.Reporting.WinForms.MissingDataSourceException: A data source instance has not been supplied for the data source 'DataSetCPU'.

at Microsoft.Reporting.WinForms.ReportViewer.RenderReportWithNewParameters(Int32 pageNumber, PostRenderArgs postRenderArgs)

OK

prmadhes-msft commented 4 years ago

@sureshpalla , Please share the repro steps and required files which you process and facing issues.

PiJoCoder commented 4 years ago

Investigation:

Hello, we did some investigation and found a SQLNexus database that showed us the issue. We discovered that this is an issue with the underlying data in the CounterData table. If you run the following query, you can reproduce the conversion failure

select cast (CounterDateTime as datetime ) CounterDateTime, CounterDateTime CounterData

Strangely, if you look at the data it seems valid date/time format and if you copy and paste a value from the CounterDateTime column and run select cast('2020-03-03 15:57:39.357' as datetime) it works just fine. Digging further we found that the last character after .357 is 0. And when we inserted a new value into the table using insert into counterdata values ('2020-12-12 15:57:39.357') we see that the last character is 32 (a space). This query shows the last character select ASCII(substring(CounterDateTime,24,1)) LastCharacter ,CounterDateTime from counterdata

Root Cause:

Update: The issue is with Relog.exe which imports the DateTime data incorrectly We attempted to reproduce this by collecting a PSSDIAG and importing via SQLNexus, but we are getting the CPU and Memory reports to work fine. We are not sure how the SQLNexus database that we found had those values inserted in there. The tool used to import data in CounterData is Relog and we are not able to understand how Relog will insert that 0 character.

Update: Found that others have run into this issue and as suspected, the issue was with Relog - see here. We will do our best to report to the Windows group who maintains Relog.exe

Resolution:

For now the solution is to manually update your CounterData table by replacing the last (24th) character with a space - ASCII = 32

update counterdata set CounterDateTime = substring(CounterDateTime, 1,23) + char(32) from counterdata

PiJoCoder commented 4 years ago

We will close this for now. Please reopen if you can provide us steps to reproduce.