Counting 1 million rows in SQL server takes 1 minute [closed]

Question

I have a very simple table:

• ID (integer primary key, ID increment on)
• field1 (large text field about 4KB per record)
• field2 (large text field about 4KB per record)

I have about 1 million rows in this table.

I tried a very simple SELECT COUNT(*) FROM table, no filters, no where clause. This query takes about 1 minute to execute.

Is this normal? I don't think 1 million records is very much for a database.

How can I figure out what is taking so long?

EDIT: one strange thing I noticed is when I use SELECT TOP(10) * FROM table, the returned result is NOT ordered by ID. That's strange to me because normally these queries should be ordered by the primary key, since it should be the default/disk ordering of the records. Not sure if this is a symptom of the problem or not

EDIT2: here is the database table in question as requested by one comment:

USE [bfcn_keys]
GO

/****** Object:  Table [dbo].[Keypairs]    Script Date: 03/05/2022 19:49:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Keypairs](
    [public] [nvarchar](max) NOT NULL,
    [private] [nvarchar](max) NOT NULL,
    [nonce] [decimal](20, 0) NOT NULL,
    [Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Keypairs] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

This table is generated by EF core, nothing special I did.

Database is a SQL Server on AWS t3.medium. Nothing else running on it.

(This is for load testing, and not production btw; I'm not storing private keys on the server)

Answer 1

1. If your intent is to get the total row count as fast as possible, you could try going with sp_spaceused or by querying sys.dm_db_partition_stats dmv.
2. Order of the results is not guaranteed unless exclusively specified, even if your select query has only ID column. More details can be found here.
3. You have mentioned using t3.medium type of instance which supports a max IOPS of 11,800, I would check IO stats in cloud watch (readOps and writeOps in particular) for the EBS volumes where your data, log and tempdb files are located to see if the disk is getting throttled. Similar metrics can also be found in windows perfmon.