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


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
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
  • I created a table called user statistic with following definition
CREATE STATISTICS [Id_New] ON [dbo].[Example]([Id])
  • Insert some data in the table
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 
  • I created a table called Example with following definition
select * from [Example] where name = 'Topic #:20'
  • 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!

Comments (0)

Skip to main content