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.
[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]
[code language="SQL"]
CREATE STATISTICS [Id_New] ON [dbo].[Example]([Id])
[/code]
[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]
[code language="SQL"]
select * from [Example] where name = 'Topic #:20'
[/code]
Enjoy!
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!
Updated Mar 14, 2019
Version 2.0Jose_Manuel_Jurado
Microsoft
Joined November 29, 2018
Azure Database Support Blog
Follow this blog board to get notified when there's new activity