I am trying to figure out the reason for a behavior that is not as I expect. I am running some tests on an isolated Azure database Standard-series (Gen5) with 8 cores. I have created +20 million records in a table called [ActivityLog] and I want to start testing my scenarios. To begin with, I have tried to see how many rows exactly I have created so I ran the command below:
select count(*) from ActivityLog with (nolock)
It took 7:41 minutes for the query to return 22,991,048 rows and the Data IO percentage (Max) jumped to 25% on SSMS.
I ran the same query at the same time on both Azure query editor and SSMS and the Data IO percentage (Max) jumped to 100% and stayed there for about 3 minutes.
I don't understand this. First isn't count something that SQL would keep in statistics?
Then should it take 7 minutes to count the primary indexes?
The script to create my table:
CREATE TABLE [dbo].[ActivityLogs](
[TenantId] [varchar](25) NOT NULL,
[Id] [bigint] NOT NULL,
[CreatedAt] [datetime2](7) NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[EntityType] [nvarchar](100) NOT NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[ContextData] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_ActivityLogs] PRIMARY KEY CLUSTERED
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I ran this query containing a field (EntityType) that I don't have any index on. It still ran the same execution plan and took the same time. I don't understand why.
select count(*) ,EntityType
from ActivityLogs with (nolock)
group by EntityType