Lesson Learned #57: Bacpac export process doesn't include the automatic statistics

[Jose_Manuel_Jurado]

First published on MSDN on Jan 15, 2019
Hello Team,

Today, I worked on a performance case that our customer claimed about a performance issue with a copied database from other one. The source database was working as they expected but the new database didn't.

Our customer mentioned that the database are identical.

Working on this issue I found one item that represents the copied database hasn't the same objects.

• When you copy the database using CREATE DATABASE .. AS COPY OF .. or Copy option from the portal. Comparing the statistics, tables, the objects are the same but, using the export/import option using BacPac we found that the automatic statistics created by SQL Engine by itself is not included in the bacpac. So, in this situation:


• I created a database called JMExample


• I created a table called Example with following definition

[code language="SQL"]
CREATE TABLE [dbo].[Example](
[Id] [int] NOT NULL,
[Name] [varchar](200) NULL,
CONSTRAINT [PK_Example] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
[/code]

• I created a table called user statistic with following definition

[code language="SQL"]
CREATE STATISTICS [Id_New] ON [dbo].[Example]([Id])
[/code]

• Insert some data in the table

[code language="SQL"]
declare @Values AS INT = 0

WHILE @Values<=40000
BEGIN
SET @Values=@Values+1
INSERT INTO [dbo].[Example] VALUES(@VALUES, 'Topic #:' + convert(varchar(20),@values))
END
[/code]

• I created a table called Example with following definition

[code language="SQL"]
select * from [Example] where name = 'Topic #:20'
[/code]

• As you could see after the execution of the select query we have a new automatic statistic created by SQL Engine

• But importing the data this automatic statistic has not been created. Even in the bacpac has not been included.

Enjoy!