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)

...where 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 COUNT.


The clustered index is the entire table, so it has to read that into memory. Using a narrower non-clustered index will consume less data and return faster.

However, if all you want is to count rows, use the system views. Here are two articles that go into more detail (the code sample below is from the first post).

DECLARE @TableName sysname
SET @TableName = 'bigTransactionHistory'

SELECT TBL.object_id, TBL.name, SUM(PART.rows) AS rows
FROM sys.tables TBL
INNER JOIN sys.partitions PART ON TBL.object_id = PART.object_id
INNER JOIN sys.indexes IDX ON PART.object_id = IDX.object_id
AND PART.index_id = IDX.index_id
WHERE TBL.name = @TableName
AND IDX.index_id < 2
GROUP BY TBL.object_id, TBL.name;

Answer left in comments by Aaron Bertrand

On a cold winter morning, older cars will take longer to get going - the oil has to warm up, the windshield needs time to defrost, etc. If you drive to work and then 10 minutes later need to drive to the store, startup time is significantly less.

This is the same with SQL Server. The first time you ask for a 13GB table, it has to first read that data from disk and into memory, and then it can read the data that's in memory. If you run the same query again, SQL Server doesn't have to do the first part, because the data is already in memory.

In the absence of a suitable index (e.g. one that is guaranteed to always have a row for every row in the base table), SQL Server will be forced to read the entire clustered index, which includes all of the columns in the table.

In the absence of a clustered index, SQL Server will be forced to read the entire table, which also includes all of the columns. If the problem you are solving is "I want to get a row count, and for it to always be quick" then use the DMVs as Randolph suggested.

If what you are solving is "13GB from spinny disk should always be fast"...in that case, yes, a non-clustered index will be faster than a clustered index (because it contains less data), but the time it takes to load into memory will still be slower the first time.

Also note that the default is for max LOB columns to be stored in-row wherever possible. Storing the LOB data off row via sp_tableoption would also make scanning the clustered index faster, since it would be so much narrower. You would need to rebuild the table or update every LOB value to actually move the LOB data off row. See the linked documentation for more information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.