none
Azure SQL - Spatial RRS feed

  • Question

  • Hi Team,

    We are currently in the middle of migrating our on premise SQL Server databases to Azure SQL.  Our current Azure SQL version is 12.0.2000.8.

    A majority of our data is spatial and is stored using the geometry data type as in NZ we are using a meter based New Zealand Transverse Mercator projection.

    We are at the final stage of our migration that includes the creation of spatial indexes.  I have hit a road block that spatial indexes are taking an insanely long period of time to create.  A number of the large ones are still running overnight whereas on premise they take an hour or so.

    Why are they taking so long?

    Our data generally has between 1000000 to 3000000 million polygons of varying sizes.

    Is there any solution?

    The syntax I am using at the moment is:

    CREATE SPATIAL INDEX mlc_block_sidx ON administrative.mlc_block   (    shape    ) 
    USING  GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =(1142899.098, 4749517.971, 2496129.370, 6192909.515)
    ,    CELLS_PER_OBJECT = 128
    , PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    On premise this index creating takes approx 30 seconds

    • Edited by Shane Turk Monday, September 30, 2019 8:48 PM
    Monday, September 30, 2019 8:39 PM

All replies

  • Hi Shane,

    Can you provide a little bit more information with regards to the current Service Tier (Azure Database), Version Information, and Compatibility Level between your on-premise environment and your Azure SQL Database instance. This information will help with isolating SQL Engine from service tier issues.

    I am looking for the output from:

    @@Version - Transact SQL Configuration Functions (link)

    You can run:

    @@VERSION

    ALTER DATABASE (Transact-SQL) Compatibility Level (link) where you are likely running 120 on-premise and 140 or 150 in your Azure SQL Database instance.

    You can run the following to identify what is running at each:

    SELECT compatibility_level FROM [sys].[databases] WHERE [name] = 'Your Database Name';

    And the following to change the Compatibility level:

    ALTER DATABASE database_name   
    SET COMPATIBILITY_LEVEL =  120;

    As for the deployment type your Azure SQL Database is deployed as, there are the DTU and vCore based options and then Basic, Standard, and Premium service tiers within each of the deployment types. This information is helpful to understand if underlying storage is part of the issue. 

    Understanding this issue will be very helpful.

    Tuesday, October 1, 2019 7:13 PM
    Moderator
  • Hi there,

    select @@version

    --Microsoft SQL Azure (RTM) - 12.0.2000.8   Aug 27 2019 17:56:41   Copyright (C) 2019 Microsoft Corporation 

    SELECT compatibility_level
    FROM   [sys].[databases]
    WHERE  [name] = 'knowledgehub-working';

    --140

    database maximum storage size 250GB

    current database size = 34.7GB

    subscription = Azure Enterprise

    pricing tier = Elastic Pool

    4 VCores selected

    I have a dataset with a geometry spatial data type of 70000 rows in a New Zealand metre based projection.  NZ Transverse Mercator EPSG 2193.  When a run a spatial index creation it just hangs whereas any other index creation or SQL update/insert/delete statement responds at levels for an On prem database.

    If there is anything else I can supply let me know.

    turks@tpk.govt.nz

    Shane Turk

    Monday, October 7, 2019 9:11 AM
  • Also,

    We have three databases all sharing the same Elastic Pool.

    Here is the T-SQL statement for creating the index.

    CREATE SPATIAL INDEX land_use_capability_sidx ON [environment].[land_use_capability]    (     shape     ) USING  GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =(1089951.840, 4823266.040, 2091997.730, 6194203.080),     CELLS_PER_OBJECT = 64, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    I am also using an integer identity column for the PK.


    • Edited by Shane Turk Monday, October 7, 2019 6:44 PM
    Monday, October 7, 2019 9:24 AM
  • Thank you for providing the additional detail. In looking through the CREATE SPATIAL INDEX (Transact-SQL) documentation, everything with the T-SQL looks correct except for the ON [PRIMARY] part. 


    CREATE SPATIAL INDEX land_use_capability_sidx

    ON [environment].[land_use_capability](     shape     )

    USING  GEOMETRY_AUTO_GRID

    WITH (

    BOUNDING_BOX =(1089951.840, 4823266.040, 2091997.730, 6194203.080),
    CELLS_PER_OBJECT = 64,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)

    ON [PRIMARY]



    Without your dataset, I can not identify if this is potentially the issue. Is this the exact query run on-premise?


    Wednesday, October 9, 2019 9:44 PM
    Moderator
  • Yes.  What would be the easiest way to get you the data?

    I will rerun without the ON [PRIMARY]

    Thursday, October 10, 2019 1:32 AM
  • It Still Hangs
    Thursday, October 10, 2019 2:29 AM
  • when the query is run on our On prem SQL Server it takes 1min 55secs.  On Azure SQL it just hangs.
    Thursday, October 10, 2019 2:30 AM
  • Hi Shane,

    If you do not have an Azure Support Plan, can you please send me your Azure Subscription ID and I can have a one-time support request to have this specific issue investigated. Please send this to me at AzCommunity.

    I will return next steps to engage Azure Support.

    Regards,

    Mike

    Thursday, October 10, 2019 4:42 PM
    Moderator
  • Hi Shane,

    Thank you for providing the requested information and having a Support Request created. I do have a question. Are the values being passed into BOUNDING_BOX Longitude/Latitude values or are they some other metric? If they are Longitude and Latitude, can you try using GEOGRAPHY_AUTO_GRID? This is just a test, as I want to add this information to the internal ticket I am filing.

    Thank you!

    Thursday, October 10, 2019 9:35 PM
    Moderator
  • Wanted to update this thread with the CREATE SPATIAL INDEX T-SQL that achieved the desired result and where 

    SORT_IN_TEMPDB = ON (was set to OFF)

    was the specific recommendation made by the PG. Additional investigation is being made into the elastic pool service tier.

    Feedback from the customer:

    When I ran the two index creation statements below, they ALL WORKED TO A PERFORMANCE LEVEL THAT I EXPECTED FROM SQL AZURE. The spatial query which will form part of our maintenance and population for use in Power BI worked as well.

     

    CREATE SPATIAL INDEX land_use_capability_sidx ON [environment].[land_use_capability]    (     shape     )

    USING  GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =(1089951.840, 4823266.040, 2091997.730, 6194203.080)

    , CELLS_PER_OBJECT = 16

    , PAD_INDEX = OFF

    , STATISTICS_NORECOMPUTE = OFF

    , SORT_IN_TEMPDB = ON

    , DROP_EXISTING = OFF

    , ONLINE = OFF

    , ALLOW_ROW_LOCKS = ON

    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

     

    CREATE SPATIAL INDEX mlc_block_sidx ON administrative.mlc_block  (   shape   )

    USING  GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =(1142899.098, 4749517.971, 2496129.370, 6192909.515)

    , CELLS_PER_OBJECT = 16

    , PAD_INDEX = OFF

    , STATISTICS_NORECOMPUTE = OFF

    , SORT_IN_TEMPDB = ON

    , DROP_EXISTING = OFF

    , ONLINE = OFF

    , ALLOW_ROW_LOCKS = ON

    , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

     

    select mlc.block_id, mlc.block_name, luc.LUC1C

    from administrative.mlc_block mlc, environment.land_use_capability luc

    where mlc.shape.STIntersects(luc.shape) = 1

    Friday, October 18, 2019 12:59 AM
    Moderator