5

I have a table with around 8 million rows with a schema of:

CREATE TABLE [dbo].[Documents](
    [Id] [uniqueidentifier] NOT NULL,
    [RemoteId] [int] NOT NULL,
    [Json] [nvarchar](max) NULL,
    [WasSuccessful] [bit] NOT NULL,
    [StatusCode] [int] NULL,
    [Created] [datetime2](7) NULL,
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Created]  DEFAULT (getdate()) FOR [Created]
GO

The json documents are of a structure of

{
    "Id": 1,
    "Data": [
        {
            "Id": 99,
            "Name": "Person 1"
        },
        {
            "Id": 100,
            "Name": "Person 2"
        }
    ]
}

I am attempting to extract the JSON array from within the document and insert it into a new table using the following query:

;WITH CTE (Json) AS 
(
    SELECT TOP 10
        JSON_QUERY([Json], '$.Data')
    FROM 
        Documents
    WHERE 
        ISJSON([Json]) > 0
)
INSERT INTO [dbo].[ParsedDocuments] (Id, Name)
SELECT
    JSON_VALUE([Value], '$.Id') AS [Id],
    JSON_VALUE([Value], '$.Name') AS [Name],
FROM 
    CTE
CROSS APPLY 
    OPENJSON([Json]) as X

What I'm finding is that if I extract and query a sample of the data, say 1000 rows. Everything works as expected and the data is inserted into the destination table. However, when I query the main table the server appears to just hang and become unresponsive.

I suspect that it is attempting to run a cross apply across all the rows before inserting data. Is there any way to improve performance? Or allow the job to begin "streaming" results into the destination table rather than attempting to batch them up?

Finally, as you can see I am employing the use of "TOP 10" results. I am however still experiencing the performance hanging. And I am unsure as to why.

Query plans for large table are never generated how when running against a sample of the data the query plan can be found here.

7

The fact that you cannot obtain even an estimated execution plan for the query against the large table suggests that compilation is waiting on statistics creation (or update) for the nvarchar(max) column.

When running against the smaller sample table, statistics creation completes quickly, so an execution plan is quick to create.

I can reproduce your situation on a low-powered (S0) Azure SQL Database. On a slightly less anaemic S3 instance, the compilation still takes around 30 seconds due to the large amount of I/O involved in reading the nvarchar(max) column to generate statistics. The premium tier is needed to get good I/O performance - even a cold P1 can get these statistics created in 3 seconds or so.

You can confirm the cause by temporarily disabling automatic statistics creation:

ALTER DATABASE CURRENT SET AUTO_CREATE_STATISTICS OFF;

The SQL server will then produce a plan quickly, albeit with warnings about missing statistics:

plan with no stats

Be sure to re-enable this feature after testing because good statistics are vital to general execution plan quality. Or commit to creating necessary statistics manually.

If the issue is instead automatic refresh of stale statistics, you could choose to have these updated asynchronously:

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Alternatively, scale your Azure SQL Database up to a performance level that suits your workload and budget.

2

Try OPENJSON. This ran 2-3 times faster in a cursory performance test I ran.

INSERT INTO [dbo].[ParsedDocuments] (Id, Name)
SELECT
       JsonId
     , JsonName
FROM Documents
CROSS APPLY OPENJSON(Json, '$.Data')
WITH (   
      JsonId int '$.Id'  
    , JsonName varchar(30) '$.Name'  
 )
 WHERE 
    ISJSON([Json]) = 1;;
1

Azure is not hanging, I think you reached your DTU limit usage when you run that query.

You can check the DTU usage on the Monitoring chart for that particular DB. more info how to check it here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-monitor

If you reach your DTU usage limit just for this query, you can change your service tier just to run that query and then return to the previous tier, or implement elastic pooling.

More info about the available DTUs here

https://azure.microsoft.com/en-us/pricing/details/sql-database/single/

  • Thanks, man, but I don't think that's the problem. I've run large queries like this before they simply need to be left for a while and eventually they will complete. How usually I can see the results streaming through the system. In this case something weird is happening with cross apply which despite having a limit like top 10 appears to cause it to hang with no results processed for an extended period of time. – Darren Jul 7 '18 at 22:02
  • you can also try, if possible, run the query on premises and then just upload the results... – Eduardo Pivaral Jul 7 '18 at 22:14
  • Wish I could, but this needs to run on a schedule. I'll leave this up for a bit and see if anyone has a fancy query hint or something. – Darren Jul 7 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.