I'm testing SQL Server 2017 performance. I've create a table like the following one:
CREATE TABLE [dbo].[AssessmentResponses]( [ResponseID] [int] IDENTITY(1,1) NOT NULL, [AssessmentID] [int] NOT NULL, [Respondent] [nvarchar](50) NULL, [Date] [datetime] NULL, [JsonSchema] [nvarchar](max) NULL)
ResponseID is a primary key supported by a clustered index.
I haven't added any other index.
Then I've loaded it with 3M of rows, the size of the table has reached 13GB, the index space 50MB (from the table properties).
SQL Server is running on my laptop (I7 with 16GB of RAM, SSD but DB files are located on an internal 7.2K SATA drive)
If I run the query
SELECT COUNT(0) FROM [AssessmentResponses]
...it takes around 2 minutes the first time, then once it has loaded the whole table in memory, it takes 1s or less.
I'm wondering if this is the right behaviour or I'm missing something in the configuration, because in production, the table could be unloaded from memory for inactivity or something else, that means that if a user will come it will take again several seconds to get a response.
Is the described behaviour the right one?
I was expecting that for a simple
COUNT, it doesn't have to load all the data in memory, maybe the data in the clustered index (50MB) could be enough to perform a