Imported Azure SQL Database is smaller and way slower

Gerrit Koekoek | IT captains 25 Reputation points
2023-11-24T08:03:11.7566667+00:00

Dear reader,

First let me explain the context. I have two tenants. Tenant one had a Azure SQL Database (Basic tier, 2GB, 5 DTUs, LRS). I had to export the database and import it to Tenant two which has the same Azure SQL DB (Basic tier). I used the export functionality in the portal to export the original DB which gave me a 200 MB .bacpac file. I imported this .bacpac to the new tenants Azure SQL DB.

Size original DB:

Used space: 1.52 GB

Allocated space: 1.83 GB

New imported DB:

Used space: 1.1 GB

Allocated space: 1.14 GB

So I already thought the new size difference was a little bit weird. But the application which uses the new DB didn't seem to notice any impact of this weird difference. The old DB contains a 9 million records postal table which runs queries like get streetname for postal code x and housenumber y. The old DB returns the results in a few ms. The new imported DB however takes around 3 minutes! for the same query. I'm not a DB performance expert but I know something went wrong looking at the size difference. Does anyone know what happened? Or does anybody know a fix for this?

Thanks in advance,

Gerrit.

Azure SQL Database
{count} votes

Accepted answer
  1. Alberto Morillo 33,421 Reputation points MVP
    2023-11-24T13:17:18.9233333+00:00

    Probably the original database has a lot of fragmentation. Usually, Azure SQL users do not perform maintenance of indexes. In addition, some methods to maintain indexes could increase their size.

    Make sure the newly created database (from the import feature) has all the indexes that the original database has. Update statistics and defrag indexes with Ola scripts.

    Hope it helps.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Gerrit Koekoek | IT captains 25 Reputation points
    2023-11-28T07:05:57.5933333+00:00

    @Alberto Morillo answer is probally also working but when he mentioned about fragmentation/statistics and defrag indexes I did some searching. I found a working solution for me. First I executed the query "EXEC sp_updatestats;" and after this query I used "ALTER INDEX ALL ON TableName REBUILD;" which successfully made my huge table very fast again. Note: the last query needs quite some space, but I knew this in advance.

    1 person found this answer helpful.
    0 comments No comments