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

Imports failing - bad table definition. #66

Closed JohnP-AWS closed 4 years ago

JohnP-AWS commented 6 years ago

The Nexus log, during import, shows: Createtable command IF OBJECT_ID ('tbl_TopN_QueryPlanStats') IS NULL CREATE TABLE [tbl_TopN_QueryPlanStats] (...(hoondehoon, borkborkbork) [stmt_text] VarChar(8192)...

Error is: System.Data.SqlClient.SqlException (0x80131904): The size (8192) given to the column 'stmt_text' exceeds the maximum allowed for any data type (8000).

This is reading the text files - (servername)__SQL Server Perf Stats Snapshot_startup.OUT (servername)__SQL Server Perf Stats_Startup.OUT (and, of course, ...snapshot_shutdown.OUT

JohnP-AWS commented 6 years ago

Side note: I downloaded the "release" - 6.0.0.8.

Since a table definition is a typo-style error, I looked through the files to see if I could find 8192. I couldn't; so I kept searching.

Since I get the error above on tbl_TopN_QueryPlanStats; I looked for that. I found it in: https://github.com/Microsoft/SqlNexus/blob/master/RowsetImportEngine/TextRowsets.xml

The error was on column stmt_text - but when the table shows up in TextRowsets.xml, it doesn't even have a column stmt_text.

Okay: I'm not a .Net guru by any stretch! But there's something non-obvious that went wrong here. Is it possible that the "release" copy of SQL Nexus that I downloaded here isn't the current release with fixes?

prmadhes-msft commented 4 years ago

@JohnP-AWS , just to repro the issue at our end ,could you please share the log which you tried to import and facing the issue.

PiJoCoder commented 4 years ago

Based on some preliminary research, the table definition (specifically column lengths for this issue) is dynamically built based on column length of the data. There is a comment that states that truncation can occur in RowsetImportEngine.VarCharColumn , but I don't see a truncation taking place here:

data = (string)Convert.ToString(columndata);

Perhaps, this is exactly where we should truncate down to 8000 characters. I think Pradeep also found another workaround. We will investigate further and see what we find

PiJoCoder commented 4 years ago

We did not find a way to reproduce the original issue reported here and cannot understand how exactly it was encountered. But we resolved the immediate issue by making the stmt_text column a varchar(max) as well as resolved a couple of related bugs that got exposed in the process.