Heavy query with 2-Abort in profiler

Mkan1512

Hi,

During a (re)search for a performance issue using the SQL Server Profiler we found (large) query's with an Error '2-Abort'.
Sometimes the reads are enormous (>200.000) and a duration of +73000
We see this query every time we open a sale/purchase order (or create a new) but not when we open a sale invoice (same table)

As a test, I removed almost all fields from page 42 (only No. and address info are still there) then we do not have the issue.
When I have only the No. field and "No. of Archived versions" (flowfield) we have the issue.


The start of the query is "SELECT TOP (50) ISNULL("Sales Header"."timestamp",0x0000000000000000) AS "timestamp",ISNULL("Sales Header"."Document Type",0) AS "Document Type",ISNULL("Sales Header"."No_",N'') AS "No_", ..."
It looks like that all the fields from table 36 are included.

Then there are several (7 for the sales order) OUTER APPLY (SELECT TOP(1) ... with an AS "SUB$No_ of Archived Versions" (for example)

Setup
The server (2012R2) is running on VMWare.
SQL (2012) & NAV (2013) are on the same server,
separate disk for Data/Log/TempDB
192Gb mem (174 for SQL)
Stats update every night


1. Error 2-Abort means that the client (not SQL) time-out. Why do we see this error (which time-out) ? How to fix
2. Is this query coming from the middle tier (I cannot find any code which is related) and is this normal behavior ?

vaprog

I would guess the query comes from a factbox. Finding the offender probably will help you figuring out, why it does not perform well.

Mkan1512

Thanks for answering, but no it is not coming from a factbox.
I have an page (as test) with only the No. field and "No. of Archived versions"

skiddoo

"No. of Archived Versions" is a FlowField (right?) this also causes these OUTER APPLY statements.

There should be a "Disable Smart SQL" option in the NST settings. Try again with disabled Smart SQL.

Explaination: With SmartSql enabled the NST is trying to combine all FactBoxes and FlowFields in one single SQL Statement. So the NST is calling the SQL server only once instead calling it for each FlowField/FactBox.
But sometimes these queries are not so smart and the SQL server is not using the proper index and doing a table scan instead.

David_Singleton

At first glance this really looks like an issue with Statistics, so I would focus on the nightly Statistics update and check that it is doing a FULL SCAN.